SQL Server error: Timeout expired

Question

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 run out of ideas a long time ago, so I am not
sure what else I can do?   Below is the error and basic code saving each PDF.    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 110   Source: .Net SqlClient Data Provider   TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
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,…

Rajanikant Hawaldar · Answer

OK, another improvement what you can do instead of normal query create stored procedure and use. hope you know the use/purpose of stored procedure

Amit Gupta · Answer

@  Ian Jannasch   Try the following optimized way to get your results Get the values in chunks like 10 slots at a time and close the connection (change slots accordingly) Write/process your files according to your requirement. Repeat step 1 until you reach end of your list. This will consume less resources and process your operation much faster without any timeout.   Good Luck!

Ian Jannasch · Answer

Thank you for the suggestions. Couple of things:

I used the suggested query and changed timout to 400 with no help.
I ran this on a duplicate databse on a different faster machine and it seems to have no problem. So it could be to do with the fact that where I run it is slow.

Rajanikant Hawaldar · Answer

Changed your query, Please observe it and use this if you still face problem then increase timeout "SELECT  dbo.DBDocument.DocumentName,dbo.DBDocBlob.Object FROM dbo.DBDocBlob, dbo.DBDocument WHERE  dbo.DBDocBlob.Object IS NOT NULL AND  dbo.DBDocBlob.DocumentID = dbo.DBDocument.DocumentID AND dbo.DBDocument.CustomerCode = '"  + CustID +  "'"

Amit Gupta · Answer

Either optimize your query (if possible) or increase timeout in connection string