Creating Linked server to SYBASE from SQL Server

In this article we going to look into creating a heterogeneous linked server. i.e. We are going to create a linked server to SYBASE database server from SQL Server environment and then going to query datas from Sybase. First step in heterogeneous linked server is to create a ODBC connection to that RDBMS platform. For creating ODBC connection we need the ODBC drivers for that RDBMS platform. In this case we need Sybase driver in client machine, this is not included in windows so you need to download it from Sybase Website. Lets discuss on this step by step

Testing Environment

Windows 7
SQL Server 2008
Sybase ASE Developer Edition 15.02
Sybase ODBC Driver 3.51
Sybase DBName : sqlarticles (authors table with 4 test records)

Implementation Steps

Download Sybase ODBC drivers from the link Get Sybase ODBC Driver . Please note from Sybase ASE 15, Sybase will not ship the 3rd party DataDirect ODBC driver, Sybase has developed their own ODBC driver and it will be shipped.
Install the Sybase driver from the downloaded setup.
Once installation completed go to Run> Type odbcad32 and press enter
In the ODBC admin window go to System tab as shown below

sybase_linked_server_1

When you click on Add button a list of ODBC drivers available in the system will be shown, select Adaptive Server Enterprise (this will not be displayed if you havent installed Sybase ODBC Drivers) and then click finish button

sybase_linked_server_2

Now you will be shown with the ODBC driver configuration, all the fields in the window are self explanatory hence fill all the details required (you can also go to other tabs for other ODBC options) and click on Test Connection button to test the ODBC connection. Now another window will pop up to enter the password, just enter the credential and click OK as shown below

sybase_linked_server_3

sybase_linked_server_4

Now you have created the ODBC connection successfully, the next step is to create a new linked server in SQL Server.
Open Management studio and connect to the SQL Server Instance
Expand Server Objects in object explorer and right click on Linked Servers folder and then click on New Linked server in the menu as shown below

sybase_linked_server_5

New Linked server window will popup, fill all the details required. Provide the name for the Linked server, Select Other Data Source Option button, Select Provider as Microsoft OLE DB Provider for ODBC Drivers and provide the ODBC name in the field Data Source.

sybase_linked_server_6

Since we dont have the facility to enter the credential details in ODBC, we need to update the details here. To accomplish this, click on the Security tab in the left hand pane and provide Sybase credentials as shown below

sybase_linked_server_7

Thats it we have created a linked server to Sybase server from SQL.

Querying Data from Sybase Linked Server

Connect to SSMS and open a new query window and type the query to retrieve datas from Sybase linked server.

select name from sybase.sqlarticles.dbo.authors

sybase_linked_server_8

Posted

December 5, 2009

DBA

VidhyaSagar

Tags:

administration, linked server, management, sybase

Comments

40 responses to “Creating Linked server to SYBASE from SQL Server”

mike

June 29, 2012

Thank you so much!!! Working great!!!!

Reply
Can’t migrate from Sybase ASA to SQL Server using SSMA – SQL Server Migration Assistant (SSMA) Team's Blog – Site Home – MSDN Blogs

February 15, 2013

[…] : http://sql-articles.com/articles/dba/creating-linked-server-to-sybase-from-sql-server/ (These are for ASE but are the same for ASA except that you have to choose a different […]

Reply
Andres Gutierrez

June 5, 2013

Hello, I am trying to make this procedure but when I test the connection, right after I give the information on the Login window, appears the following error:

[Sybase][ODBC Driver][Adaptive Server Enterprise]SQL Anywhere Error -157: Cannot convert ’08/10/09′ to a timestamp

Do you have any idea or suggestion to fix this problem? I dont know where that date comes from. Regards. Andres.

Reply
1. VidhyaSagar
  
  June 14, 2013
  
  @Andres – Sybase date format is not matched with SQL Server date format. If you are using ODBC driver then update in ODBC entry for AFTER connecting.
  
  set temporary option date_order=’MDY’
  
  If this isnt working then check SQL Server default date format and then change accordingly
  
  Reply
  1. Andres Gutierrez
    
    June 20, 2013
    
    Thanks for your answer Vidhya. However, I have this issue even before I configure the linked server in SQL Server Management Studio. This error message appears when I am configuring the DSN in the ODBC Drivers Configuration Window.
    
    Right before the part where you write “Now you have created the ODBC connection successfully…”. In that previous image, instead of “Login Succeeded” appears the error message that I wrote before:
    
    [Sybase][ODBC Driver][Adaptive Server Enterprise]SQL Anywhere Error -157: Cannot convert ’08/10/09′ to a timestamp
    
    Thanks for your suggestions. Regards, Andres.
    
    Reply
FERNANDO SOUZA DE ALMEIDA

July 2, 2013

Sybase Anywhere is the same thing?

