[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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
0
nkjohnson
Asked:
nkjohnson
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now