I want to create a linked server from Sql2000 to Sybase and from Sybase to Sql2000

-- Anonymous, June 26, 2004

Answers

Dugar,

This detailed how-to was originally provided by Haiwei Xu at microsoft.public.sqlserver.server.

To make a linked server to Sybase, you can use Sybase's OLEDB provider, or use MS OLEDB for ODBC to connect to a Sybase's ODBC Driver.

- If you are using Sybase's ODBC driver, then make a ODBC DSN first, test the connection. Then make a linked server, specify the provider to be OLEDB for ODBC, and pass the DSN name for the "Data Source".

- Sybase introduced a OLEDB provider only from Sybase version 12 onwards. If the Sybase server is on 11, but the customer loads client tools of Sybase 12 on the SQL Server box, then you can use the OLEDB provider to connect to the Sybase server.

- You could also download the latest version from http://www.sybase.com. - If you download from the Sybase site, and you wish to install the client tools only; then you do NOT need to install the "Adaptive server Enterprise", "Full Text Search", "Infomaker", "Power Dynamo" "SQL modeler", "SQL Remote for ASE". The "Adaptive server Enterprise" is the main Sybase server, which also gets downloaded as a trial version.

- Once the client tools are setup, you have to specify which Sybase servers are available on the network and what port number they are listening on. For this, you have tp modify the SQL.ini file manually, or use the "Dsedit" utility (\sybase\bin\dsedit.exe) to setup which servers you can connect to.

- To Create an entry for the Sybase server you will have to add a new ServerObject. A ServerObject in Sybase is similar to an advanced entry in Microsoft SQL server client network utility. You will need to know the Machine name for the Sybase installation and also the port number on which the server is set to listen.

- After these steps, you need to reboot the SQL Server machine so that the registry is updated properly.

- Make sure you can connect to the Sybase server using Sybase's client tools. You can use ISQL (\Sybase\OCS-12_0\bin\isql.exe) or use the "SCView" utility (\Sybase\Sybase Central 3.2\win32 \scview.exe).

- For the ISQL utility, the parameters it accepts is similar to SQL server's ISQL utility.

- For the SCView utility, choose Tools-Connection profiles-New. Once the profile is setup properly, Connect to it.

- You should be able to see the various databases from that server.

Once you make sure that you can connect using Sybase's client tools, then you can setup the linked server from SQL.

- Setup a linked server by choosing Sybase's OLEDB provider, or MS OLEDB for ODBC if you have a ODBC DSN already setup.

- Specify the appropriate Security options and provide the account that it would use to map the users connecting to the linked server.

- Check the "Allow in Process" in provider options else you could get the following error when trying to query the linked server: Server: Msg 7302, Level 16, State 1, Procedure sp_catalogs, Line 4 Could not create an instance of OLE DB provider 'Sybase.ASEOLEDBProvider'. OLE DB error trace [Non-interface error: CoCreate of DSO for Sybase.ASEOLEDBProvider returned 0x80040154].

- Check the "Collation Compatible", and uncheck the "Use Remote Collation" linked server option, for proper remoting of where clauses of character values.

- Create the linked server and test it using simple select statements. (Try Openquery syntax or simply clicking on "tables" in Enterprise Manager)

- "sp_catalogs" procedure is not supported by the provider.

- You may get a 7399 error that the remote table could not be found when you execute the query with 4 part naming scheme. For this, just make sure that the owner/schema name is appropriately named. Use the "sp_tables_ex linked_server_name" to get the catalog name / schema name / table name. Make sure that the "TABLE_TYPE" is "TABLE" and not Synonym / Alias etc.

Hope this helps,

Eric

-- Anonymous, June 28, 2004