May 18, 2013 at 8:00 pm
Hey Stan,
I just looked at your article and almost all of the graphics are coming across as solid blue boxes with a little red "x" inside a smaller box at the top left of the larger. Not sure if it's just my system or if the article code actually has a problem but I thought I'd give you a heads up.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2013 at 9:14 pm
I believe that the sqlservercentral.com server is having severe issues. I have been writing an article and can't modify it or add or delete resource files. I had the web administrator upload a file for me last week, but now I need to change it and can't.
I will email them again on Monday.
June 17, 2013 at 9:21 pm
Thank you for the detailed instructions. Are these steps specific to 2012? Will the same steps work in SQL Server 2008 R2?
June 18, 2013 at 6:10 am
I don't have 2012 yet. I did it in 2008R2.
April 10, 2014 at 12:25 pm
Hi - I am new to SSIS .
Please correct me if I have misstated anything below:
1) The SSIS package will be connecting to the report server DB.
2) SSIS package can be placed on any server.
Please let me know if there is any way one could trigger the subscriptions using ssis without connecting to the reportserver db. Say a webservice reference in the package. What should be the data connection for the package then.?
Regards
>>>>>>>>
AJV
April 10, 2014 at 1:28 pm
ajv849 (4/10/2014)
Hi - I am new to SSIS .Please correct me if I have misstated anything below:
1) The SSIS package will be connecting to the report server DB.
2) SSIS package can be placed on any server.
Please let me know if there is any way one could trigger the subscriptions using ssis without connecting to the reportserver db. Say a webservice reference in the package. What should be the data connection for the package then.?
Regards
>>>>>>>>
AJV
You are running a native SQL Server stored procedure.
EXEC ReportServer.ado.AddEvent @EventType='TimedSubscription',@EventData=subscriptionid
You don't necessarily have to run it from SSIS. Any way you can remotely execute a stored procedure will work.
I found this article that tells you how to do it from a web form or application:
http://sqlserverbiblog.wordpress.com/tag/manually-starting-subscription/[/url]
From the article: "You can use an ADO.NET SqlCommand object to execute this code from web form or application."
Hope this helps.
April 14, 2014 at 8:35 am
Thank you .
I do not have the accessiblity permission to the native sql proc.. So the only way left for me is to write a .net app to trigger the subscriptions.
But I want to know what would be the best way of achieving this . SSIS trigger of a proc or use webservice in a .net app.
Please let me know your thoughts.
Regards
>>>>>>>>>>
AJV
April 14, 2014 at 10:10 am
ajv849 (4/14/2014)
Thank you .I do not have the accessiblity permission to the native sql proc.. So the only way left for me is to write a .net app to trigger the subscriptions.
But I want to know what would be the best way of achieving this . SSIS trigger of a proc or use webservice in a .net app.
Please let me know your thoughts.
Regards
>>>>>>>>>>
AJV
I don't know what you mean by not having accessibility to ReportServer.ado.AddEvent.
You have to execute that procedure from a SQL statement. The .NET SqlCommand class would do it.
I found this link that might help:
June 18, 2015 at 11:37 pm
Good article, however did the SQL code work? I can't see it working as "ado.AddEvent"
June 19, 2015 at 12:33 am
Alternatively, we can capture the job id of that subscription.
we can trigger it as a job in Execute Sql Task.
E.g. : Execute Msdb..sp_start_job '4055734B-F633-425B-AA91-062FFE751A04'
June 19, 2015 at 3:38 am
Koen Verbeeck (3/20/2013)
Very useful article, thanks.
Apparently I already forgot I read this article 2 years ago.
Still useful 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 19, 2015 at 4:50 am
Just disable the subscription job and then execute it from T-SQL Statement Task or put the line of code into a SP that does some work and then immediately fires the report.
June 19, 2015 at 2:07 pm
Another way to kick of a subscription is to use sp_start_job and kick off the job SQL server automatically creates for each subscription. The name typically has a generic GUID name, to identify the right one I put together this script some time ago. The column RunSubscription will give you the SQL statement kicking off the subscription:
USE ReportServer;
WITH XMLTabl AS
(
SELECT cc.Name
, cc.Path
, CAST(ss.ExtensionSettings AS XML) ExtensionSettings
, ss.SubscriptionID
, ss.Description
, ss.LastRunTime
from dbo.Subscriptions ss
INNER JOIN dbo.Catalog cc
ON ss.Report_OID = cc.ItemID
)
, xmlvalues AS
(
SELECT SubscriptionID
, ParameterValue.value('(./Name/text())[1]','varchar(100)') AS Parameter
, ParameterValue.value('(./Value/text())[1]','varchar(2000)') AS Value
FROM XMLTabl tt
CROSS APPLY tt.ExtensionSettings.nodes('//ParameterValue') Parameters(ParameterValue)
)
, mail AS
(
SELECT SubscriptionID
, 1 RowID
, CASE CHARINDEX(';', Value)
WHEN 0 THEN LTRIM(RTRIM(Value))
ELSE LTRIM(RTRIM(STUFF(Value,CHARINDEX(';', Value), LEN(Value),'')))
END Mail
, CASE CHARINDEX(';', Value)
WHEN 0 THEN ''
ELSE LTRIM(RTRIM(STUFF(Value,1,CHARINDEX(';', Value),'')))
END ToParse
, Parameter
FROM xmlvalues
WHERE Parameter IN ('TO','CC','BCC')
UNION ALL
SELECT SubscriptionID
, RowID + 1
, CASE CHARINDEX(';', ToParse)
WHEN 0 THEN LTRIM(RTRIM(ToParse))
ELSE LTRIM(RTRIM(STUFF(ToParse,CHARINDEX(';', ToParse), LEN(ToParse),'')))
END Mail
, CASE CHARINDEX(';', ToParse)
WHEN 0 THEN ''
ELSE LTRIM(RTRIM(STUFF(ToParse,1,CHARINDEX(';', ToParse),'')))
END ToParse
, Parameter
FROM mail
WHERE LEN(ToParse) > 0
)
, enddate AS
(
SELECT ss.SubscriptionID
, CAST(CAST(MatchData AS XML).value('(//*:EndDate/text())[1]','varchar(100)') AS DATE) SubscriptionEndDate
FROM dbo.Subscriptions ss
CROSS APPLY (SELECT CAST(MatchData AS XML)
FROM dbo.Subscriptions
WHERE SubscriptionID = ss.SubscriptionID) ca(XML)
CROSS APPLY XML.nodes('//*:ScheduleDefinition') cb(DOW)
)
SELECT aa.Name SSRS_Name
, 'EXEC msdb.dbo.sp_start_job ''' + jj.name + '''' [RunSubscription]
, aa.Path Report_Path_On_Server
, ff.Value ReportFormat
, aa.LastRunTime
, ee.SubscriptionEndDate
, (SELECT COUNT(*) FROM mail WHERE mail.SubscriptionID = aa.SubscriptionID) [Number_Of_Recipients]
, STUFF((SELECT '; ' + RTRIM(Parameter + ':' + Mail)
FROM mail mm
WHERE aa.SubscriptionID = mm.SubscriptionID
GROUP BY SubscriptionID
, Parameter
ORDER BY Parameter DESC, Mail
FOR XML PATH('')),1,2,'') Mail_Recipients
, ISNULL(cc.Value,'') Mail_Text
, aa.Description Subscription_Description
, aa.SubscriptionID
, jj.name JobName
, jj.job_id Job_ID
FROM XMLTabl aa
INNER JOIN dbo.ReportSchedule ss
ON aa.SubscriptionID = ss.SubscriptionID
INNER JOIN msdb.dbo.sysjobs jj
ON Convert(nvarchar(128),ss.ScheduleID) = jj.name
LEFT JOIN xmlvalues ff
ON aa.SubscriptionID = ff.SubscriptionID
AND ff.Parameter = 'RenderFormat'
LEFT JOIN xmlvalues cc
ON aa.SubscriptionID = cc.SubscriptionID
AND cc.Parameter = 'Comment'
LEFT JOIN enddate ee
ON aa.SubscriptionID = ee.SubscriptionID
OPTION (MAXRECURSION 0)
June 24, 2015 at 1:29 am
keyser soze-308506 (3/19/2013)
HiI need to send distinct report to distinct persons
I have a dataset of people, with their names, email, etc
including the parameters to run the report
Could I accomplish this with SSxS ?
regards
Yes
Just create data driven subscription rather than timed subscription. Put all the email in one table and query them. The specific parametered report can go to specific email too. You even dont need SSIS for it . Only SSRS can do it
------------------------------------------------------------------------------------
Ashish
June 25, 2015 at 7:50 am
Thanks for the training.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply