mssql BULK read an osfile and output

I am attempting to use the OPENROWSET(Bulk,... method to read an image, to be used as a column on a SQL View.

CREATE TABLE dbo.tblMyFiles
      (
      ID int NOT NULL IDENTITY (1, 1),
      FilePath varchar(255) NOT NULL
      )  ON [PRIMARY]
GO
INSERT INTO tblMyFiles (FilePath) VALUES ('c:\1\test.png')
GO


This works
 SELECT IMG=(SELECT CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'c:\1\test.png', SINGLE_BLOB ) AS x) FROM MyFiles WHERE ID=1

This does not work
 SELECT IMG=(SELECT CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK tblMyFiles.FilePath, SINGLE_BLOB ) AS x) FROM MyFiles WHERE ID=1

Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'tblMyFiles'.


I've tried many different solutions.
 Quoting the tblMyFiles.FilePath...
 Putting it in an @Variable.

http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/
http://www.sqlservercentral.com/Forums/Topic469199-5-1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143948

Any suggestions?
LVL 1
SirParadoxAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
OPENROWSET BULK requires a hard-coded path name. I get around that with dynamic sql.
DECLARE @FIlePath varchar(255),
        @SQL      varchar(max)

SELECT @FilePath = FilePath
FROM   tblMyFiles
WHERE  ID = 1

SET @SQL = 'SELECT IMG=(SELECT CAST(bulkcolumn AS VARBINARY(MAX)) ' +
           'FROM OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_BLOB ) AS x) ' +
           'FROM MyFiles WHERE ID=1'

EXEC (@SQL)

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SirParadox, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.