Alex Duggleby’s Palace of Words

Some facts:

• SQL Server Reporting Services were great in the past. SSRS 2008 are better.
• MSDN is great. MSDN has lots of details. Sometimes it’s too much if you are under pressure.
• Google usually gets me the information I want faster. Especially when I need to get something done quickly.

All in all this post is for those of you that need to learn how to use SQL Server Reporting Services fast or need to solve the above mentioned problem fast, without knowing too much about the details. I read a lot of and like these introductory posts and so now I’m writing another one, here goes.

I assume you have SQL Server Reporting Services already setup (by your grand one-and-only unmissable sys-admins or by yourself, but in the latter case you probably are not the target audience for this) and you have access to the all required tools. Talking about tools, there are three you should know of:

- RSConfigTool - (Start menu > SQL Server 2008 > Configuration > Reporting Services Configuration): What do I need it for: Troubleshooting, Finding Urls, Configuration Report Server. Having problems trying to connect. See Additional Tip #1.

- Report Manager - (http://<yourserver>/reports): What do I need it for: Managing your reports (you won’t actually see anything on a clean install yet)

- Business Intelligence Studio - (Start menu > SQL Server 2008 > SQL Server Business Intelligence Development Studio): What do I need it for: Creating and editing your reports

and of course you will need SQL Server Management Studio for managing the data the report is based upon.

(side note: This tutorial is based on the 2008 version, can’t say how many differences there are between this and the older versions.)

1. Create your views

There are ways to aggregate your data in the report itself, but it’s easier and cleaner to use SQL views to provide the data for the report and use Reporting Services only for the report. So go to SSMS and create your view.

2. Create a new BIDS (Business Intelligence Development Studio) solution based on the “Report Server Project” Template. Add a new report by clicking on the Report node in the Solution Explorer. Next we need to connect to a data source. Give it a name, click edit and connect to your database.

3. Click next and the Query Designer appears. Here you enter the T-SQL statement for your data. Ideally it’s just a “SELECT * FROM yourView” (or select each column distinctly). You can test it using ‘Run Query’. You can also choose to use data from a stored procedure if you like, just select the appropriate option.

4. Design your report. You can add elements to the report surface, by right clicking on the center surface and select them from the insert menu. The most important are Header, Footer and Table. In the Header and Footer you can add Textboxes with text or by right-clicking on them and choosing expressoin you can choose some variable input. For example page number, report name etc. In the table you can drag columns from the “Report Data” (on the left showing the data source you just added). When you are finished, you might have something like this. Click preview to get a glimpse at what your boss might get.

5. Now let’s deploy it to the report server. First we must set the report server url. Right-click on the solution in the Solution Explorer and click on Properties. You will see the following dialog. Change the TargetReportFolder value to something you wish and provide the TargetServerURL as http://yourserver/reportserver (not just /report!). Press save. Now right-click on the solution again and select Deploy.

6. When it finishes open the url http://yourserver/report (not /reportserver!). You will see something like this. Go ahead and click on Test.Reports (or whatever you specified as TargetReportFolder) and then click on your test report. You will be greeted with the web version of your report.

7. Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.

8. Now you can go to Subscriptions. Click on email. Don’t have email as an option? See Additional Tip #2. Enter the recipient information as you wish and select a schedule for the sending. If you press save and encounter an error that ‘SQL Server Agent is not running’ either figure it out yourself what the problem could be or read Additional Tip #3 :). Once you have finished, lean back and let SSRS make your boss happy and in turn make you happy.

So we have seen the absolute basics of report creation. A quick and dirty introduction. This should cover your first encounters with SSRS. Hope it helps someone!

Stop reading here unless you are Troubleshooting…

Additional Tip #1:

I try connecting with SSMS (Management Studio) and it won’t connect. Maybe you renamed the server? Open RSConfigTool, go to Database > Change Database > Choose an existing report server database. Just choose the existing one, and let it run through it’s config. That should repair everything and you should be able to connect using SSMS again.

Additional Tip #2:

You don’t have email in the options for delivery in the subscriptions dialog? Probably it’s not configured. Thankfully that is very easy. You start the RSConfigTool. Connect and go to the Email tab. There you just specify a sender address and the SMTP server you would like to use. Click apply and voila. The server will automatically restart and the now the email option appears.

Additional Tip #3:

Saving the subscription results in an error message saying SQL Server Agent is not running. This is a DB or sysadmin issue, but just fire up SQL Server Configuration Manager (in the Start Menu under SQL Server 2008 > Configuration) then click on SQL Server Services, right-click on the SQL Server Agent > Properties. Go to the service tab, change Start Mode to Automatic and press OK. On the next reboot it will start automatically, but you still need to start it once for now. Right-click on the ‘SQL Server Agent’ and press Start.

So after Paris it’s back to work again and this week I need to prep a VM for a customer project. I already received a clean Windows 2003 Machine and on Monday I installed SQL Server 2005 before I went. I should have seen this coming, but anyhow today I wanted to install a Mini-SAP 4.6D.

MiniSAP (now called MiniWAS) is available for Linux as a download but the Windows version is only distributed in the shop or as part of the ABAP Objects book (I hope the program manager who made that decision never crosses my way). Anyhow, I got the CDs somehow (don’t ask) and looked at the Documentation.doc file. (Actually I of course immediatelly clicked on setup.bat and bang it crashed, but doesn’t it sound calmer when I say I looked at the documentation first?).

Seems that I have versions of the CD that contain a production bug, so after doing some manually editing in the install files (as described in the documentation) I was ready to go. It tries to install MSDE on the machine, but that fails with an (’Instance name not valid’) but I have a big SQL Server 2005 running on the machine, so I just commented out the MSDE install line in the setup.bat and it continued.

The first error I got was a collation problem. Having installed SQL Server 2005 with the default collation SAP expected it to be SQL_Latin1_General_Cp850_BIN.

Thankfully it was a clean sql server so I only needed to mount my SQL Server DVD again and run:

setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=SQL_Latin1_General_Cp850_BIN

(you can specify SAPWD if you are using mixed and not Windows Authentication). You will be prompted:
You have chosen to re-install, and therefore overwrite the system databases. Are you sure you want to proceed? Yes! (Don’t do this on a live production server. Ever.)

Started setup again, and next I was presented with an error for a registry key it couldn’t find in ‘Software\Microsoft\MSSQLServer\MSSQLServer\Parameters’. I found the MSSQLServer node but had to manually create the Parameters node. And restarted setup.bat and this time it finished without problem! Hurray.

Next up was the GUI which the original setup tried installing but couldn’t locate the setup file. It’s in the MINIGUI folder. Once that is installed, just double click the ‘Start R3 MBS’ on the desktop, then fire up the GUI and follow the rest of the instructions in the documentation. Don’t forget to install the latest Kernel Patches from the SAP Site. Voila. Hope it helps.

This is the kind of the thing you don’t want to see when you check out a project from SVN that should be compiling. It took a moment of thinking and a bit of reflectoring to find that the method was internal in the dll I was using:

The TriggerCollection is part of the table object in SQL Management Objects. I had the identical project compiling on a different PC. The funny thing was that the versions of the dll were identical. Only in reflector did I see a different version Guid:

// Assembly Microsoft.SqlServer.Smo, Version 9.0.242.0
Location: c:\Programme\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Name: Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Type: Library

// Module Microsoft.SqlServer.Smo.dll
Version: b74f0ed4-1269-420b-b67e-8bbae0c9c035
Location: c:\Programme\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Size: 1555232 Bytes

This led me to think about the service pack level installed and I noticed you need Service Pack 2 to be able to use the Add method publically. (At least it wasn’t the other way round, they could had made it internal in the newer service pack *G*. Thankfully not.) Case solved, back to getting through this pile of work…

I was preparing some demos for the upcoming Academic Community Launch Tour that we - a bunch of Microsoft Student Partners in Austria - are organizing (see http://anuga.at for details). I had just finished a wonderful little app using SQL Compact Edition, Sync Framework, ASP.net MVC and all the funky stuff. Just before I wanted to call it a night I noticed the sync portion of my app suddenly wasn’t working anymore. Somehow I had done something bad and the sync framework wasn’t finding any rows that had changed. This morning I got up and took a closer look underneath the covers of SqlCeClientSyncProvider. This is what I found out.

A word of warning: The information here only applies to Sync Services for ADO.net and is based on Version 2.0 with some help of Reflector. Use the information and my interpretation with caution. Especially beware of the fact that Sync Services for ADO.net in the Sync Framework are handled completely differently than Sync Services for File Systems and Custom Sync Providers.

The sync framework uses anchors to determine what rows have changed. These anchors are compared to the metadata in the table. When creating the tables on the server you probably inserted columns such as InsertTimestamp or UpdateTimestamp. That’s the metadata. Fine, but what happens on the client side in a SQLce database? At first I thought the metadata columns are transferred to the client in the schema (remember the SqlCeClientSyncProvider retrieves the schema to create on the table from the server) and then used for the change tracking there too.

Well not quite. In the app I’m working on I created the SQLce database first (and used used SQLMetal to generate my Linq2Sql classes by the way which is really easy. I’ll do another post in a moment). SQLce doesn’t support triggers so I only created a timestamp column called ‘TS’. When it came to sync’ing the client with a server database, that looked similar but had more metadata columns, I noticed I had no idea how the metadata part worked on the client. (It’s actually quite nice too know that for standard use cases it just works out of the book.)

Enough bla, let’s get to the details. Thanks to a couple of hours with reflector and running queries against my SQLce database I found out where the metadata goes.

Preparing my custom tables for the meta data

When you configure your SqlCeClientSyncProvider you add SyncTables with a couple of options:

foreach (SyncTable _syncTable in syncTables)
{
_syncTable.CreationOption = TableCreationOption.UploadExistingOrCreateNewTable;
_syncTable.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;
m_clientProv.Configuration.SyncTables.Add(_syncTable);
//
// This would recreate the schema for each table on each sync, which resets the timestamps to 0.
// m_clientProv.CreateSchema(_syncTable, null);
}

You can see a line at the bottom that I commented out. I figured that since my client database was in place and I specified UploadExistingOrCreateNewTable that my client database needed to be adapted to include the sync metadata. So I called CreateSchema on the provider which does just that. But you don’t want to create the schema every time, so I should actually move the call into my database initialization code, but I didn’t want to mix my sync code with my DAL code and I didn’t need to. CreateSchema is called at the beginning of the sync session only if the metadata is not found in the table.

Once that method has run all sync tables now have two extra columns: __sysChangeTxBsn and __sysInsertTxBsn. These are initialized with 0 and contain a binary timestamp.

CreateSchema also creates some more tables. First of all it creates a sync subscription table called ‘__sysSyncSubscriptions’. This contains the client’s id and it’s pairing with any server sync providers. Next up is an article table called ‘__sysSyncArticles’. An article is just a sync table, so it contains the table name, the last sent and last received anchors for a specific table.

Once it has done that it just switched on the built-in SQLce change tracking which in turn updates the metadata columns when ever a change occurs.

But wait there is one thing missing. What happens to deleted rows? So far we don’t have any tombstone or archive tables. Actually we do, a table called ‘__sysOcsDeletedRows’ was created that is used by the SQLce change tracking to store deleted rows. It has columns ‘__sysTName’ (the table name), ‘__sysRK’ (the reference key = primary key that was deleted) and the timestamp value when it was deleted and inserted.

What a strange combination of problems I had this morning.

1. First of all my Firefox installation stopped displaying anything in the Download window. It was just empty, showed me the “Clean-Up” button and was telling me that “Downloads are saved to” <nothing>. I seem to remember to have changed the default download directory yesterday, but I hadn’t noticed anything else, but it seemed to have triggered this strange effect.

Following the information on the Mozilla KB (which I later found out is actually related to the Download Actions window and not the Download window) didn’t help much. The problem was related to my profile (un- and reinstall and settings reset on Firefox didn’t help) so I deleted my old profile and created a new one. Being a Foxmarks user it didn’t hurt at all. I use almost all default settings on Firefox and my bookmarks were just sync’ed right back into my new profile. Voila… but there was more to come.

2. Fired up Outlook and was waiting for it to sync, but it just kept saying ‘Last updated <yesterday>’ and refused to download new items from Exchange. A little search revealed this community thread where I found a solution. Simply renaming the .ost (or deleting it for those of you who are brave) made Outlook resync from the start and now it’s happily syncing again.

Ok, so the problems were not so challenging, but it did make me think about why I like syncing up things to central servers. If either my bookmarks or my emails had only been available on my local machine (apart from not being able to retrieve them from anywhere else and having to manually do some syncing) I would have probably had had to invest a lot more effort into getting my data back.

This is one reason I’ve already started to switch to uploading at least a copy of or the actual document itself to my Windows Sharepoint Services running on a rented virtual machine and being backed up using MozyPro. I have access everywhere and automatic 24h backup.

<ad>I might talk a bit about this setup (and especially MozyPro) in the future, but for now here’s a little free ad: for those of you in germany, austria or nearby I can only recommend taking a look at QualityHosting. I had quite some trouble with Windows VMs from other providers until I moved there. Great quality and the guys there have been such a tremendous help with a major problem I had run into a couple of weeks ago. They have everything from basic packages (WSS VMs, Exchange VMs) right the way through to professional setups and I’m pretty sure that we’ll be staying with them for a long time to come.</ad>