Microsoft KB Archive/310822

= BUG: Xp_sendmail with @Query Parameter on a Cluster Generates Error 17969 =

Article ID: 310822

Article Last Modified on 12/10/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q310822



BUG #: 57093 (SQLBUG_70)



SYMPTOMS
If you use the @query parameter with the xp_sendmail extended stored procedure on a clustered SQL Server server, execution of the xp_sendmail stored procedure may result in error 17969 if all these conditions are true:
 * Your connection uses Trusted Security and/or Microsoft Windows NT Authentication.


 * SQL Server is clustered.

The text of the error message is:

Server: Msg 17969, Level 16, State 1, Line 0 Unable to login to SQL Server

The SQL Server error log displays messages similar to: 2027-12-01 17:20:36.50 ods     Starting SQL Mail session... 2027-12-01 17:20:36.56 ods     SQL Mail session started. 2027-12-01 17:20:44.48 logon   Login succeeded for user 'CSDOM\Administrator'. Connection: Trusted. 2027-12-01 17:20:46.47 logon   Login failed for user '\'. 2027-12-01 17:20:55.58 logon   Login succeeded for user 'sa'. Connection: Non-Trusted. 2027-12-01 17:20:56.49 logon   Login succeeded for user 'sa'. Connection: Non-Trusted.



CAUSE
You must use the virtual server name to access a clustered SQL Server. Because the default named pipe is used instead of the virtual server name, xp_sendmail cannot find SQL Server; therefore, the query fails with the &quot;Unable to login to SQL Server&quot; error message.



WORKAROUND
To avoid this behavior, you can use any one of these methods:  Create a link for the clustered SQL Server to the nonclustered SQL Server, and then have the nonclustered server handle the xp_sendmail requests. For example:

 Set up SQL Mail on the SQL Server nonclustered server. Use the sp_addlinkedserver stored procedure to link the SQL Server clustered server to the nonclustered server. Run the xp_sendmail extended stored procedure on the nonclustered server with the @query parameter, and then specify the four part linked server name to extract the data required from the linked table on the clustered server. </li> Use SQL Standard authentication instead of Microsoft Windows NT integrated authentication.

</li> Create a stored procedure and encapsulate an xp_cmdshell extended stored procedure to call osql to connect to the server with SQL authentication, and then run the xp_sendmail extended stored procedure. For example:

  Create a stored procedure to encapsulate xp_sendmail: CREATE PROCEDURE dbo.procSendMail @querytext varchar(250), @recipientstext varchar(250), @subjecttext varchar(250) -- Include parameters for all the xp_sendmail parameters, that you want to expose AS    -- Temporary buffer for the command DECLARE @strTempSQL varchar(500) -- Make sure you update your cluster name and password for the sa account in the statement SET @strTempSQL = 'C:\Mssql7\Binn\Osql.exe -S MyServerName -U sa -P MyPassword -Q&quot;EXEC master..xp_sendmail @recipients= + @recipientstext + , @query= + @querytext + , @subject= + @subjecttext + , @no_header = TRUE&quot;' -- Execute the xp_cmdshell to run the Osql.exe tool, which connects to SQL Server by using standard authentication EXEC master..xp_cmdshell @strTempSQL </li>  Use the stored procedure to send mail. For example: EXEC procSendMail 'SELECT * FROM Northwind..Customers', 'someone@example.com', 'My report' </li></ol> </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in SQL Server 7.0.