locked
Stored Procedure Bulk Insert Multi files RRS feed

  • Question

  • I am using this stored procedure to insert files from a directory to a sql table.     When I run the pocedure below I get the following  2 rows effected, 1 row effected, 1 row effected, Mseg 170 level 15 line 2 incorrect syntex near ',' then continues with 1 row effected, 1 row effected, it did write the record to my log table but did not complete the bulk insert. Can anyone help , thanks                                                                                                                                   

    GO

    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",firstrow= 4)'

    --print @query

    exec

    (@query)

    insert

    into importlog (query) select @query

    end

    Tuesday, May 22, 2012 5:56 PM

Answers

  • Make sure you havn't got a 0 length file.  If that isn't it then try manually running each bulk insert so that you can see the full errors

    or there is this one goofy problem if you are using SQL 2000

    http://support.microsoft.com/kb/324122

    Honestly you are better off using SSIS for something like this - where you have better programability and error handling


    Chuck



    Tuesday, May 22, 2012 10:21 PM

All replies

  • Care to share what statement winds up in the log?

    I'd just uncomment this line: --print @query

    Then you can see the queries that are firing


    Chuck

    Tuesday, May 22, 2012 6:36 PM
  • Found the problem.  You were using double quotes rather than 2 single quotes in the code.  Use this line instead (Copy and paste it)

    SET @Query = 'BULK INSERT ' + @Tablename + ' FROM "' + @Filepath + @Filename + '" 
    
    WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',firstrow= 4)'


    Chuck


    Tuesday, May 22, 2012 6:48 PM
  • It writes two records to the log BULK INSERT inventory_update_temp FROM "c:\import\inventory_update_temp.csv"    WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n",firstrow=4)

    the second says null

    Tuesday, May 22, 2012 8:59 PM
  • can this work through the network or does file need to live locally and if it can run via network do I need to change code.  Thanks

    I just tryed and got the following

    Msg 4861, Level 16, State 1, Line 1

    Could not bulk insert because file '\\1sl-den-db01\importThe network path was not found.' could not be opened. Operating system error code 53(The network path was not found.).

    Tuesday, May 22, 2012 9:23 PM
  • It can work through the network, but it appears that the ID you are using does not have permissions to list the contents of \\1sl-den-db01\import (or it doesn't exist)

    Chuck


    Tuesday, May 22, 2012 9:52 PM
  • You can bulk load files from network shares, but permissions must be set up appropriately. If you are logged with Windows authentication, your own permissions apply. If you are logged in with SQL authentication, the permissions of the service account for SQL Server applies.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 22, 2012 10:13 PM
  • Ok, thanks I resolved that and now I get this errror 

    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].

    Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

    Before this error I got data type mismatch so I assigned defaults to my columns which in return gave me the error above.  Thanks

    Tuesday, May 22, 2012 10:13 PM
  • Make sure you havn't got a 0 length file.  If that isn't it then try manually running each bulk insert so that you can see the full errors

    or there is this one goofy problem if you are using SQL 2000

    http://support.microsoft.com/kb/324122

    Honestly you are better off using SSIS for something like this - where you have better programability and error handling


    Chuck



    Tuesday, May 22, 2012 10:21 PM