Saturday, 14 June 2008

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

15 comments:

Boozer said...

Hi Andrew,

Many thanks for your perfectly timed post!

You are not the only person in the universe trying to do this! I have a number of access databases, linking to Sage, which I would like to 'upsize' to SQL Server 2005.

I have returned to this subject many times, with renewed determination, but have failed each time. Sage infuriates me!! I hate it!

I followed your instructions meticulously and managed to create the linked server but my view still won’t return any rows. I am getting an ‘Access Denied’ error.

SELECT STOCK_CODE, DESCRIPTION
FROM SAGELINE50V14...STOCK AS STOCK_1

My linked-server is using ‘SageLine50v14;uid=manager;pwd=;

Do you have any more detail about this please?

Thanks

Boozer.
St Albans.

Boozer said...
This comment has been removed by the author.
Boozer said...

Hi Andrew,

A big thank you for your help with this. It is really appreciated. I have now created a linked server but it is not returning data correctly.

For instance: 'select * from sage1...stock_cat' returns 901 rows instead of the full 1000!

When I execute 'select * from sagefabman...stock_cat where number = 2' I get the following error:

LE DB provider "MSDASQL" for linked server "sagefabman" returned message "Row handle referred to a deleted row or a row marked for deletion.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "MSDASQL" for linked server "sagefabman".

Record 2 definitely exist. Strange init!? Any ideas?

I created my server using the script provided - and added the sp_addlinkedsrvlogin script.

Thanks again for your help Andy.

Cheers

Boozer.

Andrew said...

Hiya boozer!
(Alright if I call you that?)

Are you absolutely certain that you have set the property page flags for the OLEDB provider for ODBC to "Level zero only". (See figure 3 in my original post.) You access this dialog as per the right mouse menu on the tree branch as shown in figure 4.

The reason I ask is because the error message you are getting is exactly the one I was getting before I did this.

Sal said...

Thanks very much!... 3 servers worked fine till of course we moved our project to production! The data was on another server and was only returning about 1/2 of the results.

Thanks again for the 'test / fail' you went though.

-- Sal

Mike Davies said...

Thanks for posting this information. It is the only useful information I have found on the subject.

I am still having problems. I have successfuly created a linked server but cannot open the catalogs and cannot run a select statement.

My select statement is:

SELECT * FROM SAGE_Linked_Server...COMPANY

The error is:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SAGE_Linked_Server".

Any help gratefully received.

Rob Edmonds said...

Hi Andrew,

Well Done.....

I have also followed your article very closly and an unable to consistantly retreive all of the data from a table, I have verified that the Level Zero is enabled... the main differene is that we are running SQL2008 and Sage V12.... Not sure if this might be the problem.... any ideas?

Cheers

Rob

Andrew said...

Hi Mike, Hi Rob.

If you have already followed all the recommendations I made above and still got problems my recommendation (apart from not using Sage accounting software) would be build up the connection from the Sage side.

Ie. make sure first of all that the data is being provided by ODBC driver first of all.

For example you could test this using MSQuery or MSAccess.

Make sure that the data is getting through that stage first... then once you are certain that ODBC driver does at least work with some thing, then tackle the SQL Linked server as a second stage.

When you get to the SQL Linked Server stage, as well as the "Level Zero" trick, the other thing I would check 3 or 4 times is the user accounts that are being used by any given part of the connection.

What SQL user account are you using. What NTdomain account or ActiveDirectory account or other windows account are using. What Sage user account are you using.

Hope some of those suggestions help.

It has been a while since I looked at this now.

The only "real" solution to this kind of thing seems to me to something like "brute determination". On the assumption that the client REALLY REALLY REALLY wants to do this, and can't be persuaded to consider an alternative accounts solution - or at least a version of SAGE that is native on SQL - the only way that it seemed to me to get it to work was to try every permutation and combination of user accounts, permissions, options, mappings etc. etc.

We might hope that either Microsoft or Sage would provide documentation - or even that they both would?

Dave said...

Hi Andrew,

Great article - but I am missing a part of the picture. Are you running a local instance of SQL that is using ODBC to access SAGE.

I am trying to use this on a Server, so does that mean installing the ODBC driver on the server itself?

Regards,

Dave

Andrew said...

Hi Dave,

I think when I wrote this article, we had a separate SQL server from the box that had the SAGE server on it. So we had 2 servers. And then a load of clients some going directly into the Sage server, and some connecting to the SQL server and some connecting to both.

You will need to have the Sage ODBC driver installed on any machine that is accessing Sage directly.

The sage ODBC driver takes a path parameter which tells it where on the file system network to look for the sage data files. You can see I have highlighted how to set this path in one of the screen shots above.

So if your SQL server is on the same box as your Sage server, then YES, you will have to have the Sage ODBC driver installed on that box.

If the SQL server is on a different box to your Sage server, you will need to have the Sage ODBC driver installed on the SQL server box. (Although I can't see that it would do any harm to put it on the SAGE box too.)

There isn't as far as I am aware any other way of connecting directly to Sage data (Sage Line 50 version 14) other than through the ODBC driver. Or at least not any better way. You could try writing your own ODBC or OLEDB driver I suppose. 8-) (Incidentally, it's probably worth checking on Sage web site from time to time that no-one official has produced an OLEDB or whatever else alternative way to connect to this data. This didn't ought to be too hard for them to do, knowing the specification of it, if they could be bothered.)

The ODBC driver does not (as far as I am aware) make any use of the SAGE executable components. Instead it just uses the network file system to connect to and interpret the SAGE data direct from the disc. It is not mediated by the SAGE server being up and running. So the critical think here is that the Windows account that is calling the ODBC driver has data access permissions and the correct path mappings [if you are using path mappings] to the location on the network where the SAGE data is. (I'm not certain that the SAGE server components aren't involved at all, but it seems reasonable. I think you can for example take the contents of an old SAGE data directory and put it at some random place on your network and connect to it there completely in the absence of any live SAGE installation running on that network. It is basically just a route into a file system path and a way of interpretting the data, and validating the SAGE security credentials... Anyone who knows any better is welcome to comment.)

One thing to be a bit careful about, while I think about it, is that there are some slightly different versions of the Sage ODBC driver that I came across. And it wasn't clear to me whether they all worked equally well. Once I found one that did work, I tended to carry it around with me, but I would have to do some hunting around to find it again now.

Hope that answers your question? Rather you than me... You have my sympathy in large measure. I am blessed to have not been asked to actually do this kind of thing for at least 6 months now!

8-)

Dave said...

Hi Andrew,

Thanks for that - clears it up nicely.

Torn between the option of a "live link" to SAGE vs just scheduling an hourly upload of the key data to a SQL table in the database I am developing. (Only need credit limits and account on hold fields as the rest is managed from the other system.)

Live would be great, and I currently have that using an access database, but with .NET its not so easy to bring 2 tables together into one datagridview when they are in separate databases - if that makes sense....

Thanks again for the article and your guidance.

Regards,

Dave

Andrew said...

Hi Dave,

Thanks.

Re: "but with .NET its not so easy to bring 2 tables together into one datagridview when they are in separate databases"

I would have thought you could send a SQL statement through to the SQL server that you have linked to SAGE something like this:

SELECT *
FROM LINKED_SAGE_SERVER...SageTableName,
OTHER_SQL_DATABASE.dbo.SqlTableName
WHERE etc.

(Note the three dots between the Linked server name and the sage table name)

Either that or create a View in the SQL database which references the table in the linked sage server.

CREATE VIEW
Blahblahblah
AS
SELECT * FROM LINKED_SAGE_SERVER...SageTableName

Then use that View just like any other table in your SQL scripts that you are sending from dot net (or equally in your stored procedures).

I mean certainly you could do something like this to get a read-only dataset - my recollection is that the SAGE ODBC driver is in any case Read Only.

Or am I missing the point?

8-)
Andrew

pete said...

I know this is an old post but I have just tried the same thing using your script but on SQL Server 2008 R2 and am having the same problem that others using Linked Servers are having in that it is missing a number of rows when I try to do a select from the tables.

It always seems to be 99 rows that are omitted... It gets row 1 but then the next one that comes along is row 100 and then all the rows to the end of the set.

Fiund a few people on the web whio have come across this but no-one with a solution so I though I'd chek with you to see if you'd come across it.

markdev said...

Hi Andrew,

Like Andrew said this may be an old post but it is so important to many developers out there who are trying to achieve this. I have followed your steps and I keep getting SQL errors all over the shop such as

OLE DB provider "MSDASQL" for linked server "SAGE" returned message "The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 19.0".
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAGE" returned message "Cannot find all files in data path".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SAGE".


When I run my query. I have the odbc driver set up on the server that has the SQL database. I have tested this with MS Access and a winforms application on the server and the ODBC driver returns data perfectly. Would you know why I am getting these errors?

Thank you for your article it is very good and only wish Sage were more helpful with this and many other data connection topics.

markdev said...

Hi Andrew,

Ok I have managed to get rid of those errors but I am getting an Access denied message only now which is promising?