Solved

Stored Procedure Runs in SQL, Fails on aspx.net

Posted on 2013-11-06
6
326 Views
Last Modified: 2013-11-16
I found a script online to view all job schedules on sql server. I've got this script set up as a stored procedure on all my sql servers and am trying to generate an aspx page that will run each of them and give me a comprehensive list of all jobs on all servers with their respective schedules.

I have created a GridVew on my aspx page and created the sqldatasource to run the sproc. However, when I test it, the results are blank. When I try to refresh the schema for the gridview to get all the columns to show up, I get this error:

Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid. Invalid object name '#xp_results'.

My connection string is correct because I'm able to run other sprocs based on the master table with no errors.
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[uspAllAgentJobs]    Script Date: 11/06/2013 09:42:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
  Created by Solihin Ho - http://solihinho.wordpress.com

  Usage : Change the value of variable @Filter
          'Y' --> display only enabled job
          'N' --> display only disabled job
          'A' --> display all job
          'X' --> display job which is duration already end
*/
ALTER PROCEDURE [dbo].[uspAllAgentJobs]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Filter CHAR(1)
SET @Filter = 'A'


DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
BEGIN
    DROP TABLE #xp_results
END


CREATE TABLE #xp_results (
     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, 
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL, 
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL)



SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
	
SET @sql = '
SELECT 
  ''sdwinsql03v'' as ServerName
, j.name AS JobName
, c.name AS Category
, CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled
, CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled
, j.description 
, CASE s.freq_type 
     WHEN  1 THEN ''Once''
     WHEN  4 THEN ''Daily''
     WHEN  8 THEN ''Weekly''
     WHEN 16 THEN ''Monthly''
     WHEN 32 THEN ''Monthly relative''
     WHEN 64 THEN ''When SQL Server Agent starts'' 
     WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs 	
, CASE s.freq_type 
     WHEN  1 THEN ''O''
     WHEN  4 THEN ''Every '' 
        + convert(varchar,s.freq_interval) 
        + '' day(s)''
     WHEN  8 THEN ''Every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' weeks(s) on '' 
        + master.dbo.fn_freq_interval_desc(s.freq_interval)					  
     WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' 
     WHEN 32 THEN ''The '' 
        + CASE s.freq_relative_interval	
            WHEN  1 THEN ''First''
            WHEN  2 THEN ''Second''
            WHEN  4 THEN ''Third''	
            WHEN  8 THEN ''Fourth''
            WHEN 16 THEN ''Last'' END 
        + CASE s.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 '' Weekday''
            WHEN 10 THEN '' Weekend Day'' END 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' END AS Occurs_detail	
