I've got the following SP to automatically insert all files in a directory into the database:
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
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 #DirEND
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

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
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/