Capturing SQL Agent Job Information

The following query will pull selected data from the sp_help_job stored procedure and put it into the requested table:

USE msdb

IF OBJECT_ID('tempdb..#TmpJobs') IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET('sqloledb'
, 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')

SELECT originating_server, name, enabled, owner, last_run_date, last_run_outcome, next_run_date
INTO Database..Table
FROM #TmpJobs

This comes in very handy if you want to use a DTS package to grab this data from multiple servers and populate one table that you can query. I am using this in correlation with a truncate command for the table so that a select * will get me only the current data, since historical information is already kept in each server's individual msdb database.

Note: In SQL2005 you will have to have the sp_configure parameter Ad Hoc Distributed Queries enabled.

Find Failed Jobs in MSDB Job Logs

This will get you a short list of the jobs that have failed and are still in sysjobhistory:

select a.server, b.name, a.message, a.run_date
from sysjobs b JOIN sysjobhistory a
on a.step_id = 0
and a.run_status = 0
order by b.name

You can run this query from a remote server via a DTS package against many servers to save some point-and-click time. Have all the data compiled into one monitoring database and you have a quick list you can scan through rather than touching every server in yoru network.

Insert Data Into One Table If Criteria Matches Data in a Different Table

I know this sounds very simplistic, but you'd be surprised how many people simply have no clue how to do this:

You want to update Column1 in TableA to match Column1 in TableB as long as Column2 in TableA matches Column2 in TableB:

Here's how the query should look:

UPDATE TableA
SET Column1 = B.Column1
FROM TableA A
INNER JOIN TableB B
ON A.Column2 = B.Column2

SQL Error 945 While Creating New Database

I ran across a problem creating a new database on a SQL Server that had recently been through a crash and rebuild. It had been running fine for months, but now whenever you tried to create a new database it would throw a SQL 945 error, referring to space or memory allocation issues and then referencing the data or log file of an unrelated database that had been previously deleted.
After some troubleshooting I came to the conclusion that there must be some orphaned data in the Master database. I first checked sysdatabases, and everything looked fine, 23 databases all in a row. Next I looked at sysaltfiles, and lo and behold there were entries for dbid 24, which did not exist. Since it was the next available dbid in sysdatabases, SQL Server was attempting to create the new database as dbid 24, but the data in sysaltfiles did not jive with it. Long story short, I removed the entries for dbid 24 in sysaltfiles and the problem was fixed.

Adding Leading Zero(s) to Field Entries

Adding Leading Zero(s) to field entries in a SQL table may be something you have to do at some point. Why? Beats me, I just know it has come up more than once for me, and the "customer is always right."

This simple query will update the data in TABLE1, field ORDER to include leading zeros for up to 10 total digits. ORDER is varchar(10)

UPDATE TABLE1
SET ORDER = right('0000000000' + ORDER, 10)

It's almost obscene how simple this is, but you will have to make sure that the queries used to add data to this table are updated to include the leading zeros in the INSERT, otherwise you will have to continuously add leading zeros to this field moving forward.