Alternatives to Linked server (the remote server is pervasive sql)

Question

We have spent in total around 50 hours trying to debug our linked server connection from sql server 2012 to pervasive sql.

Needless to say, pervasive is the issue.

We are ONLY running select queries like this:

select * from [sqlserver].[pervasiveserver].[db]..[myremotetable]

We are so frustrated with this, that I do not even want to discuss the errors that we are getting. Several experts said that it's probably related to a bad pervasive odbc driver.

We have SSRS reports whose datasource points to the sql server.

Can you suggest an alternative method to linked servers in this case? Our main task is to be able to select from a pervasive database using SSRS?

Answer 1

I would build SSIS packages to import the data into tables with the same/similar names in a new SQL Server database.

SSIS is usually more solid with ODBC drivers and has much richer error handling to get through those niggly issues. You can make a speedy start using the SSMS Import Data Wizard and saving it's results as an SSIS Package.

Keeping the same table names minimizes your SSRS rework.

The tables in SQL Server can also be indexed to deliver fast report performance.

Answer 2

Before giving up on linked servers, see if you have better results with using openquery instead of fully qualified tsql. We have a linked server from sql server to redbrick, another pair that doesn't play nicely together, but using openquery solves all these problems.

You will probably have to write a stored procedure for every query as opposed to putting the sql into your report.