Importing Image

  • 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?

  • 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

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

  • 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.

  • 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?

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

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