Freigeben über

How to create a linked server in SQL Server 2005 for Sybase ASE 12.5 database using DataDirect's ODBC Driver and OLEDB provider?

  • Artikel

Linked server in SQL Server 2005 to Sybase ASE 12.5 can be created with both ODBC driver and OLEDB provider. Below are the steps.

A. Creating Linked server for Sybase ASE 12.5 from SQL Server using DataDirect Sybase Wire Protocol ODBC driver

Install the DataDirect ODBC Driver for Sybase on the machine running SQL Server. Evaluation version is available at https://www.datadirect.com/downloads/index.ssp

Before creating a linked server, follow below steps to create and configure a DSN.

1. Open ODBC Data Source Administrator. Shortcut - "Start-->Run-->OdbcAd32".

2. Click System DSN tab and then click Add.

3. In the "Create New Data Source" window, choose "DataDirect 5.3 Sybase Wire Protocol" from the drivers available and click Finish.

4. In the "ODBC Sybase Wire Protocol driver Setup" window, specify some name for the DSN in the "Data Source Name" textbox and provide some description in the "Description" textbox.

5. Either specify Sybase server name and port no separated by comma in the "Network Address" textbox or specify the path of the interfaces file in the "Interfaces file" textbox and Server name in the "Server name" textbox. The contents of the interfaces file (.ini) file will look like below.

[server-name]

master=master-database-name,server-name,port-no

query=master-database-name,server-name,port-no

6. Specify database name in the "Database Name" textbox.

7. Click "Test Connect" button and type user name and the password for the Sybase logon account. Then click OK button and you will get "Connection established!" message if the connection succeeds.

8. Click OK in the "ODBC Sybase Wire Protocol driver Setup" window and then click OK in the "ODBC Data Source Administrator" window.

Once the DSN is created, create a linked server using steps below.

1. Open SQL Server Management Studio and connect to the SQL Server. Expand "Server Objects" and then "Linked Servers".

2. Right click on "Linked Servers" and select "New Linked Server".

3. In the "Linked Server" textbox, give some name to the linked server.

4. Select "Other data source" for "Server Type" and then choose "Microsoft OLE DB Provider for ODBC Drivers" in the "Provider" dropdown.

5. Type "Sybase" for the "Product name".

6. Type "DSN=<name-of-the-DSN>" in the "Provider String" textbox where <name-of-the-DSN> is the DSN created in above steps.

7. Type database name in "Catalog" textbox.

8. Click "Security" on the left hand side and type Sybase user name and password by selecting the option "Be made using this security context" option.

9. Click OK

11. Run a query against the linked server created and verify that it executes successfully.

 

B. Creating Linked server for Sybase ASE 12.5 from SQL Server using DataDirect Sybase Wire Protocol OLEDB Provider

Install the DataDirect OLEDB Provider for Sybase on the machine running SQL Server. Evaluation version is available at https://www.datadirect.com/downloads/index.ssp by the name “SSIS 64-bit” (32-bit as well as 64-bit provider download)

Before creating a linked server, follow below steps to create and configure a Data Source.

1. Got to "All Programs"-->"DataDirect Connect64 for SSIS 1.0.1"-->"SSIS Configuration Manager"

2. Right click on "Data Sources" and select "New Data Source".

3. In the "New Data Source" window, give some name to data source name.

4. Choose "Datadirect SSIS OLE DB Provider for Sybase" from the provider dropdown and click "Set Up Data Source" button.

5. In the "SSIS Sybase Wire Protocol driver Setup" window, provide some description in the "Description" textbox.

6. Either specify Sybase server name and port no separated by comma in the "Network Address" textbox or specify the path of the interfaces file in the "Interfaces file" textbox and Server name in the "Server name" textbox. The contents of the interfaces file (.ini) file will look like below.

[server-name]

master=master-database-name,server-name,port-no

query=master-database-name,server-name,port-no

7. Specify database name in the "Database Name" textbox.

8. Click "Test Connect" button and type user name and the password for the Sybase logon account. Then click Connect button and you will get "Successfully Connected. Data Source Configuration Verified" message if the connection succeeds. Click OK.

9. Click OK in the "SSIS Sybase Wire Protocol driver Setup" window.

10. You will get an .ids file created for the name of the data source that you created in above steps. Default path is "\Documents and Settings\user-name\My Documents\DataDirect\SSISDataSources".

Once the .ids file is created for the data source, create a linked server using steps below.

1. Open SQL Server Management Studio and connect to the SQL Server. Expand "Server Objects" and then "Linked Servers".

2. Right click on "Linked Servers" and select "New Linked Server".

3. In the "Linked Server" textbox, give some name to the linked server.

4. Select "Other data source" for "Server Type" and then choose "DataDirect SSIS OLE DB Provider for Sybase" in the "Provider" dropdown.

5. Type "Sybase" for the "Product name".

6. Type the name of the data source created in the above steps in the "Data source" textbox (This will actually be the name of the ids file without an extension).

7. Type "Provider=DataDirect.SSISOLEDBProviderforSybase.1;Persist Security Info=True;" in the "Provider String" textbox.

8. Type database name in "Catalog" textbox.

9. Click "Security" on the left hand side and type Sybase user name and password by selecting the option "Be made using this security context" option.

10. Click OK

11. Run a query against the linked server created and verify that it executes successfully.

 

Author : Deepak (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Smat (MSFT) , SQL Escalation Services, Microsoft

Comments

  • Anonymous
    December 03, 2009
    The comment has been removed

  • Anonymous
    December 06, 2009
    The comment has been removed

  • Anonymous
    May 12, 2015
    NIce

  • Anonymous
    October 20, 2015
    hi, i followed same steps, it's showing error while i am testing connection(Step 8) please suggest any hint. Test connection failed Error description : [DataDirect][OLE DB Sybase Wire Protocol provider][Sybase ASE]SQL Anywhere Error 265: Procedure ‘sp_server_into’ not found