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
WITH
(
FIELDTERMINATOR = '-',
ROWTERMINATOR = '\n'
)
GO
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
WITH
(
FIELDTERMINATOR = '-',
ROWTERMINATOR = '\n'
)
GO
Microsoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
BobRosas
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Are you running the query using Windows Authentication or Mixed mode? If you use windows Authentication, I hope it will work
ASKER
I ran the same code as admin and it worked fine. So it was a user access problem. Thanks again for all help!
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
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!