Avatar of splanton
splanton
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Running SQL Server SP from ASP causes inconsistent results

I Have an SQL Server 2008 Stored Procedure for the batch creation of some order records on our system. It is rather a large procedure and performs some quite complicated statements iteratively according to the number of records you need creating.

I use the SP to read a template held on the database in a table and create the new records from it.

I set it to create 10 new records.

When I call the procedure from SQL MS with this:
EXEC	[dbo].[usp_CreateBatchRecords]
		@WasteBatchId = 25

Open in new window

It works just fine

When I call the same SP from classic ASP with ADO and the same parameter like this:
<%
	SQLStmt = "exec dbo.usp_CreateBatchRecords '25';"
	Set RS_Procedure01 = Connection01.Execute(SQLStmt)
%>

Open in new window

I get all sorts of spurious results.
Sometimes it only produces 6 records, sometimes 5 etc.

I am thinking some sort of timeout here, but the run time is half a second or less.

What really baffles me is that the SP is self contained. If it produces one record shouldn't  it be carrying on and producing all the rest until it is done?

Regards,

Anyone have any ideas where to look
ASPMicrosoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
splanton

8/22/2022 - Mon
Big Monty

it looks like you're passing over a string of 25 when in fact it should be an int..just a shot in the dark, try it without the single quotes
splanton

ASKER
Nice idea, but surely the string Vs INT would bomb the first iteration of the SP not on the 5th, 6th, 7th or 8th.

I did try it (well you gotta try :) ) and got 8 records this time instead of ten, the next time I ran it I got 5 records created!

I am doing nothing else other than refreshing the ASP page on the browser.
splanton

ASKER
OK, tried pushing it further: 100 records.

The SP run from the SQL Server MS created all 100 fine.
Running the SAME SP from ASP resulted in 5 records three times in a row and then a 6.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
pateljitu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
splanton

ASKER
To do 100 records it takes 8 seconds

Will try the timeout later tonight :)
splanton

ASKER
Server Timeout had zero effect.

Ran the ASP script locally on the server and also as 'sa' user to make sure noting going wrong with that side of things. Again only 5 records created via ASP call and 100 via SQL Server MS call.

Iv'e double checked that the database is the same etc (I am looking at the results on the same database) and the SP doesn't exist anywhere else.

At a real loss on this one.
splanton

ASKER
I could do with some pointers on debugging/logging/tracing the issue here. The trouble is It wont end prematurely from MS.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
splanton

ASKER
Tried the setting 'SET NOCOUNT ON' in the SP

Now I get 19 records created.
Anthony Perkins

Unfortunately, not seeing the Stored Procedure or at the very least knowing whether it even returns a resultset (your ASP code assumes it does, but I have my doubts and you have not shown what output you get from SSMS) is not helping us understand what is occurring.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
splanton

ASKER
pateljitu: Your suggestion led me to an article that also included the 'SET NOCOUNT ON' command. That led to more records being retrieved and the realisation that there must be something blowing a buffer somewhere and that unsolicited output of some description was causing it (e.g. my SP).

Many thanks guys.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes