December 22, 2009 at 11:26 pm
Hi,
I am tryng to implement the filestream feature provided with sql server 2008. After writing to the file stream file using win32 API i am trying to commit the transaction . Upon doing so i am getting the following error "An uncommittable transaction was detected at the beginning of the batch. The transaction was rolled back. This was caused by an error that occurred during the processing of a FILESTREAM request in the context of this transaction. " I am hitting the remote server where the filestream data is saved.
If i try to save some small text file (10 KB)[no xml content in it] then the functionality is working properly w/o giving any issues.
I am trying to save a 1 MB XML file into the file stream column.
If i try saving xml file in the filestream column then i get this error.
I checked the @@TRANCOUNT inside my transaction and it showed as 1. I assume this means that there is only one transaction.
I am using c# code to write the file stream file to the path got by column.PathName().
The brief steps are as given below:
1)Hit the DB and get the UNC path of the file. The server is remote and not local.
2) Begin a transaction.
3)Get the transactional context by using GET_FILESTREAM_TRANSACTION_CONTEXT().
4) Write some data to filestream.
5)Commit the transaction.
I am getting the error when i am trying to commit the transaction.
I checked the path where this file is stored and a new file with the data i have written is created, but it is not getting reflected in my datarow as the transaction is not committed.
The code goes as below:
SqlConnection sqlConnection = new SqlConnection(
"Integrated Security=true;Server=MyServer;DataBase=MyDatabase");
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
int i = 0;
try
{
sqlConnection.Open();
//The first task is to retrieve the file path
//of the SQL FILESTREAM BLOB that we want to
//access in the application.
sqlCommand.CommandText =
"SELECT Top 1 Chart.PathName()FROM Archive.dbo.Records ;
String filePath = null;
Object pathObj = sqlCommand.ExecuteScalar();
if (DBNull.Value != pathObj)
filePath = (string)pathObj;
else
{
throw new System.Exception(
"Chart.PathName() failed"
+ " to read the path name "
+ " for the Chart column.");
}
//The next task is to obtain a transaction
//context. All FILESTREAM BLOB operations
//occur within a transaction context to
//maintain data consistency.
//All SQL FILESTREAM BLOB access must occur in
//a transaction. MARS-enabled connections
//have specific rules for batch scoped transactions,
//which the Transact-SQL BEGIN TRANSACTION statement
//violates. To avoid this issue, client applications
//should use appropriate API facilities for transaction management,
//management, such as the SqlTransaction class.
SqlTransaction transaction = sqlConnection.BeginTransaction();
sqlCommand.Transaction = transaction;
sqlCommand.CommandText ="SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
Object obj = sqlCommand.ExecuteScalar();
byte[] txContext = (byte[])obj;
//The next step is to obtain a handle that
//can be passed to the Win32 FILE APIs.
SafeFileHandle handle = OpenSqlFilestream(
filePath,
DESIRED_ACCESS_READWRITE,
SQL_FILESTREAM_OPEN_NO_FLAGS,
txContext,
(UInt32)txContext.Length,
0);
if (handle.IsInvalid)
{
string message = "";
message=String.Format(message,"OpenSqlFilestream() failed"+ " GetLastError() = {0:X}",GetLastError());
throw new System.Exception(message);
}
//Now that there is a Win32 compatible
//file handle, information can be written
//and read to the FILESTREAM BLOB.
byte[] buffer = new byte[512];
FileStream fileStream = new FileStream(
handle,
FileAccess.ReadWrite,
buffer.Length,
false);
int numBytes = 0;
string someData = ReadXmlData();
Encoding unicode = Encoding.GetEncoding(0);
fileStream.Write(
unicode.GetBytes(someData.ToCharArray()),
0,
someData.Length);
//Read the data from the FILESTREAM
//BLOB.
fileStream.Seek(0L, SeekOrigin.Begin);
numBytes = fileStream.Read(buffer, 0, buffer.Length);
string readData = unicode.GetString(buffer);
if (numBytes != 0)
Console.WriteLine(readData);
sqlCommand.CommandText = "Select @@TRANCOUNT";
Object obj1 = sqlCommand.ExecuteScalar();
if (obj1 != null)
{
i = (int)obj1;
}
//Because reading and writing are finished, FILESTREAM
//must be closed. This closes the c# FileStream class,
//but does not necessarily close the the underlying
//FILESTREAM handle.
fileStream.Close();
if (handle != null && !handle.IsClosed)
handle.Close();
//The final step is to commit or roll back the read and write
//operations that were performed on the FILESTREAM BLOB.
sqlCommand.Transaction.Commit();
}
catch (System.Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
sqlConnection.Close();
}
February 9, 2010 at 1:42 pm
I'm sorry that I don't have an answer to your question, but I noticed that on your post it sounds like you created a FILESTREAM that points to a location that's not on the same server where SQL Server is installed. I'm trying to set up a Filestream that uses either a UNC or a mapped drive to another server, but it tells me it's an invalid path. Can you please tell me if you really accomplished that, and if so, how?
Many Thanks,
Desiree
July 25, 2012 at 12:01 am
Looking at the code and one article about using XML in FILESTREAM , may be you are not explicitly coverting XML data to read or write to them using CAST or CONVERT function.
http://msdn.microsoft.com/en-us/magazine/dd695918.aspx
Sometimes error messages may not relate to exact nature of problem.
Cheer Satish 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply