Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BobRosas
BobRosas

ASKER

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!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Are you running the query using Windows Authentication or Mixed mode? If you use windows Authentication, I hope it will work
I ran the same code as admin and it worked fine.  So it was a user access problem.  Thanks again for all help!