Skip to content

sql

Using SQL to Query up Your Agent Job Run History

It is important to keep an eye on the run history of your agent jobs. That way you are aware if your jobs fail or the time they take to run is starting to creep upward. Ultimately, you should at the very least have email notifications sent to tell you if your jobs fail. However, what if a job that used to take one hour to run is now taking 10 hours? How will you know this is happening if the job doesn’t actually fail? The only way is to make sure you are keeping an eye on it, but that can be cumbersome with SQL Server Management Studio. One beef I have with SSMS is that it tells you the time your job started and how long it took, but to get the end time you have to do the math. Ugh! The below SQL will give you the run history of your jobs with a start and end time! Woo hoo! No more date/time math. It will also give you the duration hours, minutes, and seconds in separate columns so you can easily see trends in the time your jobs take to run. I hope you find it helpful.

use msdb

select
	J.name,
	dbo.agent_datetime(H.run_date, H.run_time) start_time,
	DATEADD(second, run_duration%100, DATEADD(minute, run_duration/100%100, DATEADD(hour, run_duration/10000, dbo.agent_datetime(H.run_date, H.run_time)))) end_time,
	run_duration/10000 duration_hours,
	run_duration/100%100 duration_minutes,
	run_duration%100 duration_seconds,
	case
		when H.run_status = 0 then 'Failed'
		when H.run_status = 1 then 'Succeeded'
		when H.run_status = 2 then 'Retry'
		when H.run_status = 3 then 'Canceled'
		when H.run_status = 4 then 'In Progress'
	end run_status,
	H.message
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobhistory H on J.job_id = H.job_id
where step_id = 0
order by
	J.name, start_time desc

Using SQL to Query up Your Agent Job Details

If you are a SQL DB admin the odds are you have a bunch of SQL agent jobs that run throughout the day. If you haven’t yet, at some point you will want to audit the specifics of those jobs and when they are running. The below script will query up the schedule details of your SQL Agent jobs. The script will also suggest SQL to change the job owner and schedule owner of any of your jobs/schedules that are owned by user logins. If you’ve not been bit by that in the past it is important to note that if a user login owns a job and that login is removed or denied access to connect to the database engine the job will cease to run correctly. Thus, I recommend setting the owner for all your jobs to “sa” to make sure that your jobs continue to run in perpetuity.

use msdb

select
	J.job_id,
	J.name 'job_name',
	case 
		when J.notify_level_email = '0' then 'Never'
		when J.notify_level_email = '1' then 'When the job succeeds'
		when J.notify_level_email = '2' then 'When the job fails'
		when J.notify_level_email = '3' then 'Whenever the job completes (regardless of the job outcome)'
		else cast(J.notify_level_email as varchar)
	end notify_level_email,
	O.name 'notify_name',
	O.email_address 'notify_email_address',
	SUSER_SNAME(J.owner_sid) 'job_owner',
	case
		when SUSER_SNAME(J.owner_sid) <> 'sa' and SUSER_SNAME(J.owner_sid) not like '##%' then 'exec dbo.sp_update_job @job_id=''' + cast(J.job_id as varchar(255)) + ''', @owner_login_name = ''sa'''
	end as job_owner_fix,
	J.enabled 'job_enabled',
	S.schedule_id,
	S.name 'schedule_name',
	S.enabled 'schedule_enabled',
	SUSER_SNAME(S.owner_sid) 'schedule_owner',
	case
		when SUSER_SNAME(S.owner_sid) <> 'sa' and SUSER_SNAME(S.owner_sid) not like '##%' then 'exec dbo.sp_update_schedule @schedule_id=' + cast(S.schedule_id as varchar) + ', @owner_login_name = ''sa'''
	end as schedule_owner_fix,
	case
		when S.freq_type = 1 then 'Once'
		when S.freq_type = 4 then 'Daily'
		when S.freq_type = 8 then 'Weekly'
		when S.freq_type = 16 then 'Monthly'
		when S.freq_type = 32 then 'Monthly relative'
		when S.freq_type = 64 then 'When SQLServer Agent starts'
	end frequency,
	left(convert(varchar, dbo.agent_datetime('19000101', S.active_start_time), 114), 8) start_time,
	case
		when S.freq_subday_interval = 0 then null
		else left(convert(varchar, dbo.agent_datetime('19000101', S.active_end_time), 114), 8)
	end end_time,
	case
		when S.freq_subday_interval = 0 then 'Once'
		else cast('Every ' + right(S.freq_subday_interval, 2) + ' ' +
			case
				when S.freq_subday_type = 1 then 'Once'
				when S.freq_subday_type = 4 then 'Minutes'
				when S.freq_subday_type = 8 then 'Hours'
			end as char(16))
	end as 'sub_frequency',
	case
		when S.freq_type = 8 and S.freq_interval & 1 = 1 then 'x'
		else null
	end sunday,
	case
		when S.freq_type = 8 and S.freq_interval & 2 = 2 then 'x'
		else null
	end monday,
	case
		when S.freq_type = 8 and S.freq_interval & 4 = 4 then 'x'
		else null
	end tuesday,
	case
		when S.freq_type = 8 and S.freq_interval & 8 = 8 then 'x'
		else null
	end wednesday,
	case
		when S.freq_type = 8 and S.freq_interval & 16 = 16 then 'x'
		else null
	end thursday,
	case
		when S.freq_type = 8 and S.freq_interval & 32 = 32 then 'x'
		else null
	end friday,
	case
		when S.freq_type = 8 and S.freq_interval & 64 = 64 then 'x'
		else null
	end saturday,
	convert(varchar, dbo.agent_datetime(S.active_start_date, '000000000'), 101) duration_start,
	case
		when S.active_end_date = '99991231' then null
		else convert(varchar, dbo.agent_datetime(S.active_end_date, '000000000'), 101)
	end duration_end
from msdb.dbo.sysjobs J
left outer join msdb.dbo.sysoperators O on J.notify_email_operator_id = O.id
left outer join msdb.dbo.sysjobschedules JS on J.job_id = JS.job_id
left outer join msdb.dbo.sysschedules S on JS.schedule_id = S.schedule_id
order by
	J.name

Doing a select on a stored procedure

I came across a scenario today where I just wished that I could do a SQL select on a stored procedure. Akin to something like this …

select * from (exec myStoredProcedure) where column = 'value'

Alas, this is not possible … urg! However, I did manage to find a workaround that gives me basically what I wanted. It involves turning on some insecure settings in SQL sever. So we want to make sure we turn them back off after the fact.

The gist of the below SQL is as follows. We first enable the insecure settings. Next we execute the stored procedure using openrowset and store the results into a temp table. Once we have the data in a temp table we can work with it just like any other table, yeay!! Finally, we want to make sure to turn off the openrowset feature again.

/**********************************/
/* Allow openrowset               */
/**********************************/
sp_configure 'Show Advanced Options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
/***********************************/

/***********************************/
/* This is the query               */
/***********************************/
select * into #tempTable from openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;','exec myStoredProcedure')
select * from #tempTable -- <-- Do your stuff here
/***********************************/


/**********************************/
/* Set the insecure settings back */
/**********************************/
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'Show Advanced Options', 0
go
reconfigure
go
/**********************************/

Finding the Character Set For a SQL Field

The other day I was working on a project and I needed to know all of the different types of characters contained in a SQL field so that I could make a regular expression pattern that would not fail. Thus, I decided to write a stored procedure that would loop through a range of characters and test if that character is contained in a specified field in any records. From there I can get a list of all of the used characters and then more intelligently write my regular expression pattern.

This could also be handy if you are trying to cast a field and you keep getting errors because of a character that won’t cast correctly. An example would be trying to cast a varchar or char field into an int and for some reason or another one of the records has a letter in the field. Your cast will fail and it can be tricky to track down where the issue is. With this stored procedure you can quickly see what the offending character is and then query up all the records containing that character.

Example usage:

exec FieldCharacterSet 'dbo', 'mytable, 'myfield', 1, 0, 255

Feel free to use this if you find is useful, and leave a comment letting me know if it worked well for you.

-- =============================================
-- Author:        Davin Studer
-- Create date: 9/26/2010
-- Description:    This stored procedure will let you
-- know all the characters that are contained within
-- a field of a specified table.  This is handy for things
-- such as creating regular expression patterns and figuring
-- out casting issues.
-- =============================================
create procedure [dbo].[FieldCharacterSet]
    @TableSchema nvarchar(255) = '',-- this is the schema for table to look at
    @TableName nvarchar(255) = '',    -- this is the table to look at
    @FieldName nvarchar(255) = '',    -- this is the field that will be scanned
    @RTrim bit = 1,                    -- should the field be right trimmed before finding the character set ... mostly for char field types
    @LowCharRange int = 0,            -- character scan start point
    @HighCharRange int = 255        -- character scan end point
