troubleshooting Question

Sage Line 50 as Linked Server SQL Express 2012

Avatar of databarracks
databarracks asked on
DatabasesMicrosoft SQL ServerTax / Financial Software
10 Comments1 Solution4749 ViewsLast Modified:
Hi Guys,

I would like to add my Sage Line 50 data as a linked server on SQL Server Express 2012. I have found a few articles on how this can be done but I cannot query my linked server and keep getting the below errors:

OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".
OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" returned message "The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 19.0".
OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" returned message "Cannot find all files in data path".
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 "SAGE_Linked_Server".

The server that holds the Sage ACCDATA is on another server to my SQL database. I have installed the Sage ODB driver on my SQL Server and I have created my System DSN as per normal(by this I mean I have managed to use Sage ODBC on my workstation). I have created my linked server with the script below:

EXEC master.dbo.sp_addlinkedserver @server = N'SAGE_Linked_Server', @srvproduct=N'SageLine50v19', @provider=N'MSDASQL', @datasrc=N'SageLine50v19', @provstr=N'SageLine50v19;uid=xxxxxx;pwd=xxxxxx;'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE_Linked_Server', @optname=N'use remote collation', @optvalue=N'true'

The Sage ODBC driver is 32 bit and my SQL Server is 32 bit. Please help I have tried absolutely everything I could think of and this link Link Sage Line 50 as sever in SQL was very useful. I copied everything he did but I get these errors?
ASKER CERTIFIED SOLUTION
Bruce Denney
Sage 50 Consultant and Integrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros