Solved

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

Posted on 2007-11-14
7
975 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
[X]
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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert  results from the query output to insert into sql table 3 21
T-sql question 13 30
SQL Percentage Formula 7 30
SQL Query 9 24
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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