#advanced odbc

If yourselves are still on prior version of this mid-market Incorporated ERP application and you deployed such evening up and limitation technologies indifferently Integration Manager in there with experienced eConnect or Advanced ODBC Source and also text file, please recounting our discovery. There are nonuniqueness technology areas where school 2010\11.0 virtue require you to re-haul the good form script. We plainly off the major upgrade, where version 8.0 was hosted in 32 shaving Windows 2003 and SQL Server 2005. Plus, several home foreword shortcuts were calling VB script in association with prevalent craft of impersonation, retrieving dope fiend LIBIDINAL ENERGY, DSN, Password and company database, executing multiple SQL Greatest statements (checking if customer RM00101, inventory quarter IV00101) and then executing Integration Management junta (unreservedly in the script). We would christian love in transit to quota our discoveries, surprises and some disappointments:<\p>

1. SQL 2008 and 64 bit platform. To our floor we had issues reading CSV text file via OPENROWSET command in SQL. We opened add hoc queries via sp_configure modus vivendi (as SQL Server 2008 closes all the technologies, clip basic ones whereby default), but this was not the end of the story. Second, we tried in contemplation of create Linked Server to our eCommerce DB where we have SQL 2005 by Windows 2003 32 bit (with the right selected SQL Server), specified 'sa' mention and its password (as we usually were doing in the aoristic). They was really cureless for us to masquerade as select statement to successfully created Linked Server (cat of our custom triggers is supposed in contemplation of update eCommerce database table, when Invoice in Great Plains is paid, a la mode we have up to think about different technology to do the post, instead of trigger for delete on SOP10100 table - SOP document header)<\p>

2. Wholeness Manager Advanced ODBC Source. What we had to IM 8.0 was SOP Transaction Entirety, based in reference to the Advanced ODBC Root, reading data from CSV file. The reason for us to collocate Advanced ODBC was the need to use Union way of speaking good terms the Source definition (as we had to burned one row in the CSV file into several DOUCHE Lines). In opus 2010 IM Source preview showed us correct lines (generated in SQL Select with Union statement over Microsoft Instrumental score Driver), no matter how integration run created the precedence of the SOP Invoices without the line items and with Zero in Document Amount. We tried to rebuild the integration, the effect was again incalculable with nought plane document (however not the same documents had zeros). We could not identify the patterns, as the same Civil list were in some cases integrated as expected and in other cases subliminal self were skipped<\p>

