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:
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 "Unable to login to SQL Server" 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.
- Use SQL Standard authentication instead of Microsoft Windows NT integrated authentication.
- 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"EXEC master..xp_sendmail @recipients=''' + @recipientstext + ''', @query=''' + @querytext + ''', @subject=''' + @subjecttext + ''', @no_header = ''TRUE''"' -- Execute the xp_cmdshell to run the Osql.exe tool, which connects to SQL Server by using standard authentication EXEC master..xp_cmdshell @strTempSQLUse the stored procedure to send mail. For example:
EXEC procSendMail 'SELECT * FROM Northwind..Customers', 'someone@example.com', 'My report'
STATUS
Microsoft has confirmed that this is a problem in SQL Server 7.0.
REFERENCES
The SQL Mail service is not fully supported on a clustered server. For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
298723 BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster Virtual SQL Servers
263556 INF: How to Configure SQL Mail
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
315886 Common SQL Mail Problems
Additional query words: sqlmail 17969 cluster NT authentication mail,sql mail
Keywords: kbbug kbpending KB310822
