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!