Error Msg 141 on Bulk Insert

I've got the following SP to automatically insert all files in a directory into the database:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE Imp_Header_PO_sp

@FilePath varchar(1000) = 'D:\EBT\Outbound\',

@WIPPath varchar(1000) = 'D:\EBT\Outbound\WIP',

@ArchivePath varchar(1000) = 'D:\EBT\Outbound\Archive',

@FileNameMask varchar(1000) = '*Header.txt'

AS

BEGIN

SET NOCOUNT ON;

declare @Filename varchar(1000),

@File varchar(1000)

declare @cmd varchar(2000)

create table #Dir (s varchar(8000))

-- Move Header files to WIP

select @cmd = 'move ' + @FilePath + @FileNameMask + ' ' + @WIPPath

select @cmd = 'dir /B ' + @WIPPath + @FileNameMask

delete #Dir

insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'

-- Import file

while exists (select * from #Dir)

begin

select @FileName = min(s) from #Dir

select @File = @WIPPath + @FileName

select @cmd = 'bulk insert'

select @cmd = @cmd + ' POWebOutHeader'

select @cmd = @cmd + ' from'

select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''

select @cmd = @cmd + ' with (Fieldterminator = ',')'

-- Import the data

exec (@cmd)

-- remove filename just imported

delete #Dir where s = @FileName

-- Archive the file

select @cmd = 'move ' + @WIPPath + @FileName + ' ' + @ArchivePath + @FileName

exec master..xp_cmdshell @cmd

end

drop table #Dir

END

GO

When I try to execute the code, I get the following error, on this line: select @cmd = @cmd + ' with (Fieldterminator = ',')'

Msg 141, Level 15, State 1, Procedure Imp_Header_PO_sp, Line 46

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I've tried to find a fix for this error, but it seams to only relate to a select statement and not a Bulk Insert. Can someone please help me figure out how to fix this error

Thanks,

Laura



Answer this question

Error Msg 141 on Bulk Insert

  • DilipK

    Causes:

    This error occurs when you are assigning the column values from a SELECT statement into local variables but not all columns are assigned to a corresponding local variable.



  • 5Rock

    You are awesome! Thanks!
  • BriTheProblem

    The error occurs because you need to double your quotes

    select @cmd = @cmd + ' with (Fieldterminator = '','')'

    instead of

    select @cmd = @cmd + ' with (Fieldterminator = ',')'

    Run these 2 example to see the error

    declare @cmd varchar(50)
    select @cmd =''
    select @cmd = @cmd + ' with (Fieldterminator = ',')'
    print @cmd

    declare @cmd varchar(50)
    select @cmd =''
    select @cmd = @cmd + ' with (Fieldterminator = '','')'
    print @cmd

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/


  • Error Msg 141 on Bulk Insert