Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

There's a long preamble to my actual question because I think the story of how I got to the question is more interesting than the question itself. If you disagree, you're welcome to skip it.

My ultimate goal (for those wishing to know the XY Problem) is to automate a report of database activity. We're using MS SQL Server at $work, and it has gobs of SQL Server Agent jobs set up to run, and once in a while we'd like to know which ones are running.

When I asked our dynamite DBA how to get the list manually, she directed me to the stored procedure "msdb.dbo.sp_help_job". Getting the results of that via trusty DBD::Sybase proved difficult. It's also not straight forward to use the stored procedure as a subquery that I could just SELECT out of.

The best resource I found for how to do that anyway was How to share data between stored procedures. I wanted to use the INSERT-EXEC method, but it can't be nested, and "msdb.dbo.sp_help_job" is using it already.

I looked into the code for "msdb.dbo.sp_help_job" and found that under everything it calls "master.dbo.xp_sqlagent_enum_jobs", which is an undocumented call to a DLL. It turns out that information about running jobs is not in the system tables (where I'd expect it). Instead, it's cached, and nothing is written to tables you can query until the job is finished. That's why it winds up having to go to some non-SQL to get information about them.

Fortunately I had the code for the stored procedure that calls that non-SQL, so I could see how to get data out of it. I did a cut-and-paste of some code that creates a temporary table to collect results via INSERT-EXEC. That left just one more problem.

The job_id that it returns does not match the ones in msdb.dbo.sysjobs, which is where I need to go to get their names. What it gave me was "5c5d38dd01d7c04ab50504e417dbbebf", but what I needed was "DD385D5C-D701-4AC0-B505-04E417DBBEBF". Luckily I found an article that describes how to do the translation in SQL. After some head-scratching, I was able to come up with some more or less equivalent Perl.

I connect with DBI this way:

my $dbh = DBI->connect( "dbi:Sybase:server=$server", $u, $p, { RaiseError => 1 }, ); $dbh->do('use msdb');

The sub that does the work I've been writing about looks like this:

sub get_running_job_info { my ($dbh) = @_; $dbh->do( <<'END_TEMP_TABLE' ); CREATE TABLE #tmpy ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_defau +lt NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL ) END_TEMP_TABLE ; # It's entirely possible this will break if run on some other version +of MS SQL $dbh->do(q{INSERT #tmpy EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 +, 'sa'}); my $sql = 'SELECT job_id,current_step FROM #tmpy WHERE running != +0'; my @job_info = @{$dbh->selectall_arrayref( $sql, {Slice=>{}} )}; for my $info_ref ( @job_info ) { my $job_id = $info_ref->{job_id}; $job_id =~ tr/a-z/A-Z/; $job_id =~ s/^(..)(..)(..)(..)(..)(..)(..)(..)(.{4})(.{12})$/$ +4$3$2$1-$6$5-$8$7-$9-$10/; my $name_sql = "SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = '$jo +b_id'"; my ($job_name) = $dbh->selectrow_array( $name_sql ); $info_ref->{name} = $job_name // $info_ref->{job_id}; } return \@job_info; }

After all that my real question is how to improve this little part:

$job_id =~ tr/a-z/A-Z/; $job_id =~ s/^(..)(..)(..)(..)(..)(..)(..)(..)(.{4})(.{12})$/$ +4$3$2$1-$6$5-$8$7-$9-$10/;

That's the part that turns "5c5d38dd01d7c04ab50504e417dbbebf" into "DD385D5C-D701-4AC0-B505-04E417DBBEBF". I suspect that what I'm doing can be done better with pack/unpack, but I don't know a lot about those. I'd settle for a more comprehensible regular expression. I'm using Perl 5.10, so use those features if you want them.

I figure also that this translation has a good name that I don't know. I expect I might change the title of my post if someone can tell me what it is.

Thank you for your attention.


In reply to Rearrange hex digits. by kyle

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2024-03-28 22:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found