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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 import from csv file error
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lappin
Posting Yak Master

182 Posts

Posted - 09/24/2012 :  07:12:34  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 09/24/2012 :  13:25:15  Show Profile  Reply with Quote
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 - 09/25/2012 :  06:21:52  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 09/25/2012 :  12:56:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000