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
Post a Comment