It’s clearly the best query, in case you are on the present model 2010/11.zero, as each guide may also help you. It’s also comparatively straight ahead export process, in case your launch is on Microsoft SQL Server platform. On this case all you have to know is evaluate tables construction, we’ll contact it beneath, open Administration Studio and start your Choose assertion workouts. In case you are on former Choose on MSDE, we advocate you put in SQL Shopper workstation and do the work in Question Analyzer. And now we’re coming to sad of us, who must cope with Pervasive SQL Server 2000/Btrieve or Ctree and associated DDF recordsdata technology, operating SQL queries both through ODBC connection or straight in Pervasive SQL Knowledge Supervisor. These variations are 7.5, 7.zero, 6.zero, 5.5, 5.zero, four.zero and in addition earlier software, Nice Plains Accounting for DOS, Home windows and Mac 9.5, 9.2 and earlier. Let’s start with the fellows who’re within the out of date know-how drawback:
1. GPA for DOS export. Traditionally it was put in on Btrieve, however when Pervasive Software program acquired Btrieve Applied sciences, this database was modernized and renamed into Pervasive SQL Server 2000. In case you are on GPA, this in all probability signifies that you acquired the software program licenses someplace previous to 1995. If that is your case, then you definately in all probability bought it on WindowsNT or Novel and since then moved it into Home windows 2003 or Home windows 2008 Server 32 bit version with Pervasive SQL 2000 database engine. Now to information extraction. In case you are not proud of printing reviews to the file after which to do textual content file parsing in Excel, then our suggestion is take into account Pervasive SQL Management Middle and do both desk export or SQL Question. In both case you want DDF recordsdata (Knowledge Definition Recordsdata – Discipline, File and Index). The strategy to provide DDF recordsdata is described in ODBC handbook for Nice Plains Accounting. There may be an issue for you, because the strategies requires unique Floppy (out of date Diskette format). If you end up carried out with DDF technology, subsequent step is to create connection to your organization from Pervasive Management Middle. That is comparatively easy, as you have to copy DDF recordsdata to your GPDATA (you probably have just one firm) or particular firm folder and declare it as the trail to your database (Pervasive makes use of recordsdata system to hook up with its tables). There are some shortcuts to know. Pervasive SQL syntax is barely totally different from Microsoft Transact SQL. It is suggested to wrap area names into double quotes. Additionally it’s typically discovered that you’ve got million or extra traces to export for the one desk. On this state of affairs take into account export it through SQL Question (the place solely required fields are specified). By unknown cause (and that is historical past at this level, so no person cares concerning the cause) export duplicates traces (whereas producing appropriate variety of area descriptions) with zeros in duplicates. Take into account opening it through Write and add Discipline descriptions (like C1, C2, and many others.) – this method ought to let you open the file in Excel, or import it into MS SQL Server Customized desk through Knowledge Import (SSIS). One other factor to think about. There may be a number of tables, residing in the identical Btrieve file. Good instance is Historic Gross sales Orders, all its tables: Traces, Tax, Order are sharing the identical file ORDHST.DAT. Such know-how would possibly lead you to the sensation that information will not be structured, corrupt, and many others. This isn’t the correct assumption. Within the case of Orders the construction is correct on the desk stage for the details about Order line with reputable Stock Merchandise. Different data, similar to Order Header are partially encoded and we advocate you to provide header for the order from the traces with connection to Buyer Grasp Desk (ARCustomer saved in CUSMAS.DAT). Nicely, in our opinion GPA information conversion will not be a straightforward one and you’ll all the time contact us to get additional assist. Once more, assume first concerning the situation, the place you print numerous reviews to extract the info into partially structural report textual content file after which attempt to week out such traces as Web page Quantity, Report Headers, and many others.
2. Nice Plains Choose on Pervasive SQL. Nicely, right here you might be in significantly better form, contemplating DDF recordsdata technology, connecting through such instruments as MS SQL Server Linked Server (please, notice that in SQL 2008 there appears to be higher to make use of SSIS). There’s GenDDF.Set file, sitting in your consumer workstation folder. Catch it with the mice and drop it on Dynamics.exe. DDF recordsdata might be produced primarily based on the metadata in Dynamics.dic Dexterity logic dictionary file. Then choose Recordsdata, Fields and Index recordsdata with DDF extension and duplicate them into your organization folder on the server. Now you might be prepared to hook up with your organization tables through Pervasive SQL Management Middle, or through MS Entry Linked Tables, Excel (through ODBC), and even create SQL Server 2000 Linked Server. What you have to evaluate is Dynamics GP Tables Construction. Open consumer interface, go to Instruments -> Useful resource Description -> Tables and benefit from the discovery. You need to know such details, as Voided transactions are saved in the identical desk as reputable ones, plus such detrimental paperwork, as Gross sales Returns are proven with constructive quantities (it’s a must to produce minus check in your SQL assertion, primarily based on the doc sort)
three. Dynamics GP eight.zero, 9.zero, 10.zero, 2010/11.zero. Please, learn earlier paragraph to get familiarized with GP desk construction, open SQL Server Administration Studio and revel in your experience in Choose assertion. You may additionally extract GP objects through Internet Companies name (primarily based on eConnect), however we imagine that is type of luxurious, because the job could possibly be carried out straight in SQL Question Analyzer