as
begin
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    set nocount on;
 
    declare
        @count bigint = 0,
        @sql nvarchar(max) = '',
        @selects nvarchar(max) = '',
        @error varchar(200) = '',
        @field nvarchar(264) = '',
        @sqlbuild nvarchar(max) = ''
    declare @chars table(
        [character] nchar(1)
    )
    declare @statements table(
        [sql] varchar(max)
    )
 
    if @TableName = ''
    begin
        set @error = 'You must supply a table name.'
        raiserror (@error, 15, 1)
    end
 
    if @FieldName = ''
    begin
        set @error = 'You must supply a field name.'
        raiserror (@error, 15, 1)
    end
    
    if @error = ''
    begin
        -- Bracket the table name to avoid naming issues
        if left(@TableName, 1) <> '[' and right(@TableName, 1) <> ']'
        begin
            set @TableName = '[' + @TableName + ']'
        end
        
        -- Bracket the field name to avoid naming issues
        if left(@FieldName, 1) <> '[' and right(@FieldName, 1) <> ']'
        begin
            set @FieldName = '[' + @FieldName + ']'
        end
        
        -- Should we rtrim the field to deal with trailing spaces ... mostly for char fields
        if @RTrim = 1
        begin
            set @field = 'rtrim(' + @FieldName + ')'
        end
        
        -- Create a temp table to hold our examples of each character matched
        if object_id('tempdb..##fieldCharacterSetTemp') is not null
        begin
            drop table ##fieldCharacterSetTemp
        end
        set @sql = 'select * into ##fieldCharacterSetTemp from (select t1.* from ' + @TableSchema + '.' + @TableName + ' t1 inner join ' + @TableSchema + '.' + @TableName + ' t2 on 1 = 1 where 1 = 0) X'
        exec(@sql)
 
        --Loop through the ascii characters low to high
        while @LowCharRange <= @HighCharRange
        begin
            --Build the from and where clauses
            set @sqlbuild = ''
            set @sqlbuild = @sqlbuild + 'from '
            if @TableSchema <> ''
            begin
                set @sqlbuild = @sqlbuild + @TableSchema + '.'
            end
            set @sqlbuild = @sqlbuild + @TableName + nchar(13)
            set @sqlbuild = @sqlbuild + 'where ' + @field
            -- If the character is A-Z or a-z then case sensitive compare
            if (@LowCharRange between 65 and 90) or (@LowCharRange between 97 and 122)
            begin
                set @sqlbuild = @sqlbuild + ' COLLATE Latin1_General_CS_AS'
            end
            set @sqlbuild = @sqlbuild + ' like '
            -- Deal with a few special SQL characters
            if @LowCharRange = 37 -- % character
            begin
                set @sqlbuild = @sqlbuild + '''%\%%'' escape ''\''' + nchar(13)
            end
            else if @LowCharRange = 39 -- ' character
            begin
                set @sqlbuild = @sqlbuild + '''%''''%''' + nchar(13)
            end
            else if @LowCharRange = 95 -- _ character
            begin
                set @sqlbuild = @sqlbuild + '''%\_%'' escape ''\''' + nchar(13)
            end
            else
            begin
                set @sqlbuild = @sqlbuild + '''%' + nchar(@LowCharRange) + '%''' + nchar(13)
            end
 
            -- Buld main counting sql
            set @sql = ''
            set @sql = @sql + 'select @countOut = count(*)' + nchar(13)
            set @sql = @sql + @sqlbuild
 
            --Get count of rows that have the character
            execute sp_executesql @sql,N'@countOut bigint = 0 output',@countOut=@count output
 
            --If any rows have this character add it to the result set
            if @count > 0
            begin
                insert into @chars values (nchar(@LowCharRange))
 
                -- Build the select sql to look at example lines that have this character
                set @selects = ''
                set @selects = @selects + 'select top 1 *' + nchar(13)
                set @selects = @selects + @sqlbuild
                
                insert into @statements values (rtrim(replace(@selects, nchar(13), ' ')))
 
                set @sql = ''
                set @sql = @sql + 'insert into ##fieldCharacterSetTemp' +nchar(13)
                set @sql = @sql + @selects
                
                exec(@sql)
            end
            
            -- Next character
            set @LowCharRange = @LowCharRange + 1
        end
 
        select * from @chars
        select * from @statements
        if object_id('tempdb..##fieldCharacterSetTemp') is not null
        begin
            select * from ##fieldCharacterSetTemp
            drop table ##fieldCharacterSetTemp
        end
    end
end

Convert CSV data into a SQL table

Recently I have been working with some report data that is stored statically in a database in CSV format. I had a need to have that CSV data displayed in a tabular format. I searched around and couldn’t find anything written to do this in SQL, so I decided to write a stored procedure to do this very thing. It will take a CSV and parse it out into a SQL table. For instance if I have report data that looks like this…

"FirstName","LastName","DOB","Children"
"John","Smith","1/5/1980","Bob,Sally,Joe,Chris"
"Jane","Smith","2/25/1982","Bob,Sally,Joe,Chris"
"Bruce","Wayne","5/13/1975",""
"Peter","Parker","5/23/1970",""
"","","",""

It would be transformed into this …

FIRSTNAMELASTNAMEDOBCHILDREN
JohnSmith1/5/1980Bob,Sally,Joe,Chris
JaneSmith2/25/1982Bob,Sally,Joe,Chris
BruceWayne5/13/1975 
PeterParker5/23/1970 
    

Note that in the last column the commas are preserved because they are within the quotation mark text qualifiers. Below is the stored procedure that does this. Feel free to use it, and leave a comment if you found it useful.

-- ==========================================================================================
-- Author:  Davin Studer
-- Create date: 4/5/2011
-- Description: This will take a CSV input
-- and transform it into a table
--
-- Params:
-- @string          - The CSV string
-- @textQualifier   - Character that denotes a string within the CSV
-- @columnDelimiter - Character that denotes different columns ... doesn't have to be a comma
-- @rowOneIsHeader  - Does the first row contain column names?
-- ==========================================================================================
create procedure [dbo].[CSVToTable]
    @string nvarchar(max) = '',
    @textQualifier varchar(1) = '',
    @columnDelimiter varchar(1) = ',',
    @rowOneIsHeader bit = 0
as
begin
    -- set nocount on added to prevent extra result sets from
    -- interfering with select statements.
    set nocount on;
 
    -- We need and input string
    if isnull(@string, '') = ''
    begin
        raiserror ('Please supply an CSV string', 15, 1)
        return
    end
 
    -- We need and column delimiter
    if isnull(@columnDelimiter, '') = ''
    begin
        raiserror ('Please supply a column delimiter.', 15, 1)
        return
    end
  
    -- Make sue the user doesn't pass null as a value
    if isnull(@textQualifier, '') = ''
    begin
        set @textQualifier = ''
    end
 
    -- Make sue the user doesn't pass null as a value
    if isnull(@rowOneIsHeader, '') = ''
    begin
        set @rowOneIsHeader = 0
    end
 
    declare
        @columns int = 1,
        @columnNames nvarchar(max) = '',
        @stop bit = 0,
        @position int = 0,
        @temp nvarchar(1) = '',
        @dataStart int = 0,
        @sql nvarchar(max) = '',
        @qualifierToggle bit = 0,
        @tempString nvarchar(max) = '',
        @delimiterReplacementUTFNumber int = 2603
 
    -- Get rid of the ##tempCSVSplitToTable table if it exists
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
 
    -- Get rid of white space
    set @string = rtrim(ltrim(@string))
 
    -- Set the EOL to char(13)
    set @string = replace(@string, char(13) + char(10), char(13))
    set @string = replace(@string, char(10), char(13))
 
    -- Deal with the delimiter character within the text qualifier characters
    if @textQualifier <> ''
    begin
        while @position <> len(@string)
        begin
            set @temp = substring(@string, @position, 1)
            if @temp = @textQualifier
            begin
                if @qualifierToggle = 0
                begin
                    set @qualifierToggle = 1
                end
                else
                begin
                    set @qualifierToggle = 0
                end
            end
            if @temp = @columnDelimiter and @qualifierToggle = 1
            begin
                set @tempString = @tempString + nchar(@delimiterReplacementUTFNumber) -- replace with UTF delimiter replacement character
            end
            else
            begin
                set @tempString = @tempString + @temp
            end
            set @position = @position + 1
        end
 
        set @string = @tempString
    end
 
    -- Get rid of text qualifier ... we don't need it now
    if @textQualifier <> ''
    begin
        set @string = replace(@string, @textQualifier, '')
    end
 
    -- Get column names
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@string, @position, 1)
        if @temp = @columnDelimiter
        begin
            set @columns = @columns + 1
            set @columnNames = @columnNames + ','
        end
        else if @temp = char(13)
        begin
            set @stop = 1
        end
        else
        begin
            set @columnNames = @columnNames + @temp
        end
        set @position = @position + 1
    end
 
    set @dataStart = @position
 
    if @rowOneIsHeader = 0
    begin
        set @dataStart = 1
        set @position = 1
        set @columnNames = ''
        while @position - 1 < @columns
        begin
            set @columnNames = @columnNames + ',Column' + cast(@position as varchar(1))
            set @position = @position + 1
        end
        set @columnNames = substring(@columnNames, 2, len(@columnNames))
    end
 
    -- Build ##tempCSVSplitToTable table
    set @sql = @sql + 'create table ##tempCSVSplitToTable (' + char(13) + '['
    set @stop = 0
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@columnNames, @position, 1)
        if @temp <> ','
        begin
            set @sql = @sql + @temp
        end
        else
        begin
            set @sql = @sql + '] nvarchar(max),' + char(13) + '['
        end
   
        set @position = @position + 1
 
        if @position - 1 = len(@columnNames)
        begin
            set @sql = @sql + '] nvarchar(max)'
            set @stop = 1
        end
    end
    set @sql = @sql + ')' + char(13)
    exec (@sql)
 
    -- insert values into ##tempCSVSplitToTable table
    set @position = @dataStart
    while @position - 1 < len(@string)
    begin
        set @stop = 0
        set @sql = 'insert into ##tempCSVSplitToTable ([' + replace(@columnNames, ',', '],[') + ']) values ('''
        while @stop = 0
        begin
            set @temp = substring(@string, @position, 1)
 
            -- end of column
            if @temp = @columnDelimiter
            begin
                set @sql = @sql + ''','''
            end
            -- EOL
            else if @temp = char(13) or datalength(@temp) = 0
            begin
                set @stop = 1
            end
            -- deal with apostrophe in data
            else if @temp = ''''
            begin
                set @sql = @sql + ''''''
            end
            -- column data that isn't an apostrophe
            else
            begin
                set @sql = @sql + @temp
            end
 
            set @position = @position + 1
        end 
        set @sql = @sql + ''')'
 
        -- Get rid of any UTF delimiter replacements that were put in to take the place of the delimiter character within the text qualifier
        if @textQualifier <> ''
        begin
            set @sql = replace(@sql, nchar(@delimiterReplacementUTFNumber), @columnDelimiter)
        end
 
        exec(@sql)
    end
 
    select * from ##tempCSVSplitToTable
 
    -- destroy ##tempCSVSplitToTable table
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
end

