by Hi,
I have a problem with some stored procedures after switching from SQL
Server 7.0 on NT4 to SQL Server 2000 on W2000.
Basically I have a stored procedure which is executed from a com
object as follows:-
Dim cnObj As New ADODB.Connection
Dim comObj As New ADODB.Command
cnObj.Open m_providerDatabaseConnection
Set comObj.ActiveConnection = cnObj
comObj.CommandType = adCmdStoredProc
comObj.CommandText = "msp_brickRankingReport"
'-Define Parameters
<OMITTED>
'-set parameters
<OMITTED>
'-Execute the stored Procedure
Dim rsObj As New ADODB.Recordset
comObj.CommandTimeout = 120
rsObj.CursorLocation = adUseClient
rsObj.Open comObj, , adOpenStatic, adLockReadOnly
This code is part of a com object.
If I call this code in a VB Test harness it works fine. If I call it
from an ASP page it sometimes works and sometimes doesn't! When it was
originally on SQL Server 7 it worked everytime from the Test harness
and asp.
Basically if i call it from ASP it can return 1 record (the result
record set should be around 9000 records). If I try again a few more
times eventually it will return all 9000!
It seems to run the stored procedure instantly, when called from asp,
rather than wait until its completed. I've checked the stored
procedure in Query Analyser and its fine.
Any ideas on fixing this would be very appreciated or at least a clue
on where to look next.
Thanks in advance,
Andy