SYMPTOMS
If you try to run a distributed query, such as
'select * from linked_server.pubs.dbo.authors', and you use the
@query parameter of the
xp_sendmail stored procedure, the distributed query may fail and you recieve the following error message:
Server: Msg 7405, Level 16, State 1, Line 0
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.
This problem only occurs if both the following conditions are true:
- SQLMail is configured on a computer that is running SQL Server 7.0.
- You are using the @query parameter in the xp_sendmail stored procedure to query a linked server.
For example, the following
xp_sendmail command fails and the 7405 error is generated regardless of which version of SQL Server is running on the linked server:
EXEC master..xp_sendmail @recipients = 'someone@example.com', @query = 'select * from linked_server.pubs.dbo.authors'
NOTE: If you are using SQLMail on SQL Server 2000, this behavior does not occur.
WORKAROUND
To work around this behavior, create a wrapper stored procedure on the linked server (the remote server computer) that includes the Transact-SQL query that you are trying to run, and then run the stored procedure through the
xp_sendmail stored procedure.
For example, use the following sample code to create a stored procedure on the linked server computer:
USE PUBS
GO
CREATE PROCEDURE testproc
AS
select * from pubs.dbo.authors
GO
Run the following
xp_sendmail stored procedure on the local server:
EXEC master..xp_sendmail @recipients = 'someone@example.com',
@query = 'linked_server.pubs.dbo.testproc'