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.

Thanks
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

culminITAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
try

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.