Saturday, August 16, 2008

SMO example

As mentioned before, I spend quite some time creating scripts in PowerShell for managing SQL server. Here is an example of a couple of SMO functions I have found very useful.

In the future, I'll try to post some examples that uses the functions below

We'll start by loading the correct assembly:

[void][reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

Or the more general "LoadWithPartialName" - that also works if you have another SMO version:

[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")

Then create a function to establish a SMO connection to an SQL instance. The function below creates a smo connection, with a user defined timeout, and applicationname. Remember that it is always recommended to give meaningful names to any connectionto SQL - it can make troubleshooting much easier.

function New-SMOconnection {
    Param ($server, $applicationName= "PowerShell SMO", [int]$commandTimeout = 5)
    Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
    if (test-path variable:\conn) {
        $conn.connectioncontext.disconnect()
    } else {
        $conn = new-object('Microsoft.SqlServer.Management.Smo.Server') $server
    }
    $conn.connectioncontext.applicationName = $applicationName
    $conn.connectioncontext.connectTimeout = $commandTimeout
    $conn.connectioncontext.connect()
    $conn
}

And a small function to remove a SMO connection once you are done with it:

function Remove-SMOconnection {
    Param ($connection)
    Write-Debug "Function: Remove-SMOconnection $connection"
    Remove-Variable -Name $connection -Scope 1
}

The above functions can be used in the following manner:

1.) Establish a SMO connection, remembering to check for errors:

$SMOconn = New-SMOconnection "MyTestServer\SQLinstance0001" "JBs Secret PowerShell App"
if ($SMOconn.connectioncontext.isopen -eq $false) {# handle error}

You now a an open SMO connection to your SQL server. If you would like to retrieve information about what version/build you SQL server is, you could use the following:

$SMOconn.Information.version

Or retrieve a list of database names:

Foreach ($database in $SMOconn.databases) { $database.name }

Remember that "Get-Member is you friend!" - you can use Get-Member (or its alias, "gm") to explore the possibilities of SMO:

$SMOconn | gm

Once you are done, remember to close the SMO connection:

Remove-SMOconnection SMOconn

8 comments:

moff said...

Worth noting that SQL2008 comes with Powershell functionality, letting you navigate SQL as as drive, eg:

cd sqlserver:\sql\mytestserver\SQLinstance0001
cd Databases
dir

or:
get-childitem sqlserver:\sql\MyTestServer\SQLinstance0001\databases|format-table name

Also new cmdlets, eg:

invoke-sqlcmd -query 'select @@version' -serverinstance 'MyTestServer\SQLinstance0001'

See BoL here: http://msdn.microsoft.com/en-us/library/cc281954.aspx

Parag said...

When i do a get-sqldata and pass the associated sqlquery, it times out after excatly 10 mins. How do we override this deafult value

Jakob Bindslet said...

Parag, could you give me an example?

Parag said...

For ex:

get-sqldata "dbdbaqa201" "master" "WAITFOR DELAY '00:11:00'"


when i run this, i times out exactly after 10mins and continues further

Parag said...

Also I am using SQLPX SMO library which has this function . I tried modifying the function in the library to this
function Get-SqlServer
{
param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.'))
#When $sqlserver passed in from the SMO Name property, brackets
#are automatically inserted which then need to be removed
$sqlserver = $sqlserver -replace "\[|\]"

Write-Verbose "Get-SqlServer $sqlserver"
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
[int]$commandTimeout = 99999
$applicationName= "PowerShell SMO Parag"
$server.connectioncontext.applicationName = $applicationName
$server.connectioncontext.connectTimeout = $commandTimeout

#trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect()
return $server

} #Get-SqlServer

but whatever is the value of the commandTimeout, the script timesout after 10mins. weird

Jakob Bindslet said...

Try the following:

$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.connection = $conn
$sqlCmd.CommandTimeout = 999999


ie. use the CommandTimeout instead of the connectionTimeout.

Parag said...

Thanks , i figured it on how to do it thru smo

$server.connectioncontext.statementtimeout =999999

James Newsome said...

Having a similiar problem with the following query keeps timing out on me.

Add-Type -path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"

$SQLSvr = "IDC1VSQL0022\HQFACTORY05_2000, 1262"
$MySQLObject = new-object Microsoft.SqlServer.Management.Smo.Server
$SQLSvr;;


$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("IDC1VSQL0022\HQFACTORY05_2000, 1262")
$MySQLObject.connectioncontext.connectTimeout = 0
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("marketing_work")




$ds = $db.ExecuteWithResults("Delete FROM [marketing_work].dbo.JN_Commission_Adj")


#Change these settings as needed
$filepath = "\\insight.com\team\finance\Partner Accounting\2015\Working Folders\James Newsome\CommissionAdjMaster.xlsx";
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
#Should be name of the sheet in Excel - ie [sheet1$]
$qry = 'select * from [sheet1$]'
$sqlserver = "IDC1VSQL0022\HQFACTORY05_2000, 1262";
$dbname = "marketing_work" ;
#Create a table in destination database with the with referenced columns and table name.
$tblname = "dbo.JN_Commission_Adj"

#drop the Table




#######################
function Get-ExcelData
{

param($connString, $qry='select * from [sheet1$]')

$conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$conn.close()
$dt

} #Get-ExcelData

#######################
function Write-DataTableToDatabase
{
param($dt,$destServer,$destDb,$destTbl)

$connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "$destTbl"
$bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname