How to set up a linked server to a Sybase database server and troubleshoot problems that may occur (280102)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q280102

IN THIS TASK

SUMMARY

This step-by-step article describes how to set up a linked server to a Sybase database server on a computer that is running Microsoft SQL Server. Additionally, this article contains basic troubleshooting steps for problems that may occur when you set up a linked server to a Sybase database server.

back to the top

Set up a linked server to a Sybase database server

To set up a linked server to a Sybase database server, follow these steps:
  1. Log on to the computer that is running SQL Server.
  2. Install the Sybase client software on the computer.
  3. To connect to the Sybase database server, install the appropriate driver on the computer. You can use the Microsoft OLE DB Provider for ODBC to connect to the Sybase database server.

    Note You can also use a third-party driver or provider to connect to the Sybase database server. If you use a third-party driver or provider, you must contact the third-party vendor for any problems that occur with the driver or with the provider.
  4. After you install the Sybase client software, restart the computer.
  5. Make sure that you can successfully connect to the Sybase database server by using the Sybase client software that you installed in step 2.
  6. Start SQL Query Analyzer, and then run the following Transact-SQL statement to set up a linked server to a Sybase database server.

    Note This script uses the Microsoft OLE DB Provider for ODBC to connect to the ODBC driver for Sybase. Replace Sybase Linked Server Name, Sybase System DSN, User Name, and Strong Password with the appropriate linked server name, System DSN, user name, and password. 
    -- Adding linked server:
 exec sp_addlinkedserver @server = 'Sybase Linked Server Name' ,
@srvproduct = 'Sybase',
@provider = 'MSDASQL',
@datasrc = 'Sybase System DSN'
 
-- Adding linked server login:
sp_addlinkedsrvlogin @useself='false ', @rmtsrvname = 'Sybase Linked Server Name',
@rmtuser = 'User Name',
@rmtpassword = 'Strong Password'
  7. To make sure that the linked server is set up correctly, run the following Transact-SQL statement:
    EXEC sp_linkedservers
    The name of the linked server that is connected to the Sybase database server may be listed in the output.
back to the top

Troubleshoot problems that may occur

If a problem occurs when you set up a linked server to a Sybase database server, follow these steps to troubleshoot the problem:
  1. Make sure that the Sybase client software is correctly installed on the computer that is running SQL Server.
  2. Make sure that the following paths are included in the PATH environment variable on the computer:
    • Sybase_Home_Path\bin
    • Sybase_Home_Path\dll
    • Sybase_Home_Path\asep
    To do so, type path at a command prompt, and then press ENTER.
  3. Verify the network connectivity between the client computer and the server computer. To do so, follow these steps:
    1. On the computer that is running SQL Server, type the following command at a command prompt, and then press ENTER:
      ping SybaseServerName
    2. On the Sybase database server, type the following command at a command prompt, and then press ENTER:
      ping SQLServerName

      If you cannot ping the server by using the server name but you can connect to the servers by using the IP address, contact your network administrator to resolve the problem.
    3. Check to see if you can connect to the Sybase database server by using the Sybase client software that you installed on the computer that is running SQL Server.
    4. If a distributed transaction is configured, make sure that the Microsoft Distributed Transaction Coordinator (MS DTC) service is running.
    5. Use the sp_addlinkedsrvlogin system stored procedure to map to the correct authentication information about the Sybase database server, and then connect to the Sybase database server.
    6. Use the client tools to configure and to test the connection to the Sybase database server.
  4. Make sure that the ODBC DSN-related configurations are correct. To do so, follow these steps:
    1. Use the ODBC Test tool or similar software to test the DSN configuration.
    2. Create a new DSN for the Sybase database server, and then test the connectivity to the Sybase database server by using the new DSN.
    3. If the issue with DSN is not resolved, add the linked server to the Sybase database server with a provider string by using a SQL script that is similar to the following:
      EXEC sp_addlinkedserver 			
