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.
