Tag Archives: Reporting Services

Cannot access reporting services (Denali CTP3)

This is a problem I’ve encountered many times. I do a fresh install of SQL Server, and when accessing Reporting Manager I get the error message:

“User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

This is caused by the UAC, and is usually fixed by running IE as an administrator. However, this didn’t work for me today on my fresh install of SQL Server Denali CTP3.

What DID work, was adding another user to the amdinistrators-group, and then running IE as administrator. The weird thing is that I didn’t need to run it in the context of the new user, and my user was a member of the administrators-group the whole time.

Datetime expressions in Reporting Services

Some nice to have date expressions in reporting services:

--First day of week (monday):
=DateAdd("d", -(WeekDay(Today(),2))+1, Today())
 
--Next monday:
=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())
 
--First day of month:
=DateAdd("d",1-DatePart("d",Today()),Today())
 
--First day of next month:
=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))
 
--First day of year:
=DateSerial(YEAR(Today()),1,1)
 
--First day of next year:
=DateSerial(YEAR(Today())+1,1,1)

Delete all Subscriptions in Reporting Services

Yesterday I spent some time trying to write a script that deletes all schedules, subscriptions and related SQL Server agent jobs from a reporting services instance by using cursors and stored procedures in the msdb and Reportserver databases.

Turns out that the reportserver tables have the necessary triggers on delete, so all you need to do is a simple

DELETE FROM ReportSchedule
GO
DELETE FROM Subscriptions
GO
DELETE FROM Schedule
GO

SSRS Subscription to file share on another domain

I spent a few days trying to get a subscription to a file share to another domain to work, before I finally figured out that it’s not possible at all. Tried both SSRS 2005 and SSRS 2008 to no avail.

Ended up with a workaround using a batch script that moves files from the file share on the local SSRS server to the external file share after the subscription is run.