Stored procedure works in ODBC but not in OLE DB

[szfq93]

We have a complex stored procedure that aggregates data (uses a group by clause, among many other things). This query, when run in the SQL Query Analyzer, returns the following SQL Server warning:

Null value is eliminated by an aggregate or other SET operation

But, it still returns the data after this statement.
When I call the stored procedure from VB code, it works okay with ODBC (OLE DB for ODBC provider). But, the exact same call, the exact same code, fails (the recordset remains closed) when I use OLE DB.

Any advice would be appreciated!

 

[sunila7]

Hi,

Wht is the error message u r getting........ can u post the code u r using.......


Sunil

 

[tlbroadbent]

I don't know the reason that OLEDB isn't working. You can eliminate the message by adding set ansi_warnings off at teh beginning of the SP. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[ClayG]

It sounds crazy, but I have had procedures that needed
"SET NOCOUNT ON" to make them work. They were fine in Query Analyzer, but OLEDB would fail.

 

[szfq93]

As a rule, we ALWAYS use SET NOCOUNT ON (except where counting is needed). So, my stored procedure already has it set.

For now, I went ahead and SET ANSI_WARNINGS OFF and that took care of the problem.

Thanks for your help!