Importing Image

Question

Importing Image

gfx99ne

SSChasing Mays

Points: 645
More actions
February 5, 2017 at 3:41 am

#401555

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 2

gfx99ne - Sunday, February 5, 2017 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?

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);

gfx99ne SSChasing Mays Points: 645 More actions · Answer 3

Thom A - Sunday, February 5, 2017 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;

Thanks buddy.

gfx99ne SSChasing Mays Points: 645 More actions · Answer 4

Eirikur Eiriksson - Sunday, February 5, 2017 4:11 AM
gfx99ne - Sunday, February 5, 2017 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?
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?