troubleshooting Question

Running SQL Server SP from ASP causes inconsistent results

Avatar of splanton
splantonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerASPMicrosoft SQL Server 2008
11 Comments2 Solutions485 ViewsLast Modified:
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
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)
%>
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros