January 25, 2008 at 2:36 pm
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' 😛
TIA
March 5, 2008 at 4:28 pm
Did you ever get this issue figured out? It's still kicking my arse all over the place.
March 6, 2008 at 6:55 am
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.
March 6, 2008 at 7:09 am
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?
March 6, 2008 at 8:25 am
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. 🙂
February 24, 2009 at 3:51 pm
Hello, this error is stressing me a lot, I can´t find a good answer in all the web. Pls Help
February 24, 2009 at 9:53 pm
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.
March 25, 2009 at 3:25 pm
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.
March 26, 2009 at 6:48 am
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 ;
April 2, 2009 at 2:08 am
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.
April 3, 2009 at 8:05 am
Can you post the entire send mail command with the @query please ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 3, 2009 at 8:41 am
It doesn't matter what the @query statement is... you can have "Select 1" as the query... if the sys.xp_logininfo SP kicks back an error for the account the agent is running under... it'll error out.
January 19, 2010 at 8:10 am
Hi everyone,
Just thought I'd add my 2 cents worth in here. I tried for a few days to resolve this issue, including:
- Changing the logging level to verbose
- The sys.xp_logininfo check
For me, the SQL I was running in the @query parameter were running fine in SSMS, however were failing with this error when running from SQL Agent. Once I ran the mail SP in SSMS, not only did it show the error, but also details on what I was doing wrong.
Not sure if that will be any help to people here or not.
cheers, Mark
January 20, 2010 at 6:44 am
Hi all,
Just wanted to add to my reply yesterday, with my issues with @query from within SQL Agent.
I was able to get it to work by first running my @query before I ran the database mail stored procedure.
I'm pretty sure there are several different causes of this error, but wanted to add my experiences in case it can help others.
cheers, Mark
February 23, 2010 at 11:58 am
I worked arround this issue by adding the following sentences before calling sp_send_dbmail:
USE msdb
EXECUTE AS USER = 'dbo'
For example:
USE msdb
EXECUTE AS USER = 'dbo'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail Profile',
@recipients = 'user@domain.com',
@query = 'SELECT
convert(varchar(20), msdb.dbo.backupset.database_name) AS "Database",
datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()) AS "Days Since Last Backup",
CONVERT(VARCHAR(20), max(msdb.dbo.backupset.backup_finish_date), 100) AS "Last Backup"
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
msdb.dbo.backupset.database_name IN (SELECT name FROM master.sys.databases)
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()) DESC',
@subject = 'dbsvc-test-01\SQL2005 Last Backup Report',
@attach_query_result_as_file = 0 ;
go
For more information look at:
http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/d8bd959f-e6cb-4a49-bcee-1e78dede5384
Adrian.-
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply