Creating a Reporting Service Report Template

I read this good, simple process for creating a template for your company reports that was worth sharing.

http://sqlblogcasts.com/blogs/testas/archive/2009/12/16/creating-a-report-template-in-ssrs-2000-2005-2008.aspx

Conditionally Blocking Scheduled Reporting

As is true for many DBAs, I have developed a custom set of SSIS packages along with a database that manages scheduled Reporting Services reports. It delivers to a mixture of email recipients and FTP locations after generating the output PDFs and Excel results.

Then came the inevitable weekend of trouble. The set of SQL Server 2005 databases, which provide the data for the reports, failed to load due to trouble with the source legacy systems. However, since the data in the SQL Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.

Therein lay the problem. I did not want any reports to generate, or worse yet email to recipients, if the recent day of data was not included.

The solution is fairly simple. The SSIS packages that load the SQL Server databases were logging to a table, and included a special status column. When each database was finished loading and had completed any subsequent updates required, a log entry with a status of DONE and UPDATED were entered. I just created a stored procedure to scan the log for databases that were scheduled to be loaded, filtered by UPDATED, and with a log time that fit into the correct date/time range for the current day's data load.

If the stored procedure found any records that did not meet the above criteria, it returned a result reporting accordingly. Then I just added a call to this stored procedure in my primary scheduled reporting stored procedure, where the list of scheduled reports to process is generated. If the data load check reported problems, I prevent the scheduled reports from processing. Then once the problem with the data load is resolved and all data is available, all I have to do it run the SQL Agent job that runs the scheduled reports and this time it will succeed.

I'm always a fan of a little more work in the back end to make a little less work for the routine. Not to mention, you don't have to answer Fred's email asking, "Why did I get two emails of the same report, and which one is right?"

Domain Move Part 2

After the first attempt at moving a SQL 2005 Server from one domain to another, I learned several lessons to help me make the permanent transition to the new domain. Here are a few of the key things I checked and/or changed.

- Verify that the new DNS server has entries for the short names of all servers that SQL Server needs to access. I found that several servers would resolve to [servername].[full domain name] but not to simply [servername]. I had to request CNAME entries for all the resources that required (due to extensive existing code) the ability to access the server through the short name.
- Make sure all services are running under local accounts.
- Make sure the Reporting Services execution account is running under a local account.
- If any objects are owned by domain level accounts, change ownership to local accounts.
- If any SQL Agent Jobs are scheduled to run under domain accounts, change them to local accounts.
- Check SSIS packages for any domain account ownership and change to local account.

I still had a few issues, but for the most part I was able to change one SQL 2005 Server from one domain to another and add a SQL 2005 and a SQL 2000 server to the domain from a workgroup with little trouble after checking the previous list and making changes as needed.

If you have any more tips, please feel free to leave comments to flesh out the topic.

SSIS Packages fail on ODBC connections after .NET Framework 3.5 SP1 or 2.0 SP2

If you work with ODBC connections through third-party ODBC drivers such as MySQL, you may have already experienced the pain of this Windows Critical Update. This is my tale of woe.

I was innocently applying critical Windows Updates to one of my SQL Servers, one is the .NET Framework 3.5 SP1. I read the details and saw that it also includes SP2 for both .NET 2.0 and 3.0, but still it is a critical update. As always, I there may be some bumps in the road but I figure I will just work through them. Updates applied. Basic database and reporting all test fine. So far so good.

The next morning, all of my SSIS packages that pull data through ODBC connections have failed, and as a result all of my subsequent scheduled reporting has also failed. That is what I call having Monday on Wednesday.

I know what happened due to my daily email report of all jobs from the last 24 hours, sorted by the errors first. In checking my SSIS log files, I find this error:

OnError,[Server],[UserAccount],[TableName],{GUID1},{GUID2},9/1/2009 11:15:48
PM,9/1/2009 11:15:48 PM,-1071607767,0x,SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source"
(31)" failed because error code 0x80131937 occurred, and the error row
disposition on "output column "[ColumnName]" (1848)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the
failure.

