asked on

Sage Line 50 as Linked Server SQL Express 2012

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".

Open in new window

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'

Open in new window

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?

Bruce Denney

Which Sage 50 is this UK,AUS,NZ,SA or US

databarracks

ASKER

Hi,

It is Sage 50 UK version 19

Bruce Denney

Not sure why you are using MSDASQL you need to create a DSN to read the data from the Tables.

ConnectionString = "ODBC;DSN=SageLine50v" & DN & ";UID=" & UR & ";PWD=" & PW & ";DIR=" & DR & ";"

example string

DSN=SageLine50v19;UID=manager;PWD=password;DIR=S:\Sage\ACCDATA;

I know sage, nothing of MS Sql

Bruce Denney

The drivers are Read only and they are stupid in that the delimiter used in ODBC is a comma, this make SQL statements ambiguous at times and this can break things that should otherwise work. So it just might not be possible.

databarracks

ASKER

Hi Bruce,

My plan is to dump the information from the sage tables into my SQL tables as their odbc is too slow otherwise. I have to use MSDASQL because it is the default ODBC Provider on SQL Server.

I know how to create the DSN as I have done this plenty of times. This is a screenshot of me creating the Linked Server on SQL.

. What am I doing wrong? Do I need to me logged into the SQL Server using windows authentication or what?

Bruce Denney

I can't stress enough my lack of knowledge on MS SQL.

The principle of what you are doing sounds fine, copy the data from the slow ODBC tables into fast SQL tables, and then use the fast tables for your application.

The way I have done this in the past, using MSAccess is to create a linked table using the ODBC drivers and then append the records from that table into a new table (using mdb or MySQL I guess it would be the same for MsSQL).

I don't understand what you are doing with MSDASQL are you looking to write an SQL statement that will mirror an ODBC data source into a new SQL table rather than doing it programmaticly? If so then I would suspect you are on a road to nowhere.

ODBC drivers support subsets of SQL and then to top this off there are issues with ambiguity as I described earlier.

databarracks

ASKER

Hi Bruce,

The reason I am using MSDASQL is because I am trying to skip the extra hurdle of using the MS Access Database then copy over to the SQL Database.

I have no doubt that your approach will works as it was my Plan B. If you see this link Link Sage Line 50 as sever in SQL you will understand what I am talking about.

I am almost there in replicating what the guy in the link did but I am having an access denied error. All the while I can view the columns in my table just with no rows present.

Again the Linked Table to Access to SQL is still in the picture but doing away with Access would be much better......I am sure you will agree?

databarracks

ASKER

If anyone is still willing to help I am ever so close to getting this to work just have one more hurdle to overcome before it works. My current linked table returns the columns for the relevant table but no rows, of which SQL throws and error saying that access is denied.

This is a permissions thing I guess. I have created the DSN on the SQL Server for the Sage ODBC connector and tested with excel and it works on the server, therefore the ODCB connection works. However when I login to the SSMS and try running a query against my linked table I get the 'Access denied' error?

My script to create my Linked Server is

/****** Object:  LinkedServer [SAGE]    Script Date: 04/02/2013 16:54:53 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SAGE', @srvproduct=N'Sage', @provider=N'MSDASQL', @datasrc=N'SageLine50v19', @provstr=N'SageLine50v19;uid=xxxx;pwd=xxxxx;'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SAGE', @optname=N'use remote collation', @optvalue=N'true'

Open in new window

Anyone's help is much appreciated?

ASKER CERTIFIED SOLUTION

Bruce Denney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

databarracks

ASKER

Hi Bruce,

After much deliberation I have decided to go with Plan B as there just isn't enough resource out there to help me with this issue except for you of course, you know what Sage are like:(

Plan B is the common way of doing it as I have wasted a lot of time trying to get it to work. I will use MS Access as a bridge to Sage and then run a job every hour to update my SQL Database. I will use a pass through query on MS Access to Sage as it is faster than the standard linked table.

Anyway thanks again for your help Bruce and if you have any other tips and tricks regarding Sage and odbc usage, I would be glad to hear them.