SQL Server error: Timeout expired

[Cyberduke]

Hi all, I am connecting to a SQL server 2005 using C#. All running on Windows server 2003. I have to read 270 000 PDF files and save them in a folder. A random time into the reading and saving the PDF files I get this error. I have changed timeout settings and so fourth, But I am not sure what else I can do more? I can post code on request.

Thank you

Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at WindowsFormsApplication1.Form1.SaveUserPDF(String FolderName, String CustID) in C:\Users\Ian\Documents\Visual Studio 2008\Projects\FilesExporter\FilesExporter\Form1.cs:line 105
Source: .Net SqlClient Data Provider
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)

 

[NoUserHere]

How did you set the timeout and where?

See : SqlCommand.CommandTimeout Property (System.Data.SqlClient)

The error being provided proves that the command doesn't have enough time before it times out. What is the command doing, ie what type of data re you saving?

 

[Cyberduke]

I set an execution timeout, But I realized now that would not have made a different since it was set on infinite when I found it. I just set it back to default then.

1. I added the command.CommandTimeout = 60; code as per the link above, no difference.
2. I have run a query that gets some objects(DocBlob) in the database(All PDF files) and then save them using filestreams back to the HardDrive.

 

[NoUserHere]

Hmm, I'm not sure about this one, and maybe someone else will have more info and suggestions for you, however, I think it might be worth checking the file size accepted by that blob field on that table column field and make sure the file you are uploading doesn't exceed the allowed space. Although, I'm sure the error should be different if this was the problem. My thoughts are that if the blob has insufficient space, it may or may not timeout. But might be worth checking. I'm a little wrapped up in a project at the moment, so maybe one of the other guys can share some other suggestions to check.

 

[Cyberduke]

I think you misunderstood my problem, I am reading from the blob in the Database and saving PDF files on the harddrive.

But thank you for the advice, I hope someone else have a good idea, I am out of them.

 

[NoUserHere]

Oh, I'm really sorry for the Miss-understanding @Cyberduke, I'm very tired and overworked. My bad on that one! I thought you were trying to insert data, not receive it...

In this case, I suggest showing the code for ONLY this method which is responsible for retrieving the BLOB and maybe there is some minor discrepancies we can find by viewing the relevant source code for this method. Can you share that, and I'm sure one of the other guys will chime in when they too get time.

 

[Skydiver]

I have run a query that gets some objects(DocBlob) in the database

Depending on how big these blobs are, this could potentially take a very long time. Are you querying from the database is batches based on total size of the blobs to be downloaded? Or are you downloading blobs just based on some fixed count of items?

 

[Cyberduke]

@Sheepings Arent we all? programmers are after all the laborers of the 21st century....

@Skydiver Your comments gave me an idea about how I was saving the files, I changed method and it seems to be running smooth now. If all works ill post what I learned. Thank you

 

[Cyberduke]

I was almost happy... it ran through once then I made a small code adjustment, just naming the files differently(Changing it back makes no difference). And now the error is back....sigh! Here is the code that saves the PDF

PS. it now breaks very quickly after around +- 150 PDF's

String queryString = "SELECT  dbo.DBDocument.DocumentName,dbo.DBDocBlob.Object FROM dbo.DBDocBlob, dbo.DBDocument WHERE dbo.DBDocBlob.DocumentID = dbo.DBDocument.DocumentID AND dbo.DBDocument.CustomerCode = '" + CustID + "' AND dbo.DBDocBlob.Object IS NOT NULL";

                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    using (var myReader = command.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                    while (myReader.Read())
                    {
                        filename= myReader.GetString(0);
                        countUser++;
                        count++;

                        var filePath1 = Path.Combine(Directory.GetCurrentDirectory(), "PDFs");
                        var filePath2 = Path.Combine(filePath1, ToSafeFileName(FolderName));
                        var filePath3 = Path.Combine(filePath2, ToSafeFileName(filename) + "(" + countUser + ").pdf");

                        if (!(Directory.Exists(filePath2)))
                        {
                            DirectoryInfo di = Directory.CreateDirectory(filePath2);
                        }

                        using (var file = File.OpenWrite(filePath3))
                        {
                            const int BUFFER_SIZE = 4096;
                            var buffer = new byte[BUFFER_SIZE];
                            var index = 0;
                            int byteCount;

                            while ((byteCount = (int)myReader.GetBytes(1, index, buffer, 0, BUFFER_SIZE)) > 0)
                            {
                                file.Write(buffer, 0, byteCount);
                                index += byteCount;
                            }

                        }

                    }
                    // Close the reader and the connection.
                    myReader.Close();
                    connection.Close();
                }



                }

 

[Cyberduke]

Any ideas?

 

[NoUserHere]

What happens when you debug your code?

Look for the discrepancies in your code as it executes. I'm sure you will find it if you spend enough time in the debugger.

For a start, your code structure isn't very efficient. This block of code has no purpose to be in the while loop :

                        var filePath1 = Path.Combine(Directory.GetCurrentDirectory(), "PDFs");
                        var filePath2 = Path.Combine(filePath1, ToSafeFileName(FolderName));
                        var filePath3 = Path.Combine(filePath2, ToSafeFileName(filename) + "(" + countUser + ").pdf");

                        if (!(Directory.Exists(filePath2)))
                        {
                            DirectoryInfo di = Directory.CreateDirectory(filePath2);
                        }

And I'd be quicker to pass your received objects from your DB reader and send them into a new thread to execute separately each file received. Your methods should have one job and they should be doing one thing only. You should structure your methods to have one job, and for each piece of data received, send each one onto a new method to write the files on a separate thread. Section this code :

using (var file = File.OpenWrite(filePath3))
                        {
                            const int BUFFER_SIZE = 4096;
                            var buffer = new byte[BUFFER_SIZE];
                            var index = 0;
                            int byteCount;

                            while ((byteCount = (int)myReader.GetBytes(1, index, buffer, 0, BUFFER_SIZE)) > 0)
                            {
                                file.Write(buffer, 0, byteCount);
                                index += byteCount;
                            }
                        }

 

[Cyberduke]

Thank you for the response, Yeah I know my code is not super great. (No excuses there. - it was supposed to be a super small tool that should have been done by now) I cleaned it up with same result.

Here is what makes this so tricky, the PDF's that I am extracting is very confidential, so the person in charge(not me ofcourse) forbade me to copy any data from the Database over to me. (We are also halfway across the world). So I can write on my pc, and then I have to test remotely, without the ability to properly debug or look at things. Unless I am aware of some debugging techniques that could be applied here.

Yes this is not great, and no, I cannot change this scenario, I have tried many times.

 

[NoUserHere]

Something else to note, while highly unlikely, but yet still you should be checking that buffer to make sure the file being received isn't bigger than the buffer.
const int BUFFER_SIZE = 4096;

It doesn't really matter if you have access to the live environment or not. You can and should replicate the environment for your debugging scenario. I'm sure you can find a random PDF generator which you could use for testing. Right?

 

[NoUserHere]

It's also worth noting that Remote debugging - Visual Studio is an option, but you will need to consult your employer for that.

 

[Skydiver]

This code:

const int BUFFER_SIZE = 4096;
var buffer = new byte[BUFFER_SIZE];
var index = 0;
int byteCount;

while ((byteCount = (int)myReader.GetBytes(1, index, buffer, 0, BUFFER_SIZE)) > 0)
{
    file.Write(buffer, 0, byteCount);
    index += byteCount;
}

Could likely be easily be replaced by something like:

myReader.GetStream(1).CopyTo(file);