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:
When I call the same SP from classic ASP with ADO and the same parameter like this:
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
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 = 25When 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)
%>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
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
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To do 100 records it takes 8 seconds
Will try the timeout later tonight :)
Will try the timeout later tonight :)
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.
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.
ASKER
I could do with some pointers on debugging/logging/tracing the issue here. The trouble is It wont end prematurely from MS.
ASKER
Tried the setting 'SET NOCOUNT ON' in the SP
Now I get 19 records created.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Many thanks guys.