Scheduling Reports

How are reporting services reports scheduled to run and email? I don't see
any links in the SQL Server Business Intelligence Development Studio. Are
there plug ins or something else from microsoft that could be missing? I have
SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
documentation?Reports are scheduled through Report Manager... Go to
http://<yourserver>/reports
Then select a report.
You should see a subscriptions tab, where you can schedule.
To schedule a report, the data source MUST use Credentials Stored Securely
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"dbach" wrote:
> How are reporting services reports scheduled to run and email? I don't see
> any links in the SQL Server Business Intelligence Development Studio. Are
> there plug ins or something else from microsoft that could be missing? I have
> SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
> documentation?
>

Scheduling Hourly Report between predefined hours

Hi everyone,

I'm using 2005 Reporting Services and trying to schedule an hourly report to be sent out between 8:00am and 8:pm. Am I doing something wrong or does SSRS really not support end time? Do I have to create 12 seperate jobs?

Any help would greatly be appreciated.

Thanks,

- gshaf

The RS scheduling does not support this. You can work around this, but it does have consequences. If you create a schedule you can then go into SQL Agent and modify the corresponding schedule to fire when you would like. The problem is that RS will reset the schedule every 12 hours. :) To stop RS from doing this, go into RSReportServer.config file and set IsSchedulingService to false. By doing this RS will not verify that the schedule data it has matches what SQL Agent has. If you view the schedule via Report Manager it will show the old schedule, not the modified SQL Agent schedule, and of course if you change it via Report Manager it will modify the SQL Agent job as well.

I hope that helps.

|||

Thanks very much... perhaps it's worth a shot.

- gshaf

Scheduling Data Transformation Services?

Hi,
Our vendor created a series of dts local packages and I'd like to have them
run each night. When I right-click one of the packages and select Schedule
Package it comes up with a default schedule. So it looks like it is already
schedule, which is fine but how do I know if the package was run? Is this
activity tracked in a log somewhere? When I look under Package Log I see
it's empty. Any suggestions?
Thanks in advance,
LinnIn enterprize manager, go to
Management / SQL Server Agent / Jobs
From there you can view job history and edit the job itself.. as well as the
schedule.
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:uBL6RelMGHA.3432@.tk2msftngp13.phx.gbl...
> Hi,
> Our vendor created a series of dts local packages and I'd like to have
> them run each night. When I right-click one of the packages and select
> Schedule Package it comes up with a default schedule. So it looks like it
> is already schedule, which is fine but how do I know if the package was
> run? Is this activity tracked in a log somewhere? When I look under
> Package Log I see it's empty. Any suggestions?
> Thanks in advance,
> Linn
>|||First..I assume you are using ss2k.
If you open up the DTS package, and click Package, Proprties..and the
Logging tab, you will see in the bottom how to create an error file.
You could also set up a task to email yourself when the task completes
successfully(of course for this to work your server must be configured
to use Exchange.)
MJKulangara
http://sqladventures.blogspot.com

Scheduling a legacy DTS package

I have a legacy DTS package on my test SQL Server 2005 in the Management\Legacy\Data Transformation Services\ folder. I can run the package, but how can I schedule it? this doesn't appear to be an option anymore like it was in 2000.

Thanks

Use the command-line option in SQL Agent and use DTSRun.

[I am assuming at this point that DTSRun is still distributed. Apologies if not]

-Jamie

|||

it is but it's called dtexec now, still doesn't work mind,

if I save the package as a .dts files and try to run it with

C:\>dtexec /f "c:\poval.dts"

I get:

--

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 14:32:26
Error: 2006-03-24 14:32:26.95
Code: 0xC0011007
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2006-03-24 14:32:26.95
Code: 0xC0011002
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Description: Failed to open package file "c:\poval.dts" due to error 0xC00CE5
08 "An invalid character was found in text content.". This happens when loading
a package and the file cannot be opened or loaded correctly into the XML docume
nt. This can be the result of either providing an incorrect file name was specif
ied when calling LoadPackage or the XML file was specified and has an incorrect
format.
End Error
Could not load package "c:\poval.dts" because of error 0xC0011002.
Description: Failed to open package file "c:\poval.dts" due to error 0xC00CE508
"An invalid character was found in text content.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document.
This can be the result of either providing an incorrect file name was specified
when calling LoadPackage or the XML file was specified and has an incorrect for
mat.
Source: {F6D6F0F3-30A1-40F6-94B5-D14419672776}
Started: 14:32:26
Finished: 14:32:27
Elapsed: 0.218 seconds

-

This, I suspect, is because there are invalid characters somewhere in the package, although it's odd that it runs fine when I run it from the DTS designer itself? Therefore I must apologise to Microsoft for not having the foresight not to include any characters that may one day be invalid when I wrote the DTS package about 4 years ago....

and If I attempt to run it directly from the server using

C:\>dtexec /SQL POVal

I get:-

-

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 14:39:16
Could not load package "POVal" because of error 0xC001000A.
Description: The specified package could not be loaded from the SQL Server datab
ase.
Source:
Started: 14:39:16
Finished: 14:39:17
Elapsed: 0.484 seconds

Am I to assume from this that the legacy packages aren't stored in msdb , Can anyone tell me where my package is then!?

I've had enough today

|||

You cannot run a DTS2000 package with DTExec.exe. You need DTSRun.exe.

-Jamie

|||d'oh, cheers

Scheduling

Hi there
I have uploaded some reports to our reporting services server, however I
want to be able to have them emailed to clients outside the network at the
end of each week,
Can you explain how to do this?
Regards
TimYou can set up a subscription via the subscription tab in Report Manager.
If you are using the Enterprise edition of Report Server you will want to
take a look into Data Driven Subscriptions.
You can find more information here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
Here is information of Data Driven Subscriptions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"catalyst" <catalyst@.discussions.microsoft.com> wrote in message
news:E5DFB9D6-5DD1-4F51-9A2A-E405BD343D84@.microsoft.com...
> Hi there
> I have uploaded some reports to our reporting services server, however I
> want to be able to have them emailed to clients outside the network at the
> end of each week,
> Can you explain how to do this?
> Regards
> Tim

schedules web page problem

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 ;)

Scheduled subscriptions don't work (but Event-driven ones do) for NS Newsletter sample

Hello,

I am using the Newsletter sample bundled with Notification Services (using SQL 2005 SP1)

When I create Scheduled subscriptions using the 'SubscriptionManagementWeb' app that ships with the sample, then copy an Events.xml file to the watch folder, I never get an alert (i.e. I set the time to now + 5 minutes, wait, never receive).

When I create an Event-driven subscription, on the other hand, and copy an Events.xml file to the watch folder, I do successfully get a notification.

I also created my own separate NS application, and it has the same problem as the sample. Event-driven subscriptions work, but Scheduled ones do not.

Any ideas?

TIA,

Alex

Hi I don't know if you have checked this in the books online but it has somethig to do with schedulled subscriptions:

Time Zone-Specific Scheduled Subscriptions

Notification Services supports scheduled subscriptions. Because a notification application can support subscribers in any time zone, it is important that Notification Services store information about the time zone for timely notification delivery.

To make multiple time zone support easier, Notification Services stores date and time information using Coordinated Universal Time (UTC) notation.

When a subscriber creates a scheduled subscription, the subscriber enters the time zone (or the time zone is obtained from other sources, such as a database or operating system setting) and the desired delivery time. When Notification Services stores the delivery time, it uses the time zone to compute the UTC time for delivery and then stores the UTC time. When the UTC time arrives, Notification Services generates notifications from any current events and then formats and distributes the notifications.

|||

Interesting. Well I am UTC - 8 hrs (same as GMT - 8 hrs except for rare circumstances). I launched the "SubscriptionManagementWeb" web application that comes with the Newsletter sample in debug mode, and saw that it was saving a subscription with:

Schedule start: "TZID=4:20070131T025500"

Schedule recurrence: "FREQ=WEEKLY;BYDAY=WE"

Even if the time zone were off, it should be triggered on a weekly basis. I've submitted test newsletters that match the scheduled subscription over a span of two weeks, never received a notification.

To test the UTC theory out, I set the schedule start to Now + 9 hours (since UTC is 8 hours ahead of my time zone). This way, I should expect to get the event in Now + 1 hour.

Result: didn't get the event, when I copied the event.xml file to the drop location. So I don't think it's time zone based.

Any other suggestions? Don't see any errors in the event log; don't see anything useful from running the Diagnostics sp's that it generated.

|||Hi Alex -

When working with scheduled subscriptions, I usually find that the problems can generally be found in one of four areas.

1) A scheduled subscription either does not exist or is incorrectly configured. You'd be surprised at how often this is the problem.

2) The scheduled subscription doesn't actually match an event that is coming in.

3) There is a problem in the matching rule so no rows are ever produced.

4) Notifications are created in the notifications table, but cannot be delivered due to a problem in the delivery protocol (incorrect permissions, etc).

You may want to look into the underlying tables and views to see if you can gain some more insight into why you're not seeing the final notification. Also change the logging level to verbose and check the Application event log.

HTH...

Joe

Scheduled subscriptions don't work (but Event-driven ones do) for NS Newsletter sample

Hello,

I am using the Newsletter sample bundled with Notification Services (using SQL 2005 SP1)

When I create Scheduled subscriptions using the 'SubscriptionManagementWeb' app that ships with the sample, then copy an Events.xml file to the watch folder, I never get an alert (i.e. I set the time to now + 5 minutes, wait, never receive).

When I create an Event-driven subscription, on the other hand, and copy an Events.xml file to the watch folder, I do successfully get a notification.

I also created my own separate NS application, and it has the same problem as the sample. Event-driven subscriptions work, but Scheduled ones do not.

Any ideas?

TIA,

Alex

Hi I don't know if you have checked this in the books online but it has somethig to do with schedulled subscriptions:

Time Zone-Specific Scheduled Subscriptions

Notification Services supports scheduled subscriptions. Because a notification application can support subscribers in any time zone, it is important that Notification Services store information about the time zone for timely notification delivery.

To make multiple time zone support easier, Notification Services stores date and time information using Coordinated Universal Time (UTC) notation.

When a subscriber creates a scheduled subscription, the subscriber enters the time zone (or the time zone is obtained from other sources, such as a database or operating system setting) and the desired delivery time. When Notification Services stores the delivery time, it uses the time zone to compute the UTC time for delivery and then stores the UTC time. When the UTC time arrives, Notification Services generates notifications from any current events and then formats and distributes the notifications.

|||

Interesting. Well I am UTC - 8 hrs (same as GMT - 8 hrs except for rare circumstances). I launched the "SubscriptionManagementWeb" web application that comes with the Newsletter sample in debug mode, and saw that it was saving a subscription with:

Schedule start: "TZID=4:20070131T025500"

Schedule recurrence: "FREQ=WEEKLY;BYDAY=WE"

Even if the time zone were off, it should be triggered on a weekly basis. I've submitted test newsletters that match the scheduled subscription over a span of two weeks, never received a notification.

To test the UTC theory out, I set the schedule start to Now + 9 hours (since UTC is 8 hours ahead of my time zone). This way, I should expect to get the event in Now + 1 hour.

Result: didn't get the event, when I copied the event.xml file to the drop location. So I don't think it's time zone based.

Any other suggestions? Don't see any errors in the event log; don't see anything useful from running the Diagnostics sp's that it generated.

|||Hi Alex -

When working with scheduled subscriptions, I usually find that the problems can generally be found in one of four areas.

1) A scheduled subscription either does not exist or is incorrectly configured. You'd be surprised at how often this is the problem.

2) The scheduled subscription doesn't actually match an event that is coming in.

3) There is a problem in the matching rule so no rows are ever produced.

4) Notifications are created in the notifications table, but cannot be delivered due to a problem in the delivery protocol (incorrect permissions, etc).

You may want to look into the underlying tables and views to see if you can gain some more insight into why you're not seeing the final notification. Also change the logging level to verbose and check the Application event log.

HTH...

Joe

Scheduled Notifications

Hi , I'm having one code for Scheduled Notification services, it's running without errors but there is no output

<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>ScheduledSubscriptions</SubscriptionClassName>
<Schema>
<Field>
<FieldName>DeviceName</FieldName>
<FieldType>nvarchar(255)</FieldType>
</Field>
<Field>
<FieldName>SubscriberLocale</FieldName>
<FieldType>nvarchar(5)</FieldType>
</Field>
<Field>
<FieldName>Prescriptionid</FieldName>
<FieldType>int</FieldType>
</Field>
<Field>
<FieldName>status</FieldName>
<FieldType>nvarchar(50)</FieldType>
</Field>
</Schema>
<FileGroup>DEFAULT</FileGroup>
<ScheduledRules>
<ScheduledRule>
<RuleName>ScheduledSubscriptionsRule</RuleName>
<Action>

INSERT INTO SchedPrescriptionNotifications
( SubscriberId,DeviceName,SubscriberLocale,Prescriptionid,status )
SELECT S.SubscriberId, S.DeviceName,
S.SubscriberLocale, C.Prescriptionid, C.status
FROM SchedPrescriptionSubscriptions S , ScheduledEventsChron C
WHERE C.Prescriptionid= S.Prescriptionid
AND C.status = S.status

INSERT SchedSubscriptionChron
(SubscriberId, Prescriptionid, status)
SELECT S.SubscriberId, S.Prescriptionid, C.status
FROM SchedPrescriptionSubscriptions S JOIN ScheduledEventsChron C
ON S.Prescriptionid= C.Prescriptionid
</Action>
<ActionTimeout>PT30M</ActionTimeout>
</ScheduledRule>
</ScheduledRules>
</SubscriptionClass>
</SubscriptionClasses>
and

my C#.net coding is

if (nsSubscription.HasTimedRule)

{

nsSubscription.ScheduleRecurrence = "FREQ=DAILY;";

nsSubscription.ScheduleStart="20070326T072720";

}

i need to know whther the format of Schedulestart property is correct...

Hi , I'm having one code for Scheduled Notification services, it's running without errors but i don't get any output

<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>ScheduledSubscriptions</SubscriptionClassName>
<Schema>
<Field>
<FieldName>DeviceName</FieldName>
<FieldType>nvarchar(255)</FieldType>
</Field>
<Field>
<FieldName>SubscriberLocale</FieldName>
<FieldType>nvarchar(5)</FieldType>
</Field>
<Field>
<FieldName>Prescriptionid</FieldName>
<FieldType>int</FieldType>
</Field>
<Field>
<FieldName>status</FieldName>
<FieldType>nvarchar(50)</FieldType>
</Field>
</Schema>
<FileGroup>DEFAULT</FileGroup>
<ScheduledRules>
<ScheduledRule>
<RuleName>ScheduledSubscriptionsRule</RuleName>
<Action>

INSERT INTO SchedPrescriptionNotifications
( SubscriberId,DeviceName,SubscriberLocale,Prescriptionid,status )
SELECT S.SubscriberId, S.DeviceName,
S.SubscriberLocale, C.Prescriptionid, C.status
FROM SchedPrescriptionSubscriptions S , ScheduledEventsChron C
WHERE C.Prescriptionid= S.Prescriptionid
AND C.status = S.status

INSERT SchedSubscriptionChron
(SubscriberId, Prescriptionid, status)
SELECT S.SubscriberId, S.Prescriptionid, C.status
FROM SchedPrescriptionSubscriptions S JOIN ScheduledEventsChron C
ON S.Prescriptionid= C.Prescriptionid
</Action>
<ActionTimeout>PT30M</ActionTimeout>
</ScheduledRule>
</ScheduledRules>
</SubscriptionClass>
</SubscriptionClasses>
and

my C#.net coding is

if (nsSubscription.HasTimedRule)

{

nsSubscription.ScheduleRecurrence = "FREQ=DAILY;";

nsSubscription.ScheduleStart="20070326T072720";

}

i need to know whther the format of ScheduleStart property is correct...

|||Hi Subathra -

Here's a link that may help. Look at the timezone component.

http://msdn2.microsoft.com/en-us/library/ms171385.aspx

I'm not sure I follow the match rule logic so I cannot comment on that part. You can do more investigation by looking into the underlying tables to see if notifications were actually produced but just not delivered.

HTH...

Joe

Schedule your own reports

Can i in reporting services 200 create some application that loops through and generates the reporting services report and email to the recepiant I specify in the mini application. I do not want to schedule the job in reporting services as the person receiving the email will be different each time based on criteria. If anyone knows the syntax to generate a report and pass the parameters, email and format it would be much appreciated

Lee,

You can using the Reporting Service Web API or you can write .NET code to generate our report, connect with System.web.mail. to email it, and you can use the render of the response to give for different type whether it's HTML, EXCEL and etc...

Here's a URL to get you going:

http://www.codeproject.com/sqlrs/SQLRSViewer.asp

schedule export of report

What is the syntax (or process) to schedule a reporting services report to be
exported as and Excel spreadsheet and then emailed to recepients?
Are there any examples that I could refer to?
I would like to write some kind of script and schedule this for every day.its the UNC mapping, you dont use the drive letter. Example:
\\server\folder1\folder2\folder3\application
"john d" <johnd@.discussions.microsoft.com> wrote in message
news:9E9BAAB5-CD24-4CCC-A892-A424BBCA498F@.microsoft.com...
> What is the syntax (or process) to schedule a reporting services report to
> be
> exported as and Excel spreadsheet and then emailed to recepients?
> Are there any examples that I could refer to?
> I would like to write some kind of script and schedule this for every day.|||nevermind, I misread your post
"john d" <johnd@.discussions.microsoft.com> wrote in message
news:9E9BAAB5-CD24-4CCC-A892-A424BBCA498F@.microsoft.com...
> What is the syntax (or process) to schedule a reporting services report to
> be
> exported as and Excel spreadsheet and then emailed to recepients?
> Are there any examples that I could refer to?
> I would like to write some kind of script and schedule this for every day.

Schedule and Delivery Processor Error

I am having issues with getting our Development Report Services Server to
schedule any reports to run. Recently, I had to change our service account
to a non-expiring one and now I get the following in our Event Log.
Schedule and Delivery Processor
Failed to decrypt symmetric key. Most likely cause is the serivce username
has changed.
Our setup is as follows:
W2K3 Web Server (running Reporting Services) and a seperate SQL 2000
Enterprise Server housing the Reporting Services database. I have run the
rsconfig and the rsactivate after changing the service account which
completed successfully.
Does anyone have any suggestions, short of totally reinstalling, to get this
error corrected and the scheduler working?
Thanks,
Greg QuinnI answered my own question. By running the rskeymgmt utility with the -d
flag I was able to delete all encrypted information from the reporting
services database and then ran the rsactivate and then reapplied the
datasources, parameters and schedules. Now everything appears to be working
correctly.
"Gregory C. Quinn, Jr." <gquinn@.yadtel.net> wrote in message
news:11vpjatmrfu45d2@.corp.supernews.com...
>I am having issues with getting our Development Report Services Server to
>schedule any reports to run. Recently, I had to change our service account
>to a non-expiring one and now I get the following in our Event Log.
> Schedule and Delivery Processor
> Failed to decrypt symmetric key. Most likely cause is the serivce username
> has changed.
> Our setup is as follows:
> W2K3 Web Server (running Reporting Services) and a seperate SQL 2000
> Enterprise Server housing the Reporting Services database. I have run the
> rsconfig and the rsactivate after changing the service account which
> completed successfully.
> Does anyone have any suggestions, short of totally reinstalling, to get
> this error corrected and the scheduler working?
> Thanks,
> Greg Quinn
>