Creating a SQL job - SQL Server Forums

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

Creating a SQL job

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-02 : 11:53:31

I need to create a sql job which does several things. First, i need the job to check for the existence of the SPROC and the existence of the related db/tables. If the sproc and those tables do not exist, create them. Then, i need the sproc to run (on a schedule, yet to be determined). I have the script to create this job below, but i know it's missing something(s). Any review and feedback is appreciated.

DECLARE @JobID BINARY(16)

EXECUTE msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'GetStatsSchedule',
@enabled = 1,
@owner_login_name = N'sa',
@description = N'Create SPROC and tables for GetStats and schedule job for execution thereof',
@category_name = N'[Uncategorized (Local)]',
@notify_level_eventlog = 2,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0;

EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Create Table',
@command = N'USE [master]
GO

/****** Object: Database [SIZING_DB] Script Date: 11/22/2011 11:27:19 ******/
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',
@subsystem = N'TSQL',
@flags = 2,
@retry_attempts = 1,
@retry_interval = 0,
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 2
;
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Create Sproc',
@command = N'USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_GetSTATS] Script Date: 11/22/2011 11:24:44 ******/
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 DB_NAME() as DBName, 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',
@subsystem = N'TSQL',
@flags = 2,
@retry_attempts = 1,
@retry_interval = 0,
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 2
;
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Execute SPROC',
@command = N'USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_GetSTATS]

SELECT ''Return Value'' = @return_value

GO',
@database_name = N'master',
@subsystem = N'TSQL',
@flags = 2,
@retry_attempts = 1,
@retry_interval = 0,
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 2
;
EXECUTE msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
GO

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-02 : 11:59:43

Also, is it possible to only have the third step of the job run on the schedule? Once it's run the first time, there is no need for it to use the first two steps again.

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-02 : 12:51:21

Going once, going twice? I have abandonment issues.
I also just realized that the create portion for the sizing_db is missing the creates for the tables within. just assume those to be inserted as steps after step 1.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources