SMO server based connection vs. “classic”

Ok, I said I was going to show an easier way than the ‘classic’ way of connecting to a database server. You’ll be amazed at how much less code there actually is (or needs) for an SMO based connection.

Fire up your trusty PowerShell IDE (mine happens to be PowerGUI made by the folks over at Quest) and paste the following into a script…

# Read table from db.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$SrvName = "YourServerName"
$DBName = "master"

$SrvObj = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $SrvName
$DBObj = $SrvObj.Databases[$DBName]

$Query = "SELECT name FROM dbo.sysdatabases"

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)

$Results.Tables[0].TableName = "DBList"
$DBList = $Results.Tables["DBList"]

foreach ($Row IN $DBList.Rows)
{ Write-Host $Row.Name }

So simple and much easier to work with than the ‘classic’ connection method. Next time around, I’ll show you how to retrieve a list of items, iterate over them and insert data back into a ‘master’ server/db.

Explore posts in the same categories: Powershell, SMO, SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: