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.
| Author |
Topic |
|
lappin
Posting Yak Master
182 Posts |
 Posted - 2012-09-24 : 07:12:34
|
| Hi I have a stored procedure which imports from a csv file and saves to a temporary table. The procedure runs fine when I run "exec myProc" but when I put the same "exec myProc" into a SQL Agent job I get an error.Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"I have also tried running job as a windows user who is member of local administrators group. I get same error except the user name is different in error message.The folder containing the file is a shared folder and the local user and network service user have full permissions on the folder and on the share. I've tried two different methods Bulk Insert and openrowset.I have also removed the saving to temp table where I just read the first few lines of the csv file but still get same error.Here is the way I read from csv file:CREATE TABLE #MyTemp ( ID int, Field1 nvarchar(5) , etc ) BULK INSERT #MyTemp FROM 'C:\folder1\MyFile.csv' WITH ( FIRSTROW =2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) --------------------------- INSERT INTO #MyTemp select *from openrowset('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}','select * from C:\folder1\MyFile.csv ' ) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-24 : 13:25:15
|
| is the csv path in your local machine or in server?is sql server instance on your same machine or in a remote machine/server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2012-09-25 : 06:21:52
|
| Hi, yes the folder is local to the server. The path is C:\myFolder where "myFolder" is windows folder name and the share name. As I mentioned above executing the procedure works but not from within agent |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-25 : 12:56:10
|
| can you try creating a proxy account and configure sql job to use it instead. give account permissions to shared folder------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
import from csv file error