SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database mail with @query issue!


Database mail with @query issue!

Author
Message
SQL Thetan
SQL Thetan
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 499
I'm trying to send db mail using @query parameter and I get the error below:

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 478
Query execution failed: Msg 15404, Level 16, State 19, Server ######, Line 1
Could not obtain information about Windows NT group/user #####, error code 0x5.

I exclude the @query parameter and the mail will be sent. The dbmail profile I'm using is public and my login is sysadmin.
It has something to do with security though.. I set up a job and ran it is an administrator which is sysadmin as well and it worked with @query! Should I be mapped to msdb in a particular way?

The query I'm testing is simple: 'select 1' Tongue

TIA
Adam Collins-467094
Adam Collins-467094
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 50
Did you ever get this issue figured out? It's still kicking my arse all over the place.
SQL Thetan
SQL Thetan
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 499
I built a string and assigned it to body as well. It just bothers me not knowing what the issue was with using the query parameter! Thanks for the response, and I haven't messed with the trigger since.
Adam Collins-467094
Adam Collins-467094
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 50
g.sarvesh (3/6/2008)
Hi,

I am using following query to use db mail sending and this is not giving any error:

EXEC msdb.dbo.sp_send_dbmail @recipients= @EMail_ID ,
@subject = 'New Job Post' ,
@body = @Job_Description_brief ,
@body_format = 'HTML' ,
@profile_name = 'Profile Name';

It is running successfully. Smile


I can do that all day long and it works splendidly. But as soon as I try to add @query = 'SELECT * FROM SomeTable', I get the error mentioned by the author of this thread. The SomeTable table only has about 10 rows in it, and I've got the user it's complaining about in the msdb database with the DatabaseMail role (not sure if that is exactly what the role is called as I'm not at work yet this morning and don't have it right in front of me at the moment) assigned.

I can just loop through my result set and build a string and bypass this whole @query parameter mess entirely, but I shouldn't have to do that; the fact that this isn't working is fairly pissing me off.

EDIT: Am I going bonkers or does my post quoting an earlier post show up before the post I quoted?
Sarvesh Kumar Gupta
Sarvesh Kumar Gupta
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 145
Hi,

I am using following query to use db mail sending and this is not giving any error:

EXEC msdb.dbo.sp_send_dbmail @recipients= @EMail_ID ,
@subject = 'New Job Post' ,
@body = @Job_Description_brief ,
@body_format = 'HTML' ,
@profile_name = 'Profile Name';

It is running successfully. Smile
john j serna
john j serna
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 353
Hello, this error is stressing me a lot, I can´t find a good answer in all the web. Pls Help
Adam Collins-467094
Adam Collins-467094
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 50
I'm sorry. This happened awhile back for me and I don't remember what I did, and I'm at a different job now.. I don't think I ever got it to work...I just worked around it.
TrailRunner
TrailRunner
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 660
I had the same problem this morning, but with slightly different message...

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 478
Msg 208, Level 16, State 1, Server ServerName, Line 7
Invalid object name 'TableName'.

My problem was fixed when I added the 'Use DatabaseName' statement for each select statements in the @query text. Hope that also solve your problem...good luck.
Randy Doub
Randy Doub
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1034 Visits: 778
I use DB Mail with @query in a stored procedure (below). The query is put in the variable @cmd. The variable @db is passed as a parameter. Lots of quotes and quoting quotes.
Maybe you just have a syntax error with quotes. Maybe you have to include USE DB in your query. Mine runs a dbcc so it can be called from any db. Also the MSDB database must have the Service Broker turned on for DB Mail.

create procedure myproc (@db varchar(50)) as
declare @subj varchar(100),
@cmd varchar(200),
@file varchar(100)
select @subj = @@servername + ' ' + @db + ' Integrity Check',
@cmd = 'dbcc checkdb(''' + @db + ''') ',
@file = @db + '_dbcc.txt' ;

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAdmin',
@recipients = 'myname@mydomain',
@subject = @subj,
@query = @cmd,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file ;
bensala
bensala
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 60
the issue is with your SQL service not able to "lookup" the AD account... Here is the cmd that DBMail runs:

EXEC sys.xp_logininfo
@acctname = 'Domain\User'


If this command kicks back an error... your dbmail is going to fail. There is an option in AD to hide a user to prevent someone from being able to list all the AD users. If this option is enabled for the user in question... DBMail will fail. MS knows about this issue and as far as I know... has no intention of fixing it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search