?
Solved

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

Posted on 2007-11-14
7
Medium Priority
?
990 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 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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