Geek QuestionFollow

Mark this one on your calendars as the first time Eld has asked you a question. It is a rarity since I know pretty much everything. Whatever I don't know, I expect you guys to know it, therefore making everything right in the world.

Now then, a for (s)he who can answer this question:


How do you do a cross ODBC-DSN query?

Example solution using middleware:

MSACCESS DB
> Linked MS-SQL Table
> Linked ORACLE table
> Linked Text file DSN table

QUERY: INSERT into MS-SQL table SELECTing from Text file with a JOIN on the Oracle table.


PROBLEM:
How do you do this without the middleware in any common programming language available. (Bonus points for ASP, VB.NET, C#, C++ solutions). Pseudo code accepted if you can back up the concept with fact.


Edit: 1 rule. You must accomplish this in one(1) Query. Loops per record are not allowed.

GO!





Edited, Oct 30th 2006 at 12:24pm PST by Elderon

Whachho talkin bout Willis? I have ODBC strings for all three databases and can connect swimmingly. A cross-DSN query is the question.

Do your own homework, cUnt.

If you are just looking to brag let's go ahead and skip to that part. I don't know how to do it and have never even considered doing something like this before, but now that you asked the question I am curious.

I would totally sig this were it not for my ultra-spiffy undead-language quote.

Eld,
If you have access to the ado.net control, that should allow you to make the connect to both Db's as well as the text file.

at least that's what the dev's and dba's here said

Elderon,

You can do it, but not all within .NET. You can only connect to one datasource at a time.

How this is commonly one is to connect to one database, build your dataset, then insert that into another database, with a new connection.

Outside of using middleware, or linked servers, you cannot connect to multiple databases with one connection object, at least in .NET.

EDIT:

This can be done with SQL 2005 Integration Services, which is part of SQL 2005 and VS.NET 2005. You can create multiple connections to data sources, create your transform packages, and load the data back into another database.

Edited, Oct 31st 2006 at 7:22am PST by Frakkor

If it's a one-time thing and you want to do it solely in .NET, I think your best bet is to load the Oracle and Text data into a dataset with relations between the tables, and then push it to SQL with a data adapter.

If you want to do it in one query and you'll be setting up a process to be reused, I think your best bet would be linked servers (haven't used integration services with SQL2005, but I suspect it works similarly). Then you can create a stored procedure to pull joined data between the Text and Oracle sources into a sql table with one statement.

Sounds interesting. I've already solved the issue for the time being by simple opening one recordset, iterating through the records and writing them one-by-one to the other dataset until rs = EOF. It works very well, but I'd like to make it into a sexy single INSERT query because I'm hoping to gain performance.


can you get more info on this? I use the (ADODB) object and have multiple connections open at once, but the problem is that if you write a nice insert query from the defined table in connection "a" and want to just pop those records into connection "b", the problem is that the query has to be assigned to one connection only ie: rs.open strSQL, db. Unless it is handled at the DB server like cross-DB queries in MSSQL where you specify the dbname.owner.table, how do you do it?



Well uber geeks usually do have uber large penises, so it just makes logical sense.

That's what I was afraid of. Unfortunately, this does not bode well for a dynamic ODBC connection to a text file. I guess I'll just have to stick with my current solution that works really well, but isn't quite as sexy.

Another good article (from MSDN) on creating linked servers (including to a text file).