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)

 

[Cyberduke]

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);

Unfortunately I am running an old .NET framework where this is not yet implemented. Sadly.

 

[Cyberduke]

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?

Yeah you are absolutely right, I made a huge database on my machine and it all worked. So it could be something with the machine I am executing it on.

 

[Cyberduke]

Ok here is what I THINK is happening, on the faster computer, SQL server can load the records fast enough so that every time the SQLReader wants a new Document there is one available, but on the actual database I need to work on, SQL Server is much slower. This is also visible if you just execute a manual query, you wait for records to load.

I added a small delay between reading each line and it has gone MUCH further than ever before, so maybe this was it. (If you don't hear from me again, assume it was)

 

[Skydiver]

Unfortunately I am running an old .NET framework where this is not yet implemented.

Then you have bigger problems. Microsoft ended support for .Net 4.0 back in Jan 2016.

Support Ending for the .NET Framework 4, 4.5 and 4.5.1 - .NET Blog

Updated (2017): See .NET Framework Releases to learn about newer releases. As previously announced, starting January 12, 2016 Microsoft will no longer provide security updates, technical support or hotfixes for .NET 4, 4.5, and 4.5.1 frameworks. All other framework versions, including 3.5...

devblogs.microsoft.com

 

[Cyberduke]

Yeah, I am fully aware of the problem(and more), Luckily this is just a "favour" I am doing for someone and not my day-job. With the delay its running quite slow but we are almost 10% there, so I am positive.

 

[NoUserHere]

I am running an old .NET framework where this is not yet implemented. Sadly.

I hope you have a good reason for running old junk?

You should be using the latest.

 

[Skydiver]

Yeah, I am fully aware of the problem(and more), Luckily this is just a "favour" I am doing for someone and not my day-job.

A key take away from that link I put in regarding end-of-life is:

As previously announced, starting January 12, 2016 Microsoft will no longer provide security updates, technical support or hotfixes for .NET 4, 4.5, and 4.5.1 frameworks.

(Emphasis mine.) Unless that system is completely air gapped, and the operators practice good hygiene protocols religiously, I would worry about the security of that machine. But given what you just said on post #12, it sounds like access to the machine is relatively easy so that machine is extremely vulnerable.

 

[Skydiver]

Anyway, the primary problem of getting the time out is from this:

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

For now lets ignore the fact that you are setting yourself up for a SQL injection attack by not using a parameterized query. A parameterized query won't really help you with the server timeout exception you are getting.

The problem is that you are simply asking for too much data in a single query. You'll need to get the data in batches. It's called "paging". This link may help:

Introduction to pagination in SQL Server

This article explains pagination basics, and shows different ways for using the pagination in non-Microsoft database platforms and in Microsoft SQL Server 2012

www.sqlshack.com

If ~150 PDFs is the breaking point, then plan on getting just 50 to 75 PDFs per "page". Loop over the pages.