>how to enable filestream on SQL Server 2008 after installation

>Alternatively, ‘FILESTREAM Access Level’ can be configured using TSQL by running the following statement.

EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Here is the script that creates a FILESTREAM enabled database.

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = ‘C:\Temp\NP\NorthPoleDB.mdf’
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = ‘C:\Temp\NP\NorthPoleFS’)
LOG ON (
NAME = NorthPoleLOG,
FILENAME = ‘C:\Temp\NP\NorthPoleLOG.ldf’)
GO

Creating a table with FILESTREAM columns
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

Inserting FILESTREAM data
— Declare a variable to store the image data
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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s