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
Rudyx - the Doctor
Rudyx - the Doctor
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4735 Visits: 2503
Can you post the entire send mail command with the @query please ...

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
bensala
bensala
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 60
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.
marklegosz
marklegosz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1172 Visits: 1180
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
marklegosz
marklegosz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1172 Visits: 1180
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
Nuncaduermo
Nuncaduermo
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 248
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.-
Wildster
Wildster
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 168
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.
abidsikandar
abidsikandar
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 39
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 ;
karthikaug18
karthikaug18
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 276
Hi ..check your AD acccount it may be locked.
arj
arj
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
Chuck Hottle
Chuck Hottle
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1205
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.
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