EADLOCK_PRIORITY LOW declare @now datetime; select @now = GETDATE(); declare @DeletedItems table (ItemID uniqueidentifier not null primary key, Intermediate uniqueidentifier null) declare @DeletedCacheSnapshots table (SnapshotDataID uniqueidentifier null) begin transaction insert into @DeletedItems select top(@MaxToClean) TempCatalogID, Intermediate from [ReportServer$SQLEXPRESSTempDB].dbo.TempCatalog TC WITH(UPDLOCK) where ExpirationTime < @now and not exists ( select 1 from [ReportServer$SQLEXPRESSTempDB].dbo.SessionData SD WITH (INDEX (IX_EditSessionID)) where SD.EditSessionID = TC.EditSessionID ) ; delete from [ReportServer$SQLEXPRESSTempDB].dbo.TempDataSources where ItemID in ( select ItemID from @DeletedItems ) ; delete from [ReportServer$SQLEXPRESSTempDB].dbo.TempDataSets where ItemID in ( select ItemID from @DeletedItems ) ; delete from [ReportServer$SQLEXPRESSTempDB].dbo.TempCatalog where TempCatalogID in ( select ItemID from @DeletedItems ) ; delete from [ReportServer$SQLEXPRESSTempDB].dbo.ExecutionCache output deleted.SnapshotDataID into @DeletedCacheSnapshots(SnapshotDataID) where ReportID in ( select ItemID from @DeletedItems ); update [ReportServer$SQLEXPRESSTempDB].dbo.SnapshotData set PermanentRefcount = PermanentRefcount - 1 where SnapshotData.SnapshotDataID in (select Intermediate from @DeletedItems union select SnapshotDataID from @DeletedCacheSnapshots) ; commit select @NumCleaned = count(1) from @DeletedItems ; END 0ŠÅ;  œ8 -- Republishing generates new ID and stores those in the object model, -- in order to resolve the data sets we need to rebind the old -- data set definition to the current ID CREATE PROCEDURE [dbo].[RebindDataSet] @ItemId uniqueidentifier, @Name nvarchar(260), @NewID uniqueidentifier AS UPDATE DataSets SET ID = @NewID WHERE ItemID = @ItemId AND [Name] = @Name 0Ãé/  r8 -- used to create snapshots CREATE PROCEDURE [dbo].[GetCompiledDefinition] @Path nvarchar (425), @EditSessionID varchar(32) = NULL, @OwnerSid as varbinary(85) = NULL, @OwnerName as nvarchar(260) = NULL, @AuthType int AS BEGIN DECLARE @OwnerID uniqueidentifier if(@EditSessionID is not null) BEGIN EXEC GetUserID @OwnerSid, @OwnerName, @AuthType, @OwnerID OUTPUT END SELECT MainItem.Type, MainItem.Intermediate, MainItem.LinkSourceID, MainItem.Property, MainItem.Description, SecData.NtSecDescPrimary, MainItem.ItemID, MainItem.ExecutionFlag, LinkTarget.Intermediate, LinkTarget.Property, LinkTarget.Description, MainItem.[SnapshotDataID], MainItem.IntermediateIsPermanent FROM ExtendedCatalog(@OwnerID, @Path, @EditSessionID) MainItem LEFT OUTER JOIN SecData ON MainItem.PolicyID = SecData.PolicyID AND SecData.AuthType = @AuthType LEFT OUTER JOIN Catalog LinkTarget with (INDEX(PK_Catalog)) on MainItem.LinkSourceID = LinkTarget.ItemID END !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!f f f êN×(<ƒ¤m®´&þ<'^î 8 CREATE PROCEDURE [dbo].[GetDataSetForExecution] @ItemID uniqueidentifier, @ParamsHash int AS DECLARE @now AS datetime SET @now = GETDATE() SELECT SN.SnapshotDataID, SN.EffectiveParams, SN.QueryParams, (SELECT CachePolicy.ExpirationFlags FROM CachePolicy WHERE CachePolicy.ReportID = Cat.ItemID), Cat.Property FROM Catalog AS Cat LEFT OUTER JOIN ( SELECT TOP 1 ReportID, SN.SnapshotDataID, EffectiveParams, QueryParams FROM [ReportServer$SQLEXPRESSTempDB].dbo.ExecutionCache AS EC INNER JOIN [ReportServer$SQLEXPRESSTempDB].dbo.SnapshotData AS SN ON EC.SnapshotDataID = SN.SnapshotDataID AND EC.ParamsHash = SN.ParamsHash WHERE AbsoluteExpiration > @now AND SN.ParamsHash = @ParamsHash AND EC.ReportID = @ItemID ORDER BY SN.CreatedDate DESC ) as SN ON Cat.ItemID = SN.ReportID WHERE Cat.ItemID = @ItemID <`‚â y8 CREATE VIEW [dbo].[ExecutionLog3] AS SELECT InstanceName, COALESCE(C.Path, 'Unknown') AS ItemPath, UserName, ExecutionId, CASE(RequestType) WHEN 0 THEN 'Interactive' WHEN 1 THEN 'Subscription' WHEN 2 THEN 'Refresh Cache' ELSE 'Unknown' END AS RequestType, -- SubscriptionId, Format, Parameters, CASE(ReportAction) WHEN 1 THEN 'Render' WHEN 2 THEN 'BookmarkNavigation' WHEN 3 THEN 'DocumentMapNavigation' WHEN 4 THEN 'DrillThrough' WHEN 5 THEN 'FindString' WHEN 6 THEN 'GetDocumentMap' WHEN 7 THEN 'Toggle' WHEN 8 THEN 'Sort' WHEN 9 THEN 'Execute' WHEN 10 THEN 'RenderEdit' ELSE 'Unknown' END AS ItemAction, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, CASE(Source) WHEN 1 THEN 'Live' WHEN 2 THEN 'Cache' WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History' WHEN 5 THEN 'AdHoc' WHEN 6 THEN 'Session' WHEN 7 THEN 'Rdce' ELSE 'Unknown' END AS Source, Status, ByteCount, [RowCount], AdditionalInfo FROM ExecutionLogStorage EL WITH(NOLOCK) LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID) <™¦Ö 8 CREATE VIEW [dbo].[ExecutionLog] AS SELECT [InstanceName], [ReportID], [UserName], CASE ([RequestType]) WHEN 1 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END AS [RequestType], [Format], [Parameters], [TimeStart], [TimeEnd], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], CASE([Source]) WHEN 6 THEN 3 ELSE [Source] END AS Source, -- Session source doesn't exist in yukon, mark source as snapshot -- for in-session requests [Status], [ByteCount], [RowCount] FROM [ExecutionLogStorage] WITH (NOLOCK) WHERE [ReportAction] = 1 -- Backwards compatibility log only contains render requests 06x­ ˜8 CREATE PROCEDURE [dbo].[GetNameById] @ItemID uniqueidentifier AS SELECT Path FROM Catalog WHERE ItemID = @ItemID 0oœ¡ ) 8 CREATE PROCEDURE [dbo].[AddDataSource] @DSID [uniqueidentifier], @ItemID [uniqueidentifier] = NULL, -- null for future suport dynamic delivery @SubscriptionID [uniqueidentifier] = NULL, @EditSessionID varchar(32) = NULL, @Name [nvarchar] (260) = NULL, -- only for scoped data sources, MUST be NULL for standalone!!! @Extension [nvarchar] (260) = NULL, @LinkID [uniqueidentifier] = NULL, -- link id is trusted, if it is provided - we use it @LinkPath [nvarchar] (425) = NULL, -- if LinkId is not provided we try to look up LinkPath @CredentialRetrieval [int], @Prompt [ntext] = NULL, @ConnectionString [image] = NULL, @OriginalConnectionString [image] = NULL, @OriginalConnectStringExpressionBased [bit] = NULL, @UserName [image] = NULL, @Password [image] = NULL, @Flags [int], @AuthType [int], @Version [int] AS DECLARE @ActualLinkID uniqueidentifier SET @ActualLinkID = NULL IF (@LinkID is NULL) AND (@LinkPath is not NULL) BEGIN SELECT Type, ItemID, NtSecDescPrimary FROM Catalog LEFT OUTER JOIN SecData ON Catalog.PolicyID = SecData.PolicyID AND SecData.AuthType = @AuthType WHERE Path = @LinkPath SET @ActualLinkID = (SELECT ItemID FROM Catalog WHERE Path = @LinkPath) END ELSE BEGIN SET @ActualLinkID = @LinkID END IF(@EditSessionID is not null) BEGIN INSERT INTO [ReportServer$SQLEXPRESSTempDB].dbo.TempDataSources (DSID, ItemID, [Name], Extension, Link, CredentialRetrieval, Prompt, ConnectionString, OriginalConnectionString, OriginalConnectStringExpressionBased, UserName, Password, Flags, Version) VALUES (@DSID, @ItemID, @Name, @Extension, @ActualLinkID, @CredentialRetrieval, @Prompt, @ConnectionString, @OriginalConnectionString, @OriginalConnectStringExpressionBased, @UserName, @Password, @Flags, @Version) EXEC ExtendEditSessionLifetime @EditSessionID END ELSE BEGIN INSERT INTO DataSource ([DSID], [ItemID], [SubscriptionID], [Name], [Extension], [Link], [CredentialRetrieval], [Prompt], [ConnectionString], [OriginalConnectionString], [OriginalConnectStringExpressionBased], [UserName], [Password], [Flags], [Version]) VALUES (@DSID, @ItemID, @SubscriptionID, @Name, @Extension, @ActualLinkID, @CredentialRetrieval, @Prompt, @ConnectionString, @OriginalConnectionString, @OriginalConnectStringExpressionBased, @UserName, @Password, @Flags, @Version) END 0¨À• Ä8 CREATE PROCEDURE [dbo].[GetDataSources] @ItemID [uniqueidentifier], @AuthType int AS BEGIN SELECT -- select data sources and their links (if they exist) DS.[DSID], -- 0 DS.[ItemID], -- 1 DS.[Name], -- 2 DS.[Extension], -- 3 DS.[Link], -- 4 DS.[CredentialRetrieval], -- 5 DS.[Prompt], -- 6 DS.[ConnectionString], -- 7 DS.[OriginalConnectionString], -- 8 DS.[UserName], -- 9 DS.[Password], -- 10 DS.[Flags], -- 11 DSL.[DSID], -- 12 DSL.[ItemID], -- 13 DSL.[Name], -- 14 DSL.[Extension], -- 15 DSL.[Link], -- 16 DSL.[CredentialRetrieval], -- 17 DSL.[Prompt], -- 18 DSL.[ConnectionString], -- 19 DSL.[UserName], -- 20 DSL.[Password], -- 21 DSL.[Flags], -- 22 C.Path, -- 23 SD.NtSecDescPrimary, -- 24 DS.[OriginalConnectStringExpressionBased], -- 25 DS.[Version], -- 26 DSL.[Version], -- 27 (SELECT 1 WHERE EXISTS (SELECT * from [ModelItemPolicy] AS MIP WHERE C.[ItemID] = MIP.[CatalogItemID])) -- 28 FROM ExtendedDataSources AS DS LEFT OUTER JOIN (DataSource AS DSL INNER JOIN Catalog C ON DSL.[ItemID] = C.[ItemID] LEFT OUTER JOIN [SecData] AS SD ON C.[PolicyID] = SD.[PolicyID] AND SD.AuthType = @AuthType) ON DS.[Link] = DSL.[ItemID] WHERE DS.[ItemID] = @ItemID or DS.[SubscriptionID] = @ItemID END T INTO DataSource ([DSID], [ItemID], [SubscriptionID], [Name], [Extension], [Link], [CredentialRetrieval], [Prompt], [ConnectionString], [OriginalConnectionString], [OriginalConnectStringExpressionBased], [UserName], [Password], [Flags], [Version]) VALUES (@DSID, @ItemID, @SubscriptionID, @Name, @Extension, @ActualLinkID, @CredentialRetrieval, @Prompt, @ConnectionString, @OriginalConnectionString, @OriginalConnectStringExpressionBased, @UserName, @Password, @Flags, @Version) END !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!DD¿¿¿¿– þ â [)Æ €Œ) õž:yRz@Path:yRz8@ServerParametersID6<8œz8binary_message_body6<8œz<conversation_group_id6<8œz8conversation_handle6<8œz0fragment_bitmap6<8œz ,fragment_size6<8œz:message_enqueue_time6<8œz&message_id6<8œz@message_sequence_number6<8œz0message_type_id6<8œz ,next_fragment6<8œz"priority6<8œz,queuing_order6<8œz 8service_contract_id6<8œz &service_id6<8œzstatus6<8œz &validation:ÜM{{ @job_id:ÜM{{$@job_name:ÜM{{4@owner_login_name6u\{(column_guid6u\{ file_id6u\{<filestream_value_name6u\{,oplsn_bOffset6u\{*oplsn_fseqno6u\{*oplsn_slotid6u\{(rowset_guid6u\{ size6u\{ status6u\{Btransaction_sequence_num:ro|$@AuthType:ro| ,@DaysInterval:ro| *@DaysOfMonth:ro| (@DaysOfWeek:ro|"@EndDate:ro|&@EventData:ro|&@EventType:ro|@Flags:ro|.@LastRunStatus:ro|*@LastRunTime:ro| 2@MinutesInterval:ro|@Month:ro|*@MonthlyWeek:ro|@Name:ro|*@NextRunTime:ro|@Path:ro|0@RecurrenceType:ro|:@ScheduledRunTimeout:ro|(@ScheduleID:ro|&@StartDate:ro|@State:ro|@Type:ro|$@UserName:ro|"@UserSid:ro| .@WeeksInterval6®€„|&commit_csn6®€„|&commit_lbn6®€„|(commit_time6®€„|$commit_ts6®€„|"dbfragid6®€„| xdes_id:N–c} ,@DaysInterval:N–c} *@DaysOfMonth:N–c} (@DaysOfWeek:N–c}"@EndDate:N–c}@Flags:N–c}.@LastRunStatus:N–c}*@LastRunTime:N–c} 2@MinutesInterval:N–c}@Month:N–c}*@MonthlyWeek:N–c}@Name:N–c}*@NextRunTime:N–c}0@RecurrenceType:N–c}:@ScheduledRunTimeout:N–c}(@ScheduleID:N–c}&@StartDate:N–c}@State:N–c} .@WeeksInterval6ç¤x}$item_guid6ç¤x},oplsn_bOffset6ç¤x}*oplsn_fseqno6ç¤x}*oplsn_slotid6ç¤x}"table_id:‡ºW~*@NextRunTime:‡ºW~(@ScheduleID:ÀÞK(@ScheduleID:yRz*@CurrentDate:yRz"@Timeout:yRz(@Expiration:yRz4@ParametersValues:yRz8@ParentParametersID:²CF{8@ServerParametersID:ëg:|6@ParametersCleaned:$Œ.}.@OldSnapshotId:$Œ.}.@NewSnapshotId:$Œ.}:@IsPermanentSnapshot:]°"~.@OldSnapshotId:]°"~.@NewSnapshotId:]°"~:@IsPermanentSnapshot:]°"~"@Machine:–Ô.@OldSnapshotId:–Ô.@NewSnapshotId:–Ô:@IsPermanentSnapshot:–ÔF@TransientRefCountModifier:–Ô6@UpdatedReferences6Oz@JobID6Oz&@JobStatus6ˆ9{,@ComputerName6ˆ9{"@JobType63¦í} @ItemID63¦í}@Name63¦í}@Path63¦í}$@ParentID63¦í}@Type63¦í}"@Content63¦í},@Intermediate63¦í},@LinkSourceID63¦í} $@Property63¦í} &@Parameter63¦í} *@Description63¦í}  @Hidden63¦í} ,@CreatedBySid63¦í}.@CreatedByName63¦í}$@AuthType63¦í},@CreationDate63¦í}4@ModificationDate63¦í}$@MimeType63¦í}.@SnapshotLimit63¦í}(@PolicyRoot63¦í}$@PolicyID63¦í}.@ExecutionFlag63¦í}"@SubType63¦í}*@ComponentID6lÊá~@Path6lÊá~ @Prefix6lÊá~.@EditSessionID6lÊá~$@OwnerSid6lÊá~&@OwnerName6lÊá~$@AuthType6¥îÕ@Path6¥îÕ$@AuthType6—SÐ{ID6—SÐ{LinkID6—SÐ{Name6—SÐ{ItemID6ÐwÄ|&@OldPrefix6ÐwÄ|&@NewPrefix6ÐwÄ|&@PrefixLen6 œ¸}"@OldPath6 œ¸}"@NewPath6BÀ¬~