[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1648
  • Last Modified:

Bulk insert parameter issue

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

1 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now