Error message

[icemel]

[Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

The above error message periodically occurs during a select statement in a stored procedure that has a REPLACE funtion in it. However, the error only occurs *sometimes*, not every time it runs.

By default, ANSI_WARNINGS is set to off in the database. Should I set ANSI_WARNINGS to on in the stored procedure? What are the ramifications of doing so... considering that this error only occurs once in a while? Seems odd.

Thank you

 

[SQLDenis]

The query you are running is against a linked server or OPENROWSET/OPENDATASOURCE right?
These are distributed queries and ANSI_NULLS and ANSI_WARNINGS need to be turned ON

ANSI_WARNINGS

When set to ON, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF.

SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.


Distributed Queries
A connection must have the ANSI_NULLS and ANSI_WARNINGS options turned on before it can execute distributed queries





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

No, this is not a distributed query across linked servers.

I guess my question is, aside from having a valid explanation for why this error occurs once in a while, is it ok to set ANSI_WARNINGS ON inside the stored procedure?

Thanks

 

[SQLDenis]

yes, it's valid
ANSI_WARNINGS ON will show messages, when you run this

select count(z.Col1) from(
select 1 Col1 union all
select null) z

then this will be the warning

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

yes, it's valid
ANSI_WARNINGS ON will show messages, when you run this

select count(z.Col1) from(
select 1 Col1 union all
select null) z

Hmm, not sure we are understanding each other? My question is, is it ok to set ANSI_WARNINGS ON in a stored procedure? Or will it cause something funky to happen?

The reason I am hesistant to add this to my stored procedure is because the error message I originally posted is intermittment only, meaning other times the stored procedure works no problem. And no, it is not a linked query. The only thing unusual about the query is that it uses the REPLACE function in it.