Reply
1. VidhyaSagar
  
  July 4, 2013
  
  Yes
  
  Reply
ashwini

September 6, 2013

after following the above steps..still i m getting error
cannot initailze data source objectof OLE DB provider for LINKED SERVER “SYBASE”

Reply
1. VidhyaSagar
  
  September 9, 2013
  
  Can you share me the screenshot of odbc driver setting?
  
  Reply
Abhi

October 23, 2013

Hi,

I am unable to query sybase system tables using linked servers.Could you please help me?

Reply
1. VidhyaSagar
  
  October 24, 2013
  
  @Abhi — What is the error message you are getting?
  
  Reply
Jaya

December 3, 2013

Hi

I am trying to execute stored procedure in sybase using linked server but i get below error message:

OLE DB provider “MSDASQL” for linked server “” returned message “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”.

Could you please help?

Thanks in advance.

Reply
1. VidhyaSagar
  
  December 16, 2013
  
  @Jaya – @Check whether the data types between the source and target are similar
  
  Reply
  1. jaya
    
    January 16, 2014
    
    Hi I have checked the datatypes they are simialr. I am trying to execute the stored procedures in below ways
    
    Execute [LINKEDSERVER].[DB].[dbo].[StoredProcedure] @P1, @P2, @P3, @P4, @P5
    
    declare @rowcount NUMERIC(38)
    declare @error_code NUMERIC(6)
    declare @error_desc VARCHAR(250)
    
    Execute [LINKEDSERVER].[DB].[dbo].[StoredProcedure] @P1, @P2, @P3, @P4, @P5, @rowcount Output, @error_code Output, @error_desc Output
    Select @rowcount As Row_Count,@error_code As Error_Code,@error_desc As Error_Desc
    
    Exec (‘EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?’,@P1, @P2, @P3, @P4, @P5) AT [LINKEDSERVER]
    
    But i am getting below metioned error
    
    Msg 7213, Level 16, State 1, Line 1
    The attempt by the provider to pass remote stored procedure parameters to remote server ‘LINKEDSERVER’ failed. Verify that the number of parameters, the order, and the values passed are correct.
    
    I am using ASEOLEDB provider or MSDASQL provider to test. I have checked no. of parameters & their data types, they all match. The same Execute statement works in sybase.
    
    I hava also enabled Dynamic Parameters in Provider properties.
    
    Please help me with this issue.
    Regards
    Jaya
    
    Reply
    1. jaya
      
      January 16, 2014
      
      Thanks Issue is resolved
      
      Reply
      1. jaya
        
        January 16, 2014
        
        This works for dynamic parameters
        
        Declare @Parameter1 NUMERIC(4)
        Declare @Parameter2 VARCHAR(1)
        Declare @Parameter3 DATETIME
        Declare @Parameter4 NUMERIC(8)
        Declare @Parameter5 VARCHAR(12)
        Declare @Parameter6 VARCHAR(28)
        Declare @Parameter7 NUMERIC(38)
        Declare @Parameter8 NUMERIC(6)
        Declare @Parameter9 VARCHAR(250)
        
        select @Parameter1 = 1
        select @Parameter2 = ‘A’
        select @Parameter3 = NULL
        select @Parameter4 = NULL
        select @Parameter5 = NULL
        select @Parameter6 = NULL
        select @Parameter7 = NULL
        select @Parameter8 = NULL
        select @Parameter9 = NULL
        
        EXEC (‘EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?,? ,? ,? ‘,@Parameter1,@Parameter2,@Parameter3,@Parameter4,@Parameter5,
        @Parameter6,@Parameter7 Output,@Parameter8 Output,@Parameter9 output) AT [LINKSERVER]
Pavithra

December 4, 2013

Hi,

I have installed Adaptive server Anywhere 11.0.1(ASA client) to create the system DSN.
using the ODBC from “c:\windows\system32\odbcad32.exe”
when i try to use this DSN created as data source for the linked server

I am getting the below error “The specified DSN contains an architecture mismatch between the driver and Application(Microsoft SQL Server ,error 7303)”

Please help me to solve this issue
Thanks in advance

Reply
1. VidhyaSagar
  
  December 14, 2013
  
  Is your operating system 32 bit or 64 bit?
  Is your Sysbase driver 32 bit or 64 bit?
  
  Reply
Mark

January 31, 2014

Your instructions are very good but I can’t get mine to work.
Can you help?
Error message: “The linked server has been created but failed a connection test… Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “TESTDB14”. OLE DB provider “MSDASQL” for linked server “TESTDB14” returned message “[Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server, Error: 7303)

Thanks,
Mark

