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…

We are currently running traces against a web applications that we are developing and one of my colleagues was telling me that the JetBrains dotTrace applications didn’t allow running profiles against the ASP.net web development server (it way grayed out). The reason was that only Visual Studio 2008 was installed, which by default puts the WebDev.WebServer.exe (which is the ASP.net web development server) into the c:\<program files>\<shared files>\Microsoft Shares\DevServer directory instead of the old Visual Studio 2005 path which was c:\windows\Microsoft .NET\Framework\v2.x.y.z\.

There is a way to profile against the VS08 web server as described here, but you can also just copy the WebDev.WebServer.exe from the one directory to the Windows\Framework directory. This probably isn’t the best way to do it (because patches will only go to the original install directory), but with it dotTrace can start a profiling session with the ASP.net web server.

Ok, so I decided to setup a new VPC image with Windows 2008, SQL 2005, WSS 3.0 and Visual Studio 2008. Basically everything is going quite well, but there are a couple of things I encountered while installing, that I thought I would share.

1. Installing SQL Server 2005 with Reporting Services

From previous experience I knew that Reporting Services requires IIS if you want to install it. The first thing you notice that the server manager has changed a little. I went ahead and installed ASP.net and the Application Server which also installed IIS, but when I started my SQL Server Setup Reporting Services was disabled, so there was something wrong with the IIS install. It took a few minutes, but it seems you actually have to install the IIS role separately. (I had chosen ASP.net, and activated further options manually.) Anyway once the following picture appeared I was set to restart my setup. This time no problem.

2. Internet Explorer Enhanced Security

I don’t like it, so I always go and uninstall it after finishing my base setup. (Usually when I start downloading stuff I need to install.) So I go to my Control Panel, go to Programs & Features and click on Manage Windows Features. Woha, the server manager appears. Ok, this is different to Windows 2003. I think it was David Platt who said something along te lines of “If you want people to use it, make it obvious. If you don’t want people to switch it off, hide it”. Well done, it’s hidden.

It took a bit of googling (sorry, but live’ling just doesn’t sound well as a verb) to find the right option to change. The feature is called IE ESC which can be found in the overview page of server manager. Now I know, now you know.

3. Installing Windows Sharepoint Services 3.0

Ok, I live’d for “Windows Sharepoint Services 3.0″ (I really did use live this time.) and downloaded the package. Started Sharepoint.exe and voila “This program is blocked.”. WTF? I read on and it asks you to install the setup package with the latest service pack and won’t accept anything else. Ok, my mistake, don’t blindly take the first link presented (I bet google would have pointed me to the service pack 1 download package - but I didn’t check).

I know it’s been a while. I’ve been busy talking about SyncFramework at the VSOne conference, helping a bit of at the Sync Framework forums and lot’s of studying going on. I promise I have some new longer posts coming soon.

Yesterday I wrote a unit test that I wanted other members on my team to execute this week to check for some problems. Where’s the problem you may ask? Well I personally like TestDriven.net and MBUnit whereas my colleagues rely on the testing suite in Team System. Fair enough, usually we don’t exchange that many tests (and no - I’m sorry about that myself actually - we don’t have continuous integration. But even if we did that probably wouldn’t be a major problem).

Anyhow I created my class library project and wrote my test. Worked great on my computer (don’t hit me for saying that). I then remembered that it needed to run under MSTest on the other dev machines. Fair enough, can’t be that hard. I referenced

Microsoft.VisualStudio.QualityTools.UnitTestFramework

and gave my methods additional attributes. (The top ones are MSTest, the bottom ones are MBUnit.)

[Microsoft.VisualStudio.TestTools.UnitTesting.TestClass]
[TestFixture]
public class EvaluatorTestSuite 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestInitialize()]
[TestFixtureSetUp()]
public void SetUp() 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestCleanup()]
[TestFixtureTearDown()]
public void TearDown() 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestMethod]
[Test]
public void Evaluate_ValidEvaluationModel_ReturnsValidEvaluationResult()

Then I went to the Test menu in Team System and wanted to execute all tests in this solution. But VS told me there aren’t any. Basically I figured out the test projects must have some special attribute that make them “testable”. They are basically just class libraries but they have a special Guid in their project file. Not quite sure why Microsoft would do that, seeing as they could just check for the attributes on the classes like TestDriven.net does, but ok. This is a quick solution to turning a class library into a MSTest testable project. Open the CSproj file of the class library project and just before the first propertygroup closes insert the following line:

<ProjectTypeGuids>

{3AC096D0-A1C2-E12C-1390-A8335801FDAB};

{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}

</ProjectTypeGuids>

Those two guids make it MSTest testable. And no, I didn’t have time to figure out why there are two and what they do exactly. If anybody cares to explain, I would love to hear about it. The file should look something like this:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProductVersion>9.0.21022</ProductVersion>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectGuid>{5273D187-1B0D-40DC-A415-36761976438B}</ProjectGuid>
    <OutputType>Library</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>TheGrandestProjectOfThemAll</RootNamespace>
    <AssemblyName>TheGrandestProjectOfThemAll</AssemblyName>
    <TargetFrameworkVersion>v3.5</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
    <ProjectTypeGuids>{3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
  </PropertyGroup>

This is an egoistic post, but I need to remember this piece of information, so I’ll put it in a place I’m sure I’ll find it again. I might do that a little more often if my Google searches take too long (or my memory fails more often).

The problem: How do I run a .ps1 file (Powershell script) from within Powershell?

Attempt 1:

PS C:foo> .Install.ps1

File C:fooInstall.ps1 cannot be loaded. The file C:fooInstall.ps1 is

not digitally signed. The script will not execute on the system. Please

see "get-help about_signing" for more details..

At line:1 char:13

+ .Install.ps1 <<<<

Ok, strange but secure. How do you switch it off?

Read here for the details or continue for the summary:

The command “Get-ExecutionPolicy” will return the policy you are currently running with.

There are three options for the execution policy that you can set:

• AllSigned (default): all scripts need to be signed.
• RemoteSigned: Only scripts downloaded from the internet must be signed.
• Unrestricted: Any scripts will run without being signed.

I changed the policy using “Set-ExecutionPolicy RemoteSigned” and voila…