Sunday, March 16, 2014

SSRS - Tip: Passing multiple values in a single parameter within a stored procedure

When modifying a drop down selector in a SSRS report to allow a user to select multiple values I noticed all other reports which provided this functionality had the accompanying SQL query embedded within the report. I thought this was strange as the majority of the other SSRS reports I had looked at used stored procedures.

After a little bit of research I found the following:
"The data source cannot be a stored procedure. Reporting Services does not support passing a multi-value parameter array to a stored procedure." Essentially, SSRS passes the multi-value parameter as a comma separated string to a stored procedure. 
Note: If the SQL query is embedded within the report, this format works perfectly fine: "IN ('Item 1', 'Item 2', 'Item 3')"

All is not lost if you do not want to embed your SQL within the SSRS report. If you read this excellent article written by David Leibowitz Puzzling Situations: Using MultiValue Parameters with Stored Procedures in SSRS, it is possible (and quite straight-forward) to write an SQL function that will convert the comma separated string into a table. The table can then be enumerated using the IN keyword within your stored procedure.

Happy days!

Wednesday, March 12, 2014

Creating local domain with IIS

Recently, I gave a presentation that required webpages to be loaded from multiple domains:

Instead of registering multiple domains and publishing a couple of websites, I was able to achieve this locally through these simple three steps.

1) Add a new Web Site

Open Information Internet Services Manager (the easiest way to do this is to search for "IIS" from the Start menu) and expand your localhost entry in the tree on the left hand side exposing the "Sites" node. Right click on this and select "Add Web Site...".

2) Configuring the new Web Site

The most important thing to notice here is that the "Host name" must match the domain that you want to create locally. With the above configuration, I would expect to access this site by navigating to "" in a browser.

3) Configure the HOSTS file

The last step is to force your computer to resolve requests to your domain to your local IIS instance and not resolve them through a DNS. This is achieved by adding a entry to the HOSTS file that points to your local machine. In Windows XP, Windows Vista, Windows 7 and Windows 8, the hosts file can be found here: c:\windows\system32\drivers\etc\HOSTS.

Any requests for "" will now be resolved to the IP address of, which is the loopback address for you machine resulting in your local IIS serving the page:

Sunday, March 2, 2014

MSSQL Maintenance Plans - Tip: Have the task name displayed in your logs

When viewing the history of a Maintenance Plan job, the name of each task can be blank making it tricky to know which row relates to which task.

When creating the Maintenance Plan task, unless a "Task Name" is specified (Properties on the specific task), no name will be displayed within the plans history. 

By adding a "Task Name", your plan history will display the task name at the row level. This helps make the plans history more readable at a glance.