We have several reports, that depending on the database size and selection criteria, can generate a very large record set. The problem is that if a user selects an overly large range in the query and then closes the report (ASP page) while the query is executing, the page closes but SQL Server continues to run, chewing up resources and keeping the SQL Server & connection busy.
It seems like this should really straight forward, but I must be missing something.
- I can't find any special way to declare the connection open
- I can't find a way to use the html <body onunload= > statement to cancel or close the ASP connection
- I can't think of a check to put inside the While loop below to abort the processing.
Any help would be greatly appreciated.
BTW: We have Response.Buffer = false to eliminate buffer overrun errors, in case that matters
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.CommandTimeout = 300 'Set timeout for commands to complete in seconds
MyConn.Open("Provider=sqloledb;Data Source=" & SQLHost & ";Initial Catalog=" & SQLDBName & "; User Id=" & SQLUser & ";Password=" & SQLPassword )
'mySQL is a big, long, multiple join Query string
Set RS = MyConn.Execute(mySQL)
while NOT RS.EOF
'More stuff inside this loop