Phone: 866-259-JAMS Email: Support@MVPSI.com

MVP Systems - Support

Home Search Tags Glossary
Welcome Guest ( Login | Register )
Latest Additions Most Popular
Home » Knowledgebase » JAMS for Windows » Extending the SQLCMD execution method to work with any server

Extending the SQLCMD execution method to work with any server

Expand / Collapse
 
     

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.

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\SQLCMDSvr

Now, open the SQLCMDSvr Execution Method in the JAMS GUI, click the "Source" tab and change the "Job Module" to "ParseSqlSvr".  Click the "Execution" tab and remove the "-S (local)" from the Command string.  Save those changes.

Next, we need to create the ParsedSqlSvr macro that the Execution Method references in it's "Job Module".   Job template macros are kept in xml files.  The C:\Program Files\MVPSI\JAMS\Scheduler\BaseMacros.xml file contains the template macros that we ship with JAMS.  While it's possible to change the macros contained in BaseMacros.xml, we don't recommend it because that file is replaced every time a JAMS upgrade is installed.  Instead, create your own file to hold your custom macros.  Our CustomMacros.xml file contains this:

<?xml version="1.0" encoding="utf-8" ?>
<ArrayOfMacro xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Macro name="ParsedSqlSvr">
    <Code>
      <![CDATA[-- JAMS Parsed Sql Script
:Connect <<ServerName>>
<<JAMS.Source>>
]]>
    </Code>
  </Macro>
</ArrayOfMacro>

It's hard to see the ParsedSqlSvr macro because it's wrapped up in xml and a CDATA section, here is the actual source of the macro:

-- JAMS Parsed Sql Script
:Connect <<ServerName>>
<<JAMS.Source>>

It's a simple 3 line macro that is expanded when a job is submitted.  The only difference between this and the ParseSql macro that ships with JAMS is the addition of the ":Connect <<ServerName>>" line.  This line references the "ServerName" parameter and tells SQLCMD to connect to the specified SQL Server instance.

We have to tell JAMS to use the CustomMacros.xml file, we do that by entering the full path to the file in the "Template Library" field of any JAMS System definition which should use CustomMacros.xml.  The "Template Library" field is located under the "Source Options" tab in the System Definition.

We're done with the customizations and we're ready to put them to good use.  We can create jobs with an Execution Method of "SQLCMDSvr" entering SQL as the job's source.  We also have to make sure that the job has a parameter named "ServerName" which is where we specify the SQL Server and Instance when we submit the job. 


Tags:

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Tuesday, November 18, 2008
Last Modified By: JohnV
Type: HOWTO
Level: Intermediate
Article not rated yet.
Article has been viewed 340 times.
Options


Powered By InstantKB.NET 2.0.3 © 2009
Execution: 0.100. 15 queries. Compression Disabled.