Microsoft KB Archive/320656

= PRB: A Distributed Query May Fail If You Use the Xp_sendmail Stored Procedure with the @query Parameter on a Linked Server =

Article ID: 320656

Article Last Modified on 8/8/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q320656





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'



STATUS
Microsoft is researching this problem and will post more information in this article when the information becomes available.

Additional query words: dblib

Keywords: kbprb kbpending KB320656

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.