The SQLCMD Execution Method executes SQL commands against the default SQL Server instance on the machine where the job is running. This article describes how you can create a new Execution Method which gets the SQL Server machine and instance from one of the job's parameters.
Note: The SQLCMDServer execution method is included in JAMS V4.6.28 or higher
We'll start by copying the SQLCMD Execution Method to a new Execution Method using the JAMS PowerShell snap-in and the following command (make sure you replace "YourServer" with the name of your JAMS Server):
PS C:\> copy JAMS::YourServer\Methods\SQLCMD JAMS::YourServer\Methods\SQLCMDServer
Now, open the SQLCMDServer Execution Method in the JAMS GUI, click the "Execution" tab and remove the "-S (local)" from the Command string. Save those changes and the SQLCMDServer execution method is ready to go.
With the SQLCMDServer execution method, you have to connect to a SQL Server instance in your job's source. You do that with the ":connect" command (don't forget the leading colon). You can use a specific Server name and instance or get them from a JAMS Parameter or Variable. Here are some examples:
:connect MySqlServer
:connect MySqlServer\SQLExpress
:connect <<SqlInstance>>
:connect MySqlServer -U Joe -P <<PasswordForJoe>>