DPM 2010 Tape Belongs to a DPM server sharing this library

I wrote a powershell script to fix this issue in 2010, it does not appear to be an issue in 2012.
a full explanation i got from here. Enjoy


$ServerInstance = "DPMSERVER\MSDPM2012"
$Database = "DPMDB"





$ConnectionTimeout = 30
$Query = "select media.BarcodeValue, media.SlotNum, media.MediaId, gmedia.MediaPoolId from tbl_MM_Global_ArchiveMedia gmedia  inner join tbl_MM_Media media on gmedia.MediaId = media.GlobalMediaId "
$QueryTimeout = 120
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds) 
$conn.Close()




$Query2 = "select library.ProductId, library.SerialNo, library.LibraryId, mpool.Name, mpool.MediaPoolId, mpool.GlobalMediaPoolId from tbl_MM_MediaPool mpool inner join tbl_MM_Library library on mpool.LibraryId = library.LibraryId where mpool.Name = 'Free Media Pool' "
$conn2=new-object System.Data.SqlClient.SQLConnection
$conn2.ConnectionString=$ConnectionString
$conn2.Open()
$cmd2=new-object system.Data.SqlClient.SqlCommand($Query2,$conn2)
$cmd2.CommandTimeout=$QueryTimeout
$ds2=New-Object system.Data.DataSet
$da2=New-Object system.Data.SqlClient.SqlDataAdapter($cmd2)
[void]$da2.fill($ds2) 
$conn2.Close()
#$ds2.Tables





























[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 

$objForm = New-Object System.Windows.Forms.Form 
$objForm.Text = "Data Entry Form"
$objForm.Size = New-Object System.Drawing.Size(300,200) 
$objForm.StartPosition = "CenterScreen"

$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter") 
    {$x=$objTextBox.Text;$objForm.Close()}})
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape") 
    {$objForm.Close()}})

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.Add_Click({$x=$objTextBox.Text;$objForm.Close()})

$Form1 = New-Object System.Windows.Forms.Form
$Form1.ClientSize = New-Object System.Drawing.Size(500, 500)


$comboBox1 = New-Object System.Windows.Forms.ComboBox
$comboBox1.Location = New-Object System.Drawing.Point(25, 55)
$comboBox1.Size = New-Object System.Drawing.Size(350, 310)


$comboBox2 = New-Object System.Windows.Forms.ComboBox
$comboBox2.Location = New-Object System.Drawing.Point(25, 300)
$comboBox2.Size = New-Object System.Drawing.Size(260, 310)


$textbox1 = New-Object System.Windows.Forms.Textbox
$textbox1.Location = New-Object System.Drawing.Point(25,100)
$textbox1.Size = New-Object System.Drawing.Size(260,20)
$textbox1.text = "BarCodeNumber"
$textbox1.Readonly = $True


$textbox2 = New-Object System.Windows.Forms.Textbox
$textbox2.Location = New-Object System.Drawing.Point(25,125)
$textbox2.Size = New-Object System.Drawing.Size(260,20)
$textbox2.text = "MediaPoolId"
$textbox2.Readonly = $True

$textbox3 = New-Object System.Windows.Forms.Textbox
$textbox3.Location = New-Object System.Drawing.Point(25,150)
$textbox3.Size = New-Object System.Drawing.Size(260,20)
$textbox3.text = "MediaId"
$textbox3.Readonly = $True


$textbox4 = New-Object System.Windows.Forms.Textbox
$textbox4.Location = New-Object System.Drawing.Point(300,150)
$textbox4.Size = New-Object System.Drawing.Size(100,20)
$textbox4.text = "Slot Number"
$textbox4.Readonly = $True

$textbox5 = New-Object System.Windows.Forms.Textbox
$textbox5.Location = New-Object System.Drawing.Point(25,325)
$textbox5.Size = New-Object System.Drawing.Size(260,20)
$textbox5.text = "Some Text"
$textbox5.Readonly = $True


$textbox6 = New-Object System.Windows.Forms.Textbox
$textbox6.Location = New-Object System.Drawing.Point(25,350)
$textbox6.Size = New-Object System.Drawing.Size(260,20)
$textbox6.text = "Serial Number"
$textbox6.Readonly = $True








foreach($objItem in $ds.Tables[0])
{
  $comboBox1.Items.add($objItem.BarcodeValue)
  
}

foreach($objitem2 in $ds2.Tables[0])
{
$comboBox2.Items.add($objitem2.GlobalMediaPoolId)
}


$Form1.Controls.Add($comboBox1)
$Form1.Controls.Add($comboBox2)
$Form1.Controls.Add($textbox1)
$Form1.Controls.Add($textbox2)
$Form1.Controls.Add($textbox3)
$Form1.Controls.Add($textbox4)
$Form1.Controls.Add($textbox5)
$Form1.Controls.Add($textbox6)

