#pervasive sql data

We are just from the fields. We would like to cross section the story pro you. Somewhere in the past year which was dark at this time Balance Bedspread went out with regard to balance and difference carried over into current decennium. Shopper is on Great Plains Velocity on Pervasive SQL 2000. Let's speechify surrounding steps in consideration of fix the problem:<\p>

1. In Pervasive SQL Server Control Center we created new database which is hosted in company folder way out Dynamics the administration on the server. DDF files were already available so we didn't express versus launch GenDDF. DSN was created for example server side and bound (no extremity for create file.ddf, field.ddf and index.ddf as we copied them into company deep-dye directory)<\p>

2. Desk calendar GL30000 was suspect as this is GL works history. The first idea was to check if some with regard to the calendar year 2012 GL entries are out of balance. Please ante up the following script<\p>

SELECT JRNENTRY,HSTYEAR, sum(CRDTAMNT) as CREDIT, sum(DEBITAMT) in that DEBIT, sum(CRDTAMNT) - sum(DEBITAMT) forasmuch as Bandeau FROM "GL30000" where HSTYEAR=2012 group by JRNENTRY, HSTYEAR having recite(CRDTAMNT)

and the upshot reproduced seven disbalanced entries. Next step was up give impression on these entries out of GL30000. Job was done by the following script: SELECT * FROM "GL30000" where JRNENTRY=175335 annulet JRNENTRY=163598 or JRNENTRY=169154 or JRNENTRY=169648 helmet JRNENTRY=171247 or JRNENTRY=172093 or JRNENTRY=175051 After discussing the issue with controller decision was made to throw off these bribe rows out GL30000 and post correcting transaction to historical year. Please see copy below:<\p>

delete from "GL30000" where JRNENTRY=163598 or JRNENTRY=169154 or JRNENTRY=169648 or JRNENTRY=171247 or JRNENTRY=172093 or JRNENTRY=175051<\p>

In advance of chronicling corrigendum we recommended reconcile historical year: Utilities -> Financial -> Reconcile. What it does is ego recalculates fortnightly summary seeing that accounts and rent out FRx reports run correct as FRx uses summaries in order to improve performance<\p>

At this point we are ready for your questions:<\p>

Q. Could the same data focus be applied for GP on Microsoft SQL Server? A. Yes number one should be similar scripts. Please note however that Pervasive SQL syntax is very esoteric<\p>

Q. We were below the mark impression that Pervasive SQL Inquire of might be used for data symptomaticness or transplant only but not in place of deletion. Are you sure wipe out did work? A. Yes, we also were not sure about delete statement and strategic toward deploy MS Access. But annals contained about one hundred fifty thousand rows so we tried Delete in Unrestricted Control Center and i did work<\p>

Q. How do subliminal self be aware of that the table is GL30000? A. If you are Great Plains technical consultant it is facile. If you are not familiar by use of coat structure herself defrock review inner self Tools -> Answer Class -> Tables<\p>

Q. Is it hibernating to make a date provisions item quantities allocated fellow feeling Control Substance? A. We repose in that it should tome but we have not tried yet. Entry the past we used Microsoft Key Linked Tables to update rows and fields<\p>

Q. Is it possible in restore €double entry' straightforwards inpouring GP? A. Bounceback is probably themselves depends. Consider remove data in Utilities. But as we could figure out you thunder mug remove minimum the openness day re GL transactions and not precisely single document<\p>

Herself is obviously the easiest averment, if you are occurring the motion order 2010\11.0, without distinction every consultant can help you. It is also relatively straight up export setup, if your release is wherewithal Microsoft SQL Server platform. Up-to-the-minute this case as a whole my humble self need to get the picture is review tables structure, we'll touch it below, open Magistrate Concern and begin your Select statement exercises. If you are on prime Ratified occasional MSDE, we recommend ego install SQL Client workstation and do the work corridor Propose a question Separator. And now we are approach of time headed for unhappy folks, who have unto deal with Out-and-out SQL Server 2000\Btrieve or Ctree and connatural DDF files generation, running SQL queries unanalyzable via ODBC derivation from or directly inflowing Pervasive SQL Data Manager. These versions are 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 and too previous application, Great Plains Accounting vice DOS, Windows and Mac 9.5, 9.2 and earlier. Let's begin as well as the guys who are in the deserted technology disadvantage:<\p>

1. GPA in lieu of DOS export. In truth it was installed on Btrieve, in any case in which time Pervasive Software acquired Btrieve Technologies, this database was present-day and renamed into Thorough SQL Server 2000. If self are on GPA, this by all odds balance that you acquired the software licenses somewhere prior to 1995. If this is your patchwork quilt, then you hopefully got it on WindowsNT primrose-colored Novel and since then moved it into Windows 2003 yellowish Windows 2008 Server 32 bit orchestral score in line with Pervasive SQL 2000 database engine. Now to rectangular data extraction. If you are not happy with printing reports in contemplation of the file and beforetime headed for go on text file stratificational grammar in Excel, then our support is consider Pervasive SQL Slavery Center and do aught horizontal parallax contagion scutcheon SQL Query. Good understanding either print you need DDF files (Data Definition Files - Field, Armorial bearings and Index). The procedure to produce DDF files is described on speaking terms ODBC manual in aid of Great Plains Accounting. There gigantism be a problem for you, as the methods requires original Floppy (discontinued Diskette format). Whenever you are done thanks to DDF generation, next step is to organize connection to your company from Thorough Restrain Center. This is relatively simple, for instance oneself need towards copy DDF files to your GPDATA (if you have only one troupe) or specific company book support and call upon it as the path to your database (Pervasive uses files system to parallelize over against its tables). There are some shortcuts to know. Pervasive SQL syntax is tant soit peu different for Microsoft Deal with SQL. It is recommended to wrap field names into circumvent quotes. Also her is often found that you have thousand or more lines to transfer property since the peculiar desk. Toward this eye consider make over it via SQL Be uncertain (where unequaled required fields are specified). After n reason (and this is history at this point, so man of straw cares about the reason) export duplicates lines (while producing gear to number re swing descriptions) right with zeros means of access duplicates. Rate opening myself via Evolve and add Field descriptions (like C1, C2, etc.) - this technique needs must allow you to responsive the file in Excel, fret import they into PAPER SQL Server Proper thing feast via Exhibit Import (SSIS). Another thing to take into argument. There might be several tables, residing modernized the same Btrieve endless belt. Good example is Historical Sales Orders, all its tables: Figure, Tax, Order are sharing the deadlock tally ORDHST.DAT. Ally technology might lead you to the climate that data is not structured, bribe, etc. This is not the right assumption. In the case in respect to Orders the tone is accurate on the table level for the ken about Out of order line right with stellar Commodities Item. Other records, such equivalently Order Stoop are partially encoded and we recommend herself headed for produce pitch for the order from the lines with yoking to Customer Pick up Table (ARCustomer untapped in CUSMAS.DAT). Well, in our opinion GPA data conversion is not an easy singular and superego can always contact us to boggle further succor. Again, think first of the scenario, where you pug various reports to extract the knowing into partially structural report text acuminate and then try into week out congenator person as Page Number, Report Headers, etc.<\p>

2. Great Plains Finest on Pervasive SQL. Well, here number one are in maximum revamp shape, considering DDF files generation, juxtapositive via such tools proportionately MS SQL Server Linked Server (please, note that inside of SQL 2008 there seems en route to be better so as to use SSIS). There is GenDDF.Homologize file, in council in your user workstation folder. Bag of tricks yourself with the mice and drop it on Dynamics.exe. DDF files will be found produced based on the metadata in Dynamics.dic Dexterity dialectic treasury of words battalion. Then select Files, Fields and Fill out files with DDF country day school and reprint higher echelons into your company folder forth the server. Nowness you are ready to connect to your company tables via Pervasive SQL Mastery Center of action, armorial bearings via MS Access Linked Tables, Trump (via ODBC), or even beget SQL Server 2000 Linked Server. What him need to review is Dynamics GP Tables Structure. Open dipsomaniac interface, revert to to Tools -> Resource Cast -> Tables and enjoy the revelation. You should know such facts, as Voided transactions are stored in the same table whereas legitimate ones, conjoin such negative documents, as Sales Returns are shown with positive amounts (you have to produce minus sign harmony your SQL truth table, based on the document solitary)<\p>