Pivoting SQL Data

So, the other day I was trying to pivot some SQL data that was basically in this format…

RECNUMBERCOLNUMBERFIELDNAMEFIELDVALUE
11FirstNameDavin
12LastNameStuder
21FirstNameMelissa
22LastNameStuder

I wanted to see it like this …

RECNUMBERFIRSTNAMELASTNAME
1DavinStuder
2MelissaStuder

It took me a while to figure out the syntax for this, but here it is. It uses the SQL pivot keyword.

This first portion dynamically creates the column names to be used in the pivot.

declare @cols varchar(max)
 
select @cols = stuff (
    (
        select '], [' + FieldName
        from table
        group by FieldName
        order by min(ColNumber)
        for
        xml path('')
    ),
    1,
    2,
    ''
) + ']'

This next part creates a query within a string that we will run with the SQL exec command.

declare @query varchar(max)
 
set @query = '
    select RecNumber, ' + @cols + '
    from (
        select RecNumber, FieldName, FieldValue
        from table
    ) dta
    pivot
    (
        min(FieldValue)
        for FieldName in (' + @cols + ')
    ) pvt
'

All that is left to do is to execute the query that was created. I ended up putting this into a stored procedure.

exec(@query)

Chaining SQL Record Filters

So, I’ve actually been using this technique for a while, but recently on a project I have been working on I again ran into the need for it.  Many times creating an application you might find yourself with a bunch of checkboxes (or anything for that matter) that you might use to build a SQL where clause, such as …

select field1, field2, field3
from table
where
    checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

The problem is that when you are building the where clause you have to check to see if you are on the first item.  If so, then do not add an “and”, otherwise add the “and”.  Or say you have a static SQL query that you often use and you want to quickly comment out the first item in the where clause but you don’t want to totally delete it.  You may have to do this …

select field1, field2, field3
from table
where
    --checkbox1 = 0
    /*and*/ checkbox2 = 1
    and checkbox3 = 1

I find this to be somewhat awkward to do.  If you are dynamically building a where clause it adds more code to have to decipher, or if you have a static query it adds unneeded edits.  I prefer the below method …

select field1, field2, field3
from table
where
    1 = 1
    and checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

By adding the “1 = 1” I can simply add every piece of the where clause with the “and” in front and I don’t have check if I am on the first item or not.  Also it makes it easy to selectively comment out parts of my static query without having to do much typing as the below example shows.

select field1, field2, field3
from table
where
    1 = 1
    --and checkbox1 = 0
    and checkbox2 = 1
    --and checkbox3 = 1

For me this has been very helpful in many projects, including my current one.  Maybe somebody else will find this approach helpful as well.