Hi..
Source File: http://www.webname.com/files/log.csv
Destination: SQL Server to process the rows

I am trying to get the above file into SQL server to query the data. The above file takes a username and password.

Do I need to create a linked server?

The only way i can think of at present is to copy the file onto the server(by FTP) and query it something like this.

select * from OpenRowset('MSDASQL'
 ,'driver={Microsoft Text Driver (*.txt; *.csv)}
 ;DefaultDir=C:\;'
 ,'select * from log.csv')

Any help or pointers appreciated
Dan

Copy the file to a place where SQL Server can see it... then, do a BULK INSERT. See Books OnLine for details.

--Jeff Moden

thanks for the reply
regarding the below I would like to declare a variable of the location, namely have the path passed in at runtime.

select * from OpenRowset('MSDASQL'
 ,'driver={Microsoft Text Driver (*.txt; *.csv)}
 ;DefaultDir=C:\;', 'select * from log.csv')

/--------------This is what I am trying
declare @file varchar(100)
set @file = 'C:\'

select * from OpenRowset('MSDASQL'
 ,'driver={Microsoft Text Driver (*.txt; *.csv)}
 ;DefaultDir=@file;'
 ,'select * from log.csv')


this does not seem to work, any help appreciated

Sorry... I don't use OpenRowSet... it's too slow for me... I just copy the file to an import staging area and do a nasty fast bulk insert. What the heck... disk space is cheap and if I need to reload for some reason... it's already local.

--Jeff Moden

Check where the parameter "c:" is. Are you thinking this is your local client c: drive or the server c: drive. Your command is executed relative to the server you established a connected to. Make sure your on the right "C:" drive/server. You may be pointing to the wrong place and thus not working.

thanks
good point, will have a look

Bulk Insert! http://p2p.wrox.com/topic.asp?TOPIC_ID=53130

--Jeff Moden