Stored Procedure Runs in SQL, Fails on aspx.net
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.
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)
ENDSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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)
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'
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I appreciate the help guys.
ASKER
Open in new window
Now the alter statement works successfully, but upon execution I get this:Open in new window
The variable IS declared, so I don't know why this error keeps popping up.