September 1, 2011 at 5:47 pm
Finally got it to work, meaning the sp would send a report. However, I tried to change the name of the file by adding the date to the end of the file. No luck.
Has anyone successfully changed the name of the file sent out? ...If not, does anyone have a method of changing the attachment name sent out?
Cheers,
Eric
December 31, 2011 at 8:46 pm
Has anyone tried this in SSRS 2008 in SharePoint integrated mode?
I've been trying to do this for several days and even though I can get the subscription to be kicked off from SQL and modify the ExtensionSettings and Parameters as soon as I update the path in the Catalog it no longer works. But it also doesn't generate any errors. As soon as I set the path back, I can get the subscription to be be kicked off from SQL again.
January 10, 2012 at 12:07 pm
I'm going to give the missing info on SSRS running in SharePoint integrated mode. Would love to see comments by anyone else who is able to do this!
SOLUTION:
If you are running a SharePoint integrated instance of SSRS then there are two other places that must be updated in order for you to successfully kick off the subscription via SQL. Remember that in an integrated mode the report is also a document in SharePoint.
Both of these additional fields are in the WSS_Content_Reports database. The first is the LeafName field in the dbo.AllDocs table. The second is the tp_LeafName field in the dbo.AllUserData table. My solution was to stored all current values of necessary IDs and the report name and path in a table before updating the SSRS and SharePoint tables. Then update the fields in the SSRS and SharePoint tables. Make sure that you put it in a Begin Transaction Commit Transaction Block or the subscription may fire before the changes are committed, this actually happened in one of my tests. Kick off the subscription and once it is done. Change the values back to their original values.
I managed to use my relationships in IT to get the necessary access to update the fields in the WSS_Content_Reports database long enough to build the solution and test that it works but now my roadblock is the internal overblown concerns of a few administrators. It is being portrayed as having the potential to cause Global Failure to SharePoint when that is simply not the case. The dangers are solely isolated to breaking the relationship of the SSRS report to the corresponding SharePoint Document as well as the corresponding permissions.
If you change only the path field in the ReportServer.dbo.Catalog table. The error msg on the subscription will say that the item was not found. If you change the LeafName field in the WSS_Content_Reports.dbo.AllDocs table in addition to the path field in the ReportServer.dbo.Catalog table but WITHOUT also changing the tp_LeafName in the WSS_Content_Reports.dbo.AllUserData table you will get a subscription error saying that the user credentials you used to kick off the subscription in SQL don't have the necessary permissions. If you change all three before kicking off the subscription it will work.
Here is the Create Table statement I used to create a history table to store the before and after values:
USE [LAF_Reporting]
Go
If (Select OBJECT_ID('dbo.SQLInitiatedSubscriptionHistory')) Is Not Null
Drop Table LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory
CREATE TABLE LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory(
[nDex] [int] IDENTITY(1,1) NOT NULL,
[ReportID] [uniqueidentifier] NOT NULL,
[Catalog_Path] [nvarchar](425) NULL,
[Catalog_PathTemp] [nvarchar](425) NULL,
[Catalog_Name] [nvarchar](425) NULL,
[Catalog_NameTemp] [nvarchar](425) NULL,
[SubscriptionID] [uniqueidentifier] NULL,
[ScheduleID] [uniqueidentifier] NULL,
[Subscriptions_Description] [nvarchar](512) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_Parameters] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_ParametersTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_ExtensionSettings] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_ExtensionSettingsTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_DataSettings] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[Subscriptions_DataSettingsTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
[ExecutedDate] [datetime] NULL,
[ExecutionStatus] [nvarchar] (260) NULL,
[Completed] [datetime] NULL,
[ExecutionTime] AS (datediff(second,[ExecutedDate],[Completed])),
CONSTRAINT [PK_SQLInitiatedSubscriptionHistory] PRIMARY KEY CLUSTERED
(
[nDex] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here are my updated statements:
Update c
Set Path = sh.Catalog_Path,
Name = sh.Catalog_Name
From ReportServer.dbo.Catalog c
Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On c.ItemID = sh.ReportID
WHERE sh.ReportID = @ReportID And
sh.SubscriptionID = @subscriptionID And
sh.ScheduleID = @ScheduleID And
sh.ExecutedDate = @ExecutedDatetime
Update ad
Set LeafName = sh.Catalog_Name
From WSS_Content_Reports.dbo.AllDocs ad (NoLock)
Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On ad.ID = sh.ReportID
WHERE sh.ReportID = @ReportID And
sh.SubscriptionID = @subscriptionID And
sh.ScheduleID = @ScheduleID And
sh.ExecutedDate = @ExecutedDatetime
Update aud
Set tp_LeafName = sh.Catalog_Name
--Select aud.tp_LeafName, ad.LeafName, ad.*
From WSS_Content_Reports.dbo.AllUserData aud (NoLock)
Join WSS_Content_Reports.dbo.AllDocs ad (NoLock) On aud.tp_ID = ad.DoclibRowId And aud.tp_ListId = ad.ListId And aud.tp_SiteId = ad.SiteId
Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On ad.ID = sh.ReportID
WHERE sh.ReportID = @ReportID And
sh.SubscriptionID = @subscriptionID And
sh.ScheduleID = @ScheduleID And
sh.ExecutedDate = @ExecutedDatetime
February 3, 2012 at 11:00 am
Hey Josh,
This works for email subscriptions, but for some reason, does not work for Windows File Share subscriptions. I am using SQL Server 2008 r2 Standard. Perhaps this is an enterprise feature? Does anyone know if this feature will be included in SQL 2012?
October 25, 2012 at 8:36 am
where do we set the name of the file in Subscription?
March 1, 2013 at 3:19 pm
Using SSRS 2005.
I've implemented everything in this article, the report name doesn't change on the attachment.
I've checked everything, the path of the previous file name is accurate.
June 24, 2016 at 10:54 am
I tried the suggestions but the email never came.
If we change the Path in Catalog table to non existent user friendly report name path, are we not breaking the path for successful execution of subscription ?
Sorry if I am missing something here
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply