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, April 3, 2009 8:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:18 AM
Points: 3,193, Visits: 2,289
Can you post the entire send mail command with the @query please ...



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #689922
Posted Friday, April 3, 2009 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 11:10 PM
Points: 2, Visits: 52
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.
Post #689968
Posted Tuesday, January 19, 2010 8:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
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
Post #849774
Posted Wednesday, January 20, 2010 6:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
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
Post #850439
Posted Tuesday, February 23, 2010 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 16, 2013 12:28 PM
Points: 27, Visits: 243
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.-


Post #871370
Posted Tuesday, August 10, 2010 9:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:09 AM
Points: 47, Visits: 166
I had a problem similar to this but when I tried to include a file attachment with the email. The solution was to execute the code on the server rather than from your profile. i.e. remotely login to the server and run it from there.
Post #966799
Posted Friday, September 17, 2010 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:46 AM
Points: 6, Visits: 36
Thanks it solve my problem
and i want to give example
how to use for person like me

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Account',
@recipients = 'email@domain.com',
@query =
'Declare @date smalldateTime
SET @date= CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ''/'' +
CAST(MONTH(GETDATE()) AS VARCHAR(2)) + ''/01 05:00:00''
AS smallDATETIME)

Select Column1,Column2,
(
select Count(1)
from DatabaseName.dbo.TableName1
where Column1=DL.Column1
and Column23=2
and ColumnDate>= @date
and ColumnText<>''SomeText''
) as CountColumn
from DatabaseName.dbo.TableName2 as DL',
@subject = 'Subject ',
@attach_query_result_as_file = 1 ;
Post #988456
Posted Thursday, September 23, 2010 11:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:39 PM
Points: 26, Visits: 248
Hi ..check your AD acccount it may be locked.
Post #992244
Posted Wednesday, February 16, 2011 4:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 9:46 PM
Points: 14, Visits: 240
Hi
I'm using sp_send_dbmail in below given format. If i execute first two lines it's showing "Mail Queued" and executing fine then i'm able to receive mail.

1 EXEC msdb..sp_send_dbmail
2 @recipients = 'kr@domain.com',
3 @subject = 'Customer Information Updated',
4 @query = 'select * from msdb.dbo.sysjobs',
5 @attach_query_result_as_file =0,
6 @query_attachment_filename = 'TEST.txt'

But when i execute whole statements at a time. Receiving the following error.

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 xxxxxxxxx, Line 1
Could not obtain information about Windows NT group/user 'domain\username', error code 0x6e.

Please help me here. How to resolve this.

Regards
Arjun



Kindest Regards,

arj
Post #1064832
Posted Tuesday, June 14, 2011 10:12 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:31 AM
Points: 198, Visits: 1,127
This could be a permission issue on the data that you are querying for the SQL Agent service account. We had a similar issue and that's what it was. I must say this is very helpful error message.
Post #1125157
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse