Operation is not allowed when object is closed 1

[Glasgow]

I have some code that I have been using for years to return the results of a SQL Server (2005) query into an ADO recordset. With one such query I am getting the message:

Operation is not allowed when object is closed

when I attempt to reference the RecordCount property of the recordset. When I run the query in management studio, it returns 9 records but, on further investigation, I realise that the query generates 2 identical warning messages:

Warning: Null value is eliminated by an aggregate or other SET operation.

Once I eliminate the source of the warning messages by adjusting the data, the VB code works fine. Is there a way I can get the logic to ignore these error messages and return the recordset anyway? Or something I can do in the query to switch off the warnings (like I might do with SET NOCOUNT ON)?

 

[gmmastros]

Or something I can do in the query to switch off the warnings (like I might do with SET NOCOUNT ON)?

SET ANSI_WARNINGS OFF

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

That looks like just the ticket, George, thank you. Funny I scanned the various SET options but I must have missed that one.

I'm away from the machine in question at the moment but I will give it a try tomorrow and see if it eliminates my VB problem. I will report back.

 

[Glasgow]

Yes that did the trick - thanks again. Can't believe I've never hit this before!