|Problems? Is your data what you think it is?|
Rearrange hex digits.by kyle (Abbot)
|on May 24, 2010 at 14:37 UTC||Need Help??|
kyle has asked for the wisdom of the Perl Monks concerning the following question:
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:
The sub that does the work I've been writing about looks like this:
After all that my real question is how to improve this little part:
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.