Friday, July 4, 2008

Updating the Stonefield Query for Sage Pro ERP Data Dictionary

Stonefield Query knows all about your Sage Pro ERP database because it has a data dictionary. A data dictionary defines the fields and tables in a database, providing features such as descriptive names so you don't have to know the real names, how tables are connected, or joined, together, and calculated values such as extended price that aren't normally stored in the database.

However, if you've added custom fields or tables to the Sage Pro database, you'll want to refresh the Stonefield Query data dictionary so you can query on those new fields or tables. This is easily done using the Stonefield Query Software Development Kit (SDK). You may also want to use the SDK if you want to report on other databases you have linked to your Sage Pro system.

The Customizing the Stonefield Query Data Dictionary topic in the Stonefield Query for Sage Pro ERP help file has complete information on how to refresh the data dictionary. However, one important thing to note is that if you're using a SQL Server database, you have to tell Stonefield Query which database to read the data structure from. You can't use the DSN that Sage Pro automatically creates because that DSN sets the default database to the Sage Pro system database, which doesn't contain the tables you're interested in. Instead, create a DSN that sets the default database to the Sage Pro data database.

Alternatively, you can change the connection type to "ODBC - Connection String" and enter a connection string similar to the following:

driver=SQL Server; server=ServerName; database=DatabaseName

where ServerName is the name of the server and DatabaseName is the name of the Sage Pro database.

You can then click the Refresh button in the toolbar to refresh the entire data dictionary or right-click a table and choose Refresh Table from the shortcut menu to refresh just that table.