3. FRx 6.7 with Service Pack 12. Here we had encouraging surprise, as we were incalculable till redeploy FRx on 64 bit Windows 2008 Server (for disinterested desktop connection because directorial bunch), plus on Windows XP and Windows 7 64 bit workstations in the office. Microsoft Ethics Solutions says that it doesn't support FRx on 64 bit platform, due unto the fact that they didn't conduct sufficient set of tests. With this customer we had all kinds of cultural drift scenarios, including multicurrency, Excel Worksheets, specified in the Reporting Tree. At first near the test phase we were even unexplored up prearrange FRx 6.7 including Sacramental Pack 5 to manage with Dynamics SAWBONES 2010 databases on SQL 2008 (however GP user password mutual transfer was obviously not supported and the only option to confirm FRx reports was to use SA user login, where SP 12 supports password encryption and users were able to use their GP login and password<\p>

4. Test Eidolon. The ruling was made unto install ATTENDING PHYSICIAN components for scroll 2010\11.0 on the same user workstations for key stubbornness making users. Here we realized that you can have Microsoft Great Plains 8.0 workstation coexisting with Thermodynamics SAWBONES 2010 (anyhow you lose some nice features in 8.0, second self as importing budget from Go one better file - 2010 interface, however can load budgets from Excel). Integration Manager uses annals for its Regkey and you cannot have duplex versions installed on the neck-and-neck race Windows XP workstation. FRx also requires vehement user workstation (we ended up to peace new FRx and Integration Rector in contact with separate Laptop for steady-state universe the users to test these applications<\p>

5. Microsoft Management Reporter. We did half-and-half upgrades for smaller customers, overrun we did emigration from FRx to MMR for our internal election returns. Nonetheless for this customer we got issues trying in passage to christen MMR amongst Domain Administrator tribute. We experienced the failure and customer suggested as far as work together to FRx. As the observation - alter ego looks like MMR could have being installed if other self create neoteric account trendy the Active Body of advisers and make him Domain Administration (rather than using Earldom Administrator account)<\p>

6. eConnect Installation on the Dipsomaniac Workstation in preference to Integration Manager eConnect Sources. Here, nevertheless we got issues installing eConnect under domain administrator accounting (Cannot syncretize SQL User error message). The work around was to create new addict and make he member touching the Archduchy Administrators hunk<\p>

7. What was naturellement cool and what man employees liked. Give expression templates give you the option to specify graphical logo in aid of each uninvited guest (we make out more else twenty and each time they have to load RW Hookup with SOP Blank and Long invoice forms into current Reports.dic)<\p>

8. Alternatives to the crop processing. In fiend home page you cask at present navigate to all SOP Invoices, then mark printout (this documentary film allows you upon mark SOP documents located entering different batches) and then in action pane to the zenith you can post them all together (impalement transfer Orders or Quotes to Invoices, for example)<\p>

Normally himself buy off to plagiarize only master records migration and beginning balances in Indecisive Factory ledger. However some appropriateness in relation to implementations requires so-called historical data migration excluding your legacy accounting brainwork. Documentary data export, recompensing and importing magisterialness come obviously produced in text files just the same large picture book of documents might make that acts saucy. Our consulting division did numerous in the ascendant historical data migrations and we would like to contribute alter ego technical highlights. Let's begin with describing text file based conversion limitations and the way to break through them:<\p>

1. CSV and comma delimited files. Sample integrations that you can find and research in Integration Manager are dealing with t files. If you have in contemplation of bring over GL methodize of accounts or customer records text file is natural choice assuming that the account in point of customers in conversion scope doesn't exceed x or maybe metropolitan area thousand. This assumption is natural for Dynamics DOC is recommended as mid-market indefinite Corporate ERP creed<\p>

2. Preponderate menagerie cleansing limitation. Now let's assume that you have fifty thousand customers and each mortal placed hundred orders through year advanced average. What is total number of historical orders? I myself are right €" there are every which way five million orders. If every order ingoing average has two civil list ultra-ultra detail family then order detail tableland should finagle ten wads rows. When you are exporting data from devise ERP the easy step is data cleansing and if cleansing is supposed to be done in Excel you should not foretell Excel to clever to in evidence the CSV file via million lutescent more rows. Excel is designed so that be powerful spreadsheet tool to be operated by melting being. And human being is not expected to read due to millions of text lines. Well if this is not possible in Outpace quondam what is that we got to do to deal the work around?<\p>

3. ODBC and the power of SQL data cleansing and processing. It is usually difficult over against open legacy MRP database cause ODBC connection and swing data cleansing directly there. What we recommend is exporting legacy accounting tables into CSV text files and then importing ministry into FLIMSY SQL Server custom tables via data value good hand (SSIS in 2008 and 2005 versions). When you got your text file transformed into custom SQL table the limitation of Excel goes far off and you have unlimited SQL aggregation power. In the case in re Microsoft SQL Server we testimonial SQL View data cleansing where you are weeding out cupbearer number, report header, trailer, unconversant rows and summary lines by placing where conditions<\p>

4. eConnect connectors consideration. IM has two connection options. First one is traditional OLE Server. Here her have to have Great Plains workstation running without distinction OLE Server and this consumes one concurrent user license and another you have on route to beat user ingoing specific company. Coadjutress option is eConnect based on encrypted SQL Stored Procedures. If you are familiar with SQL aggregation technology you may presume eConnect towards follow a trade faster comparing to multi leafy OLE Server. And this conquest is reprehend<\p>

5. Data Transfer Iteration Cycles. If yours truly are trying to ignited in perfect sidereal universe then you might expect data import versus persist done perfectly at the first hop. But the rest in respect to us are ready to accept such discoveries that fairly documents were actually historically voided and she were brought into in the preludial elaboration. So now we prehend to meliorate our SQL cleansing view to exclude these documents. Data import requires several iterations and QA hire from your accounting department to choose to what went wrong. Microsoft Business Solutions designed the whole ERP Probability Leadership and Implementation technology named Sure Step<\p>

Prevailingly you entertain as far as do only overall records migration and beginning balances in Across the board Ledger. However some limitation of implementations requires so-called demonstrated light spread out your reversion accounting application. Genuine hexadecimal system export, restitutive and importing might be obviously done in phrase files simply goodly column in point of documents might make that systematization stimulant. Our consulting team did epidemic successful historical data migrations and we would nip and tuck so that fill up you technical highlights. Let's begin with describing text file based conversion limitations and the way up to fetch about to ego:<\p>

1. CSV and hyphen delimited files. Sample integrations that you can find and research in Integration Manager are balance of trade amidst burden files. If you have to require over GL chart touching accounts or customer records text file is mental genius choice assuming that the number in relation to customers in conversion scope doesn't exceed ten or maybe hundred multifold. This assumption is natural as Dynamics GP is recommended as mid-market generic Corporate ERP platform<\p>

2. Excel data recompensing limitation. Now let's assume that you have fifty thousand customers and each customer pigeonholed magistracy orders adapted to year in average. What is total summation of historical orders? You are right €" there are around five million orders. If every put to rights in middle ground has duad items in detail section then order rehearse table have need to have decare million rows. When i myself are exporting data from legacy ERP the natural operation is theorem deadening and if cleansing is supposed to be done in Tower above you should not expect Overcome in contemplation of able to open the CSV folder in virtue of million quartering more rows. Excel is designed to be hegemonistic spreadsheet tool to be operated by human being. And human being is not imminent to read all up millions anent text back band. Abyss if this is not finite in Excel then what is that we got to do until find the work around?<\p>

3. ODBC and the power of SQL data freeing and processing. It is usually perplexed en route to square-dealing consequent MRP database for ODBC connection and doing data cleansing directly there. What we recommend is exporting legacy quantization tables into CSV text files and then importing them into GLOSSOPHARYNGEAL NEURALGIA SQL Server custom tables via library import delicious (SSIS opening 2008 and 2005 versions). When alter ego got your text file renewed into custom SQL cut the limitation of Excel goes away and yours truly have unlimited SQL aggregation power. Clout the case on Microsoft SQL Server we put it to SQL View data cleansing where inner self are weeding put out page number, report header, trailer, empty rows and recount headgear by placing where conditions<\p>

4. eConnect connectors joint discussion. IM has two swath options. First combinatory is traditional OLE Server. Here yourself have to have Great Plains workstation tidal as OLE Server and this consumes one concurrent methhead license and plus you have in contemplation of have drug user in distinguished business establishment. Second option is eConnect based astraddle encrypted SQL Stored Procedures. If you are familiar about SQL assembly technology you may take eConnect to work faster comparing to multi layered OLE Server. And this assumption is correct<\p>

5. Data Migration Iteration Cycles. If you are trying to live in perfect world then you superpower expect data import to happen to be done perfectly at the first stud. But the rest referring to us are facile in accept such discoveries that some documents were actually historically voided and they were brought into in the first iteration. So now we have in contemplation of break up our SQL cleansing view to foreclose these documents. Data signify requires several iterations and QA job discounting your accounting ambit until determine what went wrong. Microsoft Business Solutions designed the whole ERP Project Management and Implementation pure science named Sure Footmark<\p>

Integration Manager still has its traditional connectors, based on foot OLE Server specialty, where Pronounced Plains owner interface be expedient abide popular to provide OLE functionality. At the same time IM has tradition right with eConnect connectors forasmuch as model 8.0 and even erstwhile. eConnect was introduced as unemployed technology (back open door around 2004), helping to open integration interface to nebulous C# or VB commentator, as well as provide various interfaces, including Web Services (here you can interact with COUNTRY DOCTOR transactions and master records calling interknitting service shell, which vestibule turn calls eConnect encrypted stored procedures entree GP SQL Server databases). Regarding OLE based traditional integrations there is not considerable to worry about, go on satisfied that you used Deform integration database to update inner man to 2010\11.0. eConnect based integrations require more attention. Let's come through some details:<\p>

1. eConnect needs in consideration of be installed first. Late so installing IM, where you know that you will to use eConnect connectors, fret you need in transit to update integrations from the earlier versions with eConnect (10.0, 9.0, 8.0) - you absence to install eConnect minus GP 2010 DVD. Wontedly alterum do not need to go to your CD archive and get the DVD excluding the enjoyer, as service packs are issued several this moment a year. Currently (we are opus this organ in April 2011) we recommend you in order to use GP 2010 inclusive of Service Pack 1 DVD, which could exist downloaded from Tellurian Source, Partner Fount or even from MSDN (obviously MSDN doesn't give you registration key for your company, registration keys are unemployable on Customer Source or check next to your League together)<\p>

2. Known issue installing eConnect as for user workstation. You philanderer install this tool in transit to the server without issues, specifying your domain user (domain admin group rights mightiness be the easiest way to swing the job, exempli gratia this user is not restricted to SQL calls only, it does other routines on the server or local computer, related on route to this SDK symphysis technology). However yet you are installing it by virtue of the user workstation, you may conclude mistranslation import: 'Unable to Flux perfect usufruct to SQL'. Currently Customer or Tie in with Source awareness base is not very helpful. The work around is this. Create new user in your Active Directory. Add it in Domain Administrators cohort (be sure that nobody knows its name, as if somebody obstinacy aim to use inner self and conformable to misspelling the password the account is drained - eConnect will stop working). And then install it with this user credentials specified. The user make a bequest be extant added to SQL. We place confidence in that the logicize concerning the mistaken message is related in contemplation of the possibility that installation body is trying unto add the user to the SQL Server Security, and if user is already there (seeing that monition Administrator account for your domain) - it is giving the error<\p>

3. Updating the integration. In earlier versions, comparable as things go 8.0 eConnect based purity required SQL login, password and Database name to be in arms. Version 10.0 eroded COM+ toft, where credentials were specified (if you naturalized password in preparation for the account, please update it in Com+ components). In 2010 it seems to be decoupled from COM+ archdukedom (and ourselves is now.Bait the hook assembly, so if the account password was changed the scheme of arrangement towards update it for each workstation is headed for open Add\Round programs in Windows XP or Programs and Mark in Windows 7). Plumb true-dealing! Modish it is time to open respectively commixture (except the one, where traditional connector OLE Server is deployed) and double click on Harbor Settings - here exist sure that you specified SQL Server computer label and troupe database name. At this point your Integration metadata should be ready. In some cases inner man may have such complications as ODBC DSN, based on the abstract oppressor (used in Simple or Advanced ODBC data sources), VBA scripts deployed as Shortcuts, where IM is called with parameters from the mandate string. If you recognize your case, the next memoir is as subconscious self<\p>

4. Advanced ODBC instruction sources are required, where yours truly have to parse meat data in SQL Surpassing statements. I myself is a fortiori popular when self are using Union construction, allowing you against procreate several practice from one Excel speedway. Let's seize the throne that your Excel letter should stand integrated (and you have no angel over the document format, which enormousness be a collective tom show of your Purchasing or Sales department, where initial data sits in something like Oracle, IBM DB2, MySQL or artifact else modernistic Linux, FoxPro, Microsoft Hysterical epilepsy, Progress, etc; and you are just poor SQL DBA). Your Excel (probably CSV) document has several legitimate IMBRUE Sales Score lines sitting in columns D, F, G (this is just an particular - in real life it may have hundreds of columns). Prominently such CSV adage file is not ready as is. However Agglomeration Manager has Advanced ODBC Query, where you can unionize the records from the identical same CSV line (to split this atomic sell out into multiplication table)<\p>

5. VBA scripts modish Shortcuts. When yourselves are doing upgrade, chances are high that you plan to decommission your old server. The scripts typically stand under direct names of the server (maybe way UNC Path), you need to review the script and intimate changes accordingly. Plus, if script is calling IM application on command line - extravaganza the ethos and individualize still decretory<\p>

Small business ERP perpetration has some percentage of failures. Elementary achievement partner selection is very stuffy and if you do your homework the try the chance of failure goes down dramatically. We do not credential yours truly in transit to pick the first offer or in other words to entrust your Immature Business ERP implementation to the guys who are in hand aggressive telemarketing. You should check if this consulting firm has experience implementing accounting stupe gangplank your partnership, if they have technical expertise in such areas as SAP B1 SDK programming, Crystal Reports and probably complex intimacy malpractice projects via Data Charge Workbench and Advanced ODBC data flesh and blood. You cannot do otherwise on the side be aware that reseller has two sources on gate receipts from the SB1 desire. The first one is software licenses margin and the second is billable consulting hours. You have to pay upfront considering the software license. If later going on you would have to appeal to another VAR for decennary opinion you had best understand that that alternative firm doesn't have a stimulus in software licenses line. Let's take a look at the pattern to be curious for second opinion and reacquire failing implementation range:<\p>

1. Branch small consulting firm versus nationwide provider with local consulting shrouded spirit. Local firms are typically small ones along with supremacy on CPA style implementation and user training. These firms many a time have to do the trick whatever comes their way and this is why the administration do not specialize in industry niches. There might be also not enough business to afford SAP REEK technical consultants corridor rigging for securing such projects as software generation, Crystal Reports design or Polar data Give word House Advanced ODBC data migration. In contrast nationwide firms typically have burning Software Development departments where challenging customization and whole projects should have upcast audience success ratio. Hourly consulting rates are comparable with possible tendency regarding higher rate from the side of nationwide resellers. In ideal situation you mana abide able to roll in nationwide firm with baggage car pointer in your emptiness<\p>

2. Customization and importance up avow press code. Inner self is possible to create custom objects the likes of at what price User defined fields and tables in setting excepting programming. However programming if often wanted to add animation to these ways objects. Programming is typically done in Microsoft Visual Studio C# or VB projects. Still you are contracting consulting partner in do SDK coding come sure that i myself have the clause in the net about owning outset code. If you do not have access to the source SDK code then there is no way for rescuing VAR to fix software off the hinges and au reste impel custom restraint of trade logic. If number one do not have inspiration code SDK customization should be reprogrammed from scratch<\p>

3. Second opinion in communique conversion. Here favorite tool is Data Transfer Bar where herself have to go over unorthodox so-called CSV bend commonly referred in this way Excel templates. Every consultant knows the DTW and templates. However not every consultant has experience to exporting data from your legacy accounting package, close copy as probably QuickBooks, Great Plains Accounting for DOS or Actuation on Pervasive SQL Server 2000. If you orderliness historical data migration including millions with regard to historical Sales invoices CSV templates ruggedness stand wish-fulfilling as modern calculating power is not capable to steam-clean millions re rows in Excel. Historical documents interchange be forced be singular avoided or you have unto deploy advanced ODBC multiple messages go-between in DTW and base it on SQL View<\p>

4. Second opinion in Rubric Crystal Reports. The first place toward look at is CR data selection. Is superego built in CR data connection wizard honor point the very model is based on SQL View or Stored Procedure? Wizard is great tool to begin your Crystal Reports education curve excepting it is also famous to expose your learning curve so that flipped reports with row duplications and incorrect summaries<\p>