IIS hangs when certain SQL submitted

[Colin Steadman]

Does anyone have a suggestion how I could debug this
problem:

1. Data is collected from database and displayed in a form.
2. User updates data and submits it.
3. Submitted data is compared with old data.
4. SQL is generated for changes and put into an array.
5. SQL in the array is executed line by line.

I've copied the code which does the update from a working
page that does something similar. And tested it with a
basic update to be sure. But for some reason when I
submit this sql:

update table set currency = 'USD' where currency = 'GBP'
and company = 'LQ' and supplier_id = 'BCX0926' and
invoice_no = '654664432'

IIS hangs and the browser displays a blank white page. At
this point the whole site becomes unusable until I restart
the IIS Admin service.

I've pasted the sql into Toad and tested it there and it
works fine. Could anyone suggest what the problem is, or
how I could go about debugging it. Normally when I get
errors thet are written to the screen. But in this case,
I dont get anything back at all.

I've even tried creating a bare bones page just to submit
that sql (see below), but even this crashes the server.

TIA,

Colin


<%Option Explicit%>

<!-- #INCLUDE FILE="functions.asp" -->
<!-- #INCLUDE FILE="formatting.asp" -->

<%

beginPage 'Prints out the basic HTML for start of page

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.open Session("PROVIDER")
cn.BeginTrans
cn.execute "update table set currency = 'USD' where
currency = 'GBP' and company = 'LQ' and supplier_id
= 'BCX0926' and invoice_no = '654664432'"

If err.number + cn.Errors.Count = 0 Then
response.write "GOOD UPDATE"
Else
response.write "GOOD UPDATE"
End If

finishPage 'Prints out end of page HTML
%>

 

[Aaron [SQL Server MVP]]

Have you considered using a stored procedure instead of an ad hoc query?

 

[Mark Schupp]

Try losing the begin trans and the error check and see what happens.

Set cn = Server.CreateObject("ADODB.Connection")
cn.open Session("PROVIDER")
cn.execute "update table set currency = 'USD' where currency = 'GBP'
and company = 'LQ' and supplier_id = 'BCX0926' and invoice_no =
'654664432'"
cn.close

 

[Colin Steadman]

Have you considered using a stored procedure instead of an ad hoc query?

No I hadn't. But after reading some of the material on your ASPFAQ
site I'm going to try and find some time to look at this for the
benefits in performance, and to take out some of the complexity in my
pages.

In the meantime, the problem I had so much trouble with on
Thursday/Friday last week appears to have resolved itself. I went in
to demonstrate the problem to a collegue yesterday and found that it
was working!

The person who looks after IIS says he's not changed anything, and
neither has the DBA so I have no idea what changed... I dont know if
I should be relieved or even more concened, but fingers crossed it
will stay fixed.


Colin

 

[Colin Steadman]

Does anyone have a suggestion how I could debug this
problem:

1. Data is collected from database and displayed in a form.
2. User updates data and submits it.
3. Submitted data is compared with old data.
4. SQL is generated for changes and put into an array.
5. SQL in the array is executed line by line.

I've copied the code which does the update from a working
page that does something similar. And tested it with a
basic update to be sure. But for some reason when I
submit this sql:

update table set currency = 'USD' where currency = 'GBP'
and company = 'LQ' and supplier_id = 'BCX0926' and
invoice_no = '654664432'

IIS hangs and the browser displays a blank white page. At
this point the whole site becomes unusable until I restart
the IIS Admin service.

I've pasted the sql into Toad and tested it there and it
works fine. Could anyone suggest what the problem is, or
how I could go about debugging it. Normally when I get
errors thet are written to the screen. But in this case,
I dont get anything back at all.

I've even tried creating a bare bones page just to submit
that sql (see below), but even this crashes the server.

TIA,

Colin


<%Option Explicit%>

<!-- #INCLUDE FILE="functions.asp" -->
<!-- #INCLUDE FILE="formatting.asp" -->

<%

beginPage 'Prints out the basic HTML for start of page

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.open Session("PROVIDER")
cn.BeginTrans
cn.execute "update table set currency = 'USD' where
currency = 'GBP' and company = 'LQ' and supplier_id
= 'BCX0926' and invoice_no = '654664432'"

If err.number + cn.Errors.Count = 0 Then
response.write "GOOD UPDATE"
Else
response.write "GOOD UPDATE"
End If

finishPage 'Prints out end of page HTML
%>

If anyone is interested this problem appears to occur when Toad from
Quest Software has the I'm updating open. Ie when running this
command:

desc table_name

Closing Toad allows the page to run normally.