[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1072
  • Last Modified:

Import data from ODBC source into SQL-Server via T-SQL

Dear Experts,

I have a ODBC-Source named 'FOO' installed (System-DNS), and it's working fine. Now I need to read in all rows from table 'KODATEN' from that ODBC source and put them into an existing table of same structure in the SQL-Server (ODBC-driver and SQL-Server reside on the same machine).
And, most importantly, it should be done via a T-SQL statement or something else that can be used inside a stored procedure. Solutions along the lines of "click on some wizard in the administration tool" won't be of much help, I'm afraid.
Is there a way to specify the ODBC source in the FROM clause of a SELECT statement?

To further illustrate my needs: I already succeeded in importing data from a CSV-file into SQL-Server by
  BULK INSERT fcp..NTCS FROM 'D:\foo.csv'
  WITH (
     DATAFILETYPE = 'char',
     FIELDTERMINATOR = ';',
     ROWTERMINATOR = '\n',
  )

Now I need to do the very same thing except reading from ODBC rather than from a CSV-file.

I guess the answer will be rather simple for the experts but it's all the more urgent.
Eagerly looking forward to your support,
woolf
0
wlfs
Asked:
wlfs
  • 3
  • 2
1 Solution
 
SireesCommented:
>.Is there a way to specify the ODBC source in the FROM clause of a SELECT statement? <<

you can do it with OPENROWSET.

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm
0
 
SireesCommented:
Example

This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
0
 
SireesCommented:
In BULK INSERT statement  you need to specify the full path of the data file that contains data to copy into the specified table or view. BULK INSERT can copy data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

I think you should create a linked server to your ODBC Source and then use BULK INSERT

or

Set up a DTS package and then execute it via T-SQL
0
 
wlfsAuthor Commented:
Thanks a lot, that definitely seems to be the way to go.
But - please excuse my ignorance in this area - I am not able to figure out the correct datasource/provider_string.

This is the connection string that works perfectly fine in Visual Studio .Net 1.1:
Nullable=1;DSN=ODBC5_KORE;DSDLL=PWDSMF32.DLL;Y2KSplit=0;DBQ=C:\woolf\fcp\BMD\KORE\;PromptEnvironment=No

Now I tried various alternatives using 'MSDASQL' as the provider name for OLE DB Provider for ODBC:
select a.* from openrowset('MSDASQL','Nullable=1;DSN=ODBC5_KORE;DSDLL=PWDSMF32.DLL;Y2KSplit=0;DBQ=C:\woolf\fcp\BMD\KORE\;PromptEnvironment=No',KODATEN) as a;
select a.* from openrowset('MSDASQL','DSN=ODBC5_KORE;DBQ=C:\woolf\fcp\BMD\KORE\',KODATEN) as a;
select a.* from openrowset('MSDASQL','DSN=ODBC5_KORE',KODATEN) as a;

All of them behave the same. There is no error but the osql command prompt freezes. No reaction whatsoever, while at the same time I can happily access the ODBC sourve via .net, Access, or Perl.

Do you have an idea? How can I figure out the correct parameter values for the openrowset() function?

Thanks, woolf
0
 
wlfsAuthor Commented:
Hmm, openrowset(), linked server, DTS package. Especially linked server with bulk insert seems attractive.
I guess I'll just close this question and take my time to sort this all out.
Altogether I am really stunned that such a seemingly trivial question turns out to be that complicated.

regards, woolf

PS: If you have any hint about the datasource/provider_string please feel still free to post it :). I'd really like to figure that out.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now