Applies to
All ApexSQL console applications with Command line interfaces (CLI)
Summary
This article describes how to create a prompt window for ApexSQL tools using PowerShell in order to enter credential information, for example username and password.
Description
In the following example, we’ll create a prompt window, using a PowerShell script, for entering SQL Server database credentials for source and destination database that will be compared and synchronized, along with creating a timestamped HTML report and a timestamped output file.
To create the PowerShell script, I’ll refer you to the article, How to create a SQL Server logon dialog using PowerShell.
Once you’ve set up that script, to instantiate the dialog for accepting user input, we’ll need to take that input and pass it to the console application of ApexSQL Diff, in our example, via command line interface (aka CLI) switches
For our example, we called the dialog two times to accept input for two data sources, the Source and Destination data sources for a database comparison.
#create variables for our Source data source connection $SourceLogin = Show-InputForm "Source connection" "SQL Server" "Username" "Password*" "Database" $sourceServer = $SourceLogin [1] $sourceUser = $SourceLogin [2] $sourcePassword = $SourceLogin [3] $sourceDatabase = $SourceLogin [4] #create variables for our Target data source connection $DestinationLogin = Show-InputForm "Source connection" "SQL Server" "Username" "Password*" "Database" $destinationServer = $DestinationLogin [1] $destinationUser = $DestinationLogin [2] $destinationPassword = $DestinationLogin [3] $destinationDatabase = $DestinationLogin [4]
In order to pass these variable values entered in prompt windows to ApexSQL Diff, we’ll define timestamped variable, location of the application, and its CLI switches:
#defining timestamp, ApexSQL Diff location, CLI switches $timestamp = (Get-Date -Format "MMddyyyy_HHmmss") $diffLocation = "ApexSQLDiff" $diffSwitches = "/server1:$sourceServer /user1:$sourceUser /password1:$sourcePassword /database1:$sourceDatabase" + "/server2:$destinationServer /user2:$destinationUser /password2:$destinationPassword /database2:$destinationDatabase" + "/pr:""SchemaSync.axds"" /ot:html /on:""SchemaDifferenceReport_$today .html""/out:""Output_$today.txt"" /sync /v"
The next line will call the application for execution with entered values from the prompt windows:
(Invoke-Expression ("& `"" + $diffLocation +"`" " +$diffSwitches))
The last line will write the return code into the timestamped output file:
ApexSQL Diff return code: $lastExitCode"; >> "Output_$today.txt"
To learn more about return error codes, learn more from article on this here. Also, you can learn more from the following article about ways of handling database/login credentials during automated execution via the CLI.
If you want to run the whole process unattended, check out four ways of scheduling ApexSQL tools.
FAQs
Q: How can I change labels?
A: In provided example, the first variable contains all label names under the quotation marks, after the Show-InputForm function:
$sourceLogin = Show-InputForm “Source connection” “SQL Server” “Username” “Password*” “Database”
The first name will be the name of the prompt window and all next ones will be used for names above each text box. Based on the number of defined names, you’ll have to define the exact number of variables for each text box afterwards.
Q: Will these values override connection settings set in the saved project file, by providing CLI switches with prompt variables?
A: Yes. Connection CLI switches will override the specified connection settings in the saved project file.
Downloads
Please download the script(s) associated with this article on our GitHub repository
Please contact us for any problems or questions with the scripts.
