MVP Systems - Support

Extending the SQLCMD execution method to work with any server

JohnV — Tue, 18 Nov 2008 15:35:16 GMT

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.

SQL Server Express install fails

JohnV — Tue, 30 Sep 2008 11:40:16 GMT

The JAMS Scheduler for Windows requires Microsoft's SQL Server. The JAMS Setup kits will install SQL Server Express Edition if SQL Server is not already installed. There is a known problem with Microsoft's SQL Server setup which causes the installation to fail if the SQL Server Native Client is already installed. To work around this problem you have to uninstall the SQL Server Native Client and then rerun the JAMS Setup.

This issue is documented is section 2.1 of this knowledgebase article:

http://support.microsoft.com/kb/910229/en-us

How do I get the Windows client working with an OpenVMS server?

JohnV — Wed, 17 Sep 2008 17:21:04 GMT

The JAMS Windows based client tools can connect to either a Windows or OpenVMS based JAMS Server. To connect to an OpenVMS Server, you must install and configure the JAMS OpenVMS Connector. This guide will help you install, configure and troubleshoot the JAMS OpenVMS Connector.

JAMS Services fail to start

JohnV — Fri, 05 Sep 2008 14:09:14 GMT

There is a potential problem with the Microsoft .NET Framework that can prevent the JAMS Services from starting. The .NET Framework verifies an executable images digital signature when a service starts. During this verification the Framework downloads a certificate revokation list from:

http://crl.microsoft.com/pki/crl/products/CSPCA.crl

If internet access is slow or access to this URL is blocked, the service may fail to start because the Service Control Manager times out waiting for the image to start.

Here is a link to a Microsoft Knowledgebase article that talks about the problem:

http://support.microsoft.com/kb/936707

This Knowledgebase article describes how to increase the service timeout value which could be a way to work around this issue:

http://support.microsoft.com/kb/922918

Options for specifying the JAMS Server in PowerShell

JohnV — Tue, 26 Aug 2008 12:49:59 GMT

If you're using the JAMS Snapin for PowerShell you've already found that every cmdlet has a "-Server" parameter that you can use to specify which JAMS Server should be used. However, you probably don't want to use it! The -Server parameter is fine when you want to explicitly specify the JAMS Server but it's easier to use one of the defaults.

Here's how a JAMS cmdlet determines which JAMS Server it should use:

Was -Server specified on the command line?
Is the $JAMSDefaultServer PowerShell variable defined?
Is our current default location a JAMS Provider drive?
Are we being executed as batch job by a JAMS Server?
Pick the first first JAMS Provider drive.
Throw an exception.

Setting $JAMSDefaultServer is what we usually use when working interactively. You might want to set that in your PowerShell profile but, be careful or you'll break option 4. Option 4, checking to see if the cmdlet is being executed as a JAMS job, may be the most interesting option. The idea is to make it easy to use JAMS cmdlets in your jobs without requiring any changes when the job moves from a test server to a production server.

So, how can you set $JAMSDefaultServer in your profile without breaking the default host option? Simple, just check for the $Host.PrivateData. When JAMS executes a job, it creates a HostName property in $Host.PrivateData so you can do this in your profile:

if ($Host.PrivateData.HostName -eq $null)
{
$JAMSDefaultServer = "MyJAMSServer"
}

Are there any issues with OpenVMS V8.3?

JohnV — Mon, 25 Aug 2008 21:16:04 GMT

There are a couple of issues with VMS V8.3 but, they’re minor and it’s likely that you won’t run into them. The fixes for these issues are in JAMS V4.2K (and later).

If it’s not convenient to upgrade JAMS, you can avoid the issues by doing two things, first:

Make sure that your SYSUAF file does not have global buffers enabled. You can check this by doing:

$ SET DEFAULT SYS$SYSTEM
$ DIR/FULL SYSUAF

Look at “File attributes:”, the last item is the number of global buffers. If that is greater than 0, set it to zero with:

$ SET FILE/NOGLOBAL SYSUAF

The second this is:

Check the value of your CTLPAGES setting by doing:

$ WRITE SYS$OUTPUT F$GETSYI(“CTLPAGES”)

Define the JAMS_SPARE_CTLPAGES logical name to be 40% of the value of CTLPAGES so, if CTLPAGES was 1056, you would do:

$ DEFINE/SYSTEM/EXEC JAMS_SPARE_CTLPAGES 422

Let us know if you have any questions or problems.

Getting started with PowerShell

BobS — Mon, 25 Aug 2008 17:33:18 GMT

PowerShell is Microsoft's new scripting and command line environment. It's a teriffic tool that will change the way that you manage your Windows machines. This article will help you get started using the JAMS PowerShell snap-in.

Before you can use the JAMS PowerShell snap-in, you have to have PowerShell. If you don't already have PowerShell, you can download it from:

http://www.Microsoft.com/PowerShell

This is also a good place to learn about PowerShell.

Once you have PowerShell installed, you have to make sure that you have to JAMS PowerShell snap-in installed. If you installed the JAMS Client before PowerShell you must reinstall the JAMS Client to get the PowerShell snap-in.

Before you can use a PowerShell snap-in, you have to add it to your PowerShell environment. You do that with the Add-PSSnapin cmdlet. Add the JAMS Snap-in like this:

PS C:\> Add-PSSnapin MVPSI.JAMS

To get a list of the commands in the JAMS Snapin, enter:

PS C:> Get-Command -PSSnapIn MVPSI.JAMS

To get help on any of those commands (or any other PowerShell command), try:

PS C:\> Get-Help the-command-you-want-help-with

How do I set the SMTP Server?

JohnV — Mon, 25 Aug 2008 10:44:56 GMT

The best place to specify the SMTP server is in the Machine.config file. This will apply to all applications on that machine which use the SmtpClient class (unless overridden). The Machine.config file is located in the C:\Windows\Microsoft.Net\Framework\v2.0.50727\Config directory. There is also a Machine.config.comments file in that directory which contains some documentation.

You can also put the SMTP configuration in the JAMSScheduler.exe.config file which is in the C:\Program Files\MVPSI\JAMS\Scheduler directory.

An example of specifying the SMTP configuration is (you use the same syntax in either file):

<system.net>
    <mailSettings>
      <smtp deliveryMethod="Network">
        <network defaultCredentials="false" port="25" host="your.host.name.com" />
      </smtp>
    </mailSettings>
</system.net>

There is also a simple setting in the Common.config file which looks like this:

The Common.config file is also located in C:\Program Files\MVPSI\JAMS\Scheduler.

How do I shrink the history file?

JohnV — Mon, 25 Aug 2008 10:40:48 GMT

Purging JAMS history should be completely automated. When JAMS is initially installed, a job named JAMS_PURGE is created which is scheduled to run every Sunday. This job purges the JAMS History and then does a CONVERT/RECLAIM on the HIST.DAT file to reclaim space from the deleted records.

You should verify that the JAMS_PURGE job is successfully running.

If JAMS_PURGE is running, it could be that you're keeping more history than you want. You can adjust the default values for the parameters to JAMS_PURGE to change the amount of history you keep.

It could also be that the HIST.DAT file grew so large because of an unusual event and the file is mostly free space. The JAMS_PURGE job won't shrink the physical size of the file. To shrink the file while preserving the history, do this:

$ MCR JAMS_EXE:JAMS_MASTER STOP SCHEDULE/NORESTART
$ SET DEFAULT JAMS_DATA:
$ ANALYZE/RMS/FDL/OUTPUT=HISTOPT.FDL HIST.DAT
$ EDIT/FDL/NOINTERACTIVE/ANALYSIS=HISTOPT.FDL HISTOPT.FDL
$ CONVERT/FDL=HISTOPT.FDL/STATS/NOSORT HIST.DAT HIST.DAT
$ PURGE HIST.DAT
$ MCR JAMS_EXE:JAMS_MASTER START SCHEDULE

Note that this make take a LONG time.

If you don't really care about the history and you just want to quickly shrink the file, do this:

$ MCR JAMS_EXE:JAMS_MASTER STOP SCHEDULE/NORESTART
$ SET DEFAULT JAMS_DATA:
$ DELETE HIST.DAT;*
$ CREATE/FDL=HIST.FDL HIST.DAT
$ MCR JAMS_EXE:JAMS_MASTER START SCHEDULE

This is quick but, IT WILL DELETE ALL OF YOUR HISTORY.