Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

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

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

  • Comments 2

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 http://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 http://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

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • After setting OLEDB SSIS getting error

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "DataDirect.SSISOLEDBProviderforSybase" for linked server "SYD" reported an error. Access denied.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "DataDirect.SSISOLEDBProviderforSybase" for linked server "SYD".

  • Please try below and see if it resolves the error that you are getting.

    1. Change the value of "Allow in Process" in the properties of "DataDirect SSIS OLE DB Provider for Sybase" under "Server Objects" --> "Linked Servers" --> Providers. It will probably be unchecked so check it and see if it resolves the error.

    With regards to Linked Server -

    A. ‘Allow In Process’ checked for a provider means that SQL Server process will host the provider DLL and run the linked server inside SQL Server Process - so, permissions for SQL Server service account comes into play while accessing DLLs or registry information related to the provider.

    B. ‘Allow In Process’ unchecked for a provider means that a DLLHost process will host the provider and run the linked server.

    2. If the above doesn't help, uncheck the "Allow in Process" option and then follow the steps below on SQL Server computer -

    1. Start --> Run --> Dcomcnfg

    2. Component services --> Computers -> My computer --> DCOM config --> MSDAINITIALIZE

    3. Right click on MSDAINITIALIZE --> properties -->security

    4. Add the SQL Server service startup account under "launch and activation permission", "Access permission" and "Configuration permission" - you may need to select the option ‘customize’ and then click edit.

    Ensure Allow on all permissions.

    5. Reboot the SQL Server machine.

Page 1 of 1 (2 items)