hi all,
first of all i'm sorry for my bad english, i hope someone understand me.
i have a problem with schedules in reporting services.
i create a new one and i had no problem doing it.
the creation of the schedules make a new job in the sql server.
the job in the sql server works right.
instead the schedule's informartion in the reporting services web page doesn't refresh.
the schedule web page always reports the column "last execution" = never and the "next execution" = the first value.
any subscriptions set on the schedule doesn't work.
above some information about the installation:
- sql\report server is in another intranet. I manage the site from a different intranet.
- i restore on the main server the ReportServer and the ReportServerTempDB from the deployment server. i read the microsoft msdn to restore the db.
hope someone could help me
thanks
nicola
The microsoft article to restore reportserver db is very incomplete.. I do the following:
in ReportServer - Database
EXEC dbo.sp_AlterOwnerOfAgentJob @.username='ASPNET'
GO
EXEC dbo.sp_AlterOwnerOfSubscriptions 'ITEMOwner'
GO
where ASPNET is the assount of iis / application pool and ITEMOwner is a user from your domain. (usually this name has to be fullqualified domain\username)
The stored procedures are:
USE [ReportServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_AlterOwnerOfSubscriptions] ( @.username nvarchar(128))
AS
BEGIN
/* ?ndert den Besitzer der Abonnements, da sonst die Fehlermeldung:
"Benutzer XY wurde nicht gefunden"
Beim nachtr?glichen ?ndern eines Abos auftreten kann
*/
SET NOCOUNT ON;
declare @.UserID uniqueidentifier
/* ID aus Benutzertabelle holen */
select top 1 @.UserID=userid from users where username=@.username
if @.UserID is null
begin
print '======================================================='
print 'ERROR: The given user was not found'
print 'Check if''' + @.username + ''' is correct!'
print '======================================================='
end
else
begin
print 'Changing subscription user to: ' + @.username
update dbo.Subscriptions set OwnerID=@.UserID,ModifiedByID=@.UserID,ModifiedDate=getDate();
update dbo.Schedule set CreatedById=@.UserID;
end
END
GO
/* =========================================================================== */
CREATE PROCEDURE [dbo].[sp_AlterOwnerOfAgentJob] ( @.username nvarchar(128))
AS
BEGIN
/*
Diese SP ?ndert den Besitzer der SQL-Server Agent-Jobs, diese werden
f?lschlicherweise auf SYSTEM gesetzt, sollten aber ASPNET sein!
*/
SET NOCOUNT ON;
declare @.jobname nvarchar(128),@.servername nvarchar(128),@.UserID uniqueidentifier
/* Cursor zum auslesen der aktuellen Jobs */
DECLARE job_cursor CURSOR FOR select name from msdb.dbo.sysjobs
OPEN job_cursor
/* Resultset durchgehen und Besitzer jedes Jobs setzen */
FETCH NEXT FROM job_cursor INTO @.jobname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Changing owner to: ''' + @.username + ''' for job ' + @.jobname
EXEC msdb.dbo.sp_update_job @.job_name=@.jobname,
@.owner_login_name=@.username
FETCH NEXT FROM job_cursor INTO @.jobname
END
/* Resultset / Cursor wieder schlie?en und Freigeben */
CLOSE job_cursor
DEALLOCATE job_cursor
END
GO
/* =========================================================================== */
Usually it works after that.. If not rebooting is a good choice ;) I was trying for 2hours to get the subscriptions work, after rebooting all went fine ;)
