Knowledge Base

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

Article ID: 280102

Article Last Modified on 3/16/2004

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q280102

IN THIS TASK

SUMMARY
Set up a linked server to a Sybase database server
Troubleshoot problems that may occur
REFERENCES

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:

Log on to the computer that is running SQL Server.
Install the Sybase client software on the computer.
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.
After you install the Sybase client software, restart the computer.
Make sure that you can successfully connect to the Sybase database server by using the Sybase client software that you installed in step 2.
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'
```
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:

Make sure that the Sybase client software is correctly installed on the computer that is running SQL Server.
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.
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.
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'
```
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.
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: