Linking Sage Line 50 Data to SQL Server

How can I link to Sage Line from SQL Server so that I can copy data from a sage table into a SQL table?

I have tried the following:
1) Created a DSN using ODBC Data Source Administrator (and I am able to extract data using this from Sage into Excel)
2) Created a linked server (called SAGELINK) using sp_addlinkedserver and
3) Used sp_addlinkedsrvlogin to add security details i.e. username and password for Sage

I then try to query the SALES_LEDGER table (which I can link to in Excel) linked server such as:
SELECT * from SAGELINK...SALES_LEDGER
however I get an error message saying "Cannot initialise the data source object of LOE DB provider 'MSDASQL' fro linked server 'SAGELINK'

What am I doing wrong? Or is there another way to do this?
PrandeoAsked:
Who is Participating?
 
PrandeoConnect With a Mentor Author Commented:
I don't know the [Databasename] - how can I find this out?

Interestingly I've just tried the following which works:

SELECT * FROM OPENQUERY(SAGELINK, 'SELECT * FROM SALES_LEDGER')

But the simple query SELECT * FROM SAGELINK..SALES_LEDGER still gives the error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SAGELINK" does not contain the table "SALES_LEDGER". The table either does not exist or the current user does not have permissions on that table.

Why?
0
 
rickchildCommented:
Can you show me your code for sp_addlinkedserver and sp_addlinkedserverlogin ?
0
 
PrandeoAuthor Commented:
I've made some further progress. My code is
EXEC sp_addlinkedserver
@server='SAGELINK',
@srvproduct='Sage',
@provider='MSDASQL',
@datasrc='SageLocal'
Then
EXEC sp_addlinkedsrvlogin 'SAGELINK', 'false', NULL, 'manager', 'password'
I must have a link because when I query the following
EXEC sp_tables_ex @table_server='SAGELINK'
I can see all the sage tables. However when I query
select * from SAGELINK...SALES_LEDGER
I now get the error message
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SAGELINK" does not contain the table "SALES_LEDGER". The table either does not exist or the current user does not have permissions on that table.
0
 
rickchildCommented:
Yes looks like you do have a good link if you can see the tables, have you tried qualifying it a bit better

SAGELINK.[DatabaseName].dbo.SALES_LEDGER
0
 
rickchildCommented:
I am guessing the OPENQUERY is working as it is using the default database, but you query with ... may not be selecting a database.

The default database may have been set up in the DSN, take a look in there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.