October 15, 2003 at 2:56 pm
I'm having enormous difficulty getting xp_sendmail to work with the @query parameter. Need some help from anyone with experience on this one.
--
To clarify: THIS IS NOT a MAPI/EXCHANGE/SQLMail problem. I have followed and gone through every SQLMail KB article and fix I can find and the mail profiles are set up correctly. The SQLAgentMail and the SQLMail Profile is set up to run correctly, and a call to xp_sendmail @recipients='blah@blah.com', @message='Hello' works fine.
--
However, I have a stored procedure that gives me a report of jobs that ran in the past day that I want to be emailed to me every morning. I am set up as an operator in SQLAgent.
--
When I run the following:
EXEC master..xp_sendmail
@recipients = blah@blah.com'
, @message = 'Daily Job Summary'
, @query = 'exec msdb.dbo.rpt_JobSummary'
, @subject = 'Job Summary'
I get the following error:
ODBC error 8198 (42000) Could not obtain information about Windows NT group/user 'DDDD\blah.
This has driven me crazy trying to figure this out for almost two days now, and I need help!
--
Thanks in advance,
Jay
October 16, 2003 at 7:22 am
I would change the query to "sp_configure" to see whether it works. If it does, I guess you have to review your stored procedure "msdb.dbo.rpt_JobSummary".
October 16, 2003 at 7:41 am
It is not my proc. From error message, it is clearly a security context issue. When I run:
master..xp_sendmail @recipients='blah@blah.com', @message='Hello from SERVERXXX'
I receive the email correctly. When I run:
master..xp_sendmail @recipients='blah@blah.com', @message='Hello from SERVERXXX', @query='sp_who2'
I receive this message:ODBC error 8198 (42000) Could not obtain information about Windows NT group/user 'DDDD\blah.
The problem is: xp_sendmail is executing in the security context of the SQLAgentMail profile. The procedure that is executed in the @query parameter is NOT. The documentation on xp_sendmail says there is a parameter @set_user which should allow me to force the security context in which the procedure runs, but this hasn't worked. Any help is appreciated on this one.
Edited by - jpipes on 10/16/2003 07:41:25 AM
October 16, 2003 at 7:59 am
Is Windows NT group/user 'DDDD\blah your login? Have you tried to grant it to accsse SQL Server and run your sp?
According to explanation of [@set_user], If user is not specified, the security context defaults to that of the user executing xp_sendmail. That should be your login.
October 16, 2003 at 8:04 am
Have you tried to schedule it and have sa or the service account for SQL Agent own that task.
The otherway to go arround it is to give your 'domain\user account' the right to execute xp_sendmail.
mom
October 16, 2003 at 8:16 am
Allen,
quote:
Is Windows NT group/user 'DDDD\blah your login?
Yes
quote:
Have you tried to grant it to accsse SQL Server and run your sp?
Yes, I a member of sysadmins...
quote:
According to explanation of [@set_user], If user is not specified, the security context defaults to that of the user executing xp_sendmail. That should be your login.
I know, but regardless of what I set the param to be, I get the same message...
mom,
quote:
Have you tried to schedule it and have sa or the service account for SQL Agent own that task.
I am a member of sa. Plus, I need to be able to run the xp_sendmail under my own context.
quote:
The otherway to go arround it is to give your 'domain\user account' the right to execute xp_sendmail.
I am set up as an login for the server, have full rights as a member of system admins, have access to all DBs, and have given myself specific EXECUTE rights on xp_sendmail.
--
Still same error message as above.
October 16, 2003 at 8:23 am
What is MDAC version? SQL Server service pack?
Edited by - allen_cui on 10/16/2003 08:23:31 AM
October 16, 2003 at 10:06 am
SP3 SQL 2000, MDAC 2.7
October 16, 2003 at 10:20 am
Have you rebooted the server? This is a long shot, but I had the same issue before and it drove me crazy for two days. I rebooted and the problem went away.
October 16, 2003 at 1:18 pm
OK, I restarted the server and now I am getting error:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
--
Which is the MAPI error code for bad login/login failure. I think I'm about to give up on this crap. I never thought setting up a simple email notification would be so damn difficult.
--
If anyone has gotten xp_sendmail working with the @query parameter, please post some directions...thanks!
October 16, 2003 at 1:37 pm
Do you get valid results for xp_logininfo 'DDDD\blah' ?
October 16, 2003 at 1:52 pm
I use xp_sendmail with a query. Here is the syntax of the stored proc, which invokes it…
Exec master..xp_sendmail
@recipients='my email’,
@subject='Newborn Row Count',
@message='Monthly update',
@attachments='RowCount.csv',
@query='exec newborn..spRowCount',
@attach_results=true,
@width=500,
@separator = ''
This proc is run by a job. I can check the user context of the job if you like.
October 16, 2003 at 2:05 pm
I have test server setup to run both SQL Server 7.0 and SQL Server 2000 (SP3a with hotfix). XP_SENDMAIL works well in SQL Server 7.0 but received similar (Not exact) error message as you had and it worked for me before. I am wondering it could be the problem from either sp3a or those hotfixes.
Anyway, Have look following KB. You may already done that.
http://support.microsoft.com/default.aspx?scid=kb;en-us;327552&Product=sql
October 17, 2003 at 10:43 am
This is really weird. How about login as sa and try to execute your same command. I just wonder if it still give you that error. We didn't have any problem on SQL 7.0.
We have some problem on SQL 2000. SQL 2000 sp 2 introduce error on sql-mail where it would sometimes work and sometimes fail, SQL 2000 sp3 also intruduce some problem for sql-mail and there is a patch (file) from Microsoft that we had to replace. Now our sql 2000 sql-mail works fine.
We generate lots of report every morning on top of the regular job notification.
mom
October 20, 2003 at 7:25 am
Thank you guys so much for your help. I will go ahead and investigate the issue on KB... and post a reply later. Glad to hear at least someone else was experiencing this problem on SQL2K. Thanks again,
--
Jay Pipes
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply