Generate SQL Job Schedule Description

I was actually calling the system stored procedure "msdb.dbo.sp_help_jobschedule" in my previous post about modifying column list from a called stored procedure,

While working on that I noticed that [more]the description (schedule_description) being returned by stored procedure (if you set @include_description = 1) returns date and times as integer values (rather than date/time format).

While looking around I found this feedback from microsoft connect : sp_get_schedule_description produces basic output, be nice if it had formatted dates & times (you might be required to login to view it)

Turns out the behavior was changed from SQL2000 to SQL2005 and won't be changed(or corrected should I say) since other applications that depended on the format might break.

So I figured I'd have a function/method for returning a more user friendly description instead.

Found this article as a baseline: Generate Job Schedule Description and created the functions below (GetScheduleDescription and GetTimeString)

— START

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[GetTimeString]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetTimeString]

GO

— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description:    Return Time String Representation from INT (scheduled job format, HHmmss)
— =============================================
CREATE FUNCTION [dbo].[GetTimeString](@timeInteger INT)
RETURNS NVARCHAR(255)
AS
BEGIN

— convert to 12hr time
DECLARE @originalTimeInteger INT
SET @originalTimeInteger = @timeInteger
IF(@timeInteger >= 130000)
  SET @timeInteger = @timeInteger – 120000

— convert to 6 chars (pad if necessary)
DECLARE @TM VARCHAR(255)
SET @TM = REPLACE(STR(@timeInteger, 6), SPACE(1), '0')

DECLARE @RETVAL VARCHAR(255)
SET @RETVAL =
  SUBSTRING(@TM,1,2) + ':' +
  SUBSTRING(@TM,3,2) + ':' +
  SUBSTRING(@TM,5,2)
  + CASE
    WHEN @originalTimeInteger < 120000 THEN ' AM'
    ELSE ' PM'
  END
  RETURN @RETVAL
END
GO

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[GetScheduleDescription]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetScheduleDescription]

GO

— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description:    Return Schedule Description
— a modification of sp_get_schedule_description
— but there is an issue with SQL2005 where the date/times returned are in INT
— instead of recognizable date/time formats
— =============================================
CREATE FUNCTION [dbo].[GetScheduleDescription](
  @freq_type              INT          = NULL,
  @freq_interval          INT          = NULL,
  @freq_subday_type       INT          = NULL,
  @freq_subday_interval   INT          = NULL,
  @freq_relative_interval INT          = NULL,
  @freq_recurrence_factor INT          = NULL,
  @active_start_date      INT          = NULL,
  @active_end_date        INT          = NULL,
  @active_start_time      INT          = NULL,
  @active_end_time        INT          = NULL,
  @enabled                BIT          = NULL)
