Import txt file from different server into Microsoft SQL 2008

Everyday I want to automatically import a txt file (w/yesterday's date) into the sql table LogErrors. An eample file name is 01062013.txt

So far I have this but I get the error....
Incorrect syntax near '\'.
How can I fix this?

BULK
INSERT LogErrors
FROM '\\MED-WKS-21\loadlogs\Txt\' + replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt'

WITH
(
FIELDTERMINATOR = '-',
ROWTERMINATOR = '\n'
)
GO

BobRosas

Asked: 2013-01-07

Advertise Here

2 Solutions

LVL 23

Steve WalesSenior Database AdministratorCommented: 2013-01-07

It appears, from my testing and some posts I found that you can't put a variable file name in the FROM parameter of BULK INSERT.

A work around was offered here: http://stackoverflow.com/questions/5547393/bulk-insert-with-variable-file-name

I tried a little bit of code myself and was able to make this work:

declare @sql varchar(max);
declare @x char(50);
set @x = 'c:\' + 'x.txt';

set @sql = 'BULK INSERT tab1 from '''+rtrim(@x)+''' WITH (FIELDTERMINATOR = ''-'', ROWTERMINATOR = ''\n'')'
print @sql
exec (@sql)

Open in new window

You could probably build the @sql statement with your variables, I was just doing some quick and dirty testing. I got failures because there was no x.txt file in c:\ but the above snippet at least got past the errors being generated from the FROM line.

BobRosasAuthor Commented: 2013-01-07

Thank you so much for your help! That helped a lot! Now I have an access error. I tried this code...

declare @sql varchar(max);
declare @x char(50);
--set @x = 'c:\' + 'x.txt';
set @x = '\\MED-WKS-21\Txt\' + replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt';

set @sql = 'BULK INSERT LogErrors from '''+rtrim(@x)+''' WITH (FIELDTERMINATOR = ''-'', ROWTERMINATOR = ''\n'')'
print @sql
exec (@sql)

The error I'm getting is this...
Cannot bulk load because the file "\\MED-WKS-21\Txt\01062013.txt" could not be
opened. Operating system error code 5(Access is denied.).

It makes sense that it's just a rights issue but I can browse to this location and open the file from the SQL server that I'm on. Do you now if there is something else I need in the code to allow myself access to the file? Thanks again!

LVL 23

Steve WalesSenior Database AdministratorCommented: 2013-01-07

Does the user that the SQL Server service runs under have access to that path ?

Check the Security Sections of these two documents:

http://msdn.microsoft.com/en-us/library/ms188365.aspx
http://msdn.microsoft.com/en-us/library/ms175915.aspx