Bulk insert parameter issue

Posted on 2009-02-18
Last Modified: 2012-05-06
Hey all. I am able to get my code working if I hard code the path into my insert statement. I tried to follow some answers on here and over the NET, but can't seem to get my modification working to allow for a parameter path name.

The first one is the hard code and the second one, my attempt where @File_Con is the path name as well as a few other parameters. (I am using VS & SQL Server 2008 and it seems they have changed some of their system SP names. So the SP in the last line is correct)

Please advise on proper way to do this.

1) INSERT INTO OpenBLOBDB.dbo.Files (FileID,FileDesc,FileExt,FileContents)

    SELECT  NEWID(), @File_Desc,@File_Ext, BulkColumn FROM Openrowset( Bulk 'C:\Image1.jpg',SINGLE_BLOB) AS blob



2) DECLARE @SQL varchar(max);


   SET @SQL = 'INSERT INTO OpenBLOBDB.dbo.Files (FileID,FileDesc,FileExt,FileContents)

    SELECT  NEWID(),' + @File_Desc +  ',' +  @File_Ext + ', BulkColumn FROM Openrowset( Bulk ' + @File_Con + ',SINGLE_BLOB) AS blob';


    exec sp_sqlexec @SQL

Open in new window

Question by:culminIT
    1 Comment
    LVL 37

    Accepted Solution


    DECLARE @SQL varchar(max);
       SET @SQL = 'INSERT INTO OpenBLOBDB.dbo.Files (FileID,FileDesc,FileExt,FileContents)
        SELECT  NEWID(),''' + @File_Desc +  ''',''' +  @File_Ext + ''', BulkColumn FROM Openrowset( Bulk ''' + @File_Con + ''',SINGLE_BLOB) AS blob';
        exec sp_sqlexec @SQL

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now