This blog post is tested on SQL Server 2014 and PowerShell V5.

PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).

The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.

One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.

In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.

First, we need to add the ReportViewer assembly to the script. For this I am using the WinForms report viewer assembly. There is similar report viewer assembly for web forms.

See full article

We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.

The example report utilizes different variations of parameters. In the example, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.

Additional properties can be found in the Report Parameter window, including whether the parameter is nullable, if it’s multivalued, or if it’s visible or hidden, etc.

To do this in PowerShell, the first thing to do is to add the assembly that will allow us to create the ReportViewer object. Note that the version and public key token are specified, so check the version you have on your machine to ensure the accuracy.

See full blog post here

SQLSaturday #114 in Vancouver is coming up. It will be held at the BCIT Burnaby Campus – SW buildings. Have you registered yet?

If you haven’t, you can still register! I love SQLSaturdays. I personally enjoy attending them a lot, and wished there were more SQLSaturdays in the Vancouver vicinity so I can attend more. Although if I had the time and money, I’d fly to other SQLSaturdays too (did you know there’s a SQLSaturday in Honolulu, Dublin and Portugal?).

If you’re in the database/SQL Server field, you should have plenty of reasons to attend. There’s lots of awesome stuff to learn, especially since SQL Server 2012 has just launched last March 7, 2012! Plus, this is a free training event. Yup, you read it right, it’s a free awesome event. One day of SQL Server training can cost you a few hundred dollars, but SQLSaturdays are packed with different sessions, and you don’t even have to worry about the dollar cost.

Here are the event details as posted at the SQLSaturday site:

Who:
YOU! All you have to do to get involved is register online to reserve your spot. Show up and attend the sessions that you choose.
What:
This is a FREE one day training event for SQL Server developers, administrators and other data professionals.

When:
Saturday, March 17, 2012. Register online now to reserve your spot. Attendee check-in will begin at 7:30 am until 9:00 am. The first sessions of our four tracks begin at 9:30 am.

Where:
BCIT South-West Buildings
3700 Willingdon Avenue
Vancouver, BC V5G 3H2
Canada

I have a couple presentations. As usual, I am both pretty excited and nervous.

ETL with SSIS for Beginners

You’ve heard of the term “ETL” before, but you’re not quite sure what it really is and how to do it. In this session we’ll demystify this so-called ETL. Don’t worry, no previous experience required. We’ll work our way from getting your data from your sources, to cleaning and reformatting them, and loading them to your data warehouse. We will also look at some standards and conventions you should consider when doing ETL to simplify your process, and save you some headaches in the future. Of course, our go-to ETL tool is SSIS.

You did what with PowerShell?!

PowerShell is cool. And powerful. Don’t believe me? See it for yourself. Come to this session, and I will show you cool things we can do with PowerShell – ranging from backing up your servers, working with BLOBs and XML, to automating SSRS report deployments and downloads, and even integrating with SSIS. We’ll work with different facets of SQL Server from a PowerShell perspective.

See you there!

One way to bulk upload your XML files into SQL Server is by using PowerShell. In the script below I am using SQL Server 2012 and PowerShell V3 (CTP).

Note that you can use the script in SQL Server 2005/2008/R2 and PowerShell V2, with some slight changes in syntax.

I use Invoke-Sqlcmd to get this done.
First up, I have a prep T-SQL script file that I use to create my tables:

$instanceName = "KERRIGANSQL01"
$databaseName = "SQLSaturday114"
$prepfile = "C:presentationsSQLSaturday114PowerShellStuff.Table.sql"
Invoke-Sqlcmd -ServerInstance $instanceName `
-Database $databaseName -InputFile $prepfile

Nothing fancy about this table. Just a basic table with an XML field:

IF OBJECT_ID('PowerShellStuff') IS NOT NULL
DROP TABLE PowerShellStuff
GO
 
CREATE TABLE PowerShellStuff
(ID INT IDENTITY(1,1) NOT NULL,
 FileName VARCHAR(200),
 InsertedDate DATETIME DEFAULT GETDATE(),
 InsertedBy   VARCHAR(100) DEFAULT SUSER_SNAME(),
 XMLStuff XML,
 BLOBStuff VARBINARY(MAX)
)

Read the rest of this entry »

When you’re working with PowerShell and SSRS, you may occasionally come across a script that works once, then just mysteriously decides not to work anymore on a second or third invocation. Or it may just not work period, even though you think the syntax is short and straightforward, and you know you’re not misspelling any syntax.

Please note I am running this on the PowerShell ISE, and PowerGUI – and tried on both PowerShell V2 and V3.
Read the rest of this entry »

I just have to blog this little gem. Every now and then I would be running a PowerShell script, I’d get an error but I wouldn’t get anything useful from the default error message besides the line number.
Read the rest of this entry »

I wanted (needed!) to write a script to automatically generate a number of reports based on sets of parameters. Most of the scripting I’ve done before are for straight up parameters that I can pass using SMO or through the URL string.

This time, I had a cascading query-based parameter, ie one parameter that is filled out based on another parameter. I was doing my usual drill with PowerShell when I got the following infamous error:

“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”

Which is weird because I did provide all the required parameters. A quick search leads me to a few blog posts, most of which mentioned that it’s because of my query based parameter. Most also suggest the resolution is to convert my parameter which has query-based parameter to be non-query based.

The only problem is I can’t. And I don’t want to. That’s not a resolution. This report is meant to be used both by users for ad hoc purposes, and for massive downloading for month-end reports. Removing the query-based values for a parameter *IS NOT* a resolution. Not for me anyway.

Read the rest of this entry »

Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names

Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell

  $params = $null;
 
  #set parameters
  #here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 3
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);

Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)

Read the rest of this entry »