Creating a linked server for Sage line 50 version 14 on Microsoft SQL server 2005

Creating a linked server for Sage line 50 version 14 on Microsoft SQL server 2005.

Although documentation exists for creating linked servers on Microsoft SQL server 2005, and documentation exists regarding the Sage Line 50 v 14 (2008) ODBC driver, I couldn't anywhere find documentation that combined both.

If there is any, please let me know in the comments.

When I tried a fairly standard set of options for creating a linked Sage Line 50 version 14 server on SQL 2005, I tended to get problems like this:

Figure.1


The graphic shows an attempt to use the Sage Stock table via the linked server in a view on the SQL 2005 server.

You get back a few rows (the number varies) and then the error occurs:

"Row handle referred to deleted row or row marked for deletion"
and
"OLE/DB provider 'MSDASQL' Irowse getdata returned 0x80040e23"
and
"Could not get the data of the row from the OLE/DB provider 'MSDASQL'"

Other strange phenomena occured, like from an ADP (Access data project) connecting to a database that referenced the linked server, the sa account was able to connect, whereas a domain account with full access to the database and server admin rights on the SQL server was not able to use the linked server, even though all accounts were being mapped to a valid sage account with every permission under the sun granted to it. Before we could discover that, however, we had to get the thing to connect at all. And how we did that is as follows:

I spent a long time figuring it out, but maybe I'm the only person in the known universe that has ever needed to set up a Sage Line 50 linked server on Microsoft SQL 2005, so unless someone actual prompts me for more detail I'm going to be brief but include the critical things I noticed along the way.

First have a look at the following two graphics:

Figure.2



Figure.3


The first of these you have undoubtedly tried all the settings of the ODBC driver under the sun in an attempt to make it work (if you've come this far).

The second graphic however you may have passed over.

It sets the OLEDB provider for ODBC datasources settings for all linked server using the OLEDB provider for ODBC that are running on the same server.

It is not at all obvious to me what you are supposed to do if you have ODBC sources that need different alternative settings here, but I was "blessed" with only needing to conect to Sage Line 50 from SQL 2005, so I didn't have to deal with that particular problem.

The way to get to that dialog is to right click on the SQL Server Management studio hierarchy branch as showing in the graphic below:

Figure.4

Addendum - scripting out Sage Line 50 Linked server on SQL 2005

As per the request from the blogger "Boozer" from St Albans (see comments), I have added some additional detail.

These are the 2nd and 3rd pages from linked server properties dialog. (The first page is already showing above.)


Figure.5: Security tab of linked server properties dialog:


Figure.6: Sever Options tab from linked server propeties dialog:


Figure.7: This grahic shows my Sage 50 version 14 ODBC settings.


I also scripted out the creation script of my linked server using the following menu command in the SQL 2005 Managemenet studio tool.

Figure.8


This gave me the script shown below:

/****** Object:  LinkedServer [SAGE_Linked_Server]    Script Date: 06/22/2008 20:56:02 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SAGE_Linked_Server', @srvproduct=N'SageLine50v14', @provider=N'MSDASQL', @datasrc=N'SageLine50v14', @provstr=N'SageLine50v14;uid=FillinTheBlank;pwd=FillinTheBlank;'
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'

You should be able to execute this SQL script in a query window against your server to produce an identical linked server setup to the one I have.

Some of the keys for us I think were as follows:
(1) Setting the "Zero level only" flag in the OLE/DB provider for ODBC (MSDASQL) properties dialog. (See Figure.3 above.) You can access this dialog via the right-mouse menu shown in Figure.4. Before I did this I tended to get the message "Row handle referred to a deleted row or a row marked for deletion" when running a SQL query against a recordset from the linked server. SQL tools would return a few rows, anything from 1 to about 5 or 6 before coming back with this error. The number returned any time the query was run was not always the same but, tiny compared the number of records I was expecting.

(2) We discovered there was a difference when trying to connect to the sage linked server when we were using integrated security accounts vs. when we were using SQL server native accounts. The conclusions here were not very complete. Integrated NT domain accounts worked when connecting via some of the Sage accounts, but did not work when connecting via other sage accounts, even Sages accounts that had been given maximum permisions to everything on Sage. Sage accounts that did not work with the integrated NT domain SQL server accounts did however work when using the SAGE Linked server via the security context of a Native SQL Server 2005 account.

(3) Much of what we were doing had been upgraded from pervious versions of SQL server, Sage and NT. The NT domain had been upgraded from a windows small business server to domain controller to an Active Directory. Sage accounts that worked with the new active directory accounts tended to be ones that had existed on the previous Sage installation.

Sponsored by Dorchester (in Dorset) Software