2005 Bulk Insert

• Cindy Hallum

  SSCommitted

  Points: 1808

  More actions

  March 6, 2006 at 12:50 pm

  #170825

  I'm having problems getting this stored procedure to work since we moved to SQL Server 2005:

  ALTER

  PROCEDURE [dbo].[spBulkInsert]

  @PathFileName varchar(100),

  @Delimiter varchar(10),

  @TableName varchar(30)

  AS

  DECLARE

  @sql nvarchar(200)

  SET

  @sql = N'BULK INSERT ' + @TableName + ' FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''' + @Delimiter + ''') '

  EXEC

  sp_executesql @sql

  The error message is:  You do not have permission to use the bulk insert statement. 

  I have set all the permissions that I can think of.  Anybody run into this before?

   

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  March 6, 2006 at 1:05 pm

  #625010

  Hello Cindy,

  Please go through the syntax in SQL 2005. Here are the examples

  Using pipes to import data from a file

  BULK INSERT AdventureWorks.Sales.SalesOrderDetail   

  FROM 'f:\orders\lineitem.tbl'   

  WITH       

  (FIELDTERMINATOR =' |',

  ROWTERMINATOR =' |\n'

  )

  http://msdn2.microsoft.com/en-us/library/ms188365.aspx

  Thanks and have a great day!!!

  
  Lucky

• Cindy Hallum

  SSCommitted

  Points: 1808

  More actions

  March 6, 2006 at 1:05 pm

  #625009

  I got it.

  I had to give permissions for my user to ADMINISTER BULK OPERATION and this is a server-level permission.