The latest project I am working on introduced me to the wonderful world of Timberline. This is one of the few times when I truly wish the customer we’re building this for would have used Access. Really!
But, Timberline (an accounting package that sits on top of Pervasive SQL which sits on top of Btrieve) has been around for quite a while, and has a large enough user base in the construction industry that it bears looking at how we can provide a mobile solution for them.
Anyway, one of the things is that we need to be able to sync with their data. I’d looked at some options for ad-hoc querying of the database, including using the Pervasive OLEDB driver, the Pervasive ODBC Driver, and the Timberline Driver. While I have the data dictionary for the app, I wanted a better way to inspect what is in the database.
Luckily, Google Groups pointed to a post with someone talking about doing a linked server in SQL to Pervasive. I was able to get this work using the Timberline ODBC driver, and there are enough quirks to merit a post on it.
The basic concept is that after creating the linked server, we would then query it using SQL statements to pull the data from Timberline. This gives us some more options as far as flexibility of the interface.
To get the linked server working, you have to first create the linked server. Actually, you need to first create an ODBC Connection in your Server DSN, but that’s pretty straightforward. So in Query Analyzer, run the following:
@server = 'TimberlineTest',
@provider = 'MSDASQL',
@datasrc = 'DataTest'
--@Server is the name of the new linked server
--@Provider is the SQL Provider
--@datasrc is the name of a local ODBC DSN
--@srvproduct is the Timberline Driver
--@datasrc is the Server DSN you created
(BTW, if you need to drop the linked server the command is “exec sp_dropserver TimberlineTest” (without the quotes))
So, now that you have that created, you can get all the available tables:
exec sp_tables_ex 'TimberlineTest'
Finally, you can query a table by issuing the following command:
select * from [TimberlineTest].[C:\Timberline\Accounting\MyData]..[PRM_MASTER__EMPLOYEE]
--First, the name of the linked server
--Second, the path to the data Files
--Fourth, the table name.
And that’s it! As I work through this I’ll post if I come across any other oddities.