SQL Server SMO loading Assemblies Gotcha’s

One thing I’ve seen most DBA’s blogging about SQL Server and PowerShell is the use of the V1 way to load the SMO Assemblies using:
[void][reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”);
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 [3] > Add-Type -AssemblyName “Microsoft.SqlServer.Smo”;
Add-Type : Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, 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
Addtype_SMO_error
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:
v1 load SMO assembly
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 11.0.0.0.  Notice that using the V2  command “Add-Type”, was trying to find 9.0.242.0 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
Check PowerShell session loaded Assemblies
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\11.0.0.0__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”:
“C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”
In order to load a different version, you need to manually change the version number.  In my case, I need to change from 11.0.0.0 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.
Windows loaded assemblies
Finally, press enter on the following command will load the SMO assembly for SQL Server 2008/R2: (this is a one-liner)
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”
I know it’s a long line.  Remember, you just type it one time and then copy/paste to another script.
Don’t forget to save this line, then open a new PSConsole session, and run it.
Here’s screen shot how is done:
New Powershell Session and loaded SMO 2k8 Assemblies
Keep in mind, if you’re wondering what assemblies are loaded during your PowerShell session, then use the following one-liner:
[system.appdomain]::CurrentDomain.GetAssemblies() | Select location
To test your SMO loaded successfully use the following SMO lines to connect to you local SQL instance:
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’)
$MySQL.Information.version
Happy PowerShelling!!

IT Camp Saturday 2011 – Let’s Talk About PowerShell

Apologies to all waiting for this session to be posted.  Finally, I was able to upload my presentation to my SkyDrive account and make it available to all attendees.

Thanks to all who participated in this great IT Camp Event.

Here’s the link:

<>

Please, you can contact me if you have any questions:

Twitter: @MaxTrinidad, via Email at: MaxT@putittogether.net

SQL Server Denali Side-by-Side 2008 R2 BIDS issue workaround

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:

R2 BIDS error after installing SQL Denali CTP3

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.

Finally!! SQL Server Denali CTP3 is Public..

Yes, is out!!  Download at: https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

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!!

Available: SQL Server Denali CTP3 Release Note!!

Post in Twitter:

From @SQLvariant: Pretty Cool: Microsoft SQL Server Code-Named “Denali” CTP3 Release Noteshttp://social.technet.microsoft.com/wiki/contents/articles/3711.aspx #SQLDenali  (07/12/2011 – updated release note link)

(Updated: 07/09/2011 – Microsoft decided to remove the link to this Release Notes.)

From me (@MaxTrinidad): #SQLDenali #PowerShell – Make sure to read the release notes on PowerShell stuff: 1.1, 1.5, 2.4, 5.8, 5.9, 5.11.1, 5.11.3, & 8.1. Awesome!!

From me (@MaxTrinidad): #SQLDenali CTP3 comes with the long awaited SQLPS module… HURRAY!!

Yes!! Read the “Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes”: http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx (What a teaser??)

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.

WE ARE ALL WAITING!!

Truncating SQL Tables using SMO

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:

[sourcecode language=”powershell”]
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
[/sourcecode]

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:

SSMS Database Object Explorer Details - dbsize before Truncate

So, I added the following line after the truncate tables:

[sourcecode language=”powershell”]
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
[/sourcecode]

Image after truncate and running the SMO Shrink() method:

SSMS Database size after truncate and shrink process

Putting everything together into a basic function performing your tables truncate and database shrink process:

[sourcecode language=”powershell”]
Function Clear-TSQLTruncateAllTables{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True, Position=0)] [ValidateScript({$_ -ne $null})] [string] $SQLInstanceName,
[Parameter(Mandatory=$True, Position=1)] [ValidateScript({$_ -ne $null})] [string] $DatabaseName
)
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
}
[/sourcecode]

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.

For more information on this SMO DatabaseShrink Method, here’s some links: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.shrink(v=SQL.110).aspx and http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.shrinkmethod(v=SQL.110).aspx

Using Get-TSQLTableRecordCount dual syntax

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:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -ShowAllDatabases
Result:

Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: ALL

SQLServerName DatabaseName
————- ————
[MAX-PCWIN1]  AdventureWorks
[MAX-PCWIN1]  AdventureWorks2008R2
[MAX-PCWIN1]  Developer
[MAX-PCWIN1]  ReportServer
[MAX-PCWIN1]  ReportServerTempDB

Usage #2 – To Display all tables record counts in the selected Database:

Now you know the database name and you’re ready to use the command to get the record counts fo all your tables.  Here’s an example:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -DatabaseName Developer
Result:

Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: Developer

Parent      DisplayName                    RowCount DataSpaceUsed
——      ———–                    ——– ————-
[Developer] dbo.DatabaseDiskStats                 0             0
[Developer] dbo.Employees                         9           240
[Developer] dbo.ServersToCheckDiskStats           0             0
[Developer] dbo.SurfSpotTable                     4             8
[Developer] Production.ProductDescription       762           144
[Developer] Production.ProductPhoto             101          2208
[Developer] Purchasing.PurchaseOrderDetail        0             0
[Developer] Sales.SalesPerson                     0             0