Linked server to Sybase, how to do that?

Question

Linked server to Sybase, how to do that?

FreeHansje

SSChampion

Points: 11751
More actions
August 29, 2005 at 9:12 am

#168573

Hi there,
I wish to create a linked server from MS SQLServer 2K to Sybase. I create a System ODBC, which works fine with MS Access. Now I use sp_addlinkedserver to create the linked connection according the syntax in BOL, using an ODBC DSN:
EXEC sp_addlinkedserver
   @server = 'SYBASETEST1',
   @provider = 'MSDASQL',
   @datasrc = 'SYBMaster'
GO
Received message:
Server: Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
This suggests an error in the SP, since I copied this script from BOL, but I can't imagine this to be so.
I used the 2nd option to use ODBC:
EXEC sp_addlinkedserver
   'SYBASETEST1',
   '',
   'MSDASQL',
   'SYBMaster'
GO
This creates the linked-server, but I can't use it. When trying to read the tables of this link in EM, I receive the following error:
OLE DB provider 'MSDASQL' reported an error. DSN not found and no default driver specified.
I am doing something wrong here, but don't know what. Have googled the internet, but could not find a solution or an example.
Help?
TIA,
Hans Brouwer
Greetz,
Hans Brouwer

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 1

Hans,

It sounds to me as though you need to create the DSN on the same machine as the SQL Server. Any DSNs you create are stored on your local PC, and so will only be visible to your machine (which explains why Access can read from the DSN as Access is a client app). However, if SQL Server is loaded on a different server, then it will only read DSNs that are saved on that particular server.

Hope this helps.

FreeHansje SSChampion Points: 11751 More actions · Answer 2

Tnx for the response, Phil.

For the second statement you could be right. The first statement does not require a DSN, but it does require a Sybase ODBC driver, which it does. I have to find out the productname, and I can't find it...

Greetz,
Hans Brouwer

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 3

Hans,

Funny situation this. I did the very same thing as you, created a system DSN on the server, went into QA, entered the syntax and came back with exactly the same error as you're getting. So I tried going through Enterprise Manager and registering the linked server that way. And guess what - it worked!!

Don't know what the issue is with the syntax, and maybe someone with more knowledge than me can answer that one, but as a workaround, go through EM and register the linked server that way. In the dialog box, specify the Linked Server name, select the Microsoft OLE DB Provider for ODBC Drivers option, and enter the DSN name in the Data Source field.

Fingers crossed !!