RSS

An Introduction to SQL Server FileStream

30 Aug

Introduction

File Stram is new Feature in sql server 2008 This Feature Used To storage and  management of unstructured data  (example: word documents, image files, music and videos ).

FILESTREAM is not a data type.

Enable FilsStraem

  1. Enable  FILESTREAM for Transact-SQL access
  2. Enable FILESTREAM for file I/O streaming access
  3. Allow remote clients to have streaming access to FILESTREAM data
  4. To make this Open SQL Server configraution under sql services select te instance name you want to enable file stream then right click on this instance then select peroperites then filestream tab .
  5. After FileStream Enable open sql server query then ruen this script to Configure Filestream

EXEC

sp_configurefilestream_access_level, 2

GO

RECONFIGURE

GO

—————————————————————————

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = ‘E:\Temp\NP\NorthPoleDB.mdf’
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = ‘E:\Temp\NP\NorthPoleFS’)
LOG ON (
NAME = NorthPoleLOG,
FILENAME = ‘E:\Temp\NP\NorthPoleLOG.ldf’)
GO
—————————————————-
use NorthPole
go
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
———————————————————-
— 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
‘E:\Temp\NP\MicrosoftMouse.jpg’,
SINGLE_BLOB ) AS x
select @img
— Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), ‘MS1001′,’Microsoft Mouse’, @img
——————————————————————-
–Example No 2
CREATE TABLE dbo.PictureTable
(
PkId int Primary Key IDENTITY (1, 1),
Id uniqueidentifier NOT NULL Unique ROWGUIDCOL Default newid(),
Description nvarchar(64) NOT NULL,
FileSummary varbinary(MAX),
FileData varbinary(MAX) FileStream NULL
)
——————————————————————
Insert Into PictureTable([Description],[FileData])
Values(‘Hello World’, Cast(‘Hello World’ As varbinary(max)))
–And then select using the statement
SELECT [PkId],[Id],[Description],[FileData],CAST([FileData] As varchar(Max)) FROM [PictureTable]
————————————————————————————————-

References

http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

 

 
1 Comment

Posted by on August 30, 2011 in SQl server Administration

 

One response to “An Introduction to SQL Server FileStream

  1. DBA

    August 30, 2011 at 11:11 PM

    i like this feature

     

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