July 11, 2007 at 5:29 am
Hi All,
I am currently having a problem when run a stored procedure.
The set up is as follows.
[Server1].[Database1].dbo.[Proc1]
[Server2].[Database2].dbo.[Proc2]
Proc1 calls Proc2.
Proc2 has an output parameter which is to be returned to Proc1
When I run Proc1 I find that the output parameter from Proc2 is not passed back to Proc1 and the reason that I think it doesn't is because
Proc2 raises a SQL warning "Warning: Null value is eliminated by an aggregate or other SET operation"
The stange thing is that I have tried to use: set ansi_warnings off
this doesn't work.
I have also noticed that if I comment all the code out of Proc2 I still get the warning.
has anyone ever come around this before?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 11, 2007 at 7:27 am
Hey chris,
Checkout BOL for set ANSI_WARNINGS, Microsoft have made these points very clear.
1. ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement.
2. The SQL Native Client ODBC driver and SQL Native Client OLE DB Provider for SQL Server automatically set ANSI_WARNINGS to ON when connecting. So when proc2 executed inside proc1 it gets connection with server2 with ANSI_WARNINGS=ON by default. Microsoft does recommends to use ANSI_WARNINGS=ON for distributed queries though you can set it OFF.
About your questions I don't think it is because of ANSI_WARNIGS. Can you please confirm logic of your proc1 & proc2? If you can post code, it would be better to identify what exactly is going wrong.
Regards
Shrikant Kulkarni
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply