Microstrategy Blog

I have the pleasure this last week of dealing with Sage’s BusinessWorks software as a data source for MicroStrategy. Hence the slow amount of posts.

Some quick lessons…

After you created a linked data source using an ODBC connection for the Pervasive SQL database (seemed the easiest way) I found that querying it was a pain…

Sometimes I’d get:

The OLE DB provider "MSDASQL" for linked server "BUSINESSWORKS" returned an invalid column definition for table.

That’s because half the time "SELECT * FROM BUSINESSWORKS.DATABASE_NAME..GLAccount " just doesn’t work.

The solution?

SELECT * FROM OPENQUERY(BUSINESSWORKS, 'SELECT * FROM DATABASE_NAME.GLAccount')

This seems to be the fix for any Pervasive SQL linked database in SQL Express. After you get the connectivity stable, now you have to deal with the fact that this is one of those databases that has columns named “History01, History02, etc”.

Good times.