After some research, I find that this is a known issue on an msdn blog. It seems that .NET Framework 2.0 SP2 changes the way that the OdbcDataReader checks for Nulls, resulting in compatibility issues with some ODBC drivers. It impacted both the MySQL and CVIEW (for ISAM connections) ODBC drivers that I was using to pull in all my data.

As explained in the excellent blog post, the preferred option is to add error output to your SSIS packages, thereby handling the bogus errors. Unfortunately I have six or seven packages with twenty or more DataReaders in each package, so it would not be a quick fix. The blog mentions option #2 is to roll back your updates, which for me was the only option. I had production people waiting.

Due to the 3.5 constraints, I had to remove .Net 3.5 SP1, then .Net 3.0 SP2, and finally .Net 2.0 SP2. Then, because 3.0 and 2.0 updates are slipstreamed into the 3.5 SP1 update, at that point I had zero .Net frameworks installed. I had to reinstall .Net 2.0 and SP1, which is all I need for my SQL 2005 and related functionality. It took roughly 20-30 minutes to uninstall and reinstall along with reboots, not including any download requirements.

Afterward, I started playing with the website and realized the IIS default page was not even available. I looked at properties in the default website, looked at the ASP.NET tab... bingo, the ASP.NET version is set to... blank. I click the drop-down and see .Net 2.0 is available, but knowing that the scope of the settings goes far beyond this setting, I went back to the command line.

I knew I needed to run regiis, but I don't do this frequently so I searched, found a refresher, and ran the following command:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Aspnet_Regiis -i

Because I am on a 64-bit server, I was wroking in the Frameword64 subdirectory, but if you are on 32-bit, it is the same but you browse to the Framework subdirectory.

Now my baseline ASP.NET is configured in IIS. I browse to the "/reportserver" directory in IE8 and get a (403) Forbidden error. That's an improvement.

I looked around in various places at various settings for a bit, just trying to help me think. In the Reporting Services Configuration Manager, it hit me that I had just made a mess of IIS and in the process the virtual directories had been through the ringer. So under Report Server Virtual Directory and Report Manager Virtual Directory, I checked the "Apply default settings" checkbox and applied the changes.

Boom, back in business. Reports were up and running and I was able to run my SSIS package to generate all my scheduled reports. Then I remembered one other little ASP.NET application that I wrote for managing the report schedules. Tried to pull it up in the browser and it didn't open.

After looking at IIS settings for a while, I finally found the problem. So I right-click on the Virtual Directory, go to Properties-->Documents tab and default.aspx is not in the list. In the process of resets, apparently the default content page list had been reset on my virtual directory. I added default.aspx to the list and boom, everything is back up and running.

I hope that at some point Microsoft makes allowances for this issue in their .Net updates but in the meantime, doing without .Net 2.0 SP2 is fine by me.

First Attempt At Changing Domains on a SQL 2005 Server

Well I have finished my first pass at moving one of our three SQL Servers to our new domain. I had to roll it back for the moment, but I learned a great deal in the process. This server runs on Windows 2003 Server 64-bit and SQL 2005 Enterprise Edition 64-bit. This server also runs Reporting Services and Integration Services packages to run scheduled reporting, so there are a number of things to test.

1) I changed the DNS Server IP addresses in the network settings to point at the DNS server which is part of the new Active Directory.

2) I changed the domain from the current domain to the new domain and completed the required reboot.

3) I setup security groups from the new domain on the SQL Server and assigned them the appropriate permissions. By assigning the domain groups as logins, it simplifies adding and removing employee permissions by making it a simple Active Directory group addition or removal for the user account.

4) I could connect to the server from my local PC (which I had already moved to the domain) from Management Studio. That gave me comfort that basic name resolution and integrated security were working properly.

5) From an IE browser, I tried to pull up the reporting services browser. No luck. In researching it, I found that the encryption key was not working after the domain change. I also found that a domain account from the old domain was setup for "Execute as" permissions. I attempted to change this to a new domain account but it failed. Tried restoring the backup of the encryption key, failed. I tried creating a new encryption key, failed.