RETURNS NVARCHAR(255)
AS
BEGIN
  DECLARE @schedule_description NVARCHAR(255)
  SET @schedule_description =
  'Occurs ' +
  CASE
    WHEN @freq_type = 0x1 — OneTime
      THEN
        'once on '
        + CONVERT(
            CHAR(10)
            , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
            , 101 — mm/dd/yyyy
          )
    WHEN @freq_type = 0x4 — Daily
      THEN
        CASE
          WHEN @freq_interval = 1
            THEN 'daily '
          WHEN @freq_interval > 1
            THEN 'every '
            + CAST( @freq_interval AS VARCHAR )
            + ' days '
        END
    WHEN @freq_type = 0x8 — weekly
      THEN
        CASE
          WHEN @freq_recurrence_factor = 1
            THEN 'weekly on '
          WHEN @freq_recurrence_factor > 1
            THEN 'every '
            + CAST( @freq_recurrence_factor AS VARCHAR )
            + ' weeks on '
        END
        + LEFT(
             CASE WHEN @freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
           + CASE WHEN @freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
           + CASE WHEN @freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
           + CASE WHEN @freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
           + CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
           + CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
           + CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
         , LEN(
                    CASE WHEN @freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                  + CASE WHEN @freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                  + CASE WHEN @freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                  + CASE WHEN @freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                  + CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                  + CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                  + CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
              ) – 1  — LEN() ignores trailing spaces
        )
     WHEN @freq_type = 0x10 — monthly
         THEN
             CASE
                 WHEN @freq_recurrence_factor = 1
                     THEN 'monthly on the '
                 WHEN @freq_recurrence_factor > 1
                     THEN 'every '
                        + CAST( @freq_recurrence_factor AS VARCHAR )
                        + ' months on the '
             END
           + CAST( @freq_interval AS VARCHAR )
           + CASE
                 WHEN @freq_interval IN ( 1, 21, 31 ) THEN 'st'
                 WHEN @freq_interval IN ( 2, 22     ) THEN 'nd'
                 WHEN @freq_interval IN ( 3, 23     ) THEN 'rd'
                 ELSE 'th'
             END
     WHEN @freq_type = 0x20 — monthly relative
         THEN
             CASE
                 WHEN @freq_recurrence_factor = 1
                     THEN 'monthly on the '
                 WHEN @freq_recurrence_factor > 1
                     THEN 'every '
                        + CAST( @freq_recurrence_factor AS VARCHAR )
                        + ' months on the '
             END
           + CASE @freq_relative_interval
                 WHEN 0x01 THEN 'first '
                 WHEN 0x02 THEN 'second '
                 WHEN 0x04 THEN 'third '
                 WHEN 0x08 THEN 'fourth '
                 WHEN 0x10 THEN 'last '
             END
           + CASE @freq_interval
                 WHEN  1 THEN 'Sunday'
                 WHEN  2 THEN 'Monday'
                 WHEN  3 THEN 'Tuesday'
                 WHEN  4 THEN 'Wednesday'
                 WHEN  5 THEN 'Thursday'
                 WHEN  6 THEN 'Friday'
                 WHEN  7 THEN 'Saturday'
                 WHEN  8 THEN 'day'
                 WHEN  9 THEN 'week day'
                 WHEN 10 THEN 'weekend day'
             END
     WHEN @freq_type = 0x40
         THEN 'Automatically starts when SQLServerAgent start@'
     WHEN @freq_type = 0x80
         THEN 'Starts whenever the CPUs become idle'
     ELSE ''
   END
 + CASE
     WHEN @freq_subday_type = 0x1 OR @freq_type = 0x1
         THEN ' at '
           + dbo.[GetTimeString](@active_start_time)
     WHEN @freq_subday_type IN ( 0x2, 0x4, 0x8 )
         THEN ' running every '
           + CAST( @freq_subday_interval AS VARCHAR )
           + CASE @freq_subday_type
                 WHEN 0x2 THEN ' second'
                 WHEN 0x4 THEN ' minute'
                 WHEN 0x8 THEN ' hour'
             END
           + CASE
                 WHEN @freq_subday_interval > 1 THEN 's'
                 ELSE ''
             END
            + ' between '
            + dbo.[GetTimeString](@active_start_time)
            + ' and '
            + dbo.[GetTimeString](@active_end_time)
     ELSE ''
   END
 + CASE
     WHEN @freq_type IN ( 4, 8, 16, 32 ) — daily, weekly, monthly and monthly relative
         THEN '. Schedule will be used '
          +
            CASE
              WHEN (@active_end_date IS NULL OR @active_end_date = 99991231)
                THEN ' starting '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + '.'
              ELSE
                ' between '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + ' and '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_end_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + '.'
            END
     ELSE ''
   END

  RETURN @schedule_description
END

GO

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[CM1_SEL_ScheduleByJobID]')
    AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CM1_SEL_ScheduleByJobID]

— END

Again, as previously mentioned, used INSERT-EXEC to simply store the values of my "msdb.dbo.sp_help_jobschedule" query to a table variable. Then for the final SELECT statement, I called my new GetScheduleDescription

      [ScheduleDescription] = dbo.GetScheduleDescription(
          freq_type,
          freq_interval,
          freq_subday_type,
          freq_subday_interval,
          freq_relative_interval,
          freq_recurrence_factor,
          active_start_date,
          active_end_date,
          active_start_time,
          active_end_time,
          enabled)

Hope this helps anyone. 

REVISED as of 3/27/08 – handling of 12:mm PM and changes to further enhance the description returned.

Modify column list from a called stored procedure

I needed to trim down the number of columns returned from a system procedure (eg. for performance) and unfortunately [more] it's not possible to simply call:

SELECT * FROM EXEC <stored proc to call> 

Eventually, I had to resort to an INSERT-EXEC approach (insert the result set resulting from an EXEC <sp> call to a temp table).

This is susceptible to any signature changes in the called procedure but since it's a system stored procedure it's likely to get changed (hopefully even in next/upcoming versions). If it would have been a custom stored procedure, there would have been better ways to do it but since it's a system SP, had to resort to this). Other options include OPENQUERY or CURSOR but the INSERT-EXEC seems to be the lesser evil.

For more info, check out this link on How to Share Data between Stored Procedures

Visual Studio 2008, SQL Server 2008, Windows Server 2008 Launch Philippines

Microsoft Philippines Launch for these new 2008 products will be on April 10, 2008 at the SMX Convention Center, SM Mall of Asia Complex.

Public Registration will begin on March 24 on this Registration Link 

Stay tuned for updates on:

http://msforums.ph/forums/p/46593/220607.aspx#220607

