November 20, 2011 at 10:28 pm
I want to link from sqlserver 2008 to oracle and I have following error:
TITLE: Microsoft SQLServer Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
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)
------------------------------
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject" for linked server "ORACLETEST". (Microsoft SQL Server, Error: 7301)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7301&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
what shouid I do?
November 22, 2011 at 12:47 am
Did you successfully create the Linked Server for Oracle? Are you able to ping Oracle TNS?
November 23, 2011 at 1:07 am
Hi, yes I have linked to oracle but when I want to expand the catalogs>>default>>Table ,I have this error:
(TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
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)
------------------------------
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject" for linked server "ORACLETEST". (Microsoft SQL Server, Error: 7301)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7301&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
)
would you plz help me to solve the problem.Tnx
November 23, 2011 at 1:17 am
It looks like permissions issue. Can you select data from linked table with query? Please post here the error message of the same.
November 23, 2011 at 1:30 am
no I can ont select the data form the openquery.
November 23, 2011 at 2:09 am
smohammadnia.3840 (11/23/2011)
no I can ont select the data form the openquery.
Please post your query & error message here.
November 23, 2011 at 3:34 am
my error is:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
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)
------------------------------
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject" for linked server "ORACLETEST". (Microsoft SQL Server, Error: 7301)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7301&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
and my query is:
select * from openquery(bushedc,'select * from request')
November 23, 2011 at 3:39 am
smohammadnia.3840 (11/23/2011)
my error is:TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
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)
------------------------------
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject" for linked server "ORACLETEST". (Microsoft SQL Server, Error: 7301)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7301&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
and my query is:
select * from openquery(bushedc,'select * from request')
Are you kidding me?:w00t: Did you get above error when you ran query?
November 23, 2011 at 4:24 am
oh no sorry plz my error is:
Msg 7308, Level 16, State 1, Line 4
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
November 23, 2011 at 5:36 am
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
November 23, 2011 at 6:57 am
smohammadnia.3840 (11/23/2011)
oh no sorry plz my error is:Msg 7308, Level 16, State 1, Line 4
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Two questions...
1- Are you running on a 64 bits server?
2- Is the installed OLE DB provider a 32 bits one?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 25, 2011 at 10:28 pm
yes, both of your question are yes.
November 27, 2011 at 4:02 am
windows server is 64 bits and ole db providor is 32 bits one
November 28, 2011 at 11:34 am
Also the oracleversion of the backend is important as msdaora is discontinued, you better use the oracle-driver from oracle (from oracle 10 on)
December 5, 2011 at 5:17 am
smohammadnia.3840 (11/27/2011)
windows server is 64 bits and ole db providor is 32 bits one
Please take a look at this: http://sqlservercorner.blogspot.com/2009/03/linked-servers-for-64-bit-sql-2005-sp3.html
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply