Bulk Insert Error

Hello,

I am currently trying to run a Bulk insert and I keep getting the following error.

Bulk_main: The opentable system function on BULK INSERT table failed. Database ID 1, name 'impPRODUCT_SUBS'.

The command is

BULK INSERT [dbo].impPRODUCT_SUBS FROM '\\SQLSERVER\PumpData$\impFile.csv' WITH  ( FIELDTERMINATOR = '|' ,ROWTERMINATOR = '\n' ,FIRSTROW = 1 )

any Ideas.

Thanks for the help
nkjohnsonAsked:
Who is Participating?
 
SjoerdVerweijCommented:
Try doing it from a local drive. If that works, you might need to map a drive letter to the UNC path.
0
 
Scott PletcherSenior DBACommented:
Make sure the 'select into/bulkcopy' option is set on for that db.  To check, use this command:

EXEC sp_dbOption 'yourDbNameHere'


If it's turned off (it doesn't display in the list), you can turn it on using ALTER DATABASE.
0
 
Scott PletcherSenior DBACommented:
CORRECTION:

BOL says to use ALTER DATABASE, but I don't see that as a valid option.

Use sp_dbOption instead:


EXEC sp_dbOption 'yourDbNameHere', 'SELECT INTO/BULKCOPY', 'TRUE'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ken SelviaRetiredCommented:
The account SQL Server runs as probably does not have rights to the share.

Verify with;

master..xp_cmdshell 'dir \\SQLSERVER\PumpData$\impFile.csv'

0
 
nkjohnsonAuthor Commented:
You are right kselvia access was denied.  How do I go about getting access to the file?
0
 
nkjohnsonAuthor Commented:
Thank you all for your assistance.  I am going to divy up the points because all of you were actually correct.  

I was able to use the bulk insert if I referred to a local drive.  I have had problems with this before when creating Linked servers.  Does MSSQL 7.0 have problems refering to locations with network addresses?  Any last thoughs on this?  

Again thank you for your help!
0
 
Ken SelviaRetiredCommented:
Change the SQL Server service to login as a domain account (My computer+Manage+Services+MSSQL Server) and grant that account rights to the share.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.