Powershell: SQLCMD, substituting variable for -serverinstance parameter

[markrdickinson]

(code attached as file)

code.txt

(1.58 KiB) Downloaded 94 times

I am trying to get the results of the first DROPDOWN box to be able to be used as a variable, and used in place of INSTANCE_NAME in the second DROPDOWN box.

Essentially, the SELECT query from the second box needs to be run against the MASTER database in the SLQ_INSTANCE that the first DROPDOWN box results in once a choice has been made.

For example...

Lets say the server I choose from the first DropDown box is called MySQLServer01...

then I need the -serverinstance parameter in the second DropDown to be read like this... -serverinstance MySQLServer01

I believe I need to set this up as a variable, something like -serverinstance $ChoiceFromFirstDropdown.

I've tried a few variations and can't seem to get it right. Any suggestions?

Thanks in advance!

[jvierra]

Yu need to do that in an event by referencing the selectedIndex of the drop down or by referencing the value of the drop down.

This would be best if you posted your PSF file as a working example. Nothing in your code specifies any events. All work in forms is done in the form and control events.

[markrdickinson]

@jvierra...

The process in it's entirety is attached...

full code.txt

(5.15 KiB) Downloaded 109 times

. Anything proprietary has been masked (not much as it turns out).

HOW IT WORKS...
The first drop down lets you choose a list of SQL instances
The second drop down is supposed to provide a list of databases from the instance chosen in the first drop down
The third box lets you type in a UNC path to where the backup should be placed
The fourth box lets you specify the name for the database backup
The fifth box lets you specify the email address the alert is to be sent to, once the backup completes
The OK button of course kicks off the process

THE PROBLEM:
If I choose, for example, MSSQLSERVER01\PROD from the list in DropDown box 1, and manually replace the -serverinstance SQLSERVERNAME (for example... -serverinstance SQLSERVERNAME becomes -serverinstance MSSQLSERVER01\PROD), the process works perfectly.

As stated, however, my issue is getting the value returned from the 1st DropDown (in this example, MSSQLSERVER01\PROD) to be used in place of the -serverinstance SQLSERVERNAME parameter in the 2nd DropDown box (in this example, -serverinstance MSSQLSERVER01\PROD), so it pulls the list of databases available on only that server (in this example, MSSQLSERVER01\PROD).

[jvierra]

As I noted before. You have to load the second list from an event that can read the first events selection.

Since we don't have your database there is no ways to demonstrate this easily.

Start by learning about how forms are event driven.

Here are some links that will help you understand how this works.
https://info.sapien.com/index.php/guis/ ... nistrators

Here are many examples of how to use controls and control events:
https://info.sapien.com/index.php/guis/gui-controls

[jvierra]

Here is an example of how to work with this:

Code: Select all

$paramInvokeSqlcmd = @{
    Query = 'select * from VIEW_NAME order by instance_name'
    Database = 'DATABASE_NAME'
    ServerInstance = 'SQLSERVERNAME'
}
$wksList= invoke-sqlcmd @paramInvokeSqlcmd

$DropDownBox = New-Object System.Windows.Forms.ComboBox
$DropDownBox.Location = New-Object System.Drawing.Size(10,40)
$DropDownBox.Size = New-Object System.Drawing.Size(260,20)
$DropDownBox.DropDownHeight = 200
$Form.Controls.Add($DropDownBox)
$DropDownBox.Items.AddRange($wks.Instance_Name)
$DropDownBox_SelectectionChanged = {
    [System.Windows.Forms.MessageBox]::Show($this.Text)
}
$DropDownBox.add_SelectedIndexChanged($DropDownBox_SelectectionChanged)