INF: Connecting to Sybase SQL Server from ODBC Application

ID: Q95021

The information in this article applies to:

SUMMARY

Older versions of the Microsoft SQL Server ODBC driver could be used to connect to older versions of Sybase SQL Servers. This article describes how to set up the ODBC data source for these old drivers to connect to Sybase SQL Servers. If clients are already connecting to the Sybase server from Windows DB-Library applications, no extra components are required.

MORE INFORMATION

Starting with version 2.00.1912 of the Microsoft SQL Server ODBC Driver (which shipped with SQL Server version 4.21a), Microsoft SQL Server drivers are no longer certified for use against any Sybase SQL Servers. Version 1.02.3231 or earlier drivers were certified to run against older versions of Sybase SQL Servers, but none of the Microsoft SQL Server ODBC drivers was certified to run against System/10 or later Sybase servers. Because all of the Microsoft driver versions which were certified for use against Sybase are now obsolete, it is recommended that sites wanting to connect ODBC applications to Sybase servers contact Sybase or third party ODBC driver vendors for drivers certified for use against Sybase.

The key to Microsoft and Sybase SQL Server connectivity is a module called the Network Library (Net-Library). This module consists of two interfaces - one with the network and one with the application. The network interface is customized to support a particular network, but the application interface remains the same. Because ODBC, DB-Library, APT-SQL, APT-Library, and so on communicate with the Net-Library, they can be written in a network- independent fashion. In particular, the ODBC SQL Server driver can be used to connect either to Microsoft or Sybase SQL Server (on Unix and VMS), regardless of the network you are using, as long as there is a Net-Library for that network.

The following paragraphs discuss how to connect to a Sybase SQL Server from an ODBC application. For the rest of this article, it is assumed that you are connecting to a SQL Server called "Mysqlsvr." This server should be setup as an ODBC Data Source using the ODBC Administrator utility. In the ODBC Administrator, click SQL Server from the list of installed drivers, then click Add New Name. In the ODBC SQL Server Setup dialog, type a data source name (DSN). Note that a single server can function as multiple ODBC Data Sources, because each database in the server can be a data source. Thus, the data source name does not necessarily have to be the same as the server name.

Click Add after you type the data source name. In the resulting dialog box, the Network Address field will have a default entry of the form: 

   \\DATA-SOURCE-NAME\PIPE\SQL\QUERY
This is actually a named pipe name. Because a Sybase SQL Server on Unix uses sockets rather than named pipes, you must change this name so that it contains the IP address and the port identification number. This is a string of the form "ip_address,port". For example: 

   11.1.14.40,3180
For a Sybase SQL Server on VAX, this should contain a string of the form "node address,process_id" where "node address" is the DECNet node address of the server and the "process_id" is the process identification number to use for the connection. An example entry is "1.997,141". The "Network Library" field has a default entry of dbnmp3. This is the net- library if you are using named pipes. This field should contain the name of the net-library that you will need to use, which is dependent on what network you are using. For example, if you are using FTP PCTCP, then the net-library name is wdbftptc. Note that if you are already connecting to a Sybase SQL Server from an existing Windows db-lib application, then you will have the correct net-libraries. Choose OK to get back to the initial screen. The addition of the data source is now complete.

The next step is to have the Sybase Database Administrator run the INSTCAT.SQL script file. This is a file containing SQL statements that will create certain stored procedures needed to process ODBC calls. Without these procedures, Access will not be able to attach to a SQL Server table and Visual Basic 2.0 will not be able to function correctly. To run this script file, you must use ISQL. At a Unix command line or at a DOS prompt, type: 

   isql -S<servername> -Usa -P<sa-password> -i<path>\instcat.sql
If you are running Sybase SQL Server 4.2, then use the INSTCAT.SQL that ships with the application.

Finally, note that if the above procedure is followed correctly, then the following changes will appear in the ODBC.INI and WIN.INI files. The [Data Sources] section of ODBC.INI will have an entry of the form: 

   data-source-name=SQL Server
where "data-source-name" is the name of the data source that you added. There will be a new section called "[data-source-name]" containing the location of the SQL Server driver and a description of the data source. The [SQLSERVER} section of the WIN.INI file will contain an entry of the form: 

   data-source-name=<net-library-name>,<network address>
Additional reference words: 1.00 2.00 dblib odbc connectivity sql KBCategory: kbusage KBSubcategory:

Last Reviewed: September 9, 1996