Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-11-14
7
970 Views
Last Modified: 2013-12-24
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
Comment
Question by:lojk
  • 4
  • 3
7 Comments
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 500 total points
ID: 20279363
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
 
LVL 9

Author Comment

by:lojk
ID: 20280340
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
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20281603
What database system are you trying to query from the ODBC connection in SQL Server?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 9

Author Comment

by:lojk
ID: 20284386
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
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20319918
Did you have any luck on your problem?
0
 
LVL 9

Author Comment

by:lojk
ID: 20440951
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
 
LVL 9

Author Closing Comment

by:lojk
ID: 31409167
I know the question was short but a bit more interaction would have been appreciated.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question