|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Timeout - max pool size reachedI got the following message on my Sql Server 2000:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. I understand what this is when dealing with Web Application. But this problem is a small program that just opens and closes an sql table. It is running as a service and not part of a web application. Why would I get this message? Thanks, Tom
Show quote
"tshad" <tscheider***@ftsolutions.com> wrote in message Because you have a connection leak. You are opening a connection and not news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >I got the following message on my Sql Server 2000: > > Timeout expired. The timeout period elapsed prior to obtaining a > connection from the pool. This may have occurred because all pooled > connections were in use and max pool size was reached. > > I understand what this is when dealing with Web Application. But this > problem is a small program that just opens and closes an sql table. It is > running as a service and not part of a web application. > > Why would I get this message? > closing it before letting the SqlConnection object go out of scope. David Are you explicitly closing the connection (or using a using statement to
open the connection) after accessing the data? Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >I got the following message on my Sql Server 2000: > > Timeout expired. The timeout period elapsed prior to obtaining a > connection from the pool. This may have occurred because all pooled > connections were in use and max pool size was reached. > > I understand what this is when dealing with Web Application. But this > problem is a small program that just opens and closes an sql table. It is > running as a service and not part of a web application. > > Why would I get this message? > > Thanks, > > Tom > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message I am doing the following when I close the connection whether in the normal news:%23%23FaUT3tGHA.2020@TK2MSFTNGP03.phx.gbl... > Are you explicitly closing the connection (or using a using statement to > open the connection) after accessing the data? flow (try) or in the catch area. Private Sub dbCloseConnection() If SqlConnection1.State.ToString <> "Closed" Then SqlConnection1.Close() End If End Sub Tom Show quote > > -- > Peter DeBetta, MVP - SQL Server > http://sqlblog.com > -- > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >>I got the following message on my Sql Server 2000: >> >> Timeout expired. The timeout period elapsed prior to obtaining a >> connection from the pool. This may have occurred because all pooled >> connections were in use and max pool size was reached. >> >> I understand what this is when dealing with Web Application. But this >> problem is a small program that just opens and closes an sql table. It >> is running as a service and not part of a web application. >> >> Why would I get this message? >> >> Thanks, >> >> Tom >> > > Send more details (meaning more code). Specifically, send the code that
calls this Sub (including all variable declares affecting this code) and the code that calls that code. Also, what version of .NET Framework are you using? Have you looked at all code that opens a connection to ensure that you are explicitly closing it. I have seen great code that in one instance overlooks one database connection being opened and not closed which caused a connection leak. Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:OdXAL%23xuGHA.4972@TK2MSFTNGP05.phx.gbl... > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message > news:%23%23FaUT3tGHA.2020@TK2MSFTNGP03.phx.gbl... >> Are you explicitly closing the connection (or using a using statement to >> open the connection) after accessing the data? > > I am doing the following when I close the connection whether in the normal > flow (try) or in the catch area. > > Private Sub dbCloseConnection() > If SqlConnection1.State.ToString <> "Closed" Then > SqlConnection1.Close() > End If > End Sub > > Tom > >> >> -- >> Peter DeBetta, MVP - SQL Server >> http://sqlblog.com >> -- >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >>>I got the following message on my Sql Server 2000: >>> >>> Timeout expired. The timeout period elapsed prior to obtaining a >>> connection from the pool. This may have occurred because all pooled >>> connections were in use and max pool size was reached. >>> >>> I understand what this is when dealing with Web Application. But this >>> problem is a small program that just opens and closes an sql table. It >>> is running as a service and not part of a web application. >>> >>> Why would I get this message? >>> >>> Thanks, >>> >>> Tom >>> >> >> > > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message Here is the code (with most of debugging taken out). There are nested calls news:eDmmGl3uGHA.3264@TK2MSFTNGP03.phx.gbl... > Send more details (meaning more code). Specifically, send the code that > calls this Sub (including all variable declares affecting this code) and > the code that calls that code. Also, what version of .NET Framework are > you using? Have you looked at all code that opens a connection to ensure > that you are explicitly closing it. I have seen great code that in one > instance overlooks one database connection being opened and not closed > which caused a connection leak. to Sql, so I left most of the code in. The big problem is that at some point we get to the "About to execute the Reader", I get the following messages: Failure in PollAndSendEmail(About to execute the reader): There is already an open DataReader associated with this Connection which must be closed first. and Failure in PollAndSendEmail(About to execute the reader): Object reference not set to an instance of an object. (twice) and this will happen over and over then will stop and I can't stop the service at this point. The code is: ******************************************************************** Imports System.ServiceProcess Imports System.Web.Mail Imports System.Data.SqlClient Imports System.IO Public Class EmailPoller Inherits System.ServiceProcess.ServiceBase Friend WithEvents cmd_get_poller_settings As System.Data.SqlClient.SqlCommand Friend WithEvents cmd_update_msg As System.Data.SqlClient.SqlCommand Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand Const ATTEMPTED = 2 Const SENT = 3 Dim strProgress As String Dim debugging As Boolean = True Protected Overrides Sub OnStart(ByVal args() As String) 'LogInfo("EmailPoller Started") EmailQueueTimer.Start() End Sub Protected Overrides Sub OnStop() 'LogInfo("EmailPoller Stopped") EmailQueueTimer.Stop() End Sub Private Sub EmailQueueTimer_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles EmailQueueTimer.Elapsed 'LogInfo("EmailPoller Timer Interval") PollAndSendEmail() End Sub Private Sub PollAndSendEmail() Try 'LogInfo("PollAndSend() Starting") Dim strSMTPServer, strSMTPUserID, strSMTPPwd As String Dim isHTML, intInterval, intMsgQuantity, intProcessMessages As Integer Dim DR As SqlDataReader ' grab our poller/email settings from DB strProgress = "About to open connection" dbOpenConnection() strProgress = "About to execute the reader" DR = cmd_get_poller_settings.ExecuteReader While DR.Read 'LogInfo("Settings Retrieved") intProcessMessages = DR("process") intInterval = DR("interval") intMsgQuantity = DR("msg_quantity") strSMTPServer = DR("smtp_server") End While strProgress = "About to close the reader" DR.Close() strProgress = "About to close the connection" dbCloseConnection() ' reset our timer interval we just got from the database query. ' allows "tuning" the poller from a web app (in milliseconds) 'LogInfo("Resetting Timer based on retreived settings") strProgress = "About to reset the timer" EmailQueueTimer.Interval = intInterval * 1000 If intProcessMessages = 1 Then ' get TOP X messages (per intMsgQuantity from above) 'LogInfo("Getting Top X messages") Dim tblMessages As DataTable = New DataTable Dim messageRow As DataRow Dim bSent As Boolean Dim currMessage As CEmailMessage ' custom class above Dim colLocalEmailQueue As Collection = New Collection ' keep track of send success bSent = 0 ' open the DB again (just in case) and fill a table with ' intMsgQuantity disconnected messages strProgress = "About to set adaptor parms" With da_get_messages.SelectCommand.Parameters .Item("@quantity").Value = intMsgQuantity End With strProgress = "About to open connection #2" dbOpenConnection() strProgress = "About to fill tblMessages" da_get_messages.Fill(tblMessages) strProgress = "About to close connection #2" dbCloseConnection() 'LogInfo("About to Send " & tblMessages.Rows.Count.ToString & " messages") ' stash and send the disconnected messages strProgress = "About to iterate messages" For Each messageRow In tblMessages.Rows 'LogInfo("Sending a Message") currMessage = New CEmailMessage strProgress = "About to stash current record" ' stash current record's info currMessage.id = messageRow.Item("id") currMessage.strFrom = messageRow.Item("from_address") currMessage.strTo = messageRow.Item("to_addresses") currMessage.strCC = messageRow.Item("cc_addresses") currMessage.strBCC = messageRow.Item("bcc_addresses") currMessage.strSubject = messageRow.Item("subject") currMessage.strBody = messageRow.Item("body") currMessage.isHTML = messageRow.Item("is_html") currMessage.dateAttempted = messageRow.Item("date_attempted") currMessage.errMessage = "" ' attempt to send it strProgress = "About to send a message" Try 'LogInfo("Sending the Email") Dim Message As New MailMessage Message.From = currMessage.strFrom Message.To = currMessage.strTo Message.Cc = currMessage.strCC Message.Bcc = currMessage.strBCC Message.Subject = currMessage.strSubject Message.Body = currMessage.strBody If currMessage.isHTML Then Message.BodyFormat = MailFormat.Html Else Message.BodyFormat = MailFormat.Text End If SmtpMail.SmtpServer.Insert(0, strSMTPServer) SmtpMail.Send(Message) bSent = True Catch ex As Exception ' the message failed to send so bSent remains False currMessage.errMessage = ex.message LogError("Exception during EmailPoller send attempt: " & ex.message) End Try If bSent = True Then ' reset our sending indicator for the next message 'LogInfo("Message Sent") bSent = False currMessage.status = SENT Else 'LogInfo("Message Attempted") currMessage.status = ATTEMPTED End If ' store the date on which we either sent or attempted currMessage.dateAttempted = Now() ' stash it in a collection that we'll write back to DB colLocalEmailQueue.Add(currMessage) 'LogInfo("Message Stashed in update queue") Next ' write all the messages back to DB updating status ' and date/time attempted Dim intSuccess As Integer 'LogInfo("Post Send Updating") strProgress = "About to write updates back (opens a connection)" For Each currMessage In colLocalEmailQueue 'LogInfo("Updating a Message") dbOpenConnection() ' called every time just in case With cmd_update_msg.Parameters .Item("@id").Value = currMessage.id .Item("@status").Value = currMessage.status .Item("@date_attempted").Value = currMessage.dateAttempted .Item("@error_message").Value = currMessage.errMessage End With ' do it strProgress = "About to update a message" intSuccess = cmd_update_msg.ExecuteScalar() 'LogInfo("Message Updated") If intSuccess <> 1 Then LogError("Email Poller failed to update queued message status. Msg ID: " & currMessage.id.ToString) End If Next strProgress = "About to close connection #3" ' that's it! 'LogInfo("PollAndSend() Complete") dbCloseConnection() If debugging Then DebugPoller("After Closing connection") End If End If Catch ex As Exception dbCloseConnection() LogError("Failure in PollAndSendEmail(" & strProgress & "): " & ex.Message) End Try End Sub Private Sub LogError(ByVal strError As String) Dim sSource As String Dim sLog As String Dim sEvent As String Dim sMachine As String sSource = "FTS EmailPoller" sLog = "Application" sEvent = strError sMachine = "." If Not EventLog.SourceExists(sSource, sMachine) Then EventLog.CreateEventSource(sSource, sLog, sMachine) End If Dim ELog As New EventLog(sLog, sMachine, sSource) ELog.WriteEntry(sEvent, EventLogEntryType.Warning) End Sub Private Sub LogInfo(ByVal strInfo As String) Dim sSource As String Dim sLog As String Dim sEvent As String Dim sMachine As String sSource = "EmailPoller" sLog = "Application" sEvent = strInfo sMachine = "." If Not EventLog.SourceExists(sSource, sMachine) Then EventLog.CreateEventSource(sSource, sLog, sMachine) End If Dim ELog As New EventLog(sLog, sMachine, sSource) ELog.WriteEntry(sEvent, EventLogEntryType.Information) End Sub Private Function dbOpenConnection() As Boolean If IsNothing(SqlConnection1) Then LogInfo("SqlConnection1 was nothing... Newing()") SqlConnection1 = New System.Data.SqlClient.SqlConnection("Persist Security Info=False;Data Source=VS;Initial Catalog=fs;User ID=xxx;Password=yyy;") End If If SqlConnection1.State.ToString <> "Open" Then 'LogInfo("SqlConnection1 Open()") SqlConnection1.Open() End If Return True End Function Private Sub dbCloseConnection() If SqlConnection1.State.ToString <> "Closed" Then 'LogInfo("SqlConnection1 close()") SqlConnection1.Close() End If End Sub End Class Class CEmailMessage Public id As Integer Public strTo As String Public strCC As String Public strBCC As String Public strFrom As String Public strSubject As String Public strBody As String Public isHTML As Integer Public dateAttempted As Date Public status As Integer Public errMessage As String End Class **************************************************************** Could it be something with my connection string? Thanks, Tom Show quote > > -- > Peter DeBetta, MVP - SQL Server > http://sqlblog.com > -- > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:OdXAL%23xuGHA.4972@TK2MSFTNGP05.phx.gbl... >> "Peter W. DeBetta" <debet***@hotmail.com> wrote in message >> news:%23%23FaUT3tGHA.2020@TK2MSFTNGP03.phx.gbl... >>> Are you explicitly closing the connection (or using a using statement to >>> open the connection) after accessing the data? >> >> I am doing the following when I close the connection whether in the >> normal flow (try) or in the catch area. >> >> Private Sub dbCloseConnection() >> If SqlConnection1.State.ToString <> "Closed" Then >> SqlConnection1.Close() >> End If >> End Sub >> >> Tom >> >>> >>> -- >>> Peter DeBetta, MVP - SQL Server >>> http://sqlblog.com >>> -- >>> >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >>>>I got the following message on my Sql Server 2000: >>>> >>>> Timeout expired. The timeout period elapsed prior to obtaining a >>>> connection from the pool. This may have occurred because all pooled >>>> connections were in use and max pool size was reached. >>>> >>>> I understand what this is when dealing with Web Application. But this >>>> problem is a small program that just opens and closes an sql table. It >>>> is running as a service and not part of a web application. >>>> >>>> Why would I get this message? >>>> >>>> Thanks, >>>> >>>> Tom >>>> >>> >>> >> >> > > The code was not complete enough (where are SqlConnection1, da_get_messages,
and EmailQueueTimer defined, for example). Also, without actually debugging this, it is hard to diagnose where the issue may be occurring. That aside, connection-wsie, things look in order, but I suspect the issue may be a combination of code that calls this code and the exception handling. Let me illustrate one possible scenario: Let's say another piece of code instantiates the EmailPoller class. The timer event causes PollAndSendEmail to be executed, but some unmanaged exception occurs, which is not caught by the Catch ex As Exception statement. However, the calling code does catch that exception with a simple Catch statement. Now the code creates a new instance of EmailPoller, which opens a connection, and well, you get the idea. You have a leak and you are going to have to debug to find. Anyway, this is not so much a SQL Programming issue as it is a VB.NET programming issue. On a development server, some things you can do to see what's going on while the service is running. 1. Use SQL Profiler to see who is connecting and what active connections are there. 2. Run the sp_who or sp_who2 stored procs in Query Analyzer 3. Create a log in a text file for every database open and close and simply write one line for each, as shown here Open Close After a period of time, stop the service, import the text file into SQL Server, and do a count each of open and close to see if things jive. They should be equal. If not, then add additional info to the text log (date, time, connection state info, and so on) so you can narrow down when and what is causing you the grief. I'm done here, as I don't think I could help via this medium... Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:uL1G48$uGHA.4624@TK2MSFTNGP02.phx.gbl... > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message > news:eDmmGl3uGHA.3264@TK2MSFTNGP03.phx.gbl... >> Send more details (meaning more code). Specifically, send the code that >> calls this Sub (including all variable declares affecting this code) and >> the code that calls that code. Also, what version of .NET Framework are >> you using? Have you looked at all code that opens a connection to ensure >> that you are explicitly closing it. I have seen great code that in one >> instance overlooks one database connection being opened and not closed >> which caused a connection leak. > > Here is the code (with most of debugging taken out). There are nested > calls to Sql, so I left most of the code in. The big problem is that at > some point we get to the "About to execute the Reader", I get the > following messages: > > Failure in PollAndSendEmail(About to execute the reader): There is already > an open DataReader associated with this Connection which must be closed > first. > > and > > Failure in PollAndSendEmail(About to execute the reader): Object reference > not set to an instance of an object. (twice) > > and this will happen over and over then will stop and I can't stop the > service at this point. > > The code is: > ******************************************************************** > Imports System.ServiceProcess > Imports System.Web.Mail > Imports System.Data.SqlClient > Imports System.IO > > Public Class EmailPoller > Inherits System.ServiceProcess.ServiceBase > Friend WithEvents cmd_get_poller_settings As > System.Data.SqlClient.SqlCommand > Friend WithEvents cmd_update_msg As System.Data.SqlClient.SqlCommand > Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand > Const ATTEMPTED = 2 > Const SENT = 3 > Dim strProgress As String > Dim debugging As Boolean = True > > Protected Overrides Sub OnStart(ByVal args() As String) > 'LogInfo("EmailPoller Started") > EmailQueueTimer.Start() > End Sub > > Protected Overrides Sub OnStop() > 'LogInfo("EmailPoller Stopped") > EmailQueueTimer.Stop() > End Sub > > Private Sub EmailQueueTimer_Elapsed(ByVal sender As System.Object, > ByVal e As System.Timers.ElapsedEventArgs) Handles EmailQueueTimer.Elapsed > 'LogInfo("EmailPoller Timer Interval") > PollAndSendEmail() > End Sub > > Private Sub PollAndSendEmail() > Try > 'LogInfo("PollAndSend() Starting") > > Dim strSMTPServer, strSMTPUserID, strSMTPPwd As String > Dim isHTML, intInterval, intMsgQuantity, intProcessMessages As > Integer > Dim DR As SqlDataReader > > ' grab our poller/email settings from DB > strProgress = "About to open connection" > dbOpenConnection() > > strProgress = "About to execute the reader" > DR = cmd_get_poller_settings.ExecuteReader > While DR.Read > 'LogInfo("Settings Retrieved") > intProcessMessages = DR("process") > intInterval = DR("interval") > intMsgQuantity = DR("msg_quantity") > strSMTPServer = DR("smtp_server") > End While > > strProgress = "About to close the reader" > DR.Close() > > strProgress = "About to close the connection" > dbCloseConnection() > > ' reset our timer interval we just got from the database query. > ' allows "tuning" the poller from a web app (in milliseconds) > 'LogInfo("Resetting Timer based on retreived settings") > strProgress = "About to reset the timer" > EmailQueueTimer.Interval = intInterval * 1000 > > If intProcessMessages = 1 Then > ' get TOP X messages (per intMsgQuantity from above) > 'LogInfo("Getting Top X messages") > Dim tblMessages As DataTable = New DataTable > Dim messageRow As DataRow > Dim bSent As Boolean > Dim currMessage As CEmailMessage ' custom class above > Dim colLocalEmailQueue As Collection = New Collection > > ' keep track of send success > bSent = 0 > > ' open the DB again (just in case) and fill a table with > ' intMsgQuantity disconnected messages > strProgress = "About to set adaptor parms" > With da_get_messages.SelectCommand.Parameters > .Item("@quantity").Value = intMsgQuantity > End With > > strProgress = "About to open connection #2" > dbOpenConnection() > > strProgress = "About to fill tblMessages" > da_get_messages.Fill(tblMessages) > > strProgress = "About to close connection #2" > dbCloseConnection() > > 'LogInfo("About to Send " & tblMessages.Rows.Count.ToString > & " messages") > > ' stash and send the disconnected messages > strProgress = "About to iterate messages" > For Each messageRow In tblMessages.Rows > 'LogInfo("Sending a Message") > > currMessage = New CEmailMessage > > strProgress = "About to stash current record" > ' stash current record's info > currMessage.id = messageRow.Item("id") > currMessage.strFrom = messageRow.Item("from_address") > currMessage.strTo = messageRow.Item("to_addresses") > currMessage.strCC = messageRow.Item("cc_addresses") > currMessage.strBCC = messageRow.Item("bcc_addresses") > currMessage.strSubject = messageRow.Item("subject") > currMessage.strBody = messageRow.Item("body") > currMessage.isHTML = messageRow.Item("is_html") > currMessage.dateAttempted = > messageRow.Item("date_attempted") > currMessage.errMessage = "" > > ' attempt to send it > strProgress = "About to send a message" > Try > 'LogInfo("Sending the Email") > Dim Message As New MailMessage > Message.From = currMessage.strFrom > Message.To = currMessage.strTo > Message.Cc = currMessage.strCC > Message.Bcc = currMessage.strBCC > Message.Subject = currMessage.strSubject > Message.Body = currMessage.strBody > If currMessage.isHTML Then > Message.BodyFormat = MailFormat.Html > Else > Message.BodyFormat = MailFormat.Text > End If > SmtpMail.SmtpServer.Insert(0, strSMTPServer) > SmtpMail.Send(Message) > > bSent = True > Catch ex As Exception > ' the message failed to send so bSent remains False > currMessage.errMessage = ex.message > LogError("Exception during EmailPoller send > attempt: " & ex.message) > End Try > > If bSent = True Then > ' reset our sending indicator for the next message > 'LogInfo("Message Sent") > bSent = False > currMessage.status = SENT > Else > 'LogInfo("Message Attempted") > currMessage.status = ATTEMPTED > End If > > ' store the date on which we either sent or attempted > currMessage.dateAttempted = Now() > > ' stash it in a collection that we'll write back to DB > colLocalEmailQueue.Add(currMessage) > 'LogInfo("Message Stashed in update queue") > Next > > ' write all the messages back to DB updating status > ' and date/time attempted > Dim intSuccess As Integer > > 'LogInfo("Post Send Updating") > > strProgress = "About to write updates back (opens a > connection)" > For Each currMessage In colLocalEmailQueue > 'LogInfo("Updating a Message") > dbOpenConnection() ' called every time just in case > With cmd_update_msg.Parameters > .Item("@id").Value = currMessage.id > .Item("@status").Value = currMessage.status > .Item("@date_attempted").Value = > currMessage.dateAttempted > .Item("@error_message").Value = > currMessage.errMessage > End With > > ' do it > strProgress = "About to update a message" > intSuccess = cmd_update_msg.ExecuteScalar() > 'LogInfo("Message Updated") > > If intSuccess <> 1 Then > LogError("Email Poller failed to update queued > message status. Msg ID: " & currMessage.id.ToString) > End If > Next > > strProgress = "About to close connection #3" > ' that's it! > 'LogInfo("PollAndSend() Complete") > dbCloseConnection() > If debugging Then > DebugPoller("After Closing connection") > End If > End If > Catch ex As Exception > dbCloseConnection() > LogError("Failure in PollAndSendEmail(" & strProgress & "): " & > ex.Message) > End Try > End Sub > > Private Sub LogError(ByVal strError As String) > Dim sSource As String > Dim sLog As String > Dim sEvent As String > Dim sMachine As String > > sSource = "FTS EmailPoller" > sLog = "Application" > sEvent = strError > sMachine = "." > > If Not EventLog.SourceExists(sSource, sMachine) Then > EventLog.CreateEventSource(sSource, sLog, sMachine) > End If > > Dim ELog As New EventLog(sLog, sMachine, sSource) > ELog.WriteEntry(sEvent, EventLogEntryType.Warning) > End Sub > > Private Sub LogInfo(ByVal strInfo As String) > Dim sSource As String > Dim sLog As String > Dim sEvent As String > Dim sMachine As String > > sSource = "EmailPoller" > sLog = "Application" > sEvent = strInfo > sMachine = "." > > If Not EventLog.SourceExists(sSource, sMachine) Then > EventLog.CreateEventSource(sSource, sLog, sMachine) > End If > > Dim ELog As New EventLog(sLog, sMachine, sSource) > ELog.WriteEntry(sEvent, EventLogEntryType.Information) > End Sub > > Private Function dbOpenConnection() As Boolean > If IsNothing(SqlConnection1) Then > LogInfo("SqlConnection1 was nothing... Newing()") > SqlConnection1 = New > System.Data.SqlClient.SqlConnection("Persist Security Info=False;Data > Source=VS;Initial Catalog=fs;User ID=xxx;Password=yyy;") > End If > > If SqlConnection1.State.ToString <> "Open" Then > 'LogInfo("SqlConnection1 Open()") > SqlConnection1.Open() > End If > > Return True > End Function > > Private Sub dbCloseConnection() > If SqlConnection1.State.ToString <> "Closed" Then > 'LogInfo("SqlConnection1 close()") > SqlConnection1.Close() > End If > End Sub > End Class > > Class CEmailMessage > Public id As Integer > Public strTo As String > Public strCC As String > Public strBCC As String > Public strFrom As String > Public strSubject As String > Public strBody As String > Public isHTML As Integer > Public dateAttempted As Date > Public status As Integer > Public errMessage As String > End Class > **************************************************************** > > Could it be something with my connection string? > > Thanks, > > Tom >> >> -- >> Peter DeBetta, MVP - SQL Server >> http://sqlblog.com >> -- >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:OdXAL%23xuGHA.4972@TK2MSFTNGP05.phx.gbl... >>> "Peter W. DeBetta" <debet***@hotmail.com> wrote in message >>> news:%23%23FaUT3tGHA.2020@TK2MSFTNGP03.phx.gbl... >>>> Are you explicitly closing the connection (or using a using statement >>>> to open the connection) after accessing the data? >>> >>> I am doing the following when I close the connection whether in the >>> normal flow (try) or in the catch area. >>> >>> Private Sub dbCloseConnection() >>> If SqlConnection1.State.ToString <> "Closed" Then >>> SqlConnection1.Close() >>> End If >>> End Sub >>> >>> Tom >>> >>>> >>>> -- >>>> Peter DeBetta, MVP - SQL Server >>>> http://sqlblog.com >>>> -- >>>> >>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>> news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >>>>>I got the following message on my Sql Server 2000: >>>>> >>>>> Timeout expired. The timeout period elapsed prior to obtaining a >>>>> connection from the pool. This may have occurred because all pooled >>>>> connections were in use and max pool size was reached. >>>>> >>>>> I understand what this is when dealing with Web Application. But this >>>>> problem is a small program that just opens and closes an sql table. >>>>> It is running as a service and not part of a web application. >>>>> >>>>> Why would I get this message? >>>>> >>>>> Thanks, >>>>> >>>>> Tom >>>>> >>>> >>>> >>> >>> >> >> > > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message I didn't include that as that is the Sql container that is set up by VS. I news:uVDrnADvGHA.3372@TK2MSFTNGP02.phx.gbl... > The code was not complete enough (where are SqlConnection1, > da_get_messages, and EmailQueueTimer defined, for example). Also, without > actually debugging this, it is hard to diagnose where the issue may be > occurring. was concerned that there was a problem with that. Show quote > Agreed.> That aside, connection-wsie, things look in order, but I suspect the issue > may be a combination of code that calls this code and the exception > handling. Let me illustrate one possible scenario: Let's say another piece > of code instantiates the EmailPoller class. The timer event causes > PollAndSendEmail to be executed, but some unmanaged exception occurs, > which is not caught by the Catch ex As Exception statement. However, the > calling code does catch that exception with a simple Catch statement. Now > the code creates a new instance of EmailPoller, which opens a connection, > and well, you get the idea. You have a leak and you are going to have to > debug to find. > > Anyway, this is not so much a SQL Programming issue as it is a VB.NET > programming issue. > Can't really do this as this service can run for days before it happens.> On a development server, some things you can do to see what's going on > while the service is running. > 1. Use SQL Profiler to see who is connecting and what active connections > are there. > 2. Run the sp_who or sp_who2 stored procs in Query Analyzer > 3. Create a log in a text file for every database open and close and Actually, I am already doing that. I actually have about 15 lines getting > simply write one line for each, as shown here > Open > Close sent to a text file as different things happen, such as opening a connection (would have a line saying I am about to open the connection just before the OPEN and then a line after the OPEN saying that the connection was opened), various points in the program and Catch area. When the program dies, I should be able to track exactly what happened. Thanks, Tom Show quote > After a period of time, stop the service, import the text file into SQL > Server, and do a count each of open and close to see if things jive. They > should be equal. If not, then add additional info to the text log (date, > time, connection state info, and so on) so you can narrow down when and > what is causing you the grief. > > I'm done here, as I don't think I could help via this medium... > > -- > Peter DeBetta, MVP - SQL Server > http://sqlblog.com > -- > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:uL1G48$uGHA.4624@TK2MSFTNGP02.phx.gbl... >> "Peter W. DeBetta" <debet***@hotmail.com> wrote in message >> news:eDmmGl3uGHA.3264@TK2MSFTNGP03.phx.gbl... >>> Send more details (meaning more code). Specifically, send the code that >>> calls this Sub (including all variable declares affecting this code) and >>> the code that calls that code. Also, what version of .NET Framework are >>> you using? Have you looked at all code that opens a connection to ensure >>> that you are explicitly closing it. I have seen great code that in one >>> instance overlooks one database connection being opened and not closed >>> which caused a connection leak. >> >> Here is the code (with most of debugging taken out). There are nested >> calls to Sql, so I left most of the code in. The big problem is that at >> some point we get to the "About to execute the Reader", I get the >> following messages: >> >> Failure in PollAndSendEmail(About to execute the reader): There is >> already an open DataReader associated with this Connection which must be >> closed first. >> >> and >> >> Failure in PollAndSendEmail(About to execute the reader): Object >> reference not set to an instance of an object. (twice) >> >> and this will happen over and over then will stop and I can't stop the >> service at this point. >> >> The code is: >> ******************************************************************** >> Imports System.ServiceProcess >> Imports System.Web.Mail >> Imports System.Data.SqlClient >> Imports System.IO >> >> Public Class EmailPoller >> Inherits System.ServiceProcess.ServiceBase >> Friend WithEvents cmd_get_poller_settings As >> System.Data.SqlClient.SqlCommand >> Friend WithEvents cmd_update_msg As System.Data.SqlClient.SqlCommand >> Friend WithEvents SqlSelectCommand1 As >> System.Data.SqlClient.SqlCommand >> Const ATTEMPTED = 2 >> Const SENT = 3 >> Dim strProgress As String >> Dim debugging As Boolean = True >> >> Protected Overrides Sub OnStart(ByVal args() As String) >> 'LogInfo("EmailPoller Started") >> EmailQueueTimer.Start() >> End Sub >> >> Protected Overrides Sub OnStop() >> 'LogInfo("EmailPoller Stopped") >> EmailQueueTimer.Stop() >> End Sub >> >> Private Sub EmailQueueTimer_Elapsed(ByVal sender As System.Object, >> ByVal e As System.Timers.ElapsedEventArgs) Handles >> EmailQueueTimer.Elapsed >> 'LogInfo("EmailPoller Timer Interval") >> PollAndSendEmail() >> End Sub >> >> Private Sub PollAndSendEmail() >> Try >> 'LogInfo("PollAndSend() Starting") >> >> Dim strSMTPServer, strSMTPUserID, strSMTPPwd As String >> Dim isHTML, intInterval, intMsgQuantity, intProcessMessages As >> Integer >> Dim DR As SqlDataReader >> >> ' grab our poller/email settings from DB >> strProgress = "About to open connection" >> dbOpenConnection() >> >> strProgress = "About to execute the reader" >> DR = cmd_get_poller_settings.ExecuteReader >> While DR.Read >> 'LogInfo("Settings Retrieved") >> intProcessMessages = DR("process") >> intInterval = DR("interval") >> intMsgQuantity = DR("msg_quantity") >> strSMTPServer = DR("smtp_server") >> End While >> >> strProgress = "About to close the reader" >> DR.Close() >> >> strProgress = "About to close the connection" >> dbCloseConnection() >> >> ' reset our timer interval we just got from the database >> query. >> ' allows "tuning" the poller from a web app (in milliseconds) >> 'LogInfo("Resetting Timer based on retreived settings") >> strProgress = "About to reset the timer" >> EmailQueueTimer.Interval = intInterval * 1000 >> >> If intProcessMessages = 1 Then >> ' get TOP X messages (per intMsgQuantity from above) >> 'LogInfo("Getting Top X messages") >> Dim tblMessages As DataTable = New DataTable >> Dim messageRow As DataRow >> Dim bSent As Boolean >> Dim currMessage As CEmailMessage ' custom class above >> Dim colLocalEmailQueue As Collection = New Collection >> >> ' keep track of send success >> bSent = 0 >> >> ' open the DB again (just in case) and fill a table with >> ' intMsgQuantity disconnected messages >> strProgress = "About to set adaptor parms" >> With da_get_messages.SelectCommand.Parameters >> .Item("@quantity").Value = intMsgQuantity >> End With >> >> strProgress = "About to open connection #2" >> dbOpenConnection() >> >> strProgress = "About to fill tblMessages" >> da_get_messages.Fill(tblMessages) >> >> strProgress = "About to close connection #2" >> dbCloseConnection() >> >> 'LogInfo("About to Send " & >> tblMessages.Rows.Count.ToString & " messages") >> >> ' stash and send the disconnected messages >> strProgress = "About to iterate messages" >> For Each messageRow In tblMessages.Rows >> 'LogInfo("Sending a Message") >> >> currMessage = New CEmailMessage >> >> strProgress = "About to stash current record" >> ' stash current record's info >> currMessage.id = messageRow.Item("id") >> currMessage.strFrom = messageRow.Item("from_address") >> currMessage.strTo = messageRow.Item("to_addresses") >> currMessage.strCC = messageRow.Item("cc_addresses") >> currMessage.strBCC = messageRow.Item("bcc_addresses") >> currMessage.strSubject = messageRow.Item("subject") >> currMessage.strBody = messageRow.Item("body") >> currMessage.isHTML = messageRow.Item("is_html") >> currMessage.dateAttempted = >> messageRow.Item("date_attempted") >> currMessage.errMessage = "" >> >> ' attempt to send it >> strProgress = "About to send a message" >> Try >> 'LogInfo("Sending the Email") >> Dim Message As New MailMessage >> Message.From = currMessage.strFrom >> Message.To = currMessage.strTo >> Message.Cc = currMessage.strCC >> Message.Bcc = currMessage.strBCC >> Message.Subject = currMessage.strSubject >> Message.Body = currMessage.strBody >> If currMessage.isHTML Then >> Message.BodyFormat = MailFormat.Html >> Else >> Message.BodyFormat = MailFormat.Text >> End If >> SmtpMail.SmtpServer.Insert(0, strSMTPServer) >> SmtpMail.Send(Message) >> >> bSent = True >> Catch ex As Exception >> ' the message failed to send so bSent remains >> False >> currMessage.errMessage = ex.message >> LogError("Exception during EmailPoller send >> attempt: " & ex.message) >> End Try >> >> If bSent = True Then >> ' reset our sending indicator for the next message >> 'LogInfo("Message Sent") >> bSent = False >> currMessage.status = SENT >> Else >> 'LogInfo("Message Attempted") >> currMessage.status = ATTEMPTED >> End If >> >> ' store the date on which we either sent or attempted >> currMessage.dateAttempted = Now() >> >> ' stash it in a collection that we'll write back to DB >> colLocalEmailQueue.Add(currMessage) >> 'LogInfo("Message Stashed in update queue") >> Next >> >> ' write all the messages back to DB updating status >> ' and date/time attempted >> Dim intSuccess As Integer >> >> 'LogInfo("Post Send Updating") >> >> strProgress = "About to write updates back (opens a >> connection)" >> For Each currMessage In colLocalEmailQueue >> 'LogInfo("Updating a Message") >> dbOpenConnection() ' called every time just in case >> With cmd_update_msg.Parameters >> .Item("@id").Value = currMessage.id >> .Item("@status").Value = currMessage.status >> .Item("@date_attempted").Value = >> currMessage.dateAttempted >> .Item("@error_message").Value = >> currMessage.errMessage >> End With >> >> ' do it >> strProgress = "About to update a message" >> intSuccess = cmd_update_msg.ExecuteScalar() >> 'LogInfo("Message Updated") >> >> If intSuccess <> 1 Then >> LogError("Email Poller failed to update queued >> message status. Msg ID: " & currMessage.id.ToString) >> End If >> Next >> >> strProgress = "About to close connection #3" >> ' that's it! >> 'LogInfo("PollAndSend() Complete") >> dbCloseConnection() >> If debugging Then >> DebugPoller("After Closing connection") >> End If >> End If >> Catch ex As Exception >> dbCloseConnection() >> LogError("Failure in PollAndSendEmail(" & strProgress & "): " >> & ex.Message) >> End Try >> End Sub >> >> Private Sub LogError(ByVal strError As String) >> Dim sSource As String >> Dim sLog As String >> Dim sEvent As String >> Dim sMachine As String >> >> sSource = "FTS EmailPoller" >> sLog = "Application" >> sEvent = strError >> sMachine = "." >> >> If Not EventLog.SourceExists(sSource, sMachine) Then >> EventLog.CreateEventSource(sSource, sLog, sMachine) >> End If >> >> Dim ELog As New EventLog(sLog, sMachine, sSource) >> ELog.WriteEntry(sEvent, EventLogEntryType.Warning) >> End Sub >> >> Private Sub LogInfo(ByVal strInfo As String) >> Dim sSource As String >> Dim sLog As String >> Dim sEvent As String >> Dim sMachine As String >> >> sSource = "EmailPoller" >> sLog = "Application" >> sEvent = strInfo >> sMachine = "." >> >> If Not EventLog.SourceExists(sSource, sMachine) Then >> EventLog.CreateEventSource(sSource, sLog, sMachine) >> End If >> >> Dim ELog As New EventLog(sLog, sMachine, sSource) >> ELog.WriteEntry(sEvent, EventLogEntryType.Information) >> End Sub >> >> Private Function dbOpenConnection() As Boolean >> If IsNothing(SqlConnection1) Then >> LogInfo("SqlConnection1 was nothing... Newing()") >> SqlConnection1 = New >> System.Data.SqlClient.SqlConnection("Persist Security Info=False;Data >> Source=VS;Initial Catalog=fs;User ID=xxx;Password=yyy;") >> End If >> >> If SqlConnection1.State.ToString <> "Open" Then >> 'LogInfo("SqlConnection1 Open()") >> SqlConnection1.Open() >> End If >> >> Return True >> End Function >> >> Private Sub dbCloseConnection() >> If SqlConnection1.State.ToString <> "Closed" Then >> 'LogInfo("SqlConnection1 close()") >> SqlConnection1.Close() >> End If >> End Sub >> End Class >> >> Class CEmailMessage >> Public id As Integer >> Public strTo As String >> Public strCC As String >> Public strBCC As String >> Public strFrom As String >> Public strSubject As String >> Public strBody As String >> Public isHTML As Integer >> Public dateAttempted As Date >> Public status As Integer >> Public errMessage As String >> End Class >> **************************************************************** >> >> Could it be something with my connection string? >> >> Thanks, >> >> Tom >>> >>> -- >>> Peter DeBetta, MVP - SQL Server >>> http://sqlblog.com >>> -- >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:OdXAL%23xuGHA.4972@TK2MSFTNGP05.phx.gbl... >>>> "Peter W. DeBetta" <debet***@hotmail.com> wrote in message >>>> news:%23%23FaUT3tGHA.2020@TK2MSFTNGP03.phx.gbl... >>>>> Are you explicitly closing the connection (or using a using statement >>>>> to open the connection) after accessing the data? >>>> >>>> I am doing the following when I close the connection whether in the >>>> normal flow (try) or in the catch area. >>>> >>>> Private Sub dbCloseConnection() >>>> If SqlConnection1.State.ToString <> "Closed" Then >>>> SqlConnection1.Close() >>>> End If >>>> End Sub >>>> >>>> Tom >>>> >>>>> >>>>> -- >>>>> Peter DeBetta, MVP - SQL Server >>>>> http://sqlblog.com >>>>> -- >>>>> >>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>> news:uJHrh%231tGHA.4456@TK2MSFTNGP06.phx.gbl... >>>>>>I got the following message on my Sql Server 2000: >>>>>> >>>>>> Timeout expired. The timeout period elapsed prior to obtaining a >>>>>> connection from the pool. This may have occurred because all pooled >>>>>> connections were in use and max pool size was reached. >>>>>> >>>>>> I understand what this is when dealing with Web Application. But >>>>>> this problem is a small program that just opens and closes an sql >>>>>> table. It is running as a service and not part of a web application. >>>>>> >>>>>> Why would I get this message? >>>>>> >>>>>> Thanks, >>>>>> >>>>>> Tom >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Show quote
"tshad" <tscheider***@ftsolutions.com> wrote in message This is all due to bad code. Technically speaking you have too much "state" news:uL1G48$uGHA.4624@TK2MSFTNGP02.phx.gbl... > "Peter W. DeBetta" <debet***@hotmail.com> wrote in message > news:eDmmGl3uGHA.3264@TK2MSFTNGP03.phx.gbl... >> Send more details (meaning more code). Specifically, send the code that >> calls this Sub (including all variable declares affecting this code) and >> the code that calls that code. Also, what version of .NET Framework are >> you using? Have you looked at all code that opens a connection to ensure >> that you are explicitly closing it. I have seen great code that in one >> instance overlooks one database connection being opened and not closed >> which caused a connection leak. > > Here is the code (with most of debugging taken out). There are nested > calls to Sql, so I left most of the code in. The big problem is that at > some point we get to the "About to execute the Reader", I get the > following messages: > > Failure in PollAndSendEmail(About to execute the reader): There is already > an open DataReader associated with this Connection which must be closed > first. > > and > > Failure in PollAndSendEmail(About to execute the reader): Object reference > not set to an instance of an object. (twice) > > and this will happen over and over then will stop and I can't stop the > service at this point. > > . . . in this class: there are too many objects with long lifetimes whose lifecycle you have to manage and hiding in that complexity are all your problems. You need to get rid of all the class-scope variables (they serve no purpose) and replace them with local variables. Declare all your SQL objects (connection, commands, data readers) as local variables. Open the connection just before your try block and close it in the finally. Create the SqlCommands each time, and use a nested try block for the SqlDataReader. David
Show quote
"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in I didn't write this code. I am just maintaining it and trying to find this message news:54B2B6EC-BF04-4E75-A40B-62D91C602779@microsoft.com... > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:uL1G48$uGHA.4624@TK2MSFTNGP02.phx.gbl... >> "Peter W. DeBetta" <debet***@hotmail.com> wrote in message >> news:eDmmGl3uGHA.3264@TK2MSFTNGP03.phx.gbl... >>> Send more details (meaning more code). Specifically, send the code that >>> calls this Sub (including all variable declares affecting this code) and >>> the code that calls that code. Also, what version of .NET Framework are >>> you using? Have you looked at all code that opens a connection to ensure >>> that you are explicitly closing it. I have seen great code that in one >>> instance overlooks one database connection being opened and not closed >>> which caused a connection leak. >> >> Here is the code (with most of debugging taken out). There are nested >> calls to Sql, so I left most of the code in. The big problem is that at >> some point we get to the "About to execute the Reader", I get the >> following messages: >> >> Failure in PollAndSendEmail(About to execute the reader): There is >> already an open DataReader associated with this Connection which must be >> closed first. >> >> and >> >> Failure in PollAndSendEmail(About to execute the reader): Object >> reference not set to an instance of an object. (twice) >> >> and this will happen over and over then will stop and I can't stop the >> service at this point. >> >> . . . > > This is all due to bad code. Technically speaking you have too much > "state" in this class: one problem. What do you mean about too much "state"? > there are too many objects with long lifetimes whose lifecycle you have to I assume you mean the separate class CemailMessage:> manage and hiding in that complexity are all your problems. You need to > get rid of all the class-scope variables (they serve no purpose) and > replace them with local variables. > **************************************** Class CEmailMessage Public id As Integer Public strTo As String Public strCC As String Public strBCC As String Public strFrom As String Public strSubject As String Public strBody As String Public isHTML As Integer Public dateAttempted As Date Public status As Integer Public errMessage As String End Class **************************************** I was curious as to why he did this. I assume he does this so he can easily put them in a collection to use later. > Declare all your SQL objects (connection, commands, data readers) as local He is using SqlConnection and SqlCommand objects from VS 2003 for his > variables. database access. I prefer to do to use direct database access myself. >Open the connection just before your try block and close it in the finally. Why would you do that?What if you have an error with the connection? Wouldn't you want that to be inside the Try...Catch code? >Create the SqlCommands each time, and use a nested try block for the Are you talking about this code:>SqlDataReader. ********************************************** For Each currMessage In colLocalEmailQueue 'LogInfo("Updating a Message") dbOpenConnection() ' called every time just in case With cmd_update_msg.Parameters .Item("@id").Value = currMessage.id .Item("@status").Value = currMessage.status .Item("@date_attempted").Value = currMessage.dateAttempted .Item("@error_message").Value = currMessage.errMessage End With ' do it strProgress = "About to update a message" intSuccess = cmd_update_msg.ExecuteScalar() 'LogInfo("Message Updated") If intSuccess <> 1 Then LogError("Email Poller failed to update queued message status. Msg ID: " & currMessage.id.ToString) End If Next ************************************************ Thanks for the suggestions, Tom Show quote > > David > >  |
|||||||||||||||||||||||
Other interesting topics