Netezza Commands

Powershell - M plan

Add-Type -AssemblyName System.Windows.Forms

$form = New-Object Windows.Forms.Form
$form.Size = New-Object Drawing.Size @(700,500)
$form.StartPosition = "CenterScreen"


$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,20)
$objLabel.Size = New-Object System.Drawing.Size(300,30)
$objLabel.Text = "Enter Full Instance Name(CLWTD4000\Instance01)"
$form.controls.Add($objLabel)

$objInsBox = New-Object System.Windows.Forms.TextBox
$objInsBox.Multiline = $true
$objInsBox.Location = New-Object System.Drawing.Size(305,20)
$objInsBox.Size = New-Object System.Drawing.Size(330,20)
$form.controls.Add($objInsBox)


$drives = Get-WmiObject -Class Win32_Volume | Where-Object {$_.FreeSpace -gt 1073741824}


#Get Drive Information

Foreach ($drive in $drives)
{
$rootdrive = ($drive.Caption).TrimEnd("\")

If ($rootdrive -ne "C:" -and  $AvailDrives -notlike "*$rootdrive*" )
{
#We only want drives that are not the C drive, and we don't want duplicates...
#When you grab a list of drives from the volume class, you  may get multiples of the same
#drive due to mount points.
If(!$AvailDrives){$AvailDrives = $rootdrive}
Else {$AvailDrives += ","+$rootdrive}
}
ElseIf ($rootdrive -eq "C:")
{
$AvailDrive = $rootdrive
}
}



#CREATE THE DRIVE FORM:

$objTempLabel = New-Object System.Windows.Forms.Label
$objTempLabel.Location = New-Object System.Drawing.Size(10,60)
$objTempLabel.Size = New-Object System.Drawing.Size(300,60)
$objTempLabel.Autosize = $true
$objTempLabel.Text = "Select The Drive Where You Want To Copy Template(D:\):"
$Form.Controls.Add($objTempLabel)

$objTempListBox = New-Object System.Windows.Forms.ListBox
$objTempListBox.Location = New-Object System.Drawing.Point(305,60)
$objTempListBox.Size = New-Object System.Drawing.Size(330,60)
$objTempListBox.Height = 40

$objTempListBox.tabindex = 0

If ($AvailDrives)
{
foreach($AvailDrive in $AvailDrives.Split(","))
{
$objTempListBox.Items.add($AvailDrive) | Out-Null
}
}
Else
{
$objTempListBox.Items.add($AvailDrive) | Out-Null
}

$objTempListBox.SetSelected(0,$true)
$Form.Controls.Add($objTempListBox)

Function Call_DtsxFun()
{


$instanceName = $objInsBox.Text

$DtsPath=$objTempListBox.SelectedItem +"\SQLAdmin\Template"


#Create the objects that do the copy - I picked these objects to do the copy to show progress to the installer.


$FOF_CREATEPROGRESSDLG = "&H0&"
$objShell = New-Object -ComObject "Shell.Application"

$srcFile ="\\danuwbd531\D$\CreateMaintplans\template"

If(!(Test-Path $DtsPath))
{

[System.Windows.Forms.MessageBox]::Show("Folder need to create")
New-Item -type directory -Path $DtsPath | Out-Null

$objFolder = $objShell.NameSpace($DtsPath)
$objFolder.CopyHere($srcFile, $FOF_CREATEPROGRESSDLG)

}

Else
{
[System.Windows.Forms.MessageBox]::Show("Folder already exist")
$Temp=$DtsPath +"\template"
Remove-Item -Path $Temp -Force -Recurse
#Let's get everything there everytime this runs - we need to be sure everything in here is up to date.
$objFolder = $objShell.NameSpace($DtsPath)
$objFolder.CopyHere($srcFile, $FOF_CREATEPROGRESSDLG)
}

$CreateSQlJobfile =$DtsPath +"\template\SQLQuery53.sql"
$dtsxfile = $DtsPath + "\template\MaintenancePlan.dtsx"
$log = $DtsPath + "\template\create_maint_plan_out.txt"

#[System.Windows.Forms.MessageBox]::Show($instanceName)
#[System.Windows.Forms.MessageBox]::Show($CreateSQlJobfile)
#[System.Windows.Forms.MessageBox]::Show($dtsxfile)
#[System.Windows.Forms.MessageBox]::Show($log)



$xml = New-Object xml
$xml.Load( $dtsxfile)

#if ($sqlserver.Contains("\")) { $instanceName = "\"+$sqlserver.Split("\")[1] }

$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable
$ns.AddNamespace( "DTS", "www.microsoft.com/SqlServer/Dts" )

$plan_name = $xml.SelectSingleNode("DTS:Executable/DTS:Property[@DTS:Name = 'ObjectName']/text()", $ns).Value
$plan_id = $xml.SelectSingleNode("DTS:Executable/DTS:Property[@DTS:Name = 'DTSID']/text()", $ns).Value -replace "[{}]", ""
$subplan_id = $xml.SelectSingleNode("//DTS:Executable/DTS:Property[text() = 'Subplan_1']/../DTS:Property[@DTS:Name = 'DTSID']/text()", $ns).Value -replace "[{}]",""

$OldServer = """ConnectionString""" + ">server='CLWTD4000\\MAINTPLANS'"
$NewServer = """ConnectionString""" + ">server='" + $instanceName + "'"

(Get-Content $dtsxfile) |
Foreach-Object {$_ -replace $OldServer, $NewServer } |
Set-Content $dtsxfile


# create the Maintenance Plan
dtutil  /FILE $dtsxfile /DestServer $instanceName /copy sql`;"\Maintenance Plans\System databases"


# create the scheduled job for the plan
sqlcmd -S $instanceName -v InstanceName= $instanceName  -v PlanId= $plan_id -i $CreateSQlJobfile -o $log


#Job owner change to polkj11

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null

$NewOwnerLoginName = "polkj11"

$smosvr = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $InstanceName
$agent = $smosvr.JobServer;

$agent.Jobs | % {
    $_.set_OwnerLoginName($NewOwnerLoginName);
    $_.Alter();
    }

$form.close()
}


$btn = New-Object System.Windows.Forms.Button
$btn.Location = New-Object System.Drawing.Size(300,150)
$btn.Size = New-Object System.Drawing.Size(70,50)
$btn.add_click({Call_DtsxFun})
$btn.TEXT = "SUBMIT"
$form.Controls.Add($btn)

$drc = $form.ShowDialog()