Export (0) Print
Expand All

FileTableRootPath (Transact-SQL)

Returns the root-level UNC path for a specific FileTable or for the current database.

Applies to: SQL Server (SQL Server 2012 through current version).

FileTableRootPath ( [ ‘[schema_name.]FileTable_name’ ], @option )

FileTable_name

The name of the FileTable. FileTable_name is of type nvarchar. This is an optional parameter. The default value is the current database. Specifying schema_name is also optional. You can pass NULL for FileTable_name to use the default parameter value

@option

An integer expression that defines how the server component of the path should be formatted. @option can have one of the following values:

Value

Description

0

Returns the server name converted to NetBIOS format, for example:

\\SERVERNAME\MSSQLSERVER\MyDocumentDB

This is the default value.

1

Returns the server name without conversion, for example:

\\ServerName\MSSQLSERVER\MyDocumentDB

2

Returns the complete server path, for example:

\\ServerName.MyDomain.com\MSSQLSERVER\MyDocumentDB

nvarchar(4000)

When the database belongs to an AlwaysOn availability group, then the FileTableRootPath function returns the virtual network name (VNN) instead of the computer name.

The FileTableRootPath function returns NULL when one of the following conditions is true:

  • The value of FileTable_name is not valid.

  • The caller does not have sufficient permission to reference the specified table or the current database.

  • The FILESTREAM option of database_directory is not set for the current database.

For more information, see Work with Directories and Paths in FileTables.

To keep code and applications independent of the current computer and database, avoid writing code that relies on absolute file paths. Instead, get the complete path for a file at run time by using the FileTableRootPath and GetFileNamespacePath functions together, as shown in the following example. By default, the GetFileNamespacePath function returns the relative path of the file under the root path for the database.

USE MyDocumentDB;

@root varchar(100)
SELECT @root = FileTableRootPath();
@fullPath = varchar(1000);

SELECT @fullPath = @root + file_stream.GetFileNamespacePath()
FROM DocumentStore
WHERE Name = N’document.docx’;

Permissions

The FileTableRootPath function requires:

  • SELECT permission on the FileTable to get the root path of a specific FileTable.

  • db_datareader or higher permission to get the root path for the current database.

The following examples show how to call the FileTableRootPath function.

USE MyDocumentDB;
-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB”
SELECT FileTableRootPath();

-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB\MyFileTable”
SELECT FileTableRootPath(N'dbo.MyFileTable');

-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB\MyFileTable”
SELECT FileTableRootPath(N'MyFileTable');

Community Additions

ADD
Show:
© 2014 Microsoft