none
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? 

    Thursday, July 17, 2008 1:40 AM

Answers

All replies

  • 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.

    Thursday, July 17, 2008 12:56 PM
  • 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? 
    Thursday, July 17, 2008 1:31 PM
  • 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)

     

     

    Thursday, July 17, 2008 2:07 PM
  • Wow, I have finally met another person who says whack, I thought I was alone Smile

     

    Thursday, July 17, 2008 4:45 PM