Uploading files to varbinary(max) in SQL Server -- works on one server, not the other
- by pjabbott
I have some code that allows users to upload file attachments into a varbinary(max) column in SQL Server from their web browser. It has been working perfectly fine for almost two years, but all of a sudden it stopped working. And it stopped working on only the production database server -- it still works fine on the development server.
I can only conclude that the code is fine and there is something up with the instance of SQL Server itself. But I have no idea how to isolate the problem.
I insert a record into the ATTACHMENT table, only inserting non-binary data like the title and the content type, and then chunk-upload the uploaded file using the following code:
// get the file stream
System.IO.Stream fileStream = postedFile.InputStream;
// make an upload buffer
byte[] fileBuffer;
fileBuffer = new byte[1024];
// make an update command
SqlCommand fileUpdateCommand = new SqlCommand("update ATTACHMENT set ATTACHMENT_DATA.WRITE(@Data, NULL, NULL) where ATTACHMENT_ID = @ATTACHMENT_ID", sqlConnection, sqlTransaction);
fileUpdateCommand.Parameters.Add("@Data", SqlDbType.Binary);
fileUpdateCommand.Parameters.AddWithValue("@ATTACHMENT_ID", newId);
while (fileStream.Read(fileBuffer, 0, fileBuffer.Length) > 0)
{
fileUpdateCommand.Parameters["@Data"].Value = fileBuffer;
fileUpdateCommand.ExecuteNonQuery(); <------ FAILS HERE
}
fileUpdateCommand.Dispose();
fileStream.Close();
Where it says "FAILS HERE", it sits for a while and then I get a SQL Server timeout error on the very first iteration through the loop. If I connect to the development database instead, everything works fine (it runs through the loop many, many times and the commit is successful).
Both servers are identical (SQL Server 9.0.3042) and the schemas are identical as well.
When I open Activity Monitor right after the timeout to see what's going it, it says the last command is
(@Data binary(1024),@ATTACHMENT_ID decimal(4,0))update ATTACHMENT set ATTACHMENT_DATA.WRITE(@Data, NULL, NULL) where ATTACHMENT_ID = @ATTACHMENT_ID
which I would expect but it also says it has a status of "Suspended" and a wait type of "PAGEIOLATCH_SH". I looked this up and it seems to be a bad thing but I can't find anything specific to my stuation.
Ideas?
