Extending the SQLCMD execution method to work with any server
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>>
Add Your Comments