Basically everything I tried to get Reporting Services up and running under the new domain failed. I was able to join the old domain and reset Reporting Services back to a working state so I can do some more research on the issues I encountered. I am considering moving the server to workgroup security and getting everything working right first, and then joining the new domain. Until I do some more research I am not sure.

Based on month end and some other priorities, I probably will not get another attempt at this until the middle of September. If anyone reading this has some advice or some good articles/blog entries on the subject, I welcome them.

Preparing To Join A Domain and Change Domains

Currently I am managing three production SQL Servers:
SQL 2000 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Domain/AD security.

We just spent the weekend moving all the PC's to a brand new Domain controller with a new Active Directory. The servers are next.

I have been surprised at how little is on the web about joining an existing SQL Server installation to a domain, presumably because most people start on one or never move to one. There is a little more on changing domains, but it still seems a little sketchy.

So sometime in the next week, all three servers will be members of the new domain. These servers use IIS, heavy Reporting Services, heavy Integration Services, and extensive cross server connectivity.

Hopefully I will have a good report, but either way I will let you know the good, bad, and ugly.

Business Objects report scheduling

Part of our enterprise includes a Business Objects data warehouse and a host of Business Objects reports. I have mostly dealt with SQL Reporting Services or MS Access reports so I was excited recently to be asked to reschedule all the Business Objects reports.

Here are a few things I found noteworthy.

In addition to normal interval based report scheduling (i.e. daily, weekly, monthly, etc.) you can schedule based on a custom calendar. You create a named calendar and actually select the specific days that you want to schedule. It allows a high level of flexibility if your requirements are irregular.

The output options are pretty standard, PDF, Excel, and a web format, but you can easily use variable placeholders in your naming to customize the file name, email subject/message, etc. which simplifies and standardizes your file naming and/or emails.

One thing which is a problem with the scheduling, as with most out of the box solutions, is that you can not easily re-run a whole schedule day of reports. You must run the reports individually if there are problems. This is also true in SSRS reports which is why I wrote a database and SSIS packages to automate daily processing of scheduled reports. Maybe I'll blog about that another time, but it would take many posts to cover.

Overall, I am impressed with the Business Objects report scheduling ease of use and flexibility. Hopefully I'll get a shot at some report revision soon and I'll let you know how that compares to SSRS report design if I do.

www.jhughthomas.com,
www.facebook.com/jhughthomas

Fixing a Reporting Services Report that belongs to someone else

It is a common occurance in a shop with multiple report developers and without version control. Someone goes on vacation, you get a call that the report is giving an error, and then what? You don't have the local Visual Studio project or rdl file to edit. No Source Safe to pull the file from. What do you do?

Login to your reporting services host at http:\\[hostname]\Reports to get a list of folders and reports. Navigate the directory structure to find the offending report. Once you find it, you need to copy it to the local PC.

If the "Show Details" button appears on the right hand side of the sub-toolbar, click it. When it reads "Hide Details" you are ready to move on. Now click on the icon in the "Edit" column (just after the check-box). Now under the "Report Definition" section you will see an "Edit" link. When you click the "Edit" link, you will get a save dialog box and can save the report rdl file to your PC. My recomendation is to save it first to your desktop.

Then open SQL Server Business Intelligence Studio (i.e. Visual Studio) and either open a project that you use for researching that person's reports or start a new project to use for researching other people's reports. In the Solution Explorer, right-click on the Reports folder and select Add-->Existing Item...

You navigate to your Desktop (or wherever you saved the rdl) and select the rdl you just downloaded. Now it is a part of your project and you can look at data sources, evaluate issues, and exercise your genius to solve the problem.

And if the solution does not lie in the data sources, you can always setup the Target report folder, server, etc. to post changes to the actual rdl file. If you do this, I recommend holding onto that copy of the rdl that is still sitting on your desktop because it makes for a good backup in case you make things worse and need to back track.