sql server filestream

1.) Filestream TSQL

check filestream status:

SELECT * FROM sys.configurations WHERE name = 'filestream access level'

A) Pepare SQL Server

1) configuration Manager / SQL-Server Db Service Propteries / Filestream Tab: Check all
2) EXEC sp_configure filestream_access_level, 2  RECONFIGURE
3) restart SQL Server Service

B) Prepare DATABASE

use testFileStream;


alter database testFileStream ADD FILEGROUP fsg CONTAINS FILESTREAM;


ALTER DATABASE testFileStream ADD FILE ( NAME = N'fsf', FILENAME = N'D:\sqlData\filesteamtest\' ) TO FILEGROUP fsg;

C) Prepare TABLE

its necesarry to have a rowguidcol:

CREATE TABLE dbo.BLOB2 
(
  ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE ,
  BLOB VARBINARY(MAX) FILESTREAM NULL
)
otherwise you get: A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property

alter table blobs add RowID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE 

alter table blobs add  [Bytes2] varbinary(max) filestream null;

alter existing Columns doesn't work:

alter table blobs alter column ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE 

alter table blobs alter column [Bytes] varbinary(max) filestream null;

D) Insert Files

　
INSERT INTO dbo.blob2 (blob)


SELECT * FROM
OPENROWSET(BULK N'd:\9bat\test.sql', SINGLE_BLOB) AS Import


or
INSERT INTO [dbo].[FileTableTb] ([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
or

INSERT INTO [dbo].FileTableTb ([name],[file_stream]) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText') AS FileData

or Update Files:
update [dbo].FileTableTb set [file_stream] =(SELECT * FROM OPENROWSET(BULK N'd:\temp\test2.txt', SINGLE_BLOB) AS FileData) WHERE id=12

E) Check Result

select *, blob.PathName() from blob2;

in D:\sqlData\filesteamtest\
there are dirs named with guids (2-levels)

allowing a url

netsh http add urlacl url= http://+:51915/Server.wcfService/ user=domian\username