Accessing Sql FILESTREAM from CLR Stored Procedure
I am trying to access a Sql file stream from a CLR stored procedure. I created a very simple database with a single table that includes a filter column. I can successfully read from the stream using a simple console application. Here is some sample code for millet that fails:
[SqlProcedure]
public static void GetDataFromFileStream(string path, out int data)
{
using (var connection = new SqlConnection("context connection=true"))
{
connection.Open();
var transaction = connection.BeginTransaction();
var transactionContext = GetTransactionContext(connection, transaction);
// the following line throws an exception
var sqlFileStream = new SqlFileStream(path, transactionContext, FileAccess.Read);
var buffer = new byte[4];
sqlFileStream.Read(buffer, 0, 4);
data = BitConverter.ToInt32(buffer, 0);
}
}
private static byte[] GetTransactionContext(SqlConnection connection, SqlTransaction transaction)
{
using (var cmd = connection.CreateCommand())
{
const string myGetTxContextQuery = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
cmd.CommandText = myGetTxContextQuery;
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.Text;
cmd.Transaction = transaction;
return (byte[])cmd.ExecuteScalar();
}
}
An exception is thrown when trying to instantiate a SqlFileStream:
Fixed System.ComponentModel.Win32Exception Message = "Request not supported" Source = "System.Data" ErrorCode = -2147467259 NativeErrorCode = 50
Does anyone know what I am doing wrong?
source to share
I was unable to use SqlFileStream to access filters directly from the CLR (due to the issues mentioned above). The solution I ultimately made was to use a SQL stored procedure to get a subset of the thread data that I need. While not particularly efficient in some scenarios, it was sufficient for my application
CREATE PROC ReadFromFilestream
(
@pfilestreamGUID UNIQUEIDENTIFIER,
@pOffsetIntoData INT,
@pLengthOfData INT,
@pData VARBINARY(MAX) OUTPUT
)
AS
BEGIN;
SELECT @pData = SUBSTRING(ValueData, @pOffsetIntoData, @pLengthOfData)
FROM [MESL].DataStream
WHERE DataStreamGUID = @pfilestreamGUID;
END;
source to share
Do you use WITH PERMISSION_SET = EXTERNAL_ACCESS
when creating an assembly in the Server the SQL ? By default, CREATE ASSEMBLY uses the SAFE permission set, which does not include FileIOPermissions, which is required by using the SqlFileStream constructor.
source to share