Reply
1. VidhyaSagar
  
  February 5, 2014
  
  @Mark – Did you created the DSN alias properly? Are you able to test connection from DSN?
  
  Reply
  1. VidhyaSagar
    
    February 5, 2014
    
    @Elvina — State 8 represents password mismatch. Please make sure you type the password correctly. You can type it in a notepad and then copy paste it
    
    Reply
Data

February 18, 2014

Hi.. i have tried the above instruction. but i got the error below..

OLE DB provider “SQLNCLI11” for linked server “SYBASE” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI11” for linked server “SYBASE” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Please advice

Reply
susanne

March 16, 2014

Hi,

i have tried to create the linked server for my odbc source (Adaptive Server anywhere 7.0, working fine with odbc test connection). OS 64bit. when creating the linked server, it says:

Cannot initialiye the data source object of OLE DB provider “MSDASQ:” for linked server “SYBASE”. OLE DB provider “MSDASQL” for linked server “SYBASE” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)

Do you have any idea how to solve that?

Reply
1. VidhyaSagar
  
  March 19, 2014
  
  @Susanne — You need to install 64 bit sybase driver and not 32 bit.. Can you please verify that?
  
  Reply
  1. susanne
    
    March 19, 2014
    
    thanks for your answer 🙂 ASA 7 is very old and does not provide 64bit drivers. what driver shall i take to make it work?
    
    Reply
    1. susanne
      
      March 19, 2014
      
      thanks, i found another solution
      
      Reply
      1. ane
        
        August 11, 2014
        
        Hi Susanne,
        I have the same issue now as what you had.
        Can you please teach me how did you solve it?
        Your reply will be much appreciated.
        Thank you very muuch.
David

April 15, 2014

Tryng to setup a linked server between ASE 15.7 and SQL server 2008. I’ve installed 64 bit ASE drivers and have successfully tested the connection and can log on and query the ASE database via the Sybase Central app.

DSN = BARCODE

When trying to set up a Linked Server I get the “cannot initialize data source object of OLE DB provider “ASEOLEDB”

Parameters for Linked server are
Linked Server = BARCODE
Provider = Sybase OLEDB Provider
Product Name = sybase
Data Source = BARCODE

What am I missing?

Reply
Ekbal

July 17, 2014

Hi, Can anyone help me with this issue. i am trying to link server SQL Server 2008 with Sybase but i cant get pass the the error below.

Sybase Driver: Adaptive Server Anywhere 9.0

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “SYBASE”.
OLE DB provider “MSDASQL” for linked server “SYBASE” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)

Reply
1. VidhyaSagar
  
  August 9, 2014
  
  @Ekbal
  Im going to revalidate this with respect to current drivers, give me a week for udpate.
  
  Reply
  1. ane
    
    August 11, 2014
    
    Hi,
    I am dealing this same issue now.
    Is ASE PC Client 64-bit free for download?
    If anyone has, please share it to me.
    Thank you very much.
    
    Reply
  2. Mike Brown
    
    January 17, 2015
    
    Is there any resolution to this. I am experincing the same problem.
    
    Reply
    1. VidhyaSagar
      
      February 4, 2015
      
      Only thing download right driver..
      
      Reply
2. mike
  
  December 17, 2014
  
  Ekbal,
  Have you resolved the issue? I am having the same problem. SQL 32 bit is fine but we are moving to 2008 64 bit and the to 2012 4 bit If you have a solution, please share.
  Thanks
  
  Reply
Pravin

September 25, 2014

I am getting following error “The operation could not be performed because OLE DB provider “ASEOLEDB” for linked server “SYBASELinkedServer” was unable to begin a distributed transaction.” when using the below command. Please advise.

INSERT INTO EXEC )

Reply
Fix Sybase Odbc Driver Adaptive Server Anywhere General Error Windows XP, Vista, 7, 8 [Solved]

November 24, 2014

[…] Creating Linked server to SYBASE from SQL Server | … – Tryng to setup a linked server between ASE 15.7 and SQL server 2008. I’ve installed 64 bit ASE drivers and have successfully tested the connection and can log on …… […]

Reply
Umakant

October 19, 2015

Link given above to download ‘Sybase ODBC Driver ‘ is not available anymore.

Does anyone know where can this be downloaded from?

Reply
1. VidhyaSagar
  
  October 19, 2015
  
  Yep!! check with Sybase websites. It’s been many years I wrote this article.
  
  Reply
joy

January 7, 2017

Hello!!
I can’t unable to connect Sybase server on win-server 2008 R2?

Reply
ekom

February 13, 2017

Hello,

I got the followin error:

OLE DB provider “SQLNCLI11” for linked server “SYBASE” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI11” for linked server “SYBASE” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

I have already created ODBC connection and the connection test to the remote sybase server was succesful.

Please help

Reply

Creating Linked server to SYBASE from SQL Server

Comments

40 responses to “Creating Linked server to SYBASE from SQL Server”

Leave a Reply Cancel reply