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 2008 Forums
 SSIS and Import/Export (2008)
 import from csv file error

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -