SQL Server Performance Forum – Threads Archive

What ID is used for Import Wizard?

Does anyone know what ID is used for the import wizard? 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 "\MyServerMyShareTuesday_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 = ‘\MyServerMyShare’ + @dayName + ‘_DailyHours.csv’ declare @insertStatement varchar(256)
set @insertStatement =
‘BULK INSERT hoursTable FROM ”’ +
@filePathAndName +
”’ WITH (FIELDTERMINATOR = ”,”, ROWTERMINATOR = ”
”)’ exec(@insertStatement)

---

"Operating system error code 5(Access is denied.)" means it is access/Security issue. When you run the import/export wizard, it uses your authentication when run it as a job it uses sql service account.
Make sure sql service account has the access to the file. Mohammed U.

---

Specifically SQL Server agent service account. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Thanks for the info. Do you know where I can look to determine what ID the account uses?

---

You can through SQL Server Configuration Manager or Services window…
Mohammed U.

---

Or even looking at the SQL server properties. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>