none
Create linked server to SYBASE ASA 9.0

    Question

  •  

    Hi all,

     

    I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.

     

    1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.

    2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:

    • ODBC Tab
      • Data Source Name: dbNOM
    • Login Tab
      • Supply userID and Password: (selected)
      • User ID: DBA
      • Password: SQL
    • Database Tab
      • Server Name: dbNOM
      • StartLine: C:\Program Files\Sybase\SQL Anywhere 9\win32\dbeng9.exe
      • Database Name: (blank)
      • Database File: c:\Project Folder\DataBase.db

    As I said, when i go for "test connection", works fine.

     

    3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:

    • General
      • Linked Server: lkDbNom
      • Server Type: Other Data Source
      • Provider: Sybase Adaptive Server Anywhere Provider 9.0
      • Product Name: Sybase
      • Product Name: dbNom (the dsn name, right?)
    • Security
      • Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN)

    All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:

     

    SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')

     

    And i get the following message:

     

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'SYBASE'.

     

    Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied.
    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476


     

    So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.

     

    The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:

     

    1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.

    2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server.

    3) Created a connection in visual studio 2008  (server explorer - data connections), i can retrieve sdata...but of course the provider is  .NET Framework Data Provider for ODBC and i cannot use the same connection string.

     

    Any ideas?

     

    P.S.

    Wednesday, April 02, 2008 4:17 PM

