How To Uninstall SQL Server 2008 Reporting Services

I was setting up a CRM development server with SQL 2008 and made the quick, uninformed, and ultimately time-consuming decision to install Reporting Services in SharePoint integrated mode. I was also going to install WSS 3.0, so I thought I'd try this out.

Unfortunately, I found out later that CRM 4.0 does not support reporting services in SharePoint integrated mode. So, if I had been thinking clearly, this would have been a not too difficult problem: just uninstall reporting services and reinstall it in the right mode.

My problem: I couldn't figure out for the life of me how to uninstall Reporting Services for SQL 2008. I tried everything I could think of to remove it. It wasn't listed separately under the Programs and Features list, and I couldn't find any other way despite hours (ugh!) of searching.

I finally figured out that if I went to the Add/Remove Programs area (Programs and Features in Server 2008), and started an uninstall of SQL Server 2008, I would get the option to select which feature of SQL I wanted to remove. Duh. I feel like an idiot.

Line Breaks for nvarchar TextArea fields in SQL Reports

This one had me stumped for a while. I was writing a report for a CRM client who had a number of nvarchar textarea fields on an entity in CRM. The fields had lots of free-form text with line breaks in them. For example:

This is line 1.
This is line 2.
This is line 3.

When I was creating the report in Visual Studio/SQL Reporting Services, the fields rendered correctly, but when I deployed it to CRM and ran the report, all the lines ran together, like this:

This is line 1. This is line 2. This is line 3.

I tried everything I could think of to format the field in the report layout, but nothing worked. After some googling around, I realized I needed to do some manipulation in my query. So I changed the relevant part of my select statement to something like this:

REPLACE(CAST(CRMAF_FilteredEntity.new_CustomField AS nvarchar(MAX)), CHAR(10),
CHAR(13) + CHAR(10)) AS CustomField

What this does is renders the field and replaces the line feed ("CHAR(10)") with both a Carriage Return and a Line Feed ("CHAR(13) + CHAR(10)"). And now my reports render correctly!

The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

In a recent upgrade environment, now running CRM 4.0, I found that reports stopped working from CRM. What was strange was that reports could still be run from the SQL Reporting Services website, just not from CRM - not even on the CRM server, so I knew it wasn't just a Kerberos authentication problem (and SQL was on the same box with CRM anyway!). After checking and re-checking all the settings in the SSRS website, the registry hive for CRM, and everywhere else I could think of, I opened a support ticket with Microsoft.

But whenever I have to ask for outside help, I feel like I need to re-double my efforts in locating the problem and fixing it myself. (I should probably just open tickets all the time!). After much searching, I found an obscure reference on a SQL forum about this error (The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.) and a mention of an update for SQL 2005. I logged onto the SQL server and ran Windows Update, and there it was - KB948109. Downloading this update and installing it fixed the problem. After the update, there was an error in the event log indicating that the .NET Framework 2.0 could not recompile, but upon running a report and waiting a long time for the app to compile, reports started working again throughout the network. Yay!

Here's a link to the update for more information:
http://support.microsoft.com/kb/948109

SQL Reporting Services Login Issue With CRM 3.0 - Another Fix

Ever since CRM 3.0 hit the market there have been issues with getting reports to work with new installations. Particularly troublesome is when the SQL Server is on a different server than CRM. There are a number of KB articles and documents that Microsoft has released pertaining to this problem. It often comes down to the now-infamous Kerberos double-hop: When a user requests a report through the CRM interface their credentials are passed (via Kerberos) to the SQL Reporting Services server. No problem up to that point. But then the SRS server requests the data from the SQL Server and may fail to pass the originating user's credentials to the SQL Server. This is the double-hop that the credentials need to make. The problem can be complicated by duplicate SPNs, trust for delegation not being correctly configured and many other factors depending on the network topology.

I thought I had seen them all, but an installation we did this week presented new challenges. Users were getting the dreaded error: "Cannot create a connection to data source 'CRM'. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I followed all the standard KB articles out there (links at the end of this post) to no avail. I am stubborn so it was a while before I gave up and got Microsoft involved. I'm glad I did. Their friendly engineer sent me a number of diagnostic tools and had me go through many steps to make sure everything was configured correctly. What finally got the problem resolved was the following:

Adding the CRM installation account credentials to the data source:
1) Open Report Manager (http://server/reports).
2) Click the Show Details button on the MSCRM_DataSource.
3) Click the Edit button for the DataSource.
4) Select the "Credentials stored securely in the report server" radio button and enter the credentials for the CRM installation account or a domain administrator.
5) Select the following two checkboxes:
- "Use Windows credentials when connecting to the data source" and
- "Impersonate authenticated user after a connection has been made to the data source."
6) Click "Apply" and attempt to run the reports as a user logged on to a client machine.

So, the lesson is, if you've tried everything, try something else. I don't recall ever having to set this before in numerous other CRM deployments, but in this case, this is what did the trick. Hope you find this helpful. If not, here are some other troubleshooting resources:

http://support.microsoft.com/kb/909509/en-us
Additional Setup Tasks Required if Reporting Services Is Installed on Different Server

Fix For Weird Error When Installing SQL Reporting Services 2000

We did an install of CRM Professional the other day where we got an error that SQL Reporting Services activation failed during the install. We ignored that error and went on with the installation, figuring we'd install SQL RS afterwards.

So when I went to install SQL RS (or SRS - which one is the preferred abbreviation?), since I was working remotely and the CRM install CD was still in the server, I navigated to the SRS folder inside the CRM installation directory and clicked setup.exe. This gave me a warning that the .NET framework 1.1 was not installed and that Visual Studio .NET 2003 was not installed.

Now I know that VS.NET wasn't installed - and wasn't needed, but the 1.1 framework WAS installed. In fact 1.1, 2.0 and 3.0 frameworks were all installed. (Hint) Though I've never had a problem running these frameworks side-by-side on a CRM server. CRM was working normally - and for a sanity check I went back into IIS to make sure. I also verified that the Default website was using 1.1. So why wasn't the SQL RS installation seeing this? If I ignored these warnings, the installation completed with no other errors, but the virtual directories and folders for SQL RS weren't created.

Fortunately, I found this post on Lance's Whiteboard: http://weblogs.asp.net/lhunt/archive/2004/04/05/107950.aspx which described the problem. To summarize:

There was a registry key that sets what version of the .NET framework new sites/applications will use in IIS by default. Even though you can go in and set the Default website to use an older version of the framework, when you install an application into that website, it might not like the registry key that says it should use the 2.0 framework by default.

The key is \\HKLM\SOFTWARE\Microsoft\ASP.NET\RootVer and you need to change the value to the correct version of 1.1.4322.xxx that you have installed. (To find out the exact version, you can go into C:\Windows\Microsoft.NET\Framework\v1.1.4322 and right click on the aspnet_wp.exe and choose Properties and look at the version.)

After a reboot, the SQL RS installation still threw a warning up that Visual Studio .NET 2003 was not installed - but ignoring that, that installation went through successfully. Had to then create the folder in the SQL Reports website for the CRM installation (matching the name to the Org name in Deployment Manager) and then use the publishreports.exe to get the canned reports into CRM.

Crystal Reports Tips - Changing WeekToDateFromSun For Mon-Sun Schedule

One of the things I like most about MS CRM is that it has challenged me to learn a lot of other skills and applications that I might not have learned otherwise. My entree into Microsoft CRM was due to my experience with the web, marketing, and business process improvement. Since diving in, however, I have become the jack of all trades (though master of none) in things like ASP.NET (I'm working to learn C#), SQL Administration, Outlook and IE support, and -- the topic of today's blog -- Crystal Reports.

I must say that I have enjoyed learning to use Crystal and milking the data out of CRM in all sorts of useful ways. I have a hard time sitting and reading how-to books, though I've done some of that with Crystal. I am most comfortable learning how to use applications by experimentation, and scouring the internet for tips and tricks.

So, here's a tip I would like to pass on to someone else who is scouring the internet to try to figure this out in Crystal.

CHANGING CRYSTAL'S WeekToDateFromSun TO FIT A MONDAY - SUNDAY SCHEDULE

I had to build a series of reports for a client of mine whose reporting week starts on Monday and runs through Sunday. If they want to look at a report for an activity and select the records for Week-To-Date, Crystal 9 has a nice built in way to do this. You simply click on Report > Select Expert and add a new selection criteria. You select the field you want to check against and then an operator like "is in the period" and choose the WeekToDateFromSun option. This is great. There is also a LastWeek option.

These work very well for standard Sunday through Saturday weeks. But the dilemma I faced was that my client's week is Monday through Sunday. So here's the code you need to select records based on this type of criteria. Again, in Crystal, click on Report, but this time click Selection Formulas > Record. In the window that opens up, add this code:

if DayOfWeek(currentDate) = 1
then
{activity.date} in
CurrentDate - Dayofweek(CurrentDate) - 5 to
CurrentDate - Dayofweek(CurrentDate) + 1

Else
{activity.date} in
CurrentDate - Dayofweek(CurrentDate)+ 2 to
CurrentDate - Dayofweek(CurrentDate) + 8

and

After the word "and" you can of course add any other selection criteria you desire, or remove the word altogether. Now, here's the code to view Last Week's activities, based on a Monday through Sunday work week:

if DayOfWeek(currentDate) = 1
then
{activity.date} in
CurrentDate - Dayofweek(CurrentDate) - 12 to
CurrentDate - Dayofweek(CurrentDate) - 6

Else
{activity.date} in
CurrentDate - Dayofweek(CurrentDate)- 5 to
CurrentDate - Dayofweek(CurrentDate) + 1

and

There you go. Hope you find this helpful in your next Microsoft CRM project -- or any project using Crystal Reports.