none
Openrow set and dynamic query

    Question

  •  SELECT ID,  Image

              FROM  tblstock
                 CROSS APPLY
                         OPENROWSET(BULK '+Image_File_Path+ ',SINGLE_BLOB) AS x(Image)

    how to pass column name to openrowset? 

    I tried with dynamic column name is not passing value

     Thanks

    Kalyan

    Monday, July 08, 2013 6:34 PM

All replies

  • BulkColumn is the default name.

    SELECT BulkColumn AS PhotoJpeg
                    FROM   OPENROWSET(BULK 'f:\temp\ALASKALODGE.JPG',
                           SINGLE_BLOB) AS x


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, July 09, 2013 12:21 AM
    Moderator
  • thanks,  this works 

    SELECT BulkColumn AS PhotoJpeg FROM OPENROWSET(BULK 'f:\temp\ALASKALODGE.JPG', SINGLE_BLOB) AS x

    i need to use the column from driving table

    Image_File_Path is column from tblstock table

    SELECT ID,  Image

              FROM  tblstock
                 CROSS APPLY
                         OPENROWSET(BULK '+Image_File_Path+ ',SINGLE_BLOB) AS x(Image)

    Tuesday, July 09, 2013 1:30 AM
  • See this example

    USE AdventureWorks2012;
    GO
    CREATE TABLE myTable(FileName nvarchar(60), 
      FileType nvarchar(60), Document varbinary(max));
    GO
    
    INSERT INTO myTable(FileName, FileType, Document) 
       SELECT 'Text1.txt' AS FileName, 
          '.txt' AS FileType, 
          * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
    GO


    Many Thanks & Best Regards, Hua Min

    Tuesday, July 09, 2013 1:53 AM
  • Try the below sample, (not tested)

    DECLARE @STR NVARCHAR(MAX)
    SET @STR='COL1,COL2,COL3'
    SET @STR='SELECT * FROM OPENROWSET(''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',''SELECT ' + @STR 
    			+ ' FROM TABLENAME'')'
    --PRINT @STR
    EXEC(@STR)


    Regards, RSingh

    Tuesday, July 09, 2013 3:41 AM