We help IT Professionals succeed at work.

SQL Filestream syntax

Hi,
I have this following example

DECLARE @img AS VARBINARY(MAX)

 

-- Load the image data

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))

      FROM OPENROWSET(

            BULK

            'C:\temp\MicrosoftMouse.jpg',

            SINGLE_BLOB ) AS x

           

-- Insert the data to the table          

INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)

SELECT NEWID(), 'MS1001','Microsoft Mouse', @img





what I want to do is replace
  'C:\temp\MicrosoftMouse.jpg'
with a variable file name

I tried taking out the apostrophes, and adding @file_location (which I declared) but it doesn't seem to like it.

Do I need to do something like
' " + @file_location + "'


an example would be appreciated. Thanks
Comment
Watch Question

Commented:
See example below for putting just image in to table...
declare @file_location varchar(80)
declare @insertSQL varchar(3000)
set @file_location =  'C:\temp\MicrosoftMouse.jpg


set @insertSQL = 
'INSERT INTO Items(ItemImage) 
SELECT * FROM 
OPENROWSET(BULK N''' + @file_location  + ''', SINGLE_BLOB) as filename'
exec(@insertSQL)

Open in new window

Commented:
Sorry....see corrected post below...
declare @file_location varchar(80)
declare @insertSQL varchar(3000)
set @file_location =  'C:\temp\MicrosoftMouse.jpg


set @insertSQL = 
'INSERT INTO Items(ItemImage) 
SELECT * FROM 
OPENROWSET(BULK N''' + @file_location  + ''', SINGLE_BLOB) as ItemImage'
exec(@insertSQL)

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
To answer this question, I needed to setup a test database, since I don't normally use FileStream, and it had to be configured.

These are the steps that I followed:

1) Use the Sql Server Configuration Tools to configure the server to allow FILESTREAM.

2) Run this configuration query in SQL Server Management Studio:

    EXEC sp_configure filestream_access_level, 2;
    GO
    RECONFIGURE;
    GO

         Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.

3) Create a database, adding a FileStream file group named ''FileStreamGroup1"

4) Define a data container for the FileStream:

ALTER DATABASE Test ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'C:\Temp\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO

5) Add a table with a varbinary(max) FILESTREAM column

CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO

Reference:
FILESTREAM Storage in SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx
Most Valuable Expert 2012
Top Expert 2008

Commented:
You gotta love it, when 2 comments come in at the same time!! *BIG GRIN*

Author

Commented:
Hi,

Thanks for the replies.
To Carsrst who's solution I think was very close.

I can't get it working, but close I think,
INSERT INTO [CandidateDocuments] (candidatedocumentID ,candidateID , document )
SELECT NEWID(), @candidate_id, @document

I need to have this query in a stored procedure where the two parameters are the @candidate_id, and @document

can you provide the syntax please so I can get it working and close the question. many thanks

Author

Commented:
thsi worked