operation not allowed when the object is closed when running more advanced query

by

This is a common problem caused by row counts being interpreted as output from a Stored Procedure when using ADODB with SQL Server.

To avoid this remember to set

SET NOCOUNT ON;

in your Stored Procedure this will stop ADODB returning a closed recordset, or if for whatever reason you don’t want to do this (not sure why as you can always use @@ROWCOUNT to pass the row count back), you can use

'Return the next recordset, which will be the result of the Stored Procedure, not 
'the row count generated when SET NOCOUNT OFF (default).
Set rs = rs.NextRecordset()

which returns the next ADODB.Recordset if ADODB has detected one being returned by the Stored Procedure (might be best to check rs.State <> adStateClosed when dealing with multiple ADODB.Recordset objects).

More Related Contents:

  1. SQL QUERY COUNT THEN ADD
  2. SQL server command help and tutorials for specific commands?
  3. How do I UPDATE from a SELECT in SQL Server?
  4. How to calculate age (in years) based on Date of Birth and getDate()
  5. CROSS JOIN vs INNER JOIN in SQL
  6. Referring to a Column Alias in a WHERE Clause
  7. INNER JOIN vs LEFT JOIN performance in SQL Server
  8. Function vs. Stored Procedure in SQL Server
  9. How to implement LIMIT with SQL Server? [duplicate]
  10. converting Epoch timestamp to sql server(human readable format)
  11. What are Covering Indexes and Covered Queries in SQL Server?
  12. How to get difference between two rows for a column field?
  13. What’s the best way to select the minimum value from several columns?
  14. Add a summary row with totals
  15. Counting DISTINCT over multiple columns
  16. How to check if a stored procedure exists before creating it
  17. Insert Into… Merge… Select (SQL Server)
  18. Correct use of transactions in SQL Server
  19. The parameterized query expects the parameter which was not supplied
  20. SQL Server: drop table cascade equivalent?
  21. SQL Server converting varbinary to string
  22. SSIS Source Format Implicit Conversion for Datetime
  23. How do I prevent a database trigger from recursing?
  24. UNION the results of multiple stored procedures
  25. Executing SQL Server Agent Job from a stored procedure and returning job result
  26. how does SELECT TOP works when no order by is specified?
  27. Add a column to existing table and uniquely number them on MS SQL Server
  28. How to execute a stored procedure inside a select query
  29. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  30. SQL Inner Join On Null Values

Leave a Comment