Let's go crazy - accessing Timberline / Pervasive Data from a SQL Linked Server
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:
(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:
Finally, you can query a table by issuing the following command:
And that's it! As I work through this I'll post if I come across any other oddities.
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:
EXEC sp_addlinkedserver
@server = 'TimberlineTest',
@provider = 'MSDASQL',
@srvproduct='Timberline Data',
@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
--Third, Nothing
--Fourth, the table name.And that's it! As I work through this I'll post if I come across any other oddities.
posted by Cory Foy at
3:51 PM
13 Comments:
Circle Software developed CircleDataCenter which takes Sage Timberline Office data from Pervasive to a Microsoft SQL data warehouse. It's an exact stamped copy of the Timberline data and you can use your existing Crystal Report designs by redirecting them to the warehouse. There is a dramatic increase in reporting speed (typical benchmark is a report that took 2 hours now takes 20 seconds to run), you can combine data to report from multiple folders regardless of general ledger and job costs formats. Easily integrates with other SQL based systems. To request more information, send email to infouse@circlesoftware.com
By
Susan Thesing, at 1:42 PM
I am trying to do the same thing with timberline but i am having security issues. I would like to know if you were able to successfully do this...do you think I could ask you a few questions via email?
mike.gould@homewoodcorp.com
By
Mike Gould, at 8:15 AM
I encountered security issues also when I attempt to list all the tables:
Msg 7416, Level 16, State 2, Procedure sp_tables_ex, Line 12
Access to the remote server is denied because no login-mapping exists.
I was running the linked server from an instance of MSDE. I had to create a default MSDE instance and relink. Also I had to specify a timberline user that had access to the database as a security remote logon for the Timberline Linked server.
Hope this helps.
By
Anonymous, at 10:56 AM
I'm able to run the exec sp_tables_ex 'TimberlineTest' and it works but only give me one table. Any suggestions?
Thanks,
Mike
By
Anonymous, at 8:09 AM
Cory, great job explaining this. There was one piece missing - adding credentials for the linked server. This should do the trick:
EXEC sp_addlinkedsrvlogin
@rmtsrvname='timtest'
, @useself='false'
, @rmtuser='your user'
, @rmtpassword='your password'
Cheers,
Daniel Williams
dlwiii at g mail
By
Daniel Williams, at 3:56 PM
I did the same thing to create a link in sql 2005 but when i ran EXEC sp_tables_ex 'TimberlineTest' I got following message: OLE DB provider "MSDASQL" for linked server "TimberlineTest" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TimberlineTest".
However i can create link tables in MS Access from ODBC DSN that i used to create a link in SQL 2005.
Any Idea why i am getting this error?
By
Bab, at 12:09 PM
Are you sure that the DSN is a system DSN, not a user one, and that the DSN is valid?
In the sp_addlinkedserver call, the DSN is given by the argument to @datasrc
By
Daniel Williams, at 12:16 PM
Thanks for yor reply i was using user DSN. It work when i use system DSN.
But how i was able to create a link in Access with User DSN?
By
Bab, at 12:49 PM
Babs, the reason it works under Access is because you run Access under your current user account - it is an interactive application. But SQL Server runs as a service, under a different account. That account has no idea about your user account and its user-specific DSNs. In general, I just always use system DSNs to avoid issues like this.
By
Daniel Williams, at 12:58 PM
How to create a linked Server for MAS 90? I have Mas 90 4.0 ODBC Driver. and used the following parameters
EXEC sp_addlinkedserver
@server = N'Server',
@provider=N'MSDASQL',
@srvproduct=N'',----MAS 90 4.0 ODBC Driver
@datasrc=N'SOTAMAS90'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'Server',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'User',
@rmtpassword = N'password'
But when i exec sp_testlinkedserver Server, it just keep executed and never stopped even after long hours. I have to manually stop the query.
Do you know what is wrong with the syntax I have?
By
Babin, at 6:23 AM
Hi Cory,
I am having an issue trying to link my Timberline DB to SQL server 2005.
I used the following criteria:
Provider: Pervasive.SQL V8 OLE DB Provider
Product name: Timberline Data
Data source: [Timberline Data Source]
Provider string: MSDASQL
but I am getting the following error:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "PervasiveOLEDB" for linked server
"TIMBERLINE".
OLE DB provider "PervasiveOLEDB" for linked server "TIMBERLINE" returned message
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.
No work was done.".
OLE DB provider "PervasiveOLEDB" for linked server "TIMBERLINE" returned message "Mode,
Protection Level, or an unknown parameter has been set (incorrectly) in the connection string".
(Microsoft SQL Server, Error: 7303)
Any help would be greatly appreciated.
Thanks,
Joe
By
Anonymous, at 9:07 AM
Also, make sure the service for Distributed Transaction Coordinator is started.
By
Anonymous, at 12:32 PM
Our organization uses Timberline Estimating from Sage using Pervasive. I can't think of anything more annoying to work with -- even the "authenticated" edition of SqlAnywhere wasn't as hard to connect to from applications.
The estimating package is -- if you can imagine it -- even MORE annoying than the accounting package because each individual estimation project becomes its own logical database and requires an individual connection to get to it. But of course you need a connection to the "master" database as well because it contains the global data.
By
Don, at 7:56 AM
Post a Comment
Links to this post:
Create a Link
<< Home