Is it possible to have two SQL Server instances, one 2008 R2 and another 2012, to run on the same machine with both having filestream enabled?
I tried to setup this in local dev machine and I can get the services running. But when I try to read a filestream from the 2008 R2 instance, I get a ArgumentException.
The path in both instance looks alright.
SQL Server 2008 R2
\\DEVMC\MSSQLSERVER\v1\TESTDB\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960
SQL Server 2012
\\DEVMC\EXPRESS2012\v1\TESTDB12\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960
Is there any magical setting which could make it work in both instances or is just simply not do-able?
Here is the exception stacktrace when trying to access the file using C# test code.
System.ArgumentException : An invalid parameter was passed to the function.
at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
at SimpleTests.FileStreamTest.ReadFilestream(String connectionstring, String fileid) in FileStreamTest.cs: line 47
And here is the NUnit test method I used to verify the issue. FileStore access to TESTDB fails, but TESTDB12 works fine.
[TestCase(new object[] { "Data Source=localhost;Database=TESTDB;Integrated Security=True;Connect Timeout=120;", "525F2031-8D8C-45FF-8386-E1DD5F11C960" })]
[TestCase(new object[] { "Data Source=localhost\\express2012;Database=TESTDB12;Integrated Security=True;Connect Timeout=120;", "9AC28DC0-B927-4075-8D31-00E343E15663" })]
public void ReadFilestream(string connectionstring, string fileid)
{
const string _selectSql =
@"SELECT fileData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM coreFileStore
WHERE fileStoreId = @FileStoreId";
using (SqlConnection connection = new SqlConnection(connectionstring))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
string filepath;
byte[] txnInfo;
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = _selectSql;
command.Parameters.AddWithValue("FileStoreId", new Guid(fileid));
command.Transaction = transaction;
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
filepath = reader.GetString(0);
txnInfo = reader.GetValue(1) as byte[];
}
}
Console.WriteLine(filepath);
using (SqlFileStream sqlFileStream = new SqlFileStream(filepath, txnInfo, FileAccess.Read))
{
using (StreamReader reader = new StreamReader(sqlFileStream))
{
string x = reader.ReadToEnd();
Console.WriteLine(x);
}
}
}
}
}
