I am trying to use a variable filepath in a SQL Openrowset command. I'm aware that it can't explicitly accept a variable and that I need to make use of dynamic SQL.
What currently works -
SELECT @file_stream = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\Temp\print4.pdf', SINGLE_BLOB) AS x
However if I try to use my variable filepath
declare @file_stream VARBINARY(MAX)
declare @filePath NVARCHAR(128)
set @filePath = 'C:\Temp\print4.pdf'
set @command = N'SELECT @file_stream = CAST(bulkcolumn AS varbinary(MAX))
from OPENROWSET(BULK ' + @filePath + ',
SINGLE_BLOB) ROW_SET'
EXEC sp_executesql @command, @filePath, @file_stream;
I get the error 'Msg 137, Level 15, State 2, Line 15 Must declare the scalar variable "@filePath".'
I'm sure this is an issue of syntax but haven't been able to figure out how it should be formatted yet.
Change your script like below.
DECLARE @file_stream VARBINARY(MAX)
DECLARE @command nvarchar(1000)
DECLARE @filePath NVARCHAR(128)
set @filePath = 'C:\Temp\print4.pdf'
set @command = N'SELECT @file_stream1 = CAST(bulkcolumn AS varbinary(MAX))
from OPENROWSET(BULK ''' + @filePath + ''',
SINGLE_BLOB) ROW_SET'
EXEC sp_executesql @command, N'@file_stream1 VARBINARY(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT
select @file_stream