All replies

  • Thank you for a very detailed explanation.

     

    Please see the following links:

    http://msdn2.microsoft.com/en-us/library/ms188427.aspx

    http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt 

    http://sql.dzone.com/news/querying-active-directory-thro

     

    Don't enclose SYBASE in aposhrophes in OPENQUERY.

     

    Let me know if it helps.

    Thursday, April 03, 2008 12:16 AM
  • Oops...that was an obvious mistake! But even without enclosing the linked server name it is not working, of course now i get a different error (better)

     

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "ASAProv.90" for linked server "LKDBNOM" reported an error. Access denied.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "ASAProv.90" for linked server "LKDBNOM".

     

    I think that this error and the fact that i can´t retrieve the catalog in the object explorer are related, but i really don´t get why if there is somekind of denied access when i test the connection it succeeds!!! (by the  way, my linked server name is LKDBNOM, i think i wrote it wrong on my question)

     

    Thanks for the help, any more ideas would be really appreciated.

    Thursday, April 03, 2008 3:26 AM
  • What about this:

     

    ... -> Linked Servers -> New Linked Server...

     

    Provider: Sybase Adaptive Server Anywhere Provider 9.0

    Product Name: <empty>

    Data Source: <empty>

    Provider String: Data source=<path to your file>;User Id=<login>;Password=<password>

    Catalog: <empty>

     

    This is the format of the connection strings for Sybase provider:

    Sybase ODBC Connection String

    Driver={SYBASE ASE ODBC Driver};Srvr=myservername;Uid=myusername;Pwd=mypasswd

    Sybase OLEDB Connection String

    Provider=Sybase.ASEOLEDBProvider;Server Name=myservername,5000;Initial Catalog=mydemodb;User Id=myusername;Password=mypassword

     

    You might want to play with the conection string (a.k.a Provider String) and do not use ODBC DSN unless absolutely required.

     

    Hope this works.

    Thursday, April 03, 2008 4:37 AM
  • Hey,

     

    Both connection strings won´t work because they are for ASE (Adaptive Server Enterprise) and I´m trying with ASA (Adaptive Server Anywhere). From the same page i got two more examples:

     

    http://www.connectionstrings.com/?carrier=sybase

     

    Provider=ASAProv;Data source=myASA;

     

    Provider=ASAProv.90;Eng=server.database_name;Uid=myUsername;Pwd=myPassword;Links=tcpip(Host=servername);

     

    First one, forces to use ODBC DSN and stills shows that the connection succeeds, but the minute I try to retrieve the catalog shows the same error.

    Second one, applies only for remote servers, in this case it´s an embedded database, so it doesn´t work neither.

     

    But, after a lot of reading and consulting Sybase Documentation i got a list of all connection parameters and a way to test the connection String (dbPing Utility)

     

    http://manuals.sybase.com/onlinebooks/group-sasarc/awg0702e/dbrfen7/@Generic__BookTextView/6042;hf=0;lang=es#X

     

    So, for Sybase the right connection string is:

     

    Provider Name: Sybase Adaptive Server Anywhere Provider 9.0

    Product Name: <empty>

    Data Source: <empty>

    Provider String: dfb=<path to db file>; uid= DBA; pwd=SQL

     

    and the same...

     

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "ASAProv.90" for linked server "lkdbnom" reported an error. Access denied.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "ASAProv.90" for linked server "lkdbnom".

     

    ¿Anybody has ever done this?

    Another alternative could be using MDASQL as a provider but of course is OLEDB provider for ODBC...

     

    Thursday, April 03, 2008 9:18 PM
  • This is interesting.

    1. Can you verify that the account SQL Server is running under has read/write permissions on the sybase db file?
    2. Do you have spaces in the path to the file?
    3. I wonder if the space between "=" and "DBA" in the connection string matters.
    4. Do you have other means of opening the db file? (e.g. RowsetViewer http://msdn2.microsoft.com/en-us/library/ms714336(VS.85).aspx)
    Thursday, April 03, 2008 10:04 PM
  • Ummm finally i got time to do this:  

     

    1.- Yes, the account is my NT account and it has full control on the server (administrator), and it is the same account i use to connect to sql server.

    2.- No spaces.

    3.- tried both ways and the result is the same.

    4.- I downloaded MDAC SDK, installed rowset viewer and i can access data, the only thing is that line in red copied below (noconnection) ¿Does it give any clue? Also, remember i can access data with ole db .net data provider for odbc

     

    When i establish the connection

    CoCreateInstance(ASAProv.90, 0x00000000, 0x00000001, IID_IUnknown, &0x00B43EB4) - S_OK

    IPersist::GetClassID(ASAProv.90) - S_OK

    IDBProperties:Tongue TiedetProperties(1, 0x001554C8) - S_OK

    IDBInitialize::Initialize() - S_OK

    IDBCreateSession::CreateSession(0x00000000, IID_IUnknown, &0x00B4A04C) - S_OK

    IConnectionPointContainer::FindConnectionPoint(IID_IDBAsynchNotify, &0x00000000) - CONNECT_E_NOCONNECTION

    IDBCreateCommand::CreateCommand(0x00000000, IID_ICommand, &0x00B49AF0) - S_OK

    ICommandProperties:Tongue TiedetProperties(1, 0x00168DD8) - S_OK

     

    When i use the IopenRowset Command (but it still retrieves data)

    IOpenRowset:SurprisepenRowset(0x00000000, {"nom_Robot"}, NULL, IID_IRowset, 1, 0x00168DD8, &0x00B4AD08) - S_OK

    IConnectionPointContainer::FindConnectionPoint(IID_IDBAsynchNotify, &0x00000000) - CONNECT_E_NOCONNECTION

    IConnectionPointContainer::FindConnectionPoint(IID_IRowsetNotify, &0x00B4C9D0) - S_OK

    IConnectionPoint::Advise(0x008D4C68, &0x00b46538) - S_OK

    IColumnsInfo::GetColumnInfo(&21, &0x0016A840, &0x00150DF0) - S_OK

    IAccessor::CreateAccessor(0x00000002, 21, 0x00174B28, 7800, &0x00000001, 0x0016AC68) - S_OK

    IRowset::GetNextRows(0x00000000, 0, 18, &1, &0x001298EC) - DB_S_ENDOFROWSET

    IRowset::GetData(0x014C00A8, 0x00000001, 0x001758A8) - S_OK

     

    When i double click on a row retrieved (but it still retrieves data)

    IGetRow::GetRowFromHROW(0x00000000, 0x014C00A8, IID_IRow, &0x00B48A68) - S_OK

    IConnectionPointContainer::FindConnectionPoint(IID_IDBAsynchNotify, &0x00000000) - CONNECT_E_NOCONNECTION

    IColumnsInfo::GetColumnInfo(&21, &0x00177728, &0x001709C0) - S_OK

    IRow::GetColumns(21, 0x00163890) - S_OK

     

    When i disconnect  

    IAccessor::ReleaseAccessor(0x00000001, &0) - S_OK

    Monday, April 07, 2008 9:55 PM
  • I solved the same issue by doing this, 

    1. Open object explorer

    2. Click on Linked Server

    3. Click on Providers

    4. Double click on ASAProv.90

    5. Check the box which says Allow Inprocess 

    works like a charm


    Adnan Bhatti

    • Proposed as answer by CsharpBsharp Tuesday, August 14, 2012 6:39 AM
    Tuesday, August 14, 2012 6:39 AM