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

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.

|||

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/

|||You are awesome! Thanks!