Microsoft Philippines Heroes Happen

Hope to see you there!

Introduction to SQL Server 2008 Free eBook

I belive all you need is a Windows Live ID.

The book is not complete yet. Only chapter 1 (Declarative Management Framework) is currently viewable without signing up and signing up includes Chapter 11 (Transact-SQL Enhancement). But when the book is completed the whole content of the book will be available for free. The 1st Chapters is enough for a teaser.

Click here

Problem with SQL Database Mail: ExternalMailQueue missing

UPDATE: Just wanted to point out that it's likely that this issue was after installing .NET Framework 2.0 Service Pack 1 (SP1)

I just updated an entry in the technet forums regarding a previous
issue I've encountered before. I was able to solve my issue by
reinstalling SQL Server (which is not a very brilliant solution) but
since I only have the issue in my development machine, reinstalling was
an option and worked for me.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2828943&SiteID=17&mode=1

Reading SQL error messages and sp_executeSQL

"Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."

A few days ago, I ran into the error mentioned above. sp_executeSQL requires that the first two parameters (statement and parameter declaration) be of type ntext, nchar or nvarchar

http://dotnetjunkies.com/WebLog/richard.dudley/archive/2004/09/29/27169.aspx

When I ran into this error, I copied the error message and googled (so much for the "googler stereotype") right away. Only to later realize that if I had read the error message well, I should have easily determine the cause. I was passing an argument to the stored procedure so I thought it was weird that it was still looking for one. I thought it was one of those weird errors and jumped right into that conclusion. I do pay attention to details but there are just those days that you still fail to do so. So just a debugging reminder, read the error message before you google.

*** sp_executeSQL is a stored procedure which is best used for dynamic SQL queries. One of it's popular use is to prevent SQL injection. I'm thinking of posting an entry on sql injection but there are a lot of articles out there that likely explains more clearly and interesting that my explanation would be so probably next time. Know though that stored procedures doesn't guarantee full protection from SQL injection especially if you still concatenate inside your stored procedures. And for that (plus other cases where you really need dynamic queries) have a look at sp_executeSQL.

SSL and SQL Server 2005

Getting an "error occured during the pre-login handshake". in SQL Server?

I just spent the last few hours trying to figure out why I can't connect to my local SQL 2005 instance and getting the error above. I was using SQL Management Studio.

I tried tweaking Network/Client Configuration, Protocols using SQL ConfigurationManager, Surface Area Configuration and the error messages changed from one to another but still no luck. Some claim that this issue was solved by changes to protocols but it wasn't my case. [more]

I looked for answers in the net and didn't quite get what was wrong.

But this first link helped (common SQL protocol issues/errors)
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

And then I ended up with these post and the symptoms matched my issue (especially when I connected using MDAC – Admin > ODBC Sources)
http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

As soon as I saw the 2nd link above, I knew I'm close plus the fact that I've been working on creating and assigning SSL certifcate for my local IIS using SelfSSL (comes with IIS Resource Kit). It's weird though that it was only today that I encountered it, probably something I did before I slept last night.

It took me more time to fix the issue though (even after following the instructions; that is clearing my user certificates via certmgr.msc). When opening the SQL Configuration Manager and checking for the available certificates, two certificates still appear. I just deleted them using certmgr.msc but they're still there. After a few more minutes of headache, I opened mmc.exe, added the "Certificates" snap-in and there I saw that two other certificates that weren't removed (even if it no longer exists in certmgr.msc). I think this has something to do with "local machine versus user certificate personal store". certmgr.msc is the personal store, while you could open the local machine certs by the "Certificates" snap-in in MMC. Not sure but clearing the local certs from the MMC snap-in, then restarted SQL Service did the trick for me.

Also, it's worth noting that when generating a certificate using SSL, it seems to help avoid this issue if you DON'T use the machine name (it is the default) and instead specify the domain or maybe even the localhost (using the CN switch)

I'll revisit this post next time (yeah I know, my thoughts are scattered) but just wanted to post it incase someone comes across it and despite finding the links still got into the issues I encountered. If you have any questions, feel free to contact me.

I thought of restoring a previous image of my system but where's the thrill in that. And besides, did learn a lot from this.

Also, tried to "google" the "error occured during the pre-login handshake" again just now and did return some posts (on SSL issue hints) which I didn't see when I started poking into this. I think keyed in the wrong keywords. So be careful when googling for the right phrase. And when getting weird/vague errors in SQL, visit the 1st link I gave above, or try connecting using different approaches (MDAC, SSMS or ADO.NET).

Also check this out http://support.microsoft.com/kb/900497. If the above doesn't work then try clearing (not deleting) the Certificate registry entry mentioned in the article

Have to get back to work, been stalled long enough.