Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Bulk Insert Error

Posted on 2004-08-12
7
Medium Priority
?
788 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 600 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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 70

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 300 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

972 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