THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

SMO Changes from SQL 2005 to SQL 2008

A year ago in anticipation of publishing a book on SMO (which never came to be) I wrote a number of chapters demonstrating how to manage specific sets of objects, and I provided examples in VB.Net, C# and PowerShell. As should be expected, I thoroughly tested all of the code samples to make sure they worked.

This year I've been part a couple of book projects, one being the MVP Charity book which will come out this spring, and I just completed the chapter called "Scripting with PowerShell" for Paul Nielsen's SQL Server 2008 Bible. Without thinking much about it I included a couple of scripts that I'd written for the SMO book as examples. Unfortunately, I didn't test them again in my SQL Server 2008 environment.

I've already blogged here about the relocation of objects from the SMO.dll to the SMOExtended.dll.

What burned me most recently was a change in how you load a table object. Under SQL 2005 the following code worked fine:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null

$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MyServer\MyInstance"

#Reference the AdventureWorks database.
$db = $s.Databases["AdventureWorks"]

#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables["Employee", "HumanResources"]

The script just wouldn't work using the SQL Server 2008 SMO DLLs. Here's what I ended up doing, and this worked.

#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables | where-object {$_.Name -eq 'Employee' -and $_.Schema -eq 'HumanResources'}

I'm not sure why the first construct wouldn't work, but I couldn't even load the ErrorLog table using the first method. (I used that table for testing because it's in the dbo schema, which my login had as its default, so I didn't need to specify the schema name.)

Needless to say, any scripts you use today which rely on the behavior of the SQL 2005 DLLs need to be thoroughly tested with the SQL 2008 DLLs after they've been installed.

Allen

Published Friday, February 20, 2009 10:17 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

Allen;

Have you noticed any SMO performance improvement at all from 2005 to 2008? SMO is functional, but performance is entirely a different story.

February 24, 2009 10:44 AM
 

AllenMWhite said:

Linchi,

I've been in a consulting/training role since just before SQL Server 2008 came out, so I haven't had much opportunity to test performance.

February 26, 2009 12:33 PM
 

mksql said:

"A year ago in anticipation of publishing a book on SMO (which never came to be)..."

A book on SMO would have been a good thing, since it is often harder to find good docs and examples on SMO than with other APIs. What happened to the book material? Any thoughts on releasing it as a ebook or community document?

February 27, 2009 1:49 PM
 

Andrew said:

Just curious if you have come across any more changes. I have an application that runs fine with SMO 9, however after updating all my references and rebuilding for SMO 10 I'm getting loader lock exceptions thrown when I try to connect to databases now.

March 11, 2009 1:22 PM
 

tony said:

and sql 2008 sp1 adds a bug where the size property does not return the correct value!

tr

September 10, 2009 6:22 PM
 

Chad Miller said:

Allen,

Here's another alternative:

$db.Tables.Item("Employee","HumanResources")

January 19, 2010 11:13 AM
 

Reinis said:

OMG!! Thank you thank you thank you.  Finally someone showed me how to specify a table that belongs to a schema other than dbo.  MS scripting guy says "(By the way, this command works because my default schema is dbo together with this table. More about how to browse other schemas later)" but never tells you how to do it. What a lifesaver!

February 8, 2010 12:34 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement