Using sqlcmd with a batch scheduler like Control-M

A recent project that I worked on required that I run some sql scripts (targeting sql server) in Control-M. The obvious thing to do then is to save the scripts in a directory on the same host as the agent and use sqlcmd to run the script and send the output to a file. Looking at the usage for sqlcmd showed that there is a -o flag to specify the output file. So far, so good. I configured my job and ran it, getting a successful execution which seemed odd as I had deliberately altered the SQL statement so it would fail, to validate that the job would report errors. A look in the sysout displayed no error messages. After a bit of head scratching, I had a look in the output file (configured by the -o flag) and found the error message in there. This is not very useful when you are trying to automate something, so I needed to find another way. There is no option with sqlcmd to send output and errors to different locations, so you need to use DOS to do so. The way to do this is to direct stdout to the output file and stderr to the screen (which in this case is written to the sysout stored by Control-M) The way to do this is to have a command like

sqlcmd -E -S <some server> -d <some database> -i"<some sql file>" -t600 -W -I -b -r0 1>"<some output file>

where the

-i"<some sql file>"

will send the query output to the file specified and all other output ( from the -r0   flags) will go to the screen.

Note also the -b flag - this will stop the script from running when there is an error rather than continuing and will result in sqlcmd ending non 0 - and hence Control-M will know there is a error.

DOS script to move files when a file is not open by another process.

In a previous post I showed how you can test to see if a file is open in a Powershell script. If you don't have, or don't want, to use Powershell you can use the following in a DOS shell:

FOR /F "delims=" %i IN ('DIR /b "\\some path\*.ext" > do (
(( CALL ) >> "\\some path\%i" ) 2>nul && ( MOVE "\\some path\%i" "\\another path\%i" ) || ( ECHO the file is being created ) )

The for loop is important, even if you are looking for a specific file because the part of the command

( CALL ) >> "\\some path\%i" ) 2>nul

is attempting to write to the file. If the file is present and open by another process, this will fail. If the file is not present, it will be created as an empty file (which is why you want the FOR loop as only files that exist will be worked on). If the file is present, this command exits successful and the shell will then execute the MOVE command (or anything else you put between the "( )"), otherwise the shell will execute the ECHO. If the FOR is looking for *.*, this script will test directories, however they will always fail as you cant write text to a directory so the script will print out the text from the ECHO which does not cause issues.

Note, this command will always exit with error level 0, so if you are using this a scheduler you need to look for errors in the output (in Control-M, that is an ON statement where CODE=*, STATEMENT="*cannot*, etc)

Powershell script to move files when a file is not open by another process.

Recently I wrote a powershell script which was required to move any file found in a directory and move it into another directory (the script is called by a Control-M cyclic job that run every 5 mins to transfer files to/from a manage file transfer utility) One of the considerations with this is to ensure that the files being moved are not open or being written to when attempting to move them. I also found I needed to handle the case where the destination already contained a file with the same name, and that file was open. Move-Item with the -force flag is not enough as this will fail if the file is open for editing.

How to do this - you need to test to see if the file is open by another process. The following code fragment is an example of this:

foreach ($fileName in $inPath)
{
    if(Test-Path $fileName)
    {
        try { [IO.File]::OpenWrite($fileName).close() }
        catch 
        { 
            Write-Host "the file is open" 
            continue
        }
     }
     Move-Item $fileName $destination -force
}   

So what is happening here is that the script is iterating through each file in the input directory. It then tests to see if the file can be read by the current process. Next, the script will try to open the file for editing - if this fails the current iteration of the loop will be exited, else the file will be moved.

This example is only testing for the file existing and being open in the destination folder - you also need  to test to see if the file in the input location is being written to... the code for which is basically the same.

EDIT: For a DOS version, go to this post

Invoke a control-m job when another job has failed n times

On the Linkedin Control-M group there have been a couple of questions on how to trigger a job, or raise an error, once a cyclic job had failed n number of times. I'm not too keen on such a pattern - if a job fails once it should stay failed until the root cause is fixed. If an error is to be expected, then the process called by the job should gracefully handle that case, and fail for all others. However, if you have a valid case to do this, then here is a short guide on implementing same. This works on Control-M version 7.

1) Create the cyclic job that could fail. In this job, create an ON statement where the CODE=NOTOK, DO <condition> (to be used by the second job below), and DO rerun.

2) Create a job that will catch the error. This is another cyclic job that is triggered by the condition posted from job 1's ON statement and will also delete this condition on completion (so it will only execute when job 1 runs, fails and posts the condition). For this job, put the following command line

echo ERROR_RUN_%%RUNCOUNT.

(Should work for Windows and Unix hosts) On the Steps tab of this job, create an ON statement where CODE=*ERROR_RUN_3* DO NOTOK, 

What will happen here is that when this job is run 3 times, it will fail due to the DO NOTOK (change the 3 for however many cycle you need) You can then use this job to raise an Shout or trigger of some other process you require.

3) To extend this solution, by making it possible to re-run job 2, you will need to change the command line in job 2 so that you can pick up the error on every n'th run. One way to do this is to alter the command to print out the run count divided by the number of runs to fail on. In many programming languages this is the result of the moduls operator. For example, if you are running on a windows host you could use the following as job 2 command line:

powershell -command "$result=%1 %% 3; $themsg='ERROR_RUN_' + $result; Write-Host $themsg"

(you can do something similar in UNIX as many shells have a built in modulus operator, or you could run a AWK script to print out the result). Add on the steps tab an entry PARM1=%%RUNCOUNT,  which becomes the run count (%1) on the command line at run-time. The Powershell modulus operator % needs to be escaped by another % which is why the command contains %% due to the way that Control-M parses windows command lines.


Then alter the ON statement to DO NOTOK (or what ever other action to take) when CODE=ERROR_RUN_0  (because you want to error on every 3rd run, as the result of 3 modulus 3 is 0) and now you have a job that will fail on every 3rd run.

A worked example of autocomplete for a ASP.NET MVC application using HTML

In my previous post I noted that a autocomplete could be implemented using a HTML <datalist>, so here is an example of implementing this. The approach I have taken is to create a partial view that contains the search text field and the <datalist>. There is a corresponding controller action that is called by the page when it is loaded. I left the code to create the form and the submit button in the parent page basically so I could toggle between this solution and the jQuery version.

So, the partial view looks like

@model List<string>
<input list="searchString" name="searchString">
<datalist id="searchString">
    @{
        foreach (string mName in Model)
        {
            <option value = "@mName" ></option>
        }
    }
</datalist>

The Model used by this partial view is defined in the controller. 

[ChildActionOnly]
public ActionResult listMemberNames( )
{
     List<Member> memberList = db.Members.ToList();
     List<string> mbrNames = new List<string>();

     foreach(Member mbr in memberList)
     {
          mbrNames.Add(mbr.MemberName);
     }
     mbrNames.sort();
     return PartialView("_memberNamesList", mbrNames);
}

The performance of this method could be improved by querying just the unique member names from the database rather than using entity framework to retrieve all the member information (db.Members.ToList()),then iterating this list to get the unique list of names which is then sorted. In this case member name is made up of three columns, memberSalutation + memberFirstName + memberLastName so I have not bothered to do that optimization for this example.
 
Finally, in the parent view:

@using (Html.BeginForm("Index", "Members", FormMethod.Get))
{
    <p>
        @*Find by name: @Html.TextBox("searchString", ViewBag.CurrentFilter as String, new { data_autocomplete_source = Url.Action("QuickSearchMembers", "Members") })*@
        @Html.Action("listMemberNames")
        <input type="submit" value="Search" />
    </p>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
} 

Run the app, and the rendered HTML for this page contains the markup we want:

<h2>Members</h2>
<form action="/Members" method="get">    <p>
         
        <input list="searchString" name="searchString">
<datalist id="searchString">
            <option value = " The Presedant" ></option>
            <option value = "Mrs Jane Doe" ></option>
            <option value = "Mr John Doe" ></option>
            <option value = "Mrs Member1 Mbr1" ></option>
            <option value = "Mrs Member2 Mbr2" ></option>
            <option value = "Mrs Member3 Mbr3" ></option>
            <option value = "Mrs Member4 Mbr4" ></option>
            <option value = "Mrs Member5 Mbr5" ></option>
            <option value = "Mrs Member6 Mbr6" ></option>
            <option value = "Mrs Member7 Mbr7" ></option>
            <option value = "Mrs Member8 Mbr8" ></option>
            <option value = "Mrs Member9 Mbr9" ></option>

</datalist>

        <input type="submit" value="Search" />
    </p>
<p>

 

A worked example of autocomplete for a ASP.NET MVC application using jQuery

This post contains a worked example of a autocomplete text box for a ASP.NET MVC application along with some comments on the implementation.

The first step is to add the jQuery library for jQuery.UI.Combined by adding it to the project using Nuget

Once that has been added, ensure that the scripts are served up by adding this code to RegisterBundles(BundleCollection bundles) in  \App_Start\BundleConfig.cs

 bundles.Add(new ScriptBundle("~/bundles/jqueryui").Include("~/Scripts/jquery-ui-{version}.js"));

With the library included in the build, add a include for autocomplete.css and jqueryui into the .cshtml file where it is required. In my project I added it to _Layout.cshtml

<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - Toy Library</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/jqueryui")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
    <link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
    <link href="~/Content/themes/base/autocomplete.css" rel="stylesheet" type="text/css" />
</head>

Next is to add a method to the controller that will return a Json list of the search terms you need. This example is a little longer that what you would normally need because the search string is made up of two properties of the model - ie the code below to gets two lists then concatenate them together to create the final list.

        //return a list of member names which contain the searchExpr
        //for use by the autocomplete search box for members data_autocomplete_source = Url.Action("QuickSearchMembers", "Members")
        public ActionResult QuickSearchMembers( string term )
        {
            List<Member> memberList = db.Members.Where(a => a.FirstName.Contains(term)).ToList();
            List<Member> memberListLast = db.Members.Where(b => b.LastName.Contains(term)).ToList();

            //merge together.
            if (memberList.Count > 0) { memberList.Concat(memberListLast); }
            else { memberList = (List<Member>)memberListLast; }
           
            //return only 5 items to reduce bandwidth
            if (memberList.Count > 5)
            {
                memberList.RemoveRange(5, memberList.Count - 5);
            }
            var memberNames = memberList.Select(b => new { value = b.MemberName });
            return Json(memberNames, JsonRequestBehavior.AllowGet);
        }

Another feature of this code is that the returned list is limited to 5 values. This is done to reduce the size of the Json packet sent on each request if there is the potential to have a large amount of records retruned (I have some more comments on this below).

In the .cshtml page where this will be used, add the following mark-up to display the search text box

@using (Html.BeginForm("Index", "Members", FormMethod.Get))
{
    <p>
        Find by name: @Html.TextBox("searchString", ViewBag.CurrentFilter as String, new { data_autocomplete_source = Url.Action("QuickSearchMembers", "Members")})
        <input type="submit" value="Search" />
    </p>
}

Note the name of the first argument to Url.Action - this needs to match the name of the method in the jQuery function that is also added to the page:

<!-- below is to autocomplete search text box -->
<script type="text/javascript" language="javascript">
       $(document).ready(function () {
            $("#searchString").autocomplete({
                source: '@Url.Action("QuickSearchMembers", "Members")'
            });
        })
</script>

 To change the styling of the suggested values, you can edit the .css file /Content/themes/base/autocomplete.css

.ui-autocomplete {
    position: absolute;
    top: 0;
    left: 0;
    cursor: default;
    background-color: lightgray;
}

For example, I added an attribute to make the background light grey so the suggestions are easier to read

 So, how does this work? When the user changes the content of the search text box the .autocomplete jQuery method is called, which will then call the public ActionResult QuickSearchMembers( string term )  method from the controller returning a list of suggestions that is displayed on the screen. Due to this, this technique can result in a lot of network traffic as each character entered (or removed) from the text box will result in a call to the server and the response of a Json payload - which is why I think it is a good idea to limit the number of items returned in the Json packet.

An alternative method is to make use of the HTML <datalist> tag and a list of data returned from the controller. see http://www.w3schools.com/tags/tag_datalist.asp for some additional details. This method would reduce network requests to once per page request. However, this would only work if the number of possible suggestions is small otherwise page size would become large and page load times would suffer. (edit - see my next post on the implementation of this method)

One drawback to both methods is that additional data is server up from the web server, which mean additional load and for a cloud hosted application additional cost. So for a high traffic site, you would need to ask if the autocomplete feature is really worth the additional expense.

A salutation drop down with validation in ASP.net MVC

Here is one way to have a drop down with a salutation list in ASP.net MVC. This technique will implement client and server side validation, and is easy to update at a later stage to get the list of data attributes from a dynamic source. As the model data is in a class, it can be associated to many views.

Step 1, Create a model to contain the list of values. 

 In this example the code is as follows:

    public class SalutationsList
    {
        public static IEnumerable<SelectListItem> GetSalutationsList()
        {
            IList<SelectListItem> items = new List<SelectListItem>
            {
                new SelectListItem{Text = "", Value = ""},
                new SelectListItem{Text = "Mr", Value = "Mr"},
                new SelectListItem{Text = "Mrs", Value = "Mrs"},
                new SelectListItem{Text = "Ms", Value = "Ms"},
                new SelectListItem{Text = "Sir", Value = "Sir"},
                new SelectListItem{Text = "Esq", Value = "Esq"},
                new SelectListItem{Text = "Dr", Value = "Dr"}
            };
            return items;
        }
    }

The key point to note is that this class has a static method that returns  IEnumerable<SelectListItem>. The method is static as it will not change for the life of the application, so there is no need for it to be instantiated each time it is used which will save a bit of CPU and memory - important for a busy site.

Step 2, Create the custom validation method.

For this example, you can use the following code:

    public class SalutationsListValidateAttribute : ValidationAttribute
    {
        protected override ValidationResult IsValid(object value, ValidationContext validationContext)
        {
            string theValue = "";  //the deafult is a empty string.
            if (value != null)
            {
                theValue = value.ToString();
            }

            foreach (SelectListItem salutationValue    in SalutationsList.GetSalutationsList())
            {
                if (salutationValue.Text.ToString() == theValue)
                {
                    return ValidationResult.Success;
                }
            }
            return new ValidationResult("The selected value is not in the list of accepted salutations.");
        }
    }

This will ensure that the model will validate against the list of values in the SalutationsList class. If the implementation of  SalutationsList changes to dynamically read data from a data source, this validation code will still be valid.

Step 3, Add the salutation attribute to the model it is used with.

The code for this example is

        [SalutationsListValidate]
        public string Salutation { get; set; } //class SalutationList

Step 4, Add (Update) code to your views.

 The code in your view will look something like:

        <div class="form-group">
            @Html.LabelFor(model => model.Salutation, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(x => Model.Salutation, ToyLibrary.Models.SalutationsList.GetSalutationsList(), htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.Salutation, "", new { @class = "text-danger" })
            </div>
        </div>

Summary.

 By using this approach, the validation will be applied on client and server side. This is handy, if someone tries to over post, then the field will be validated. Also, this will validate any updates you try to do as part of database initialization using entity framework. That said, you will be serving up some unobtrusive java script to validate on the client side which may be a consideration.

Displaying a dynamic chart in ASP.NET MVC

In this post I will show you two options for creating a chart in ASP.NET MVC application using data from a model. These examples were run in the community edition of Visual Studio with ASP.NET version 5. For these examples I am using the below model called ChartModel which returns a list of objects called DataPoints. In this case the model is hard coding the DataPoints object, but this would be substituted for a database call to retrieve a list of data to be displayed for a real application.

    public class DataPoints

    {

        public DataPoints(string nameTxt, int dataVal, string commentTxt )

        {

            nameText = nameTxt;

            dataValue = dataVal;

            commentText = commentTxt;

        }

        public string nameText { get; set; }

        public int dataValue { get; set; }

        public string commentText { get; set; }

    }

    public class ChartModel

    {

        public List<DataPoints> getDataPoints()

        {

            List<DataPoints> dataPointsList = new List<DataPoints>();

            dataPointsList.Add(new DataPoints("Cat", 10, "Meow"));

            dataPointsList.Add(new DataPoints("Fish", 8, "Blooop"));

            dataPointsList.Add(new DataPoints("Cow", 5, "Moo"));

            dataPointsList.Add(new DataPoints("Sheep", 8, "Baaaa"));

            dataPointsList.Add(new DataPoints("Frog", 2, "Glumph"));

            return dataPointsList;

        }

    }

Option 1, using the ASP.NET chart control.

ASP.NET has a built in chart control which provides basic charting functionality. This control works by taking a data set and creating an image file. This image is then sent to the clients browser. More details on this control can be found http://www.asp.net/web-pages/overview/data/7-displaying-data-in-a-chart

For this example, use the following is the controller code. This will create a bar chart showing the nameText and dataValue fields of the DataPoints objects:

        public ActionResult GetChartImage()

        {

            var chartData = new ChartModel();

            var dataPointsList = chartData.getDataPoints();

           

            var key = new Chart(300, 300, ChartTheme.Blue)

                .AddTitle("ASP.NET Chart")

                .SetXAxis("Animal")

                .SetYAxis("Count")

                .AddSeries(

                chartType: "Bar",

                name: "Animals",

                axisLabel: "axis",

                xField: "nameText",

                yFields: "dataValue",

                xValue: dataPointsList,

                yValues: dataPointsList);

            return File(key.ToWebImage().GetBytes(), "image/png");

        }

This is the view code:

<h1>Chart example - ASP.NET chart </h1>

<img src="@Url.Action("GetChartImage")" />

 

which looks like:

Option 2, using Google Charts

Google Developers provides a comprehensive and powerful charting library that is free to use. More details can be found https://google-developers.appspot.com/chart/interactive/docs/ Google charts uses some JavaScript libraries to draw the chart in the clients browser.

This library can be combined with a controller that returns Json to create a dynamic chart. Some details on this can be found http://www.c-sharpcorner.com/UploadFile/4d9083/how-to-create-google-charts-with-mvc-4/ which the below example is based on.

First is the controller code to return the data set in Json format:

        public JsonResult GetJsonChartData()

        {

            var chartData = new ChartModel();

            return Json(chartData.getDataPoints(), JsonRequestBehavior.AllowGet);

        }

 

Next, the following code should be added to the view:

<h1>Chart example - Google chart </h1>

<div id="googlechartdiv" style="width: 300px; height: 300px;">  </div>

This sets up a div that the Google chart library will draw the chart in. Next add the JavaScript scripts to the view that will get the Json data from the controller action and render it into the div:

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">google.load("visualization", "1", { packages: ["corechart"] });</script>

    <script type="text/javascript">

        $(function () {

            $.ajax({

                type: 'POST',

                dataType: 'json',

                contentType: 'application/json',

                url: '@Url.Action("GetJsonChartData")',

                    data: '{}',

                    success: function (chartsdata) {

                        // Callback that creates and populates a data table,

                        // instantiates the pie chart, passes in the data and

                        // draws it.

                        var data = new google.visualization.DataTable();

                        data.addColumn('string', 'nameText');

                        data.addColumn('number', 'dataValue');

                        for (var i = 0; i < chartsdata.length; i++)

                        {

                            data.addRow([chartsdata[i].nameText, chartsdata[i].dataValue]);

                        }

                        // Instantiate and draw our chart, passing in some options

                        var chart = new google.visualization.BarChart(document.getElementById('googlechartdiv'));

                        chart.draw(data,

                          {

                              title: "Google Chart in Asp.net",

                              position: "top",

                              fontsize: "16px",

                              chartArea: { width: '60%' },

                              vAxis: { title: 'Animal' },

                              hAxis: { title: 'Count' },

                              legend: { position: "none" },

                          });

                    },

                    error: function () {

                        alert("Error loading data! Please try again.");

                    }

                });

            })

</script> 

which results in a chart that looks like:

Comments

Both options enable data to be displayed in a chart that is dynamically created at run time. Both options allow for a large variety of chart types to be implemented and allow a number of customization. The Google Developer option will – depending on the amount of data points – send less data from the server hosting your application to the client which could be a consideration in choosing the approach to take. However, Google Developer could change their implementation at any time that would brake you application, whereas the ASP.NET charting option would only change with a re-compile of your project.