IIS (ASP.NET application) with SQL going crazy!

[Evan Camilleri]

I have a really strange problem.

I have an ASP.NET 2 c# application which on load access 2 stored procedures.
All work OK (normally)

The application has an ajax timer which every now and then calls the server.

Then at one point in time I get an error. On refresh I see that the stored
procedure is being called but it returns nothing (If I call it via SQL
Manager Studio passing same parameters it works well).

I press refresh, the first stored procedure works but the second fails
(returning no data but no exeception)

I press refresh, the first fails,

etc, etc.


Then I resart IIS and all works well (.... till system hangs again)


What's happening? And how I can go around it???


Evan

 

[Andrew J. Kelly]

I would trace it with profiler to see exactly what is sent to SQL Server and
if there are any errors occurring that maybe the app isn't picking up.

 

[Evan Camilleri]

It's really annoying! I am pasting a BAD trace and a GOOD trace. In the
BAD SQL is not receiving the currentUser for sure BUT the application is
'sending' them (when I debug). Maybe Windows integration security is the
problem?

this is a BAD trace:

declare @p2 int
set @p2=NULL
declare @p3 int
set @p3=NULL
declare @p4 int
set @p4=NULL
declare @p5 int
set @p5=NULL
declare @p6 numeric(38,10)
set @p6=NULL
declare @p7 bit
set @p7=0
exec spr_LineTick @currentLine=11,@producedPerHourThisShift=@p2
output,@producedThisShift=@p3 output,@rejectsPerHourThisShift=@p4
output,@rejectsThisShift=@p5 output,@totalDowntime=@p6 output,@newShift=@p7
output
select @p2, @p3, @p4, @p5, @p6, @p7

==

this is a GOOD trace:

declare @p1 int
set @p1=11
declare @p2 int
set @p2=388
declare @p4 datetime
set @p4=''2007-08-25 13:50:00:000''
declare @p5 datetime
set @p5=''2007-08-25 21:50:00:000''
declare @p6 nvarchar(25)
set @p6=N'BV-2007-5054'
declare @p7 nvarchar(50)
set @p7=N'SO-5860-01'
declare @p8 nvarchar(25)
set @p8=N'U1B > Line 3'
declare @p9 int
set @p9=580
declare @p10 bit
set @p10=0
exec spr_GetShiftData @currentLine=@p1 output,@pshift=@p2
output,@currentUser=N'DOMAIN\user',@pshiftStart=@p4 output,@pshiftEnd=@p5
output,@docRef=@p6 output,@itemCode=@p7 output,@lineFriendlyName=@p8
output,@targetPerHour=@p9 output,@onBreak=@p10 output
select @p1, @p2, @p4, @p5, @p6, @p7, @p8, @p9, @p10

==

 

[Andrew J. Kelly]

If what you are saying is that the parameter @CurrentUser is not getting
passed in the bad trace I don't see how that can be related to SQL Servers
authentication. If if wasn't authenticating correctly you would not get be
able to execute the sp at all. I would look at the app that is making the
call and find out why it is omitting the parameter in the first place. That
may be something to do with security but not from a SQL Server standpoint as
you are way past that aspect of security when you exec the proc.

 

[Evan Camilleri]

I think the problem is with IIS

Evan

 

[Mr. Arnold]

It's really annoying! I am pasting a BAD trace and a GOOD trace. In the
BAD SQL is not receiving the currentUser for sure BUT the application is
'sending' them (when I debug). Maybe Windows integration security is the
problem?

<snipped>

I think you have a programming issue, where as, the parameters are being
lost by the application while in session. The fact that you have to press
the Refresh button should be an indication that the application has lost
session state and has aborted.

In my dealings with Ajax Pro .Net, making a call to a Ajax Web Method to
access a routine on the Code Behind File accessing a database, the code has
to be solid on its error trapping so that some kind of error is returned on
the callback, otherwise, you're missing the abort.

In addition to this, your application can be doing something so detrimental
to IIS that it's causing IIS to hang/abort as well.

 

[Evan Camilleri]

I think i solved the issue.... quite stiupid error (but doing lots of
windows programming, not only ASP.NET...)

The SqlConnection was static (shared) and all kinds of strange errors
happen!

Evan