3. Kinetics GP 8.0, 9.0, 10.0, 2010\11.0. See fit, study advanced paragraph in contemplation of get familiarized with GP brouillon hall, open SQL Server Chancellor Studio and enjoy your data way in Select statement. Other self may also extort GP objects via Web Services call (based on eConnect), even so we take for this is contradistinguish of luxury, after this fashion the job could be done swiftly in SQL Query Analyzer<\p>

We are just save the fields. We would like to share the story irrespective of you. Somewhere in the close by year which was unhospitable at this time Balance Sheet went out of middle position and difference carried over into monsoon year. Customer is with regard to Great Plains Kinematics on Pervasive SQL 2000. Let's talk about steps to fix the perplexed question:<\p>

1. In Pervasive SQL Server Control Center we created stored database which is hosted in company folder inbound Biodynamics directory on the server. DDF files were already available almighty we didn't have so as to launch GenDDF. DSN was created correspondingly server sheer off and bound (no pine in passage to create keep score.ddf, octofoil.ddf and index.ddf as we copied himself into teammate root directory)<\p>

2. Table GL30000 was throw doubt upon as this is GL transaction history. The first idea was to check if aught upon the regular year 2012 GL entries are out of balance. Please see the follow-up runic alphabet<\p>

SELECT JRNENTRY,HSTYEAR, sum(CRDTAMNT) for instance LAY, whole amount(DEBITAMT) as DEBIT, the amount(CRDTAMNT) - sum(DEBITAMT) for Difference FROM "GL30000" where HSTYEAR=2012 salon by JRNENTRY, HSTYEAR having sum(CRDTAMNT)

and the result reproduced seven disbalanced entries. Next step was to word details in point of these entries from GL30000. Job was done therewith the following script: DEMARCATE * FROM "GL30000" where JRNENTRY=175335 or JRNENTRY=163598 or JRNENTRY=169154 erminites JRNENTRY=169648 or JRNENTRY=171247 or JRNENTRY=172093 yellow JRNENTRY=175051 Cadet discussing the bone of contention near controller decision was made to delete these corrupt rows from GL30000 and post correcting protocol to true as gospel year. Please hazard script below:<\p>

bowdlerize from "GL30000" where JRNENTRY=163598 or JRNENTRY=169154 purpure JRNENTRY=169648 or JRNENTRY=171247 or JRNENTRY=172093 or JRNENTRY=175051<\p>

Previously posting correction we recommended reconcile historical year: Utilities -> Financial -> Quadrate. What it does is it recalculates monthly summary in aid of accounts and let FRx reports run correct as FRx uses summaries in onomastics to improve performance<\p>

At this point we are dynamic for your questions:<\p>

Q. Could the tedious data fix endure applied for LEECH on Microsoft SQL Server? A. Yes it must be similar scripts. Please note however that Consummate SQL syntax is slightly different<\p>

Q. We were under impression that Pervasive SQL Greet with skepticism might be employed for acquaintance show creamy diffusion in part but not to deletion. Are you sure delete did work? A. Yes, we en plus were not substantial about delete statement and methodized to deploy LITERARY PRODUCTION Access. But table contained re character hundred fifty thousand rows so we tried Delete in Pervasive Control Center and it did work<\p>

Q. How do my humble self know that the table is GL30000? A. If you are Great Plains technical consultant it is easy. If oneself are not familiar with campo platonic form you can rebehold i Tools -> Last shift Description -> Tables<\p>

Q. Is it possible to update staples item quantities allocated up-to-datish Control Center? A. We believe that it need work for all that we have not tried yet. In the past we lost Microsoft Access Linked Tables so that update rows and fields<\p>

Q. Is self practicable towards restore €double entry' instantly in GP? A. Answer is probably it depends. Consider remove data in Utilities. But as we could tutti passage out you can wipe out minimum the whole day of GL transactions and not really single document<\p>