Click here to Skip to main content
14,304,866 members

SQLXAgent - Jobs for SQL Express - Part 6 of 6

Rate this:
5.00 (4 votes)
Please Sign up or sign in to vote.
5.00 (4 votes)
30 Sep 2017CPOL
Create and run jobs kinda like SQL Server Enterprise - Interesting Coding

Part 1 - Using SQLXAgent
Part 2 - Architecture and Design Decisions
Part 3 - The CSV and Excel Importer Code
Part 4 - Job Scheduling Code
Part 5 - How Packages Are Run
Part 6 - Interesting Coding, and Challenges (this article)

Introduction

This article is the 6th part of a larger article series describing the SQLXAgent utility. In this article, I'll be describing some of the interesting coding that was done. I don't anticipate this article being very long, because to be honest, after writing five parts for this article series, I'm kinda bored with the whole thing.

NOTE: Code snippets presented in the article may or may not reflect the absolutely latest and greatest version of the code. In the event that it does not exactly match the actual code, it will fairly close. Such is the nature of noticing issues while writing an article.

Creating the SQLXAgent Database From Scripts

The act of creating the SQLXAgent database required a bit more effort than I was expecting.

What's Happening?

I wanted to be able top report progress as the scripts were run, and this required an interface, which meant that the code that was responsible for creating the database had to be "notifiable".

Create Database progress

In order to make the term "notifiable" meaningful, I had to break up one big script into 35 discreet script files. In the process of testing this code, I discovered that SQL Server (Express?) does not support GO statements in batch queries. I also discovered that creating a store procedure via a batch query requires that the CREATE PROCEDURE line is the first non-comment line that can appear in the query.

The act of running the scripts was made somewhat less tedious because I included them as (non-copied) embedded resources, so I could process all queries dealing with tables, and then stored procedures by processing them in a loop. Here's the method that runs the stored procedure queries.

/// <summary>
/// Adds the stored procedures to the database one at a time
/// </summary>
private bool AddStoredProcs()
{
    bool result = true;
    // get a list of all the stored procedure resources
    string[] procs = this.assy.GetManifestResourceNames().Where(x=>x.IsLike("%SQLXAgent_SP_%")).ToArray();
    // set the appropriate connection string
    DBObject2.ConnectionString = this.sqlxConnStr;
    // loop through the list of stored proc queries
    foreach (string name in procs)
    {
        // do some completely unnecessary name formatting for the benefit of the UI.
        string[] parts = name.Split('.');
        string filename = string.Concat(parts[parts.Length-2],'.', parts[parts.Length-1]);
        string info = filename.Replace("SQLXAgent_", "").Replace("SP_", "sp_");
        // run the sql query
        if (!this.RunSQLCommandFromFile(filename, string.Concat("Create proc ", 
                                                                info.Substring(0, info.IndexOf(".")))))
        {
            result = false;
            break;
        }
    }
    return result;
}

Schedule Timing

When I first started testing the scheduling code, I observed that each subsequent run of a given job would start running a number of seconds beyond the expected start time. To remedy the situation, I recalculate the next run time at the beginning of the RunSchedule method, zeroing the seconds out along the way.

Application Settings

The problem was that I had multiple applications that required certain settings that were modified in SQLXAgent. The solution was to "map" the SQLXAgent.exe.config file so that I could load it from any application within the solution. Here's the code I used:

ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap() { ExeConfigFilename = this.ConfigFile };
this.AppConfig = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);

Even the application that owns the file - SQLXAgent - uses the code above (conveniently made available via a static class in the SQLXAgentSettings assembly). This kept things much simpler downstream.

XAML Stuff

The single most problematic XAML issue I encountered was binding radio buttons to a boolean property. After taking a couple fo stabs at (okay, about a dozen stabs is a more accurate number), I found this CodeProject tip/trick:

Binding radio Buttons to a Single Propert, by Volodymyr Trubachov.

History

  • 29 Sep 2017 - Initial publication.
     

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

#realJSOP
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
-- There are no messages in this forum --
Article
Posted 30 Sep 2017

Stats

4.1K views
28 downloads
3 bookmarked