Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    25

    Unanswered: Incorrect syntax near @File

    I am using this bulk insert command in procedure below. I am passing variable @File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

    Thanks


    ALTER procedure sp_BulkInsert1
    @File varchar(1000)

    AS

    BULK INSERT SQL_Tests.dbo.xRSA FROM @File
    WITH
    (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    CODEPAGE = 'RAW',
    TABLOCK
    )

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because the syntax requires a constant, you can't use a variable... At least not directly anyway!
    Code:
    ALTER procedure sp_BulkInsert1
       @File varchar(1000) 
    AS
    
    EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @File + '''
    WITH
    (
       DATAFILETYPE = ''char''
    ,  FIELDTERMINATOR = '',''
    ,  ROWTERMINATOR = ''\n''
    ,  CODEPAGE = ''RAW''
    ,  TABLOCK
    )' )
    
    RETURN
    GO
    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    25

    Thumbs up Execute Command

    Quote Originally Posted by Pat Phelan
    Because the syntax requires a constant, you can't use a variable... At least not directly anyway!
    Code:
    ALTER procedure sp_BulkInsert1
       @File varchar(1000) 
    AS
    
    EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @File + '''
    WITH
    (
       DATAFILETYPE = ''char''
    ,  FIELDTERMINATOR = '',''
    ,  ROWTERMINATOR = ''\n''
    ,  CODEPAGE = ''RAW''
    ,  TABLOCK
    )' )
    
    RETURN
    GO
    -PatP
    Why do I need to use Execute Command?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you check BOL for the syntax of the BULK INSERT command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Because it's dynamic sql...

    Pat I can't get the injection in to the vien....maybe you can...I'm sure it can be done

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    
    CREATE PROC sp_BulkInsert1
       @File varchar(1000) 
    AS
    
    EXECUTE ('BULK INSERT myTable99 FROM ''' + @File + '''
    WITH
    (
       DATAFILETYPE = ''char''
    ,  FIELDTERMINATOR = '',''
    ,  ROWTERMINATOR = ''\n''
    ,  CODEPAGE = ''RAW''
    ,  TABLOCK
    )' )
    
    RETURN
    GO
    
    DECLARE @x varchar(1000)
    SELECT @x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO' 
    EXEC sp_BulkInsert1 @x
    GO
    
    DROP PROC  sp_BulkInsert1
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:
    Code:
    CREATE FUNCTION dbo.FixQuote(@pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
    BEGIN
       RETURN Replace(@pcIn, '''', '''''')
    END
    Given that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •