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:
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.
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
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
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.
I’m able to run the exec sp_tables_ex ‘TimberlineTest’ and it works but only give me one table. Any suggestions?
Thanks,
Mike
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
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?
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
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?
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.
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?
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
Also, make sure the service for Distributed Transaction Coordinator is started.
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.
does this enable updating of any and all feilds in timberline? I think many are locked.
Since this is at the top of Google’s search for Timberline and ODBC, I thought I should add that querying via Timberline’s ODBC driver is very, very slow. Further googling confirms this is not just me.
I found it far easier to import the table via SSIS in VS2008 (DTS in 2005). This is tricky too. I had to create an ADO.NET to SQL task for every table I wanted to use (about 100). The DATE_STAMP and TIME_STAMP columns are weird and need some transformations before MSSQL will accept.
Has anyone heard of Pervasive’s Data Integrator? I am trying to create a data warehouse using that tool. A linked server is ok until we archive data out of timberline. I would like to have all of the data in a sql table. Timberline doesn’t make the ddfs available. any ideas where those could be purchased??
By far the easiest solution to getting Timberline data into SQL is to use Argus SQL Timberline Warehouse, which used to be called Circle Data Center. ATSW adds 1 column to each table – a database ID – so no matter how many Timberline databases you have, they all end up combined in the same SQL tables.
We update SQL every 15 minutes during the workday and have converted most of our reporting to SQL. Each update cycle takes less than 3 minutes.
Do you know the approx cost that solution is?
Enterprise License
Annual Support & Maintenance $9,500.00
$1,900.00
Any number of Sage Timberline databases to unlimited SQL databases, over 4GB
Professional License
Annual Support & Maintenance
Any number of Sage Timberline databases to unlimited SQL or MSDE databases up to a combined total between 2 and 4 GB
$5,000.00
$1,000.00
Standard License $3,500.00
Annual Support & Maintenance $700.00
Any number of Sage Timberline databases to unlimited SQL or MSDE databases up to a combined total of 2GB
Chris,
How much data are you moving every 15min? We are currently using Argus to move Timberline data (appx 200 selected files) representing appx. 1-2GB of data but it takes much longer than 3 min. Do you have any tips for speeding up this process? Note: Both the Pervasive and SQL databases reside on the the same physical server and we have upgraded the CPU to a quad, but did not get any real noticable performance increase. We are currently refreshing the data every hour but would like to get down to every 15min.
Thanks,
Brian
Have you guys have any luck using the linked server to update data in the Timberline tables? I always get the error message below when I attempt an update.
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider “MSDASQL” for linked server “timberlinetest” does not support the required transaction interface.
FormPaper.com covers a large legal and real estate market. We have Real Estate, Legal, and Office forms software. We also provide many services like a lawyers 50000 database, property listing service, and much more.
In trying to execute your query above in MSSQL 2008 with SQL Server Management Studio. I was able to get the link server to show up however when trying to get the available tables it would just try to execute for long periods of time but does not seem to be responding. How might your guidelines above change for SQL2008? Is there a better way to accomplish this connection in MSSQL 2008? Any help would be much appreciated. Thanks.
Extraction time depends on the size of your Timberline database, the number of columns you are trying to extract as well as how many users are accessing Timberline and what they are doing, for example posting something. I’ve designed my own data extraction in SQL 2008 and the process runs every 10 minutes. The Timberline ODBC driver and Pervasive are too primitive, so you need to archive very old data if you want fast data extraction. Why would you want your data extraction to run through old data over and over?
Drop me an e-mail if you want to discuss: canguruaus@yahoo.com.au
It may be worth taking a look at http://www.boomi.com as a simple solution. Our tool allows you to use a “Visio-like” work flow to build the integration and uses branded and generic application/file type specific connectors to foster the data exchanges.
Happy to provide white papers and answer questions if any of you would like more information.
Stephen Hannaway
stephen_hannaway@dell.com
Hmm… a couple of notes. I tried this with a 64-bit SQL Server 2008 R2 server, and no dice, as it can’t see the 32-bit ODBC DSN.
It is possible though to pull data from Timberline through their ODBC driver via SSIS.
the ODBC driver does return these data collections with the GetSchema() call, but these are the useful collections:
MetaDataCollections
Tables
Columns
Indexes (requires a filter to pull index fields for a specific table)
DataTypes
There could be an issue in SSIS pulling data from fields defined in Timberline as Text(254). If you do not redirect those fields in the field mapping (it doesn’t matter if there is anything to redirect them to…), extracting the data will work, but in the process the actual SSIS engine will die.
On the SQL Server side, define the fields receiving Text(###) or Char(###) data from Timberline as nvarchar(###)…
Timberline has a Time field type. it is possible to map this to a TIME data type in SQL Server 2008. The DATE field can map to a Datetime data type in SQL Server.
Other possible Timberline data types through ODBC:
Checkbox -> System.boolean -> bit
Byte -> System.byte -> hmm… byte?
Binary -> System.Byte[] -> varbinary
Memo -> System.String -> nvarchar(max)
for Doubles, size, radix & precision information can be retrieved also…