February 5, 2017 at 3:41 am
Hi,
Why this works: SELECT * FROM OPENROWSET (BULK 'C:\Users\kift\Documents\milkyway.jpg', SINLGE_BLOB) AS BLOB
but this does not work:
DECLARE @STR varchar(30)
SET @STR = 'C:\Users\kift\Documents\milkyway.jpg'
SELECT * FROM OPENROWSET (BULK @STR, SINLGE_BLOB) AS BLOB
i've also tried as SELECT * FROM OPENROWSET (BULK ''@str'', SINLGE_BLOB) AS BLOB
How to get it to work please?
February 5, 2017 at 4:00 am
Apart from that it's spelt SINGLE (not SINLGE), you're attempting to pass a variable as part of the SQL, which you can't do. Much the same way couldn't do SELECT * FROM @Table, where @Table is the name of a table in your current database.
You'll need to use "D-SQL" (Dynamic SQL), to do this. Something like:DECLARE @sql VARCHAR(MAX);
DECLARE @STR VARCHAR(30);
SET @STR = 'C:\Users\kift\Documents\milkyway.jpg';
SET @sql ='SELECT * FROM OPENROWSET (BULK '''+ @STR + ''', SINGLE_BLOB) AS BLOB;';
EXEC @sql;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 5, 2017 at 4:11 am
gfx99ne - Sunday, February 5, 2017 3:41 AMHi,Why this works: SELECT * FROM OPENROWSET (BULK 'C:\Users\kift\Documents\milkyway.jpg', SINLGE_BLOB) AS BLOB
but this does not work:
DECLARE @STR varchar(30)
SET @STR = 'C:\Users\kift\Documents\milkyway.jpg'
SELECT * FROM OPENROWSET (BULK @STR, SINLGE_BLOB) AS BLOBi've also tried as SELECT * FROM OPENROWSET (BULK ''@str'', SINLGE_BLOB) AS BLOB
How to get it to work please?
The reason is that it has to be string literals, you cannot use a variable, here is a way around this
😎DECLARE @FILE_NAME NVARCHAR(MAX) = N'C:\Users\kift\Documents\milkyway.jpg';
DECLARE @IMPORT_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM OPENROWSET (BULK ''{{@FILE_NAME}}'', SINGLE_CLOB) AS BLOB',N'{{@FILE_NAME}}',@FILE_NAME);
EXEC (@IMPORT_STR);
February 6, 2017 at 11:00 pm
Thom A - Sunday, February 5, 2017 4:00 AMApart from that it's spelt SINGLE (not SINLGE), you're attempting to pass a variable as part of the SQL, which you can't do. Much the same way couldn't do SELECT * FROM @Table, where @Table is the name of a table in your current database.You'll need to use "D-SQL" (Dynamic SQL), to do this. Something like:
DECLARE @sql VARCHAR(MAX);
DECLARE @STR VARCHAR(30);SET @STR = 'C:\Users\kift\Documents\milkyway.jpg';
SET @sql ='SELECT * FROM OPENROWSET (BULK '''+ @STR + ''', SINGLE_BLOB) AS BLOB;';EXEC @sql;
Thanks buddy.
February 6, 2017 at 11:03 pm
Eirikur Eiriksson - Sunday, February 5, 2017 4:11 AMgfx99ne - Sunday, February 5, 2017 3:41 AMHi,Why this works: SELECT * FROM OPENROWSET (BULK 'C:\Users\kift\Documents\milkyway.jpg', SINLGE_BLOB) AS BLOB
but this does not work:
DECLARE @STR varchar(30)
SET @STR = 'C:\Users\kift\Documents\milkyway.jpg'
SELECT * FROM OPENROWSET (BULK @STR, SINLGE_BLOB) AS BLOBi've also tried as SELECT * FROM OPENROWSET (BULK ''@str'', SINLGE_BLOB) AS BLOB
How to get it to work please?
The reason is that it has to be string literals, you cannot use a variable, here is a way around this
😎DECLARE @FILE_NAME NVARCHAR(MAX) = N'C:\Users\kift\Documents\milkyway.jpg';
DECLARE @IMPORT_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM OPENROWSET (BULK ''{{@FILE_NAME}}'', SINGLE_CLOB) AS BLOB',N'{{@FILE_NAME}}',@FILE_NAME);
EXEC (@IMPORT_STR);
Hi, why there are so many @FILE_NAME at the end of string? How is it beneficial please?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply