Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','.

Question

Help!

I am fairly new to this, but I am trying to execute a stored procedure that imports multiple files at once, and I get this error when I try to execute it as such

 Exec usp_ImportMultipleFiles 'N:\Marketing\Group_Public\Mailing Lists\2008 Mailings\E-Blasts\RFM' , '*.csv' , 'temptables.dbo.email_RFM'

This is the stored procedure:

ALTER procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
    WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @query
exec (@query)
--insert into logtable (query) select @query
end

drop table #y



Can anyone tell me what I am doing wrong? 

Answer 1

Phil,

 

Welcome to the forums.  when I copy and paste your provided text, I am getting double quotes " where I should be seeing two single quotes ''.  They look the same on here, but have a very different purpose in code syntax.

 

Try the following:

 

Code Snippet

ALTER procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500),

@pattern varchar(100), @TableName varchar(128)

as

set quoted_identifier off

declare @query varchar(1000)

declare @max1 int

declare @count1 int

Declare @filename varchar(100)

set @count1 =0

create table #x (name varchar(200))

set @query ='master.dbo.xp_cmdshell ''dir '+@filepath+@pattern +' /b'''

insert #x exec (@query)

delete from #x where name is NULL

select identity(int,1,1) as ID, name into #y from #x

drop table #x

set @max1 = (select max(ID) from #y)

--print @max1

--print @count1

While @count1 <= @max1

begin

set @count1=@count1+1

set @filename = (select name from #y where [id] = @count1)

set @Query ='BULK INSERT '+ @Tablename + ' FROM '''+ @Filepath+@Filename+'''

WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'

--print @query

exec (@query)

--insert into logtable (query) select @query

end

drop table #y

 

 

 

I have also moved your post from the .NET Framework forum to the Transact-SQL forum.

Answer 2

Thanks for the reply, and that did work.  However I have one last problem, when I execute the stored procedue, it can not find my files.  This is the error I get:

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\RFM1File Not Found" does not exist.


This is the command to execute the stored procedue:


Exec usp_ImportMultipleFiles 'C:\RFM1' , '*.csv' , '[temptables].[dbo].[email_RFM]'

Any ideas why it can not find my files? 

Answer 3

Phil,

 

You need another whack behind the FilePath:

 

Code Snippet

declare @query varchar(400)

declare @filepath varchar(100)

declare @pattern varchar(100)

set @filepath = 'c:\windows\'

set @pattern = '*.log'

set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'

exec (@query)

 

 

Answer 4

Wow, I have finally met another person who says whack, I thought I was alone