May 19, 2020 at 8:27 am
Hi Folks,
I want to send a daily report with SQL Query For that, i have implemented a query to send daily alerts with scheduling.
The issue is if i execute with inbuilt command as 'SP_WHO' it is working fine. If i replace any small select query in the query variable it is raising the issue. below is my SQL . Please help me
DECLARE @query NVARCHAR(MAX)
DECLARE @query1 NVARCHAR(MAX)
SELECT @query1='select * from SP_RMT'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EMAIL',
@recipients = 'c.b@gmail.com',
@body = 'Hi All,
Please find the attached RMT Report with status as of today.
Thanks & Best Regards
ADM Team',
@subject = 'RMT Daily Report' ,
@query = @query1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'RMT.csv';
May 19, 2020 at 9:02 am
The only thing that stands out is the name "SP_RMT" I take it that, that object is a Stored Procedure via the prefix "SP".
If so you cant select direct from a SP like your doing, you need to load the result set into a table and select from that table instead, or issue an EXECUTE SP_RMT instead.
Can you post the error message your getting also.
May 19, 2020 at 9:42 am
Hi Anthony,
I tried with Execute as well getting issue ,Tried with table same error.user have admin access,Tried with sa user too still same issue.
DECLARE @query NVARCHAR(MAX)
DECLARE @query1 NVARCHAR(MAX)
SELECT @query1='select * from ACT_Email'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EMAIL',
@recipients = 'c.b@gmail.com',
@body = 'Hi All,
Please find the attached RMT Report with status as of today.
Thanks & Best Regards
RDMH Team',
@subject = 'RMT Daily Report' ,
@query = @query1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'RMT.csv';
Error :
Date 5/19/2020 11:37:51 AM
Log Job History (test)
Step ID
Server SPWDFVM4945
Job Name test
Step Name
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User GLOBAL\cb1. The last step to run was step 1 (test).
May 19, 2020 at 1:10 pm
whats the output of the job step, not the overall job
May 19, 2020 at 7:41 pm
There is one additional parameter (at least) that is required here. You need to specify the database where you want to execute the code.
, @execute_query_database = 'user database'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2020 at 3:31 am
Step need to just fetch the data from table by using select statement and the result needs to attach in email notification.
That is the objective.
May 20, 2020 at 4:33 am
Tried with @execute_query_datebase='Database' Still no luck
May 20, 2020 at 6:45 am
post the full details of the job output, not just what the overall job says, theres more to the job, expand it and select the details from the step
May 20, 2020 at 6:20 pm
Tried with @execute_query_datebase='Database' Still no luck
What does this mean? Did you get an error - if so, what was the error?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply