Solved

Bulk Insert Error

Posted on 2004-08-12
7
780 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:nkjohnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 150 total points
ID: 11788408
Try doing it from a local drive. If that works, you might need to map a drive letter to the UNC path.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 25 total points
ID: 11788595
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11788656
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 75 total points
ID: 11788674
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
 

Author Comment

by:nkjohnson
ID: 11792156
You are right kselvia access was denied.  How do I go about getting access to the file?
0
 

Author Comment

by:nkjohnson
ID: 11792773
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
 
LVL 12

Expert Comment

by:kselvia
ID: 11793508
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question