, CASE s.freq_subday_type 
     WHEN 1 THEN ''Occurs once at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
     WHEN 2 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Seconds(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 4 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Minute(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 8 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Hour(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN ''On date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' At time: '' 
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN ''Start date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' end date: '' 
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + '' '' 
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE 1 = 1 
@Filter
ORDER BY j.name'

IF @Filter = 'Y'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ')
ELSE
IF @Filter = 'N'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ')
ELSE
IF @Filter = 'X'
   SET @sql = REPLACE(@sql,'@Filter', 
                            'AND s.active_end_date < convert(varchar(8),GetDate(),112) ')
ELSE
   SET @sql = REPLACE(@sql,'@Filter','')

EXEC(@sql)

END

Open in new window

What am I missing here?
0
Comment
Question by:Hers2keep
  • 3
  • 2
6 Comments
 
LVL 32

Assisted Solution

by:Big Monty
Big Monty earned 150 total points
ID: 39628435
try using a Table variable instead:

DECLARE @xp_results TABLE ([id] [int] NULL.......

temp tables are lost once the session is closed down
0
 

Author Comment

by:Hers2keep
ID: 39628506
Okay I made that change and the code looks like this:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAllAgentJobs]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Filter CHAR(1)
SET @Filter = 'A'

DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname
DECLARE @xp_results TABLE (
     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, 
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL, 
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL
     );

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
	
SET @sql = '
SELECT 
  ''sdwinsql03v'' as ServerName
, j.name AS JobName
, c.name AS Category
, CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled
, CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled
, j.description 
, CASE s.freq_type 
     WHEN  1 THEN ''Once''
     WHEN  4 THEN ''Daily''
     WHEN  8 THEN ''Weekly''
     WHEN 16 THEN ''Monthly''
     WHEN 32 THEN ''Monthly relative''
     WHEN 64 THEN ''When SQL Server Agent starts'' 
     WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs 	
, CASE s.freq_type 
     WHEN  1 THEN ''O''
     WHEN  4 THEN ''Every '' 
        + convert(varchar,s.freq_interval) 
        + '' day(s)''
     WHEN  8 THEN ''Every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' weeks(s) on '' 
        + master.dbo.fn_freq_interval_desc(s.freq_interval)					  
     WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' 
     WHEN 32 THEN ''The '' 
        + CASE s.freq_relative_interval	
            WHEN  1 THEN ''First''
            WHEN  2 THEN ''Second''
            WHEN  4 THEN ''Third''	
            WHEN  8 THEN ''Fourth''
            WHEN 16 THEN ''Last'' END 
        + CASE s.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 '' Weekday''
            WHEN 10 THEN '' Weekend Day'' END 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' END AS Occurs_detail	
, CASE s.freq_subday_type 
     WHEN 1 THEN ''Occurs once at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
     WHEN 2 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Seconds(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 4 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Minute(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 8 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Hour(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN ''On date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' At time: '' 
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN ''Start date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' end date: '' 
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + '' '' 
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN @xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE 1 = 1 
@Filter
ORDER BY j.name'

IF @Filter = 'Y'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ')
ELSE
IF @Filter = 'N'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ')
ELSE
IF @Filter = 'X'
   SET @sql = REPLACE(@sql,'@Filter', 
                            'AND s.active_end_date < convert(varchar(8),GetDate(),112) ')
ELSE
   SET @sql = REPLACE(@sql,'@Filter','')

EXEC(@sql)

END

Open in new window

Now the alter statement works successfully, but upon execution I get this:
/*------------------------
EXEC uspAllAgentJobs
------------------------*/
Msg 1087, Level 15, State 2, Line 86
Must declare the table variable "@xp_results".

Open in new window

The variable IS declared, so I don't know why this error keeps popping up.
0
 
LVL 12

Assisted Solution

by:deanvanrooyen
deanvanrooyen earned 350 total points
ID: 39632080
temp tables are lost once the session is closed down - strictly speaking this is not correct, loosely speaking yes it is :) I am only mentioning this because I was told to explicitly drop a temp table in a proc once and I decided to do some digging, and yes good practise generally wins over technicalities but I'm always up for sharing really good info when I find it...

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 39634832
your are executing a string that has no knowledge of the temp table as it is declared outside of the string.

I cant fix it as I dont have your master functions - what you can do...

move the start of the string declaration above the temp table e.g.

DECLARE @sql VARCHAR(8000)
SET @sql = '
DECLARE @is_sysadmin INT
.....
this line will need a double quote around sysadmin
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N''sysadmin''), 0)


Then add a print statement just before the string is executed then you can grab that and execute is as a normal script which is way easier to debug... e.g. you will see things like you cannot add nolock hints to your temp table, its just so much easier

...
   PRINT @sql
EXEC(@sql)
0
 

Accepted Solution

by:
Hers2keep earned 0 total points
ID: 39639603
I was not able to get this script working correctly. However, I did find another script that does exactly what I wanted. It is a combination of a function and a stored procedure.
USE [msdb]
GO

/****** Object:  UserDefinedFunction [dbo].[udf_schedule_description]    Script Date: 11/11/2013 13:10:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT , 
  @freq_interval INT , 
  @freq_subday_type INT , 
  @freq_subday_interval INT , 
  @freq_relative_interval INT , 
  @freq_recurrence_factor INT , 
  @active_start_date INT , 
  @active_end_date INT, 
  @active_start_time INT , 
  @active_end_time INT ) 
RETURNS NVARCHAR(255) AS 
BEGIN 
DECLARE @schedule_description NVARCHAR(255) 
DECLARE @loop INT 
DECLARE @idle_cpu_percent INT 
DECLARE @idle_cpu_duration INT 

IF (@freq_type = 0x1) -- OneTime 
BEGIN 
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2)) 
RETURN @schedule_description 
END 
IF (@freq_type = 0x4) -- Daily 
BEGIN 
SELECT @schedule_description = N'Every day ' 
END 
IF (@freq_type = 0x8) -- Weekly 
BEGIN 
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on ' 
SELECT @loop = 1 
WHILE (@loop <= 7) 
BEGIN 
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1)) 
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', ' 
SELECT @loop = @loop + 1 
END 
IF (RIGHT(@schedule_description, 2) = N', ') 
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' ' 
END 
IF (@freq_type = 0x10) -- Monthly 
BEGIN 
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month ' 
END 
IF (@freq_type = 0x20) -- Monthly Relative 
BEGIN 
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the ' 
SELECT @schedule_description = @schedule_description + 
CASE @freq_relative_interval 
WHEN 0x01 THEN N'first ' 
WHEN 0x02 THEN N'second ' 
WHEN 0x04 THEN N'third ' 
WHEN 0x08 THEN N'fourth ' 
WHEN 0x10 THEN N'last ' 
END + 
CASE 
WHEN (@freq_interval > 00) 
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval)) 
WHEN (@freq_interval = 08) THEN N'day' 
WHEN (@freq_interval = 09) THEN N'week day' 
WHEN (@freq_interval = 10) THEN N'weekend day' 
END + N' of that month ' 
END 
IF (@freq_type = 0x40) -- AutoStart 
BEGIN 
SELECT @schedule_description = FORMATMESSAGE(14579) 
RETURN @schedule_description 
END 
IF (@freq_type = 0x80) -- OnIdle 
BEGIN 
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
N'IdleCPUPercent', 
@idle_cpu_percent OUTPUT, 
N'no_output' 
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
N'IdleCPUDuration', 
@idle_cpu_duration OUTPUT, 
N'no_output' 
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600)) 
RETURN @schedule_description 
END 
-- Subday stuff 
SELECT @schedule_description = @schedule_description + 
CASE @freq_subday_type 
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2)) 
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)' 
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)' 
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)' 
END 
IF (@freq_subday_type IN (0x2, 0x4, 0x8)) 
SELECT @schedule_description = @schedule_description + N' between ' + 
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) ) 

RETURN @schedule_description 
END
GO

Open in new window

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[uspAllAgentJobs]    Script Date: 11/11/2013 13:11:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspAllAgentJobs]
AS
/* SQL SERVER 2000 */
BEGIN
SET NOCOUNT ON;
SELECT 'sd-sql' AS serverName, sysjobs.name, CAST((sysjobschedules.active_start_time / 10000) AS VARCHAR(10)) + ':' + 
RIGHT('00' + CAST((sysjobschedules.active_start_time % 10000) / 100 AS VARCHAR(10)),2) active_start_time,  
dbo.udf_schedule_description(sysjobschedules.freq_type, 
sysjobschedules.freq_interval,  
sysjobschedules.freq_subday_type, 
sysjobschedules.freq_subday_interval, 
sysjobschedules.freq_relative_interval,  
sysjobschedules.freq_recurrence_factor, 
sysjobschedules.active_start_date, 
sysjobschedules.active_end_date,  
sysjobschedules.active_start_time, 
sysjobschedules.active_end_time) AS ScheduleDscr, sysjobs.enabled 
FROM sysjobs INNER JOIN 
sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id 
END

GO

Open in new window

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[uspAllAgentJobs]    Script Date: 11/11/2013 13:13:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspAllAgentJobs]
AS
/* SQL SERVER 2005+ */
BEGIN
SET NOCOUNT ON;
SELECT 'sdwinsql03v' AS serverName, dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))   
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time,  
dbo.udf_schedule_description(dbo.sysschedules.freq_type, 
dbo.sysschedules.freq_interval,  
dbo.sysschedules.freq_subday_type, 
dbo.sysschedules.freq_subday_interval, 
dbo.sysschedules.freq_relative_interval,  
dbo.sysschedules.freq_recurrence_factor, 
dbo.sysschedules.active_start_date, 
dbo.sysschedules.active_end_date,  
dbo.sysschedules.active_start_time, 
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled  
FROM dbo.sysjobs INNER JOIN  
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN  
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id  

END

GO

Open in new window

0
 

Author Closing Comment

by:Hers2keep
ID: 39653037
I appreciate the help guys.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now