Link to home
Start Free TrialLog in
Avatar of splanton
splantonFlag 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?


Anyone have any ideas where to look
Avatar of Big Monty
Big Monty
Flag of United States of America image

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
Avatar of splanton


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.
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.
Avatar of pateljitu
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To do 100 records it takes 8 seconds

Will try the timeout later tonight :)
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.
I could do with some pointers on debugging/logging/tracing the issue here. The trouble is It wont end prematurely from MS.
Tried the setting 'SET NOCOUNT ON' in the SP

Now I get 19 records created.
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.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.