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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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...

 

Answer 5

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)

Answer 6

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:etProperties(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:etProperties(1, 0x00168DD8) - S_OK

 

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

IOpenRowset:penRowset(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

Answer 7

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