Working with FileTables in SQL Server 2012 – Part 1- Troubleshooting

Catalog Views

select db_name(database_id) DatabaseName,non_transacted_access,non_transacted_access_desc,directory_name from sys.database_filestream_options

select object_name(object_id),object_name(parent_object_id) from sys.filetable_system_defined_objects

select * from sys.filetables

select * from sys.tables where is_filetable=1

[Function] FileTableRootPath : Returns the root-level UNC path for a specific FileTable or for the current database.

USE <Database Name>
GO
select FileTableRootPath(null,null)

[Function] GetFileNamespacePath : Returns the UNC path for a file or directory in a FileTable.

USE SQL2016DB
GO
DECLARE @root varchar(100)
DECLARE @fullPath varchar(1000);
SELECT  @root = FileTableRootPath();
SELECT  @fullPath = @root + file_stream.GetFileNamespacePath() FROM [DocumentStore]
WHERE Name = N'IMG_20140524_185328587.jpg';

select  @fullPath

SELECT file_stream.GetFileNamespacePath(1, Null) AS FilePath FROM [DocumentStore]

ft_1

GetPathLocator : Returns the path locator ID value for the specified file or directory in a FileTable.

use <database name>
go
SELECT GetPathLocator('\\LENOVO-PC\SQL2016\Demo1\DocumentTable\IMG_20140518_195515101_HDR.jpg')

ft_2

sp_kill_filestream_non_transacted_handles : Closes non-transactional file handles to FileTable data.

sp_kill_filestream_non_transacted_handles [[ @table_name = ] ‘table_name’, [[ @handle_id = ] @handle_id]]

sp_kill_filestream_non_transacted_handles

ft_3

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *