Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?

Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?

• Prem_sk

  I think if you use the DSN name, you don't need to specify the @provstr.

  I used the GUI interface to setup the linked server, I can run sp_tables_ex to view the tables. But the tables do not show up when I click the linked server, I don't know if it is normal or not

  
  

• John Kimball

  I have an ASE linked server working using the following:

  EXEC master.dbo.sp_addlinkedserver @server = N'ServerNameHere',
  @srvproduct=N'Sybase',
  @provider=N'MSDASQL',
  @catalog=N'DatabaseNameHere,
  @datasrc=N'DSNCreatedUsingASEDriver'

  I then set the username and password using the SQL management console..

  I could then get a quey working. I have not yet tested inserts/updates.

  Other useful items:

  EXEC sp_tables_ex
  @table_server ='ServerNameHere'

  
  
  

• FLee

  From the quick research I did online, I think your error is a SQL Server or ADO error rather than a Sybase error. I checked some sites on Sybase and I can't even find an error code 203 for Sybase. It jumps from error code 201 to error code 205.

  The first thing I would try is a different syntax.

  I use either of these syntaxes for SELECT, INSERT, UPDATE and DELETE statements:

  Code Snippet

  SELECT * FROM OPENQUERY (DataSourceName, 'QueryString');

  SELECT * FROM linked_server_name.catalog.schema.object_name;

  I use this syntax for stored procedures:

  Code Snippet

  EXECUTE ('QueryString') AT LinkedServerName

  The four-name seems to only work with a SQL Server linked server. If you look at the Books Online article "EXECUTE (Transact-SQL)", you'll see that the four-part name examples they give link to SQL Server linked servers. Linked servers that connect to other providers use the syntax I showed you above.

  To give you an example, I have a LinkedServer named "STAGING_STAG" and I want to run a stored procedure in the "staging_stag" database named "dvUpdateExtractionDates". I'm going to run a Sybase stored procedure from within Management Studio.

  If I use the four-part name syntax (linked_server_name.catalog.schema.object_name),

  Code Snippet

  EXECUTE STAGING_STAG.staging_stag.dbo.dvUpdateExtractionDates 100

  I get this error:

  OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "STAGING_STAG" returned message "[Native Error code: 911]
  [DataDirect ADO Sybase Provider] Attempt to locate entry in sysdatabases for database 'staging_stag" ' by name failed -
  no entry found under that name. Make sure that name is entered properly.".

  Msg 7212, Level 17, State 1, Line 1
  Could not execute procedure 'dvUpdateExtractionDates' on remote server 'STAGING_STAG'.

  But if I use this syntax,

  Code Snippet

  EXECUTE ('dbo.dvUpdateExtractionDates 100') AT STAGING_STAG

  It works.

  Try your procedures in Management Studio first, if they work there, see if they work from within your application code.

  
  

• d3v

  I followed the steps to set up a linked server to my Sybase 12.5. I can run any query such as:

  select * from syb.ltms.dbo.tblUser

  But, when i run a stored procedure like:

  exec syb.ltms.dbo.spGetUser

  I get the error:

  [Native error code: 203]

  ....

  ....

  [Data Direct ADO Provider] The name "ltms" is an invalid identifier.

  Why is this so

  
  

• JimmyG

  when I try to run a query, I got the following error:

  Cannot open the table "tableName" from OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "LinkedServerName". Unknown provider error.

  Any thoughts

  
  

• kingtaj

  I finally found a solution!

  There is a helpful document on the Sybase website titled "Configuring a Microsoft SQL Server Linked Server with Sybase ASE".

  You'll find it here: http://www.sybase.com/content/1029118/1029118.doc

  After reading this document, I learned about an additional configuration setting called "Allow inprocess". In SQL Server 2000, you would find this option by clicking the "Provider Options" button in the "New Linked Server" dialog box. You do not have this button in the SQL Server 2005 dialog box. So the question is, where is it

  You will find it in the "Providers" folder under "Server Objects --> Linked Servers" in Object Explorer. Expand the tree of providers, then right-click on "Sybase.ASEOLEDBProvider". This will open the "Provider Options" dialog box in which you can enable this configuration option.

  Once I enabled "Allow inprocess", I was able to issue SELECT statements against the Sybase database. However, I could still not execute stored procedures against this database. This was also an easy fix. All I had to do was enable "Rpc Out" in the "Server Options" tab of the :"Linked Server Properties" dialog box for the linked server I created.

  I am now able to connect to the Sybase linked server and run my queries and stored procedures.

  Here are the steps I followed in order to create the linked server:

  1) Install the Sybase ASE client on the server

  2) Configure a Data Source in the Sybase Configuration Manager. In my case, I named it "eurserv6StagingStag".

  3) Configure the Linked Server

  Code Snippet

  EXEC master.dbo.sp_addlinkedserver

  @server = N'STAGING_STAG',

  @srvproduct=N'eurserv6StagingStag',

  @provider=N'Sybase.ASEOLEDBProvider',

  @datasrc=N'eurserv6StagingStag'

  Note that the "Product Name" (@srvproduct) and the "Data Source" (@datasrc) are both the name of the Data Source I created in Step 2

  Code Snippet

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'data access', @optvalue=N'true';

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'rpc out', @optvalue=N'true';

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'connect timeout', @optvalue=N'0';

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'collation name', @optvalue=null;

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'query timeout', @optvalue=N'0';

  EXEC master.dbo.sp_serveroption @server=N'STAGING_STAG', @optname=N'use remote collation', @optvalue=N'true';

  All other options are set to "False".

  Code Snippet

  EXEC master.dbo.sp_addlinkedsrvlogin

  @rmtsrvname=N'STAGING_STAG',

  @useself=N'False',

  @locallogin=NULL,

  @rmtuser=N<RemoteUsername>,

  @rmtpassword=<RemoteUsernamePassword>;

  3) Configure the Linked Server Provider (Sybase.ASEOLEDBProvider)

  a) Browse to "Server Objects --> Linked Servers --> Providers" in Object Explorer

  b) Right-click "Sybase.ASEOLEDBProvider" and select "Properties"

  c) Check the checkbox for "Allow inprocess"

  d) Click "OK"

  
  
• Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?