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

• SQLPhil

  SSCarpal Tunnel

  Points: 4204

  More actions

  August 31, 2005 at 3:32 am

  #586388

  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

  August 31, 2005 at 5:48 am

  #586412

  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

  September 1, 2005 at 9:23 am

  #586865

  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 !!