Is it possible to query ODBC datasources with T-SQL?

[KCcasey]

Hi Guys,

As above - - I have an ODBC data source that I want to use in a query using T-SQL without having to import it in using DTS. Is this a can do or a can't do?

Thanks in Advance!!

Casey.

 

[JamesLean]

If you will be accessing it regularly then look at setting up a linked server. If it is only a one-time connection then look at the OPENROWSET function.

--James

 

[KCcasey]

Hi James,

Thanks for the tip. I've added the data source as a linked server - - but I'm having problems figuring out how to query it.

Heres the set-up of my linked server...
[BLUE]
SRV_NAME:
CBA_linkedINMASTER
SRV_PROVIDERNAME:
MSDASQL
SRV_DATASOURCE
CBA[/BLUE]

... I've tried using a linked server name, but that didn't work. I think because I didn't supply the catalog or the schema names. Then I tried using the OPENROWSET function and that failed as well.

Can anyone

A) Tell where I can find more information about my datasource? (ODBC Administrator wasn't very helpful)

B) Tell me whats wrong with my OPENROWSET or SELECT (using linked serve name) statement?

SELECT *
FROM CBA_linkedINMASTER...[INMASTER]
GO

SELECT a.*
FROM OPENROWSET('MSDASQL',
   'CBA;SERVER=CBA_linkedINMASTER',
   'SELECT * FROM INMASTER') AS a

Error message returned when trying either of the above statements...

Server: Msg 7399, Level 16, State 1, Line 5
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].