Bulk insert not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Bulk insert not working

Author

Topic

matty1stop
Starting Member

37 Posts

Posted - 2006-12-19 : 11:06:04

I am trying to run a bulk insert statement, but continue to get error messages. I have checked with our server team and they state that the SQLService network ID has rights to the fileshare.

I can import the file to the network sql server using the import wizard. I can import the file to my local server from the network share using the same bulk insert statement. But when I try to run the bulk insert from the network SQL Server I get the following message. As I said the server team claims that the sql service ID has rights to the share.

Thanks for your help,
Matt

Error Message:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\MyServer\MyShare\Tuesday_DailyHours.csv" could not be opened. Operating system error code 5(Access is denied.).

Here is the code

declare @dayName varchar(32)
declare @filePathAndName varchar(256)

set @dayName =
CASE datepart(dw,getdate())
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
When 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
When 7 THEN 'Saturday'
END

set @filePathAndName = '\\MyServer\MyShare\' + @dayName + '_DailyHours.csv'

declare @insertStatement varchar(256)
set @insertStatement =
'BULK INSERT hoursTable FROM ''' +
@filePathAndName +
''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

exec(@insertStatement)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:11:57

The file and "\\MyServer\MyShare\Tuesday_DailyHours.csv" must be set to allow access and read for the account for which the SQL SERVER is run under.

Peter Larsson
Helsingborg, Sweden

matty1stop
Starting Member

37 Posts

Posted - 2006-12-19 : 11:44:56

I forgot to mention that this is in SQL Server 2005. Do you know how to determine which ID is used when executing the code through the query analyzer or a SQL SERVER AGENT Job (neither of which work) as opposed to the ID used when running the import wizard?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:49:22

Start with allowing EVERYONE access to the path and file specified.

Peter Larsson
Helsingborg, Sweden

matty1stop
Starting Member

37 Posts

Posted - 2006-12-19 : 11:52:36

If only I worked in a bureacracy-free environment that would allow that. I can't get Network security to go for that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:54:27

Check which account is used for running SQL Agent. Allow that account to the path specified, or you almost doomed and stuck.

Peter Larsson
Helsingborg, Sweden

matty1stop
Starting Member

37 Posts

Posted - 2006-12-19 : 12:18:07

sorry for the dumb question but how can I tell which ID runs the SQL Agent and which runs the import wizard?

Thanks for the help

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-20 : 14:59:26

Using the Services applet in Control Panel. Find the SQL Server 2005 service. Right click it, and choose "Properties". Click the "Login" tab, and look at the account (usually Local System).

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources