How to use SqlFileStream for transactional access to SQL Server 2012 FileTable?

Question

I'm trying to use the SqlFileStream object in a WCF service to get a handle to a specific file that is in a SQL Server 2012 FileTable. I'm able to get the path and transaction context like you would expect with no issues using this piece of code:

            using (SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["FileStorage"].ConnectionString))
            {
                con2.Open();                                         
                string getFileHandleQuery = String.Format(
                    @"SELECT FileTableRootPath(), file_stream.GetFileNamespacePath(), GET_FILESTREAM_TRANSACTION_CONTEXT()
                      FROM {0}
                      WHERE stream_id = @streamId", "FSStore");

                byte[] serverTransactionContext;
                string serverPath;
                using (SqlCommand sqlCommand = new SqlCommand(getFileHandleQuery, con2))
                {
                    sqlCommand.Parameters.Add("@streamId", SqlDbType.UniqueIdentifier).Value = new Guid(finalFileHandleStreamId);

                    using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        sqlDataReader.Read();
                        serverPath = String.Concat(sqlDataReader.GetSqlString(0).Value, sqlDataReader.GetSqlString(1).Value);
                        serverTransactionContext = sqlDataReader.GetSqlBinary(2).Value;
                        sqlDataReader.Close();
                    }
                }
                con2.Close();
            }

However, once I try and actually use the path and transaction context to create a new SqlFileStream:

using (SqlFileStream dest =
              new SqlFileStream(serverPath, serverTxn, FileAccess.Write))
{
    ...
}

The above blows ups with the following exception: The mounted file system does not support extended attributes.

Can someone please explain to me what I'm doing wrong here?

Thanks!

Your code looks fine, but without much more detail it sounds like you may have an issue with your setup on the server side. If you haven't come across these yet, these are two good sources for setting up your server/database to use sqlfilestream.[link](https://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/) and [link](http://lennilobel.wordpress.com/2011/08/22/using-sqlfilestream-in-c-to-access-sql-server-filestream-data/) — Michael, Dec 06 '13 at 04:15

score 3 · Answer 1 · edited Feb 27 '14 at 19:16

If you are trying to use FileTable and receive an error when new a SqlFileStream object, please check the filePath value.

SqlFileStream sfs = new SqlFileStream(filePath, objContext, System.IO.FileAccess.Read); <-- Error "The mounted file system does not support extended attributes"

Correct way to obtain the filePath value is

SELECT [file_stream].PathName() FROM dbo.fTable WHERE name = 'test.xlsx'

filePath value should look like:

\\HOSTNAME\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\test\dbo\fTable\file_stream\A654465D-1D9F-E311-B680-00155D98CA00\VolumeHint-HarddiskVolume1

not like:

\\HOSTNAME\MSSQLSERVER\Store\fDirectory\test.xlsx

That is required by design. Please refer to https://connect.microsoft.com/SQLServer/feedback/details/729273/sql-server-denali-filetable-access-using-sqlfilestream-returns-error-the-mounted-file-system-does-not-support-extended-attributes

and Access FILESTREAM Data with OpenSqlFilestream http://technet.microsoft.com/en-us/library/bb933972.aspx

How to use SqlFileStream for transactional access to SQL Server 2012 FileTable?

1 Answers1