Solved

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

Posted on 2007-11-14
7
962 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now