I have heard people talking about SQL Linked Servers to Providex data, but I have not been able to successfully set up a linked server to the providex data.  I am assuming I am missing a step somewhere, but I would be very interested in this or pass through queries.  Is there some connection string syntax that I am missing?

Here are my notes on the Linked Server setup:

SQL queries directly to Providex Tables.

Configure a Silent DSN

As usual…but , if using 64-bit SQL, with 64-bit ODBC driver v4.4.4 (the version that comes with Sage 2014) or above.

Configure SQL Server options

From MS SQL Server Management Studio

Scroll to Server Objects => Linked Servers => Providers

Right Click MSDASQL => Properties

The only options selected should be “Allow in Process” & “Level zero only”

Select the above options and click OK

From The SQL Server “New Query”

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Execute the query

Create the linked server in SQL Management Studio

Linked Servers Right Click => New Linked Server

Linked Server Name = DSN NAME

Server Type = Other Data Source

Provider = Microsoft OLE DB Provider for ODBC Drivers

Product name = DSN NAME

Data source = DSN NAME

Define a Connection String(Provider String)

Driver={MAS 90 4.0 ODBC Driver}; UID=userid; PWD=password; Company=ABC; Directory=\\UNC to ….\MAS90; LogFile=C:\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1; SERVER=NotTheServer

*Notes

You should be able to copy / paste the connection string from the DSN (tweaked as required).

SILENT=1; Since SQL Server is running as a Service, any errors that force a dialog will not display and cause the process to continue running, adding SILENT = 1 will raise any errors encountered.

Change the SQL Service to run under a domain login (so it can access the UNC in the connection string).

I read on SageCity that the DirtyReads / BurstMode references need to be removed, but I'm not sure that is necessary.

Test the connection

SELECT * FROM OPENQUERY (LinkedServerName, 'Select * from GL_Account')

Kevin, thanks very much, I can't wait to try this.  This probably opens an entire world of possibilities.

It's read only, but is a real game-changer for complex reporting requirements.  I've had report batches that took 12-18hrs (with logic in CR only) converted to run in under 5 minutes when the complex logic was moved into SQL views.

Kevin.  Thanks again.  It is wonderful to see in action

A word to anyone else out there setting this up, chances are your SQL is 64 bit and you will want to make sure that you have the 64bit odbc installed so you can create your silent 64 bit DSN.

Kevin.  Thanks again.  It is wonderful to see in action

A word to anyone else out there setting this up, chances are your SQL is 64 bit and you will want to make sure that you have the 64bit odbc installed so you can create your silent 64 bit DSN.