December 2011 - Posts

Powershell Snapins with SQL 2012
08 December 11 07:23 PM | MartinBell | 2 comment(s)

Because the only Adventureworks database on codeplex for SQL Server 2012 is a case sensitive database, I thought I would check my change-collation script to see what needed changing. I thought the only issue would be changing SqlServerCmdletSnapin100 to SqlServerCmdletSnapin110 but unfortunately that wasn’t the case Sad smile


Add-PSSnapin SqlServerCmdletSnapin110

Gave the error message:

Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.

At line:1 char:13

+ Add-PSSnapin <<<< SqlServerCmdletSnapin110

+ CategoryInfo : InvalidArgument:

(SqlServerCmdletSnapin110:String) [Add-PSSnapin], PSArgumentException

+ FullyQualifiedErrorId :


So the snapin hadn’t been registered!  This can be confirmed when nothing is returned using the command:

Add-PSSnapin –Registered SqlServerCmdletSnapin110

So how do you register a snapin? Fortunately example 3 of the help for Add-PSSnapin lays it out for you. I found the dll in:

C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\

So the commands I used were:

set-alias installutil


installutil "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll"

installutil "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll"

get-PSSnapin -registered

add-PSSnapin SQLServer*110

This was great and I got the script running with only one other non SQL 2012 issue that needing fixing is was ready to go.

I wondered why Microsoft had decided not to automatically register the DLL for you? My question was answered by Bob Beauchemin who explained the changes

In SQL Server 2012 the PowerShell components for SQL Server are implemented as a module rather than as a snapin. To install it, just start (vanilla) PowerShell and enter "import-module sqlps" at the prompt (or put it in your profile.ps1) and ignore the warning about unapproved verbs. Or use"import-module sqlps -DisableNameChecking". That's the equivalent of what SQLPS 2012 does by default.

Some of the docs look like they haven't yet been updated, but describes the general concepts/changes. You'll also get the new SQLAS modules, and there's some new "subdirectories" for XEvent and SSIS. The sqlps module is in C:\Program Files (x86)\Microsoft SQL Server\110\Tools on a 64-bit install.”

This explains why all my bingoogle searchs for combinations of “snapin” “SQL 2012” “denali” and “pssnapin” turned up nothing!

You can read more about importing the sqlps module here
Loading the whole module seems to me a bit of an overkill if you only want the commandlets, but who am I to argue with the new order?

I then decided that it would be a good idea to come up with something to put in my script which determine whether the system required the SQL 2008 snapins or the SQL 2012 module to be loaded. This is what I have come up with:

if ( Get-PSSnapin -Registered | where {$ -eq 'SqlServerProviderSnapin100'} )
    if( !(Get-PSSnapin | where {$ -eq 'SqlServerProviderSnapin100'}))
        Add-PSSnapin SqlServerProviderSnapin100 | Out-Null
    } ; 
    if( !(Get-PSSnapin | where {$ -eq 'SqlServerCmdletSnapin100'}))
        Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null
    if( !(Get-Module | where {$ -eq 'sqlps'}))
        Import-Module 'sqlps' –DisableNameChecking

It assumes SQL 2008 servers will have the SqlServerProviderSnapin100 and SqlServerCmdletSnapin100 registered otherwise it is a SQL 2012 system.

My updated Change-Collation script can be found here

SQLBits X–Registrations are open
07 December 11 04:14 PM | MartinBell | with no comments

Today we opened registrations for SQLBits X. Everyone registered on should have received an email, but in case you aren’t here is the information:

Dear Sir/Madam,
We have two pieces of big news for you this week. In the words of organiser Tim Kent “It’s gonna be massive”

Here at SQLBits we are so honoured and delighted to announce that Microsoft recognises SQLBits X as the official UK SQL Server 2012 Technical Launch Event.

And, if that’s not enough…..then

Registration for SQLBits X - The UK SQL Server 2012 Technical Launch Event, is officially open! We simply could not wait and decided to open up registration immediately.


Oh and also… we have received submissions from Microsoft’s Cathy Dumas, Elad Ziklik and Matt Masson. As well as MVPs; Denny Cherry, Adam Machanic and Stacia Misner. Not to mention the array of other Microsoft Certified Masters, Microsoft Most Valuable Professionals and Microsoft Staff who will be attending. In the meantime, check out the sessions that have already been submitted from both the best SQL Server speakers in the world and from new speakers.

New to SQLBits, allow us to explain what you would expect at a SQLBits event.

The conference will be taking place between the 29th and 31st of March 2012 at the Novotel London West. The event will be the biggest and best yet, we have capacity for 900 people and expect to sell out really quickly.

The format for SQLBits X is the same as always with leading speakers from all around the world – with the added buzz of the UK SQL Server 2012 Technical Launch Event thrown in for good measure.

Thursday will be a series of full day sessions delivered by renowned presenters followed by Fusion-io’s follow-up to last year’s highly entertaining Crappy Code Games in the evening.

Friday is a day filled with regular sessions delivered by our highest rated speakers and Microsoft superstars and will feature an all new SQLBits Social event that should not be missed!

As always we will conclude proceedings on the Saturday with our free Community Day.

We want as many SQL Server professionals to take advantage of this unique event. To that end, for the fourth conference in a row, we are freezing the prices to attend.

The prices for SQLBits X are as follows; register early to take advantage of our early bird discounts.

All prices are exclusive of VAT. Prices are in UK Pounds Sterling (GBP). 

Cut-Off Date
midnight on the


Deep Dive

Full Conference
Thursday & Friday

Community Day

Early Bird

5th Jan 2012






4th Mar 2012




Last Minute




Discounts are available from local user groups. Contact your user group organiser for details.

Extra Discount - 20% off when 6 or more people book for the whole conference.

We are also really excited to announce that the SQLBits website is now running on SQL Server 2012. There are some really exciting additions that we are hoping to bring you based on the new functionality in SQL Server 2012. Why not download SQL Server 2012 RC0 now to experience the new functionality for yourself.

It is a testament to previous SQLBits attendees and sponsors that we have received this recognition. We hope we can thank all attendees, both new and old at the official UK SQL Server 2012 Technical Launch Event!


SQLBits Committee

Calling DTUtil recursively from Powershell
01 December 11 10:47 PM | MartinBell | with no comments

I’ve used DTUtil numerous times to load SSIS packages, but usually it is to load one or more packages to the same location. From the command prompt you can use a command such as

FOR %i in (*.dtsx) DO DTUTIL –File “%i” –Decrypt SecretPassword –Destserver SQLServerInstance –Encrypt SQL; “%~ni”; 5

If you are not familiar with DTUIL check out the
documentation on MSDN.

Using the above command is fine if you have all the packages in one directory and they all go to the same destination. If you have multiple directories containing files then you will need to repeat the command for each directory. With Powershell it is very easy to recurse through directories and process each file and there are many example available such as
this one that will give you a quick start.

The main issue using DTUtil is that if you try to copy a package to a folder that doesn’t exist DTUtil will automatically create the folder, therefore for each directory you need to check the existence of the destination folder. To do this I have used DTUTIL and the /FE flag, then checking the $LASTERROR variable tells me if it exists.

The whole script can be found HERE. There are other ways of doing this, but combining this utility with Powershell gives you a quick solution to the problem.

Filed under: ,

This Blog

SQL Blogs