Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database mail with @query issue! Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 2:36 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:31 AM
Points: 42, Visits: 419
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' :P

TIA
Post #447830
Posted Wednesday, March 05, 2008 4:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 2:56 PM
Points: 38, Visits: 50
Did you ever get this issue figured out? It's still kicking my arse all over the place.
Post #464834
Posted Thursday, March 06, 2008 6:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:31 AM
Points: 42, Visits: 419
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.
Post #465110
Posted Thursday, March 06, 2008 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 2:56 PM
Points: 38, 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. :)


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?
Post #465126
Posted Thursday, March 06, 2008 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 02, 2012 2:26 AM
Points: 86, Visits: 94
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. :)
Post #464955
Posted Tuesday, February 24, 2009 3:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 7:10 AM
Points: 14, Visits: 313
Hello, this error is stressing me a lot, I can´t find a good answer in all the web. Pls Help
Post #663889
Posted Tuesday, February 24, 2009 9:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 2:56 PM
Points: 38, 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.
Post #664000
Posted Wednesday, March 25, 2009 3:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:10 PM
Points: 90, Visits: 474
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.

Post #683724
Posted Thursday, March 26, 2009 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 2:25 PM
Points: 145, Visits: 635
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 ;
Post #684061
Posted Thursday, April 02, 2009 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 9:15 AM
Points: 2, Visits: 51
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.

Post #688596
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse