SSRS Parameters

I keep searching for the same default values for my date parameters.

Enough! Time for a list!

Yesterday 

=DateAdd("d",-1,Today())

Monday of last week

=DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today))

Friday of last week 

=DateAdd(DateInterval.Day, 6-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today))

First day of last month

=dateadd("m",-1,dateserial(year(Today),month(Today),1))

First day of this month

=dateadd("m",0,dateserial(year(Today),month(Today),1))

First day of next month

=dateadd("m",1,dateserial(year(Today),month(Today),1))

Last day of last month

=dateadd("m",0,dateserial(year(Today),month(Today),0))

Last day of this month

=dateadd("m",1,dateserial(year(Today),month(Today),0))

Last day of next month

=dateadd("m",2,dateserial(year(Today),month(Today),0))

One year ago

=DateAdd("yyyy", -1,Today())

First Day of this year

=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

More to come... 

Reporting Services Errors

Or: Why does it work on the source server, but give us errors on the client machine?

An error occurred during client rendering.
An error has occurred during report processing.
Cannot read the next data row for the dataset ThisDataSet.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Before I got as far as enabling remote errors, I tried running the report on the local server, where, of course, it was impossible to even navigate to the report as this error popped up:

User 'Domain\UserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed,

which was fixed by running Reports in Internet Explorer using Run As Administrator permissions, and then granting the appropriate access via the Security settings within SSRS (a little bizarre, I'm sure you'll agree, as if I could get to the report using the client machine, I should have been able to see it on the local server using exactly the same Windows login.

Then, however, the report ran perfectly happily. I poked about for the original error. I found an article telling me where to find the Report Server Log files, and looking in them, found this:

WARN: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet. ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.webserver!ReportServer_0-29!26e4!07/19/2011-13:05:43:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I looked at the dates on both versions of the report. They were both definitely dates. However. On the local server, the format was

m/dd/yyyy hh:mm:ss AM

and on the client machine it was

dd/mm/yyyy hh:mm:ss

Aha.

The date on the local server is in UTC, because that's what the third party supplier wanted. The date everywhere else is in local time. And, as a result, the date on the local server is in the format that the report expects, whereas the date on the client machine isn't...

And, since whomever wrote the report didn't think that around the world, people do use different date formats, and that reports might not be run on the local server, the whole thing came to a crashing halt.

The workaround, other than running the report on the server, is to manually change the date format to the one which works. But, my goodness, that's a faff: and what's wrong with having one of those little calendar thingies to click on?

Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

More Fun And Games with SCOM

For various reasons, chief among them the requirement for a more scalable deployment and the fact that the SCOM Server was "really starting to struggle" according to our Chief System Support Analyst (and, he should know: he's the one that's using the server all the time), we decided to shift the OperationsManager database, the OperationsManagerDW database and the ReportingServer databases onto a shiney new SQL Server. I am all about the shiney, after all. I am a girl geek.

Now, I thought it would be simple enough. Shift the OpsManager db. Shift the OpsManagerDW db. Shift the Report Server stuff. There are some nice articles on how to do this. Including a whole article about moving Report Server databases. Go and have a look at them.

Firstly, notice how the article about moving Report Server is almost identical to the one about moving OperationsManagerDW? That was the first gotcha. If you are moving the entire SQL Server, you need to follow the instructions in the article about moving in the document entitled 'How to Move the Operations Manager Reporting Server in Operations Manager 2007'. Ignore the one about 'How to Move the OperationsManagerDW Database in Operatios Manager 2007', other than the information about the Logins (step 9 onwards) and data sources (step 13 onwards).

Second gotcha? There is no sensible time to move SQL Server Reporting Services (SSRS) if you have custom reports. If you move it before the SCOM Report Server component, then there is the risk that installing SCOM's Report Server will wipe the custom reports. If you move it after the SCOM Report Server component, then there is the risk that you destroy the SCOM Report Server: it relies on a functional instance of SSRS.

The mistake I made was to move OperationsManagerDW according to that document's instructions, and then try to move the ReportServer having moved over the Report Server Databases part way through the process. This was silly: the ReportServer component did not install properly, it destroyed SSRS to the point that I had to reinstall it, and the IIS Application Pools refused to run:

Source W3SVC
Category (0)
Event 3221226474
Computer SQLSCOM

Message
Application pool 'ReportServer' is being automatically disabled due to a series of failures in the process(es) serving that application pool.

Source W3SVC
Category (0)
Event 2147484657
Computer SQLSCOM

Message
A process serving application pool 'ReportServer' terminated unexpectedly. The process id was '7468'. The process exit code was '0xffffffff'.

(there were several errors with the exit code '0xffffffff')

These linked to:
Error loading configuration file: Root element is missing.
w3wp!library!5!22/06/2010-09:20:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.

Report Server's Logs also gave me:
An internal error occurred on the report server. See the error log for more details., Incorrect security descriptor version;

and

w3wp!library!7!06/22/2010-11:01:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Unable to load assembly Microsoft.EnterpriseManagement.Reporting.Security;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EnterpriseManagement.Reporting.Security' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.EnterpriseManagement.Reporting.Security'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String name)


If I tried running the application pools under the Local\System Account, well, they didn't fail, but Reporting Services refused to function. I had service unavailable messages, I had unable to connect messages, and I had a lovely set of errors for which I was referred to the Installation Log File for SCOM Report Server, and to look for 'value 3'. I had error 108 all about me.


In other words, I had a partial installation of SCOM Report Server, and it destroyed SSRS. Nothing wanted to work.

I fixed this by:

Backing up everything I could think of.
Uninstalling and Reinstalling SSRS (I had tried to revert back to the original databases on the server, with the original .snk file - and every other .snk file I could find. No dice). With SP3 (as it was SQL 2005).
Using Add and Remove Programs to get rid of the SCOM Report Server, which also took out the Data Warehouse component, but not the Data Warehouse itself. Windows was under the impression SCOM Report Server was properly installed - trying to repair that particular component made no difference whatsoever.
Using the resetsrs.exe tool, as part of the SCOM installation disk to make sure that SSRS was working properly.
Keeping a close eye on IIS and Application Pools while installing.
Treble checking that SSRS was working: without SSRS working, SCOM Report Server won't install or work.
Stopping the SCOM Services.
Reinstalling SCOM Report Server, and only installing the Report Server element.

This worked: however, I'd lost my custom reports. I will, at some point, try shifting SSRS to another server to see what went wrong with moving SSRS - at the moment, I'm quite happy with the default reports. The custom reports can be recreated based on the screenshots we took of them as a backup before we failed to move SSRS. It wasn't the end of the world to lose them.

The final hiccup with the whole thing was the removal of SQL from the original SCOM server. This went without a hitch: until we got a whole batch of errors to inform us that the Scheduler Data Source Module Failed Initialization: The Microsoft Operations Manager Scheduler Data Source Module has some invalid configuration. The IIS Discovery Probe Module Failed Initialization: an Error initializing IISDiscoveryProbe from config. The answer, after a lot of swearing was here. Turns out that the MSXMLS parser got uninstalled with SQL: I had no idea that there was a dependency for that, and we simply reinstalled that. All was then well.

If I were going to do this again, I would make very sure that I could restore SSRS properly before I did anything else. I would also test moving everything in a VM, and test moving it in different orders, to work out how best to get SSRS and all custom reports across.