scripts fail when added to an agent job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

scripts fail when added to an agent job

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-02 : 21:50:53

I have a sql agent job that is supposed to do the following:
1. Create a new db (sizing_db)
2. create the tables in that db (4 tables in total)
3. Create a sproc in the master db
4. Exec a system stored procedure

When i fire off any of the create scripts independently, they all work flawlessly. When i combine them as seperate steps in the agent job, the job consistenly fails. Any idea why? The create script for the agent job is pasted below. Actually, the create scripts are combined as one step and the exec of the system store procedure is set as step 2. The failure happens even if all of the create scripts are their own step:

USE [msdb]
GO

/****** Object: Job [1] Script Date: 12/02/2011 20:52:49 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/02/2011 20:52:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SMARTCOPINC\jhamel', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 12/02/2011 20:52:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [master]
GO

/****** Object: Database [SIZING_DB] Script Date: 12/02/2011 14:32:17 ******/
CREATE DATABASE [SIZING_DB] ON PRIMARY(
NAME = N''SIZING_DB'',
FILENAME =
N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SIZING_DB.mdf'',
SIZE = 3072KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N''SIZING_DB_log'',
FILENAME =
N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SIZING_DB_log.ldf'',
SIZE = 1024KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)
GO

ALTER DATABASE [SIZING_DB]
SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
BEGIN
EXEC [SIZING_DB].[dbo].[sp_fulltext_database] @action = ''enable''
END
GO

ALTER DATABASE [SIZING_DB]
SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [SIZING_DB]
SET ANSI_NULLS OFF
GO

ALTER DATABASE [SIZING_DB]
SET ANSI_PADDING OFF
GO

ALTER DATABASE [SIZING_DB]
SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [SIZING_DB]
SET ARITHABORT OFF
GO

ALTER DATABASE [SIZING_DB]
SET AUTO_CLOSE OFF
GO

ALTER DATABASE [SIZING_DB]
SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [SIZING_DB]
SET AUTO_SHRINK OFF
GO

ALTER DATABASE [SIZING_DB]
SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [SIZING_DB]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [SIZING_DB]
SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [SIZING_DB]
SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [SIZING_DB]
SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [SIZING_DB]
SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [SIZING_DB]
SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [SIZING_DB]
SET DISABLE_BROKER
GO

ALTER DATABASE [SIZING_DB]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [SIZING_DB]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [SIZING_DB]
SET TRUSTWORTHY OFF
GO

ALTER DATABASE [SIZING_DB]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [SIZING_DB]
SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [SIZING_DB]
SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [SIZING_DB]
SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [SIZING_DB]
SET READ_WRITE
GO

ALTER DATABASE [SIZING_DB]
SET RECOVERY FULL
GO

ALTER DATABASE [SIZING_DB]
SET MULTI_USER
GO

ALTER DATABASE [SIZING_DB]
SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [SIZING_DB]
SET DB_CHAINING OFF
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [2] Script Date: 12/02/2011 20:52:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_GetSTATS] Script Date: 12/02/2011 14:29:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetSTATS]
WITH

EXECUTE AS CALLER
AS

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
DATA VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t
EXEC sp_msForEachTable ''EXEC sp_spaceused ''''?''''''

INSERT INTO SIZING_DB.dbo.DBTableStats
SELECT NAME,
ROWS,
reserved,
DATA,
index_size,
unused,
GETDATE() AS datecreated
FROM #t

INSERT INTO SIZING_DB.dbo.rowcounts
SELECT DB_NAME() AS DBName,
GETDATE() AS DateCreated,
SUM(CAST([rows] AS INT)) AS [rows]
FROM #t

DROP TABLE #t

INSERT INTO sizing_db.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units

INSERT INTO sizing_db.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, ''spaceused'') AS pages_used,
FILEPROPERTY(DF.name, ''spaceused'') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM sizing_db.dbo.dbstats dbstats
INNER JOIN sizing_db.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

SELECT *
FROM sizing_db.dbo.DBTableStats ds

GO

',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [3] Script Date: 12/02/2011 20:52:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_msForEachdb sp_getstats',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Bangers and Mash are neither "Bangers" nor "Mash".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 00:14:12

can you tell us what error you're getting when they're run as steps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-03 : 08:37:51

I was just getting a syntax error very early in the script. I've since moved past that by adding each step incrementally and adding IF EXISTS statements. Now i'm at the next hurdle. See my more recent post. I'm in the last two steps of this, then i'll generate the create script for the agent job and hope that it's syntax holds together.

Bangers and Mash are neither "Bangers" nor "Mash".

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-05 : 09:15:07

Just for the record, i discovered that the problem lies within SQLAssistant, which i typically use to format my code (to give it a cleaner visual flow) at the end of a project. When i combine the steps in this agent job and leave them just as they were upon cutting and pasting the code, all is well. If it then apply SQLAssistant's "Format Code" command, it wreaks havoc with the syntax. I'll choose working code over pretty code any day of the week.

Bangers and Mash are neither "Bangers" nor "Mash".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources