Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/101073

From BetaArchive Wiki
Knowledge Base


ACC: Connecting to SYBASE SQL Server from an ODBC Application

Article ID: 101073

Article Last Modified on 1/26/2005



APPLIES TO

  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Visual Basic 3.0 Professional Edition
  • Microsoft FoxPro 2.5b



This article was previously published under Q101073

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

You can connect to a SYBASE SQL Server computer from an ODBC application (such as Microsoft Access, Microsoft Visual Basic, or Microsoft FoxPro for Windows) using the ODBC SQL Server driver. This article describes how to set up the ODBC data sources.

If you are already connected to SYBASE SQL Server from Microsoft Windows DB-Library applications, no other components are necessary.

MORE INFORMATION

The key to both Microsoft SQL Server and SYBASE SQL Server connectivity is a module called the 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; however, the application interface remains the same.

Because ODBC, DB-Library, APT-SQL, APT-Library, and so forth communicate with the Net-Library, these utilities can be written independent of the network. The ODBC SQL Server driver, in particular, can be used to connect either to a Microsoft SQL Server computer or to a SYBASE SQL Server computer (on a UNIX or VMS system), as long as there is a Net-Library for whatever network you are using.

The following paragraphs discuss how to connect to a SYBASE SQL Server from an ODBC application. The remainder of the article assumes that you are connected to a SQL Server called "Mysqlsvr." This server should be set up as an ODBC data source, using the ODBC Control Panel utility, as follows:

  1. Open ODBC Control Panel and click Add.
  2. Select SQL Server from the list of installed drivers and click OK.
  3. In the ODBC SQL Server Setup dialog box, type a data source name and a description (optional).


Note that a single server can function as multiple ODBC data sources, since each database in the server can be a data source. The data source name need not be the same as the server name.

  1. Enter the true name of the server, then enter the network address.


Because a SYBASE SQL Server on UNIX uses sockets and not named pipes, the network address must contains the IP address and the port identification number. This string is in the form "ip_address,port" (for example, "11.1.14.40,3180").

For a SYBASE SQL Server on a VAX system, this string should be in the form "<node address>,<process_id>", where <node address> is the DECnet node address of the server and <process_id> is the process identification number to use for the connection (for example, "1.997,141").

The Network Library field should contain the name of the Net-Library you need to use. This is dependent on what network you are using. For example, if you are using the FTP Software product PCTCP, the Net-Library name is "wdbftptc".

  1. Click Options to specify a database name or a language name.
  2. Click OK to go back to the first screen.
  3. Run the INSTCAT.SQL script file.


NOTE: This is a file containing SQL statements that will create certain stored procedures needed to process ODBC calls. Without these procedures, Microsoft Access cannot attach a SQL Server table and Visual Basic is not able to function correctly.

At the UNIX command line or the MS-DOS prompt, type the following:

isql -S<servername> -Usa -P<sa-password> -i<path>\instcat.sql

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

   <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 will contain the following entry:

   data-source-name=<net-library-name>,<network address>
                


The following products are manufactured by vendors independent of Microsoft: DECnet, VMS, and MicroVAX by Digital Equipment Corporation; FTP Software by FTP Software, Inc.; DB-Library, Net-Library, and SYBASE SQL Server by Sybase, Inc.; and UNIX by UNIX System Laboratories. We make no warranty, implied or otherwise, regarding these products' performance or reliability.

REFERENCES

For more information about setting up Microsoft Access to use SQL databases, see Microsoft Access "User's Guide," version 2.0, Chapter 1, "Setting Up Microsoft Access," page 8

For more information about installing stored procedures using the Microsoft Access Help menu, make sure you have set up a SQL Server datasource using the ODBC Administrator in the Windows Control Panel. Then, search for "Sybase SQL Server," and then "Installing ODBC Drivers and setting up Data Sources." In the Installing ODBC Drivers And Setting Up Data Sources Help window click the "SQL Server Driver" hotlink. In the SQL Server Driver Help window click the "Installing the Catalog Stored Procedures" hotlink.


Additional query words: third-party

Keywords: kbinfo kb3rdparty kbusage KB101073