And, there’s a reason for still using this method, which I will show in a minute. Now, PowerShell Version 2.0, introduce a new command “Add-Type” use to simplify loading the assemblies:
Add-Type -AssemblyName “Microsoft.SqlServer.Smo”;
And, works Great! Except, when you’re working with different versions of SQL Server on the same box. You will experience some issues loading the SMO Assembly using the “Add-Type”:
Here’s the error message:
PS  >Add-Type -AssemblyName “Microsoft.SqlServer.Smo”; Add-Type : Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=22.214.171.124, Culture=neutral, PublicKeyToken =89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified. At line:1 char:9 + Add-Type <<<< -AssemblyName “Microsoft.SqlServer.Smo”; + CategoryInfo : NotSpecified: (:) [Add-Type], FileNotFoundException + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand
I’m sure, many if you may have seen this error, and that’s why people sometime go back to use the old faithful V1 format.
Now, you need to be careful because you might be loading the wrong SMO assemblies to use against your database. But, for most cases, I sure it’s OK to load the latest one because it can handle older versions. This is why you most test all your scripts before putting them in your production environment.
Well, the old and faithful V1 “ [reflection.assembly]::Load… ” to load your SMO assemblies work because it grabs the latest version from your windows “Assembly” folder. Here’s an example:
In my environment I do have SQL Server Express, SQL Server 2008 R2 Developer, and SQL Server Denali. So, when I use V1 way to load my SMO, you will notice in the PSConsole that it’s loading the assembly from SQL Server SMO version 126.96.36.199. Notice that using the V2 command “Add-Type”, was trying to find 188.8.131.52 and fail. Even, if this error might be due to a registry issue, this is no the assembly I want to load.
Now, can we still use V2 “Add-Type” command to load the correct version of this assemblies. Hell Yea!!
Here is how:
1. You need to confirm the full path and filename of the SMO DLL, which is located in the “C:\Windows\Assembly” folder.
2. It helps to load the latest SMO assembly using V1 way, then use the following .NET line to list the location of that assembly: [system.appdomain]::CurrentDomain.GetAssemblies() | Select location
3. Now, look for your loaded SMO assembly location, then highlight and press ‘Enter’ to copy the line: C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\184.108.40.206__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
4. Use the V2 “Add-Type” command with the “-Path” parameter, and paste (right-click mouse) to add the copied SMO full path. This value must be a string so use quotes around it. Don’t press enter yet!!
Sample line copied but change version to “10.0.0.0”:
In order to load a different version, you need to manually change the version number. In my case, I need to change from 220.127.116.11 to use my SQL Server SMO version 10.0.0.0. This is why you need to check for your multiple SMO version already installed in your system under you Windows Assembly folder.
Finally, press enter on the following command will load the SMO assembly for SQL Server 2008/R2: (this is a one-liner)
As we all are desperate trying to get into the new SQL Server Denali CTP3, we are going to face some few stones in the road. If you have a clean virtual machine to installed it, then you are OK. But, if you are going to create another instance side-by-side with another SQL Server engine, then keep this in mind.
First, about the installation experience, there are good points. As far as my installation, it was smooth. Prior the CTP3 installation I had to remove my previous SQL Denali CTP1, and it went without a problem.
In case, you’re a SQL Developer and multiple version of SQL installed, then you must be aware that you may have a problem openning your SSIS 2008 R2 solution in your R2 “Business Intelligence Developement Studio” (BIDS) after installing CTP3
Now, last time I did a SQL Server 2008 R2 Side-by-Side installation, I had SQL Server 2005 BIDS installed and working properly. But installing SQL Server 2008 R2 did affect my SQL Server 2005 BIDS. So, I did experience the same situation after installing CTP3 and trying to use SQL Server 2008 R2 BIDS.
Here’s the error you will get when trying to open a previously created SSIS solution:
What’s the workaround?
1. You need to have you SQL Server 2008 R2 media.
2. Go to your Control Panel, add/remove programs.
3. Right-Click on “Microsoft SQL Server 2008 R2 (64-bit)“, and select “Remove“.
4. Follow all the prompts, select SQL Instance, check to remove only the “Business Intelligence Developement Studio“, and complete the uninstall.
5. I would recommend to reboot at the end of this process.
6. Now, you can reverse this process by Right-Click on “Microsoft SQL Server 2008 R2 (64-bit)” again, and select “Add“.
At the end of this process, you will be able to successfully open your SSIS R2 solution in your R2 BIDS.
Let’s keep trying more stuff with SQL Denali CTP3.
You must have a valid Live ID account to be able to proceed downloading the SQL Server Denali CTP3, and must allow to install the Activex control or use the Java Applet for the Download Manager to begin the process.
Don’t forget the read the Release Notes, and have fun with it!!
About the SQLPS Module. Finally, Microsoft have provided us with a PowerShell Module that will load all available SQL Server PowerShell commands (SQLPS). But, keep in mind, that this module only load the command use with the SQL Database Engine. You will still need to manually load the Master Data Services commands, plus whatever they will give us.
So, for now, it’s a waiting game to see SQL Server Denali CTP3 come out.
Here’s a good example of how useful is SMO in your SQL Server Developement environment. As I the task repopulating data in a quarterly basis, I need to clear all my staging tables in databases before my import processes. OK! I could just create a T-SQL query with all my truncate lines. But, this will force me to open SSMS, file open my T-SQL file, and then execute the query. Or, I can create simple PowerShell function so I can run from my PowerShell Console, or ISE editor when it’s needed.
In my previous blog, I showed the Get-TSQLTableRecordCount function which allow me to either list all my databases, or display a list of row count for all the tables in the database. I can use this function to verify that my PowerShell table truncate function is working.
So, when using SMO, how many lines of code it will take me to create this function? About 11 line of code. But truely, the heart of this code is only 3 lines, and rest if only adding the necessary code to it a function.
Here’s the basic 3 lines that make the truncate work:
Of course, you need to provide both the SQL Server instance, and the database name
Note: Be careful with this function because is meant to truncate all tables in the given Database.
I went back to SSMS to graphically confirm that all data was removed, but I found out that the database size was still showing. I knew I had to incorporate the SMO Shrink() method for the database after doing the SMO TruncateData() method on each of the tables.
Here’s an image showing the database size after the truncating all tables:
So, I added the following line after the truncate tables:
This is just a teaser code so you can see the potential and benefit in understanding SMO in PowerShell. I will be incorporating a more elaborate version of this function into my SQLDevTools Module available in CodePlex in the next release.
Here’s one of the function that’s part of my recent SQLDevTools Module – “Get-TSQLTableRecordCount” which is a good example of a multi-purpose command. The main purpose of this function is to assist in displaying the record count of all tables in the database. But, at the same time it can assist you to provide a list of all users databases.
Usage #1 – To show all databases:
In case that you don’t remember the database name in your SQL Server instance, this command give you the ability to list all databases. Here’s an example: