October 28, 2015 at 9:15 am
Hi everyone, I have a job set up which runs a query and most of the time would not give out any results, but if any records show up then job name: 'D26XXXXX-XXXX-XXXX-XXXX-XXXXXXXX' begins as follows:
if @@ROWCOUNT > 0
begin
USE msdb
EXEC sp_start_job @job_name = 'D26XXXXX-XXXX-XXXX-XXXX-XXXXXXXX'
end
The problem with the job_name 'D26X' is that it only goes to the manager and not to the individual collector. I am trying to keep the Job 'D26X' intact also send out an email to marc and Anthony if there loan shows up in the result set.
LoanID........ CollectorName ....... CollectorEmailAddress
1001 ............... Marc.......................marc@gmail.com
1002 ............ Anthony.................Anthony@gmail.com
October 28, 2015 at 9:26 am
Have you opened the job and more particularly that job step? Likely, all you need to do is modify the call and add more recipients.
Look for something like:
@recipients = 'John@AdventureWorks.com',
And add other recipients using a semi colon as such:
@recipients = 'John@AdventureWorks.com';'marc@gmail.com';'Anthony@gmail.com',
Note: This is all assuming that it's using sp_send_dbmail
October 28, 2015 at 9:39 am
On the description of the Job 'D26XXX' I see the following note:
'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
On the one and only step I see
'exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='2021c27f-0a5a-45f7-9f4d-4a35349d8122'
October 28, 2015 at 12:20 pm
Would anyone know where and how to edit the following
'exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='2021c27f-0a5a-45f7-9f4d-4a35349d8122'
October 28, 2015 at 12:25 pm
SQLPain (10/28/2015)
On the description of the Job 'D26XXX' I see the following note:'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
On the one and only step I see
'exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='2021c27f-0a5a-45f7-9f4d-4a35349d8122'
That explains the funky job name. It would seem that the job is owned by reporting services. It's been awhile since I've worked with SSRS but you'll need to find the associated report and edit that.
You could query the catalog and subscriptions tables on the Reporting Services db to find the actual report name. If I had it installed I'd be able to write something up for you, but alas...
October 28, 2015 at 12:32 pm
yeah that's true, currently I have more than 50 jobs set up on reporting server, all with funky name, don't know how to figure out which one is associated with the above the job..
Confused..
October 28, 2015 at 12:42 pm
Under the subscriptions or catalogs table, Am I looking for
'D26XXX' Or '2021cXXXXX' ?
October 28, 2015 at 12:58 pm
SQLPain (10/28/2015)
yeah that's true, currently I have more than 50 jobs set up on reporting server, all with funky name, don't know how to figure out which one is associated with the above the job..Confused..
i use this query to generate metadata so i can do two things:
find the right job related to an SSRS report to run on demand.
update the description of the job to have the job name.
SELECT distinct 'exec msdb.dbo.sp_start_job @job_id = ''' + convert(varchar(40),b.job_id) COLLATE SQL_Latin1_General_CP1_CI_AS + '''; --' + + b.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' : ' + e.name COLLATE SQL_Latin1_General_CP1_CI_AS As Cmd,
'UPDATE msdb.dbo.sysjobs set description = ''SSRS Report Subscription: '+ e.path COLLATE SQL_Latin1_General_CP1_CI_AS + CHAR(13) + CHAR(10) + e.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''' WHERE job_id = ''' + convert(varchar(40),b.job_id) +''';' AS Fix,
-- b.job_id As JobID,
-- b.name AS JobName
e.name
, e.path
, d.description
, a.SubscriptionID
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b
ON convert(varchar(40),a.ScheduleID) = b.name
JOIN ReportServer.dbo.ReportSchedule c
ON b.name = convert(varchar(40),c.ScheduleID)
JOIN ReportServer.dbo.Subscriptions d
ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e
ON d.report_oid = e.itemid
where 1 = 1 and laststatus <> 'Pending'
--and path like '/Month End/%'
--and e.name like 'CPU%'
order by e.name
Lowell
October 28, 2015 at 12:59 pm
Try this:
SELECT
ScheduleID,
[Path],
[Name],
s.Description
FROM ReportServer.dbo.catalog c
JOIN ReportServer.dbo.Subscriptions s on c.ItemID = s.Report_OID
JOIN ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
October 28, 2015 at 1:02 pm
Lowell (10/28/2015)
SQLPain (10/28/2015)
yeah that's true, currently I have more than 50 jobs set up on reporting server, all with funky name, don't know how to figure out which one is associated with the above the job..Confused..
i use this query to generate metadata so i can do two things:
find the right job related to an SSRS report to run on demand.
update the description of the job to have the job name.
Thanks for stepping in Lowell, I really should get SSRS up and running again. Even if it's just for practice. 😉
October 28, 2015 at 1:08 pm
Keep in mind that you are not going to edit the job, or the data in the underlying tables.
You will need to go into the correct report, and modify the subscriptions.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 28, 2015 at 2:11 pm
Oh wow, thank you guys. I already saved both of the queries. I was able to track down the report it was on a different server and not on reports/reports.
Going back to my original questions, how do I insert the collectors email address ? I only want to send email to the collectors who are displayed in my result set and not all of them.
It was mentioned earlier that I can include the collectors email addresses under subscriptions but that would be sending out emails to everyone and not specific to those collectors displayed in our result set.
October 28, 2015 at 5:11 pm
I can't give you any advice on SSRS itself, but if you're using sp_send_dbmail to send your email, then you're probably using T-SQL to generate your list of recipients. In that case, there are many ways to build your recipient list. You want to end up with a semicolon-delimited list of email addresses, such as 'a@test.com; c@test.com' that comes from a table.
Here's an example of using string concatenation in a single query to produce your list in a variable. You'd want to use your table; this is just for demonstration.
--create a test table and populate a few rows
if object_id('tempdb.dbo.#t', 'u') is not null drop table #t;
CREATE TABLE #t (
Email varchar(64),
Active bit);
INSERT INTO #t(Email, Active)
VALUES('a@test.com', 1),
('b@test.com', 0),
('c@test.com', 1);
--select our concatenated list of email addresses
DECLARE @strTo varchar(500) = '';
SELECT @strTo = STUFF(@strTo + '; ' + Email, 1, 1, '')
FROM #t
WHERE Active = 1;
Then, in your call to sp_send_dbmail, assign your @strTo variable to the @recipients parameter of the procedure.
Like I said, there are many approaches to this problem. Wayne Sheffield has an excellent article demonstrating the FOR XML approach at http://qa.sqlservercentral.com/articles/comma+separated+list/71700/.
October 28, 2015 at 7:10 pm
You need to develop a data driven subscription.
This article should get you started.
https://msdn.microsoft.com/en-us/library/ms169673.aspx
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 29, 2015 at 11:16 am
Another option (could possibly be considered a hack) is rather than start the subscription job, instead send a link to the report. I have a job that has several steps of data massaging and then the last step is to notify everyone via email that the report is available. I include a link to the report in the email and am able to dynamically control the recipient list based on whatever condition I want:
declare @Subject varchar(100)
declare @Body varchar(max)
declare @RecipientList varchar(max)
-- You could dynamically set your recipient list here
select @RecipientList = 'recipient1@mydomian.com; recipient2@mydomain.com; recipient3@mydomain.com'
select @Subject = 'My dynamic subject line here'
-- Then just email them a link directly to the report
-- rather than fire the report subscription
select @Body = '<span style="font-family: Arial; font-size: 12px">Click the link below to access the blah blah report.'
select @Body +'<a href="http://YourReportServer/Reports/Pages/Report.aspx?ItemPath=%2fReports%2fYourReportHere">Your Report Here</a></span>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfile',
@recipients = @RecipientList,
@subject = @Subject,
@body = @Body,
@body_format = 'HTML'
Good luck -
Lisa
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply