n_sudarsan
asked on
linked server connection to the Sybase database from SQL server 2012
I am trying to create a linked server connection to the Sybase database from SQL server 2012 (OS: windows server 2008 R2 standard),
on SQL server 2012 machine , I installed Sybase Software Developers Kit 15.7 ,OLEDB Provider (64 bit) for the connectivity to the source Sybase database.
I follow the following steps to create a connection to the Sybase DB
1. I added a data source through the utility sybdrvadm pointing to the source Sybase database, Connection also succeeded.
2. Then I created a linked server from SQL server 2012 using the data source created in the previous step (step one), here also connection succeeds and linked server gets created.
But the issue is, I am not able to view the tables list, in other words , when I expand the linked server [linked server] -->Catalogs-->Default-->Ta
TITLE: Microsoft SQL Server Management Studio
--------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Manag
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)". Deferred accessor validation occurred. Invalid binding for this column. (Microsoft SQL Server, Error: 7346)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=7346&LinkId=20476
Can you please guide me how I can fix this error and successfully connect to the (Source) Sybase database
data-source-tosybase-step1.bmp
error-msg-view-table-list-step3.bmp
Linked-server-step2.bmp
on SQL server 2012 machine , I installed Sybase Software Developers Kit 15.7 ,OLEDB Provider (64 bit) for the connectivity to the source Sybase database.
I follow the following steps to create a connection to the Sybase DB
1. I added a data source through the utility sybdrvadm pointing to the source Sybase database, Connection also succeeded.
2. Then I created a linked server from SQL server 2012 using the data source created in the previous step (step one), here also connection succeeds and linked server gets created.
But the issue is, I am not able to view the tables list, in other words , when I expand the linked server [linked server] -->Catalogs-->Default-->Ta
TITLE: Microsoft SQL Server Management Studio
--------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Manag
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)". Deferred accessor validation occurred. Invalid binding for this column. (Microsoft SQL Server, Error: 7346)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=7346&LinkId=20476
Can you please guide me how I can fix this error and successfully connect to the (Source) Sybase database
data-source-tosybase-step1.bmp
error-msg-view-table-list-step3.bmp
Linked-server-step2.bmp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe you should be also able now to run queries directly via linked server by using full (four) name qualifier instead of openquery - like a direct select from table or views
SELECT col1,col2,col... FROM LinkedSRVname.catalog.sche
SELECT col1,col2,col... FROM LinkedSRVname.catalog.sche
ASKER
I just used the OLEDB provider as you have suggested, it is the same behaviour.
However, I just noticed now that openquery is correctly working though the tables are not listed (in both the approaches) ( I just did not check the openquery earlier). As the requirement is met this question can be closed.
However, I just noticed now that openquery is correctly working though the tables are not listed (in both the approaches) ( I just did not check the openquery earlier). As the requirement is met this question can be closed.
I believe this is because you added a DSN for it in OS and that points to a speciffic catalog/database right? This is the same with any other ODBC System or Data DSN.
I would try add a new Linked server and just use the OLEDB provider instead if you installed one for Sybase 64bits. You should be able to see it listed under Provider under SSMS Server Objects and use that one same as you would use a SQL CLI OLEBD provider.