fahimi
جمعه 16 آبان 1393, 23:08 عصر
با توجه به اینکه تیبل از نوع FileTable با فیلد ها ثابت ایجاد می شود . و اجازه تغغیر آن را نیز نمی دهد چگونه می توان تیبلی را با آن مرتبط کرد. من از روش زیر استفاده کرده ام
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'H:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='H:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'H:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO
-- Create FileTable Table
USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO
SELECT *
FROM FileTableTb
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO
INSERT INTO [dbo].[FileTableTb]
([name],[file_stream])
SELECT
'dbHafez.accdb', * FROM OPENROWSET(BULK N'd:\dbHafez.accdb', SINGLE_BLOB) AS FileData
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO
USE FileTableDB
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[is_directory]
FROM [dbo].[FileTableTb]
GO
SELECT FileTableRootPath('FileTableTb') AS FileTableRootPath
SELECT GetPathLocator('\\ALFA\MSSQLSERVER\FileTableDB\Fil eTableTb_Dir')
SELECT file_stream.GetFileNamespacePath()
FROM [dbo].[FileTableTb]
منبع :
http://blog.sqlauthority.com/2012/04/14/sql-server-working-with-filetables-in-sql-server-2012-part-1-setting-up-environment/
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'H:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='H:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'H:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO
-- Create FileTable Table
USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO
SELECT *
FROM FileTableTb
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO
INSERT INTO [dbo].[FileTableTb]
([name],[file_stream])
SELECT
'dbHafez.accdb', * FROM OPENROWSET(BULK N'd:\dbHafez.accdb', SINGLE_BLOB) AS FileData
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO
USE FileTableDB
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[is_directory]
FROM [dbo].[FileTableTb]
GO
SELECT FileTableRootPath('FileTableTb') AS FileTableRootPath
SELECT GetPathLocator('\\ALFA\MSSQLSERVER\FileTableDB\Fil eTableTb_Dir')
SELECT file_stream.GetFileNamespacePath()
FROM [dbo].[FileTableTb]
منبع :
http://blog.sqlauthority.com/2012/04/14/sql-server-working-with-filetables-in-sql-server-2012-part-1-setting-up-environment/