IIS hangs when certain SQL submitted

**Aaron [SQL Server MVP]** · Jul 19 '05, 01:09 PM

Re: IIS hangs when certain SQL submitted

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

--

Expired DNS Hosting Services | DNS Made Easy

http://www.aspfaq.com/

Please contact this domain's administrator as their DNS Made Easy services have expired.

(Reverse address to reply.)

"Colin Steadman" <anonymous@disc ussions.microso ft.com> wrote in message
news:2a45f01c46 59a$8e7dbaf0$a3 01280a@phx.gbl. ..[color=blue]
> 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%>
>
> 
> 
>
> <%
>
> beginPage 'Prints out the basic HTML for start of page
>
> Dim cn
> Set cn = Server.CreateOb ject("ADODB.Con nection")
> cn.open Session("PROVID ER")
> 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
> %>[/color]

**Mark Schupp** · Jul 19 '05, 01:10 PM

Re: IIS hangs when certain SQL submitted

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

Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.open Session("PROVID ER")
cn.execute "update table set currency = 'USD' where currency = 'GBP'
and company = 'LQ' and supplier_id = 'BCX0926' and invoice_no =
'654664432'"
cn.close

--
Mark Schupp
Head of Development
Integrity eLearning

404 Not Found

https://www.ielearning.com

"Colin Steadman" <anonymous@disc ussions.microso ft.com> wrote in message
news:2a45f01c46 59a$8e7dbaf0$a3 01280a@phx.gbl. ..[color=blue]
> 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%>
>
> 
> 
>
> <%
>
> beginPage 'Prints out the basic HTML for start of page
>
> Dim cn
> Set cn = Server.CreateOb ject("ADODB.Con nection")
> cn.open Session("PROVID ER")
> 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
> %>[/color]

**Colin Steadman** · Jul 19 '05, 01:12 PM

Re: IIS hangs when certain SQL submitted

"Aaron [SQL Server MVP]" <ten.xoc@dnartr eb.noraa> wrote in message news:<u2aFadbZE HA.1656@TK2MSFT NGP09.phx.gbl>. ..[color=blue]
> Have you considered using a stored procedure instead of an ad hoc query?[/color]

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** · Jul 19 '05, 01:14 PM

Re: IIS hangs when certain SQL submitted

"Colin Steadman" <anonymous@disc ussions.microso ft.com> wrote in message news:<2a45f01c4 659a$8e7dbaf0$a 301280a@phx.gbl >...[color=blue]
> 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%>
>
> 
> 
>
> <%
>
> beginPage 'Prints out the basic HTML for start of page
>
> Dim cn
> Set cn = Server.CreateOb ject("ADODB.Con nection")
> cn.open Session("PROVID ER")
> 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
> %>[/color]

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.

IIS hangs when certain SQL submitted

IIS hangs when certain SQL submitted

Comment

Comment

Comment

Comment