@server = 'Sybase Linked Server Name',	
@srvproduct='Sybase'
@provider = 'MSDASQL' ,
@provstr = 'DRIVER={<Driver Name};SRVR=Sybase Server Name;UID=User Name;PWD=Strong Password'
  5. Verify the Microsoft Data Access Components (MDAC) installation and the Sybase ODBC driver installation. To do so, follow these steps:
    1. Install the latest version of MDAC. For more information about the latest version of MDAC, visit the following Microsoft Web site:

      Data Access Technologies

    2. Make sure that the following registry entry for the ODBC System DSN contains the correct value for the driver path:

      HKEY_LOCAL_MACHINE\Microsoft\Software\ODBC\odbc.ini\<ODBC System DSN>\Driver

      By default, the value of the registry entry is C:\WINNT\system32\Sysybnt.dll.
  6. Use SQL Query Analyzer to run Transact-SQL statements on the linked server to identify any errors that may occur when you connect to the linked server. To do so, follow these steps:
    1. Run the following Transact-SQL statement to make sure that the tables in the Sybase database can be viewed:
      exec sp_tables_ex 'SybaseLinkedServerName'
    2. Run the following Transact-SQL statements to query the Sybase database server by using the four-part name syntax and the OPENQUERY Transact-SQL statement: Statement 1
      DBCC TRACEON(3604, 7300)
go
SELECT * FROM SybaseLinkedServerName.DatabaseName.Owner.TableName
go
      Statement 2
      DBCC TRACEON(3604, 7300)
go
SELECT * FROM OPENQUERY(SybaseLinkedServerName, "Select * from DatabaseName.Owner.TableName")
go
      Note If you run the DBCC TRACEON (3604, 7300) statement, and then you run the Transact-SQL statement, you may see additional information in the error message. The information that you see depends on the provider that you use.

Troubleshoot common problems

If you use SQL Query Analyzer to query data from the linked server that is connected to the Sybase database server, you may experience problems. The following list includes some of the possible problems and the methods that you can use to troubleshoot each problem: Problem 1 You receive the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Check to see if the System DSN that was used to create the linked server is correct. If you used a provider string to configure the linked server, you must make sure that the driver name in the provider string is correct.Problem 2 You receive the following error message:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [INTERSOLV][ODBC SQL Server driver][SQL Server]ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Check to see if the server name that was used to configure or to create the linked server is correct. If you used a provider string to configure the linked server, make sure that the server name in the provider string is correct.Problem 3 You receive the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [INTERSOLV][ODBC SQL Server driver][SQL Server]ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.] [OLE/DB provider returned message: [INTERSOLV][ODBC SQL Server driver][SQL Server]Login failed.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Check to see if the user name and the password that were used to configure or to create the linked server are correct. If you used a provider string to configure the linked server, make sure that the user name and password in the provider string are correct. Problem 4 You receive the following error message:
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
If you use a four-part name syntax to query the data from the linked server database, you may receive this error message. To work around this problem, you can use the OPENQUERY syntax to query the data from the linked server database. You can turn on trace flag 7300 to receive more detailed information about this error message. To turn on trace flag 7300, run the following Transact-SQL statement: 
DBCC TRACEON(7300)
Problem 5 You receive the following error message:
Specified driver could not be loaded due to system error 126 (Sybase System 11)
You may receive this error message when you install the ODBC driver for Sybase. You may be able to resolve this problem by installing the latest version of MDAC. Problem 6 If you try to run the remote stored procedures on the linked Sybase database server, the linked server may stop responding. This problem occurs because SQL Server does not support running remote stored procedures on database servers other than the instance of SQL Server.

back to the top

REFERENCES

For more information about linked servers in SQL Server, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8fhv.asp

For more information about optimizing distributed queries, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6oxf.asp

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

137637 INF: ODBC connections to Sybase SQL Servers

239883 FIX: SYBASE CT-Library clients cannot connect to Microsoft SQL Server 7.0

314530 PRB: Error message 7399: "Timeout expired" occurs with linked server query

255097 BUG: Error 7356 from a distributed query

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

back to the top
Modification Type:MinorLast Reviewed:3/16/2004
Keywords:kbSybase kbtshoot KbClientServer kbHOWTOmaster kbRegistry kb3rdparty kberrmsg KB280102 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.