SQL 2000 Problems with stored Procs run from Com via ASP

SQL 2000 Problems with stored Procs run from Com via ASP

Post by Andy Domin » Tue, 04 Sep 2001 23:41:12


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
  '-set parameters
  '-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,


1. sql 2000/OLE DB connection problem via ASP script on multiinstance server


We have win 2k server running both sql 7 and sql 2000 on it.

I installed sql 2000 instances with "port 0" and sql 7 on "port 1433".
It is possible to connect successfully with EM using "dynamically allocate
option to sql 2000 instances.
Also it is possible to connect via ODBC to sql 2000 instance.

The failure happens when connecting with SQL OLE DB to sql 2000 instance via
ASP script from the web server. They need to enter a port number, which is
setup up dynamically.
Please, help us to understand how to resolve a problem.

Thank you in advance.


3. VP/ASP/COM and SQL 2000 Authentication

4. Transaction Question

5. Connecting with (Local) via asp.net To sql server 2000

6. New Book?

7. ASP to SQL Server 2000 via ADO

8. Informix LRU adjustment advice wanted

9. Can't connect to SQL 2000 via ASP.Net

10. SSL encrypted database connection between ASP (via ADO) and SQL Server 2000

11. How to export SQL Server 2000 data to Excel via ASP

12. Debugging Stored procs in SQL 2000

13. Client XML file upload to SQL 2000 via ASP