ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 调用sp_send_mail时出现Msg 22050和Msg 14661错误

调用sp_send_mail时出现Msg 22050和Msg 14661错误

原创 Linux操作系统 作者:hexiaomail 时间:2010-06-09 14:30:55 0 删除 编辑

环境:SQL Server 9.0.4053
使用域帐号mydomain\hex登录,利用msdb.dbo.sp_send_dbmail发送邮件,在使用其中的@query参数后就出现错误。
USE msdb
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
-------------------------------------------
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 15404, Level 16, State 19, Server FIHSER55\SQL2005, Line 1
Could not obtain information about Windows NT group/user 'mydomain\hex', error code 0x5.

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 208, Level 16, State 1, Server MYSER55\SQL2005, Line 1
Invalid object name 'dbo.DrvSpace'.

注:
[ @query = ] 'query' 表示要执行的查询。 查询结果可以作为文件附加,或包含在电子邮件的正文中。 查询的类型为 nvarchar(max),并且可以包含任何有效的 Transact-SQL 语句。

分析:
在进行一般性发送邮件都正常,而用上@query查询功能并做为一个附件就会出错,错误信息显示不能得到域帐号的信息。

通用切换SQL Server帐号test登录运行:
USE msdb
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
--------------------------------------------------------------------------------------
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 15406, Level 16, State 1, Server MYSER55\SQL2005, Line 1
Cannot execute as the server principal because the principal "test" does not exist, this type of principal cannot be impersonated, or you do not have permission.
是由于没有服务器权限的问题,为什么还要server principal?

查看SQL Server服务启动的帐号为:MYSER55\SQL1,它应该是具有SERVER系统的权限。
将MYSER55\SQL1帐号加入到msdb数据库中,并以MYSER55\SQL1做为登录帐号:
USE msdb
EXECUTE AS LOGIN = 'MYSER55\SQL1'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
---------------------------------------------------------------------
mail quene.

邮件发送成功!

如果使上述邮件发送成功,需要具有Windows 系统相关的权限.
验证Windows用户和 Windows 组的信息
EXEC sys.xp_logininfo @acctname = 'MYSER55\SQL1'
------------------------------------------------
Command(s) completed successfully.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9932141/viewspace-664801/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-07-05

  • 博文量
    86
  • 访问量
    190234