• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 997
  • Last Modified:

microsoft, sql server, 2000, Querying ODBC Data sources from inside SQL View/SP

I have a Machine DSN on my server that points to a ODBC data source (lets call it External+.UDD).
This DSN allows us access via a third party implementation of the SQL driver (called transoft) to what i think is an informix database on a SCO-UNIX box.

Im quite familiar with all aspects of SQL but i cant find a simple example of querying an ODBC datasource to provide a rowset that can be joined to my internal SQL tables and then presented out via SQL (and eventually into a .net application using the Native .net SQL Client) as a view.. Theres so much out there about Querying SQL via ODBC that i cant find what im looking for (which is querying ODBC sources from within SQL)

All help appreciated... I think it might be as simple as an 'openrowset'.  I cant hold a copy of the ODBC data in sql (that would be really easy for me to achieve already) and periodically update, the data must be pulled live (and intelligently) from the ODBC driver as required.
0
lojk
Asked:
lojk
  • 4
  • 3
1 Solution
 
digital_thoughtsCommented:
Checkout the books online (help file) to setup a linked server.  You can then use OPENQUERY to query the linked server and join to that table.
0
 
lojk.Net and Infrastructure ConsultantAuthor Commented:
OK, nice... That got me a bit further through and I now have a linked server called ExternalWrapper and that enumerates all of the tables correctly.

My first step was to drag a linked table into a new view window in the main DB and as expected that created a SQL statement like this

SELECT     *
FROM         EXTERNALWRAPPER..root.DAYBOOK_1 DAYBOOK_1


But i still cant query the tables correctly. I generally get a message (similar to)


[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='MSDASQL', TableName='[EXTERNALWRAPPER]..[root].[DAYBOOK_1]', ColumnName='CURRENCY_CODE', ExpectedLength='3', ReturnedLength='1']


The '..' instead of just one '.' made me a little suspicious so i have tried it in a few different combinations with and without double dot and preceeding names but makes no difference.


So just to make sure i tried using the OpenQuery command and created this view select

SELECT *
FROM OPENQUERY(EXTERNALWRAPPER, 'SELECT * FROM root.DAYBOOK_1 DAYBOOK_1')

this generates exactly the same error as above.


Any suggestions?
0
 
digital_thoughtsCommented:
What database system are you trying to query from the ODBC connection in SQL Server?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
lojk.Net and Infrastructure ConsultantAuthor Commented:
as I say above it is a fairly obscure odbc driver called transoft / usql but I think the problem actually lies more with the oledb for odbc driver that the sql server link is using... I haven't checked whether the sql server has been service packed but I will check that first thing tomorrow. I can generate similar queries directly on the original odbc driver (SELECT * FROM root.DAYBOOK_1 DAYBOOK_1) using microsoft query/excel which points me away from the odbc driver directly causing the problem (and I have updated that to the latest version already)

it sounds to me like its because the odbc driver specifies it will return a fixed length field, the oledb driver accepts this but then the odbc driver (that feels a bit buggy to me) returns less than promised and this raises the oledb error.

I was toying with the idea of writing an sp to enumerate the rows one by one and catch any errors but I don't know sp well enough and this needs to be fixed before I will have time research/do this.
0
 
digital_thoughtsCommented:
Did you have any luck on your problem?
0
 
lojk.Net and Infrastructure ConsultantAuthor Commented:
The original Question was effectively solved by your answer thank you.

However the second part required a little more effort to get working.(i think it is related to a bug/shortfall in the SQL Server Driver for ODBC).

I had to create an Access Database with linked tables to the ODBC driver, create corresponding views in the Access DB to all of the ODBC DB Tables and then added that Access Database as an OLEDB linked server instead and Querying the AccessViews to fix the problem.
0
 
lojk.Net and Infrastructure ConsultantAuthor Commented:
I know the question was short but a bit more interaction would have been appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now