$ComboBox1_SelectedIndexChanged={
$textbox1.text = $ComboBox1.Text

If ($ComboBox1.text -eq "" ) {$TextBox1.text = "N/A"}

    ELSE
{

$cb1val = $Combobox1.Text

$Query3 = "select media.BarcodeValue, media.SlotNum, media.MediaId, gmedia.MediaPoolId from tbl_MM_Global_ArchiveMedia gmedia  inner join tbl_MM_Media media on gmedia.MediaId = media.GlobalMediaId where media.BarcodeValue = '$cb1val' "

$conn3=new-object System.Data.SqlClient.SQLConnection
$conn3.ConnectionString=$ConnectionString
$conn3.Open()
$cmd3=new-object system.Data.SqlClient.SqlCommand($Query3,$conn3)
$cmd3.CommandTimeout=$QueryTimeout
$ds3=New-Object system.Data.DataSet
$da3=New-Object system.Data.SqlClient.SqlDataAdapter($cmd3)
[void]$da3.fill($ds3) 
$conn3.Close()




foreach($objitem3 in $ds3.Tables[0])
 {$Textbox2.text = $objitem3.GlobalMediaPoolId

$textbox2.Text = $objitem3.MediaPoolId
$textbox3.Text = $objitem3.MediaId
$textbox4.Text = $objitem3.SlotNum 

 
 }

}

}


$ComboBox2_SelectedIndexChanged={

$cb2val = $Combobox2.Text

$Query4 = "select library.ProductId, library.SerialNo, library.LibraryId, mpool.Name, mpool.MediaPoolId, mpool.GlobalMediaPoolId
from tbl_MM_MediaPool mpool
    inner join tbl_MM_Library library
        on mpool.LibraryId = library.LibraryId
where mpool.Name = 'Free Media Pool' AND  mpool.GlobalMediaPoolId = '$cb2val'"


$conn4=new-object System.Data.SqlClient.SQLConnection
$conn4.ConnectionString=$ConnectionString
$conn4.Open()
$cmd4=new-object system.Data.SqlClient.SqlCommand($Query4,$conn4)
$cmd4.CommandTimeout=$QueryTimeout
$ds4=New-Object system.Data.DataSet
$da4=New-Object system.Data.SqlClient.SqlDataAdapter($cmd4)
[void]$da4.fill($ds4) 
$conn4.Close()



foreach($objitem4 in $ds4.Tables[0])
 {$Textbox5.text = $objitem4.ProductID
  $Textbox6.text = $objitem4.SerialNo
 
 
 
 
 
 }










}






$ComboBox1.add_SelectedIndexChanged($ComboBox1_SelectedIndexChanged)
$ComboBox2.add_SelectedIndexChanged($ComboBox2_SelectedIndexChanged)








$Button = New-Object System.Windows.Forms.Button
$Button.Location = New-Object System.Drawing.Point(400, 400)
$Button.Size = New-Object System.Drawing.Size(98, 23)
$Button.Text = "Change"
$Button.add_Click({


$mediaID = $objitem3.MediaId
$medpoolid = $objitem3.MediaPoolId

[void][System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’)
$result = [Microsoft.VisualBasic.Interaction]::MsgBox(“Do you want to move $cb1val from
$medpoolid
Mediapool to 
$cb2val
”, ‘YesNoCancel,Question’, “Respond please”)
switch ($result) {

‘No’ { Write-Host "User Selected Not to update record" }
‘Cancel’ { Write-Host “Canceled by user” }
‘Yes’ { 

Write-Host "yes" 

Write-host "updating $cb1val to MediapoolId $medpoolid"
Write-Host " $mediaID"




$Query99 = "update tbl_MM_Global_ArchiveMedia
set MediaPoolId = '$medpoolid'
where MediaId = '$mediaID'"
Write-Host $query99

$conn99=new-object System.Data.SqlClient.SQLConnection
$conn99.ConnectionString=$ConnectionString
$conn99.Open()
$cmd99=new-object system.Data.SqlClient.SqlCommand($Query99,$conn99)
$cmd99.CommandTimeout=$QueryTimeout
$ds99=New-Object system.Data.DataSet
$da99=New-Object system.Data.SqlClient.SqlDataAdapter($cmd99)
[void]$da99.fill($ds99) 
$conn99.Close()
$ds99.Tables[0]





}





}




})
$Form1.Controls.Add($Button)

$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(70, 90)
$label.Size = New-Object System.Drawing.Size(98, 23)
$label.Text = ""
$Form1.Controls.Add($label)

[void]$form1.showdialog()

Comments

Popular posts from this blog

OSX Mavericks \ Yosemite on a T520 - i7 16GB 256GB SSD

VMware View 2-factor authentication with Google Authenticator - Part Three

VMware View 2-factor authentication with Google Authenticator - Part Four