Timberline and Pervasive Posted by Mischa S.

Back

Date: August 21, 2012

So, this one isn't really even related to coding, but was a brain stumper that had me and another db nerd facepalming for hours.  We had a client that had data stored in Timberline, which is an abstraction layer (accounting views, reports, blah) of Pervasive SQL.  All the data was in Pervasive, but we couldn't the stored proc sp_linkedserver to play nice in SQL 2005 with Timberline. 

The first issue we had, was that SQL ran as a user that didn't have access to the Timberline dbs.  We were using Razor SQL to get in and look around, which was using a JDBC driver, and that apparently doesn't first force authentication on the windows level, but rather just passes the credentials of the user we specified.  We were specifiying a username and password in the DSN, but SQL was trying to authenticate first as ITSELF and then as the user we specified.  I'm sure that is by design, but since when have I ever liked how Microsoft designs things?!

Next issue we ran into, was we could now see the tables in our linked server in SQL studio manager, but we couldn't read any data.  At this point, it was 6pm and we'd been bruising our faces with our palms for too long to care anymore and we went home. 

Next day, my colleague calls me and says he linked to Timberline using Microsoft Access via linked tables (using the Timberlin ODBC Driver) and he's got the data flowing.  I never would've done that, because I think MS Access was created to destroy humanity.  However, in this instance, it worked.  He created a view and joined the tables with the Access WYSIWYG editor and BAM!  I was amazed.  Mostly because Access actually created a solution.  In my experience, it only creates problems and orphan records.

So, ups to MS Access....never thought I'd say that.