How to Download All Your SSRS Report Definitions (RDL files) Using PowerShell

Here’s a short PowerShell script that :
1. Connects to your report server
2. Creates the same folder structure you have in your Report Server
3. Download all the SSRS Report Definition (RDL) files into their respective folders

In addition to backing up your Source Project, your ReportServer database, or good old RSScripter (see this is just another way you can “backup” or archive your reports.
Continue reading

Listing SSRS ReportServer Items Using PowerShell

Just a short tidbit on how to list your ReportServer Items using PowerShell

#note this script is tested on PowerShell v2 and SSRS 2008 R2

$ReportServerUri = "http://yourserver/ReportServer/ReportService2005.asmx";
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;

#check out all members of $Proxy
#$Proxy | Get-Member

$items = $Proxy.ListChildren("/", $true);

$items | select Type, Path, ID, Name | sort-object Type, Name

Continue reading

Geocode Locations Using Google Maps v3 API and SSIS

I wanted to do a cool experiment on SSIS that I haven’t tried before. I am really interested in this whole mapping component on SSRS, and I know that most likely the biggest challenge of companies is how to geocode addresses that are already in their databases. So what I wanted to do was to try and retrieve lat/long information from public geocoding web services within SSIS.

Please note that the exercise below is purely for experimentation. Please read the restrictions of each of the geocoding services, and go with whichever is appropriate for your purpose.

Also this is a simplistic code to show you how to get the latitude/longitude information. On your own versions, test thoroughly and add the appropriate exception handlers.

Ok, let’s start.

Continue reading

Transfer data from MySQL to SQL Server Using SSIS

I will be doing a series of tutorials (some simple, some more involved) in SSIS in the next little while. In addition to step by step instructions, I will also be providing the video tutorial equivalents which will be posted at the Black Ninja Software website. Once the video tutorials are up, I will be updating the individual links.

Now for this simple problem. You have a MySQL database, and you want to be able to dump data from it to SQL Server using SSIS.

Here are the steps:

1. If you haven’t already, download and install MySQL Connector for ODBC.

2. Create a DSN for your MySQL

a. Go to Start > Administrative Tools > Data Sources (ODBC)
b. Create a new User DSN (in my case I called it MySQL55)

Continue reading

Troubleshooting a “Doomed” SQL Server Transaction

Recently we encountered an unusual error message:

System.Exception: sprocname_Exception: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

It seems we’ve hit a corner case. This apparently happens when you have a TRY/CATCH exception handling mixed with old style exception handling (see Alexander Kuznetsov’s article), which may lead to an “uncommittable” transaction.

What’s an “uncommittable” transaction? In our case, we had a stored procedure that inserts records to a table that has an insert trigger. By default in the stored proc, XACT_ABORT if OFF, but in the trigger we turned it on. If an error is thrown in the trigger and caught by the CATCH block, this transaction is technically uncommittable hence we get the error.

So to fix, either:
– Use Remus Rusanu’s template for error handling in stored procedures

We went with Remus Rusanu‘s template. Problem fixed. Thanks Remus!

Great SQL Server Free Video Resources

More of a note to self (ie a list of videos that I’d like to watch at least a few times), but hopefully this list would be useful to you too. Great video resources, a wealth of information. Thanks to the people who created these great videos and made them available to the community.


in a heap – in no particular order
MCM Readiness Videos



Technet SQL Server 2008 Videos



Brent Ozar – Video Tutorials



Technet SQL Server How Do I videos


in a heap – in no particular order
Pragmatic Works


Idera On Demand WebCasts

Prologika SSRS Video Demos