Job Scheduling in sql server express edition

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

SQL Server 2005 Forums

Transact-SQL (2005)

Job Scheduling in sql server express edition

Author

Topic

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-29 : 23:20:16

Hi,
one can tel to do job scheduling in sql server 2005 express edition.
i am new to this please one can help .

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-30 : 00:09:11

Create the script you want to execute....
then put this in a batch file.. with your info

sqlcmd -S.\sqlexpress -i"D:\SQL\Scripts\YourDailyRoutines.sql"

Then point to the batch file from a scheduled Task.
hth,
..bob

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 00:14:51

i dnt knw h to write script

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 00:16:21

can u tel me some of the link which helps to do job scheduling

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-30 : 01:11:26

Have a look at:
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 02:30:38

how service broker is helpfull for sheduling job

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 04:56:28

Hi,
can one tel me step by step procedure to perform

Scheduling Jobs in SQL Server Express

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 05:59:18

hi,

i my schedulling job in sql server express is running but dnt know how to take backup every 5 min

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-30 : 06:04:46

quote:
Originally posted by yadhu_cse

hi,

i my schedulling job in sql server express is running but dnt know how to take backup every 5 min

Write a sp for backup and call the SP in the job every 5 minutes.

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 06:11:58

i just followed the article by sqlteam http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

and
http://msft.itcommunity.ru/blogs/_aslsqlru/archive/2009/12/19/87818.aspx

i dnt know where to change

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-30 : 06:39:22

Can you post the code you have tried ?

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-30 : 06:56:08

this is the code i am trying .

ALTER DATABASE test SET ENABLE_BROKER
GO

USE test
GO

IF object_id('ScheduledJobs') IS NOT NULL
DROP TABLE ScheduledJobs

GO
CREATE TABLE ScheduledJobs
(
ID INT IDENTITY(1,1),
ScheduledSql nvarchar(max) NOT NULL,
FirstRunOn datetime NOT NULL,
LastRunOn datetime,
LastRunOK BIT NOT NULL DEFAULT (0),
IsRepeatable BIT NOT NULL DEFAULT (0),
IsEnabled BIT NOT NULL DEFAULT (0),
ConversationHandle uniqueidentifier NULL
)
GO

IF object_id('ScheduledJobsErrors') IS NOT NULL
DROP TABLE ScheduledJobsErrors
CREATE TABLE ScheduledJobsErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorLine INT,
ErrorNumber INT,
ErrorMessage NVARCHAR(MAX),
ErrorSeverity INT,
ErrorState INT,
ScheduledJobId INT,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO

IF OBJECT_ID('usp_RemoveScheduledJob') IS NOT NULL
DROP PROC usp_RemoveScheduledJob

GO
CREATE PROC usp_RemoveScheduledJob
@ScheduledJobId INT
AS
BEGIN TRANSACTION
BEGIN TRY
DECLARE @ConversationHandle UNIQUEIDENTIFIER
-- get the conversation handle for our job
SELECT @ConversationHandle = ConversationHandle
FROM ScheduledJobs
WHERE Id = @ScheduledJobId

IF @@ROWCOUNT = 0
RETURN;

-- end the conversation if it is active
IF EXISTS (SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = @ConversationHandle)
END CONVERSATION @ConversationHandle

-- delete the scheduled job from out table
DELETE ScheduledJobs WHERE Id = @ScheduledJobId

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
INSERT INTO ScheduledJobsErrors (
ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, ScheduledJobId)
SELECT ERROR_LINE(), ERROR_NUMBER(), 'usp_RemoveScheduledJob: ' + ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @ScheduledJobId
END CATCH

GO
IF OBJECT_ID('usp_AddScheduledJob') IS NOT NULL
DROP PROC usp_AddScheduledJob

GO
CREATE PROC usp_AddScheduledJob
(
@ScheduledSql NVARCHAR(MAX),
@FirstRunOn DATETIME,
@IsRepeatable BIT
)
AS
DECLARE @ScheduledJobId INT, @TimeoutInSeconds INT, @ConversationHandle UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN TRY
-- add job to our table
INSERT INTO ScheduledJobs(ScheduledSql, FirstRunOn, IsRepeatable, ConversationHandle)
VALUES (@ScheduledSql, @FirstRunOn, @IsRepeatable, NULL)
SELECT @ScheduledJobId = SCOPE_IDENTITY()

SELECT @TimeoutInSeconds = DATEDIFF(s, GETDATE(), @FirstRunOn);
-- begin a conversation for our scheduled job
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [//ScheduledJobService]
TO SERVICE '//ScheduledJobService',
'CURRENT DATABASE'
ON CONTRACT [//ScheduledJobContract]
WITH ENCRYPTION = OFF;

-- start the conversation timer
BEGIN CONVERSATION TIMER (@ConversationHandle)
TIMEOUT = @TimeoutInSeconds;
-- associate or scheduled job with the conversation via the Conversation Handle
UPDATE ScheduledJobs
SET ConversationHandle = @ConversationHandle,
IsEnabled = 1
WHERE ID = @ScheduledJobId
IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
INSERT INTO ScheduledJobsErrors (
ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, ScheduledJobId)
SELECT ERROR_LINE(), ERROR_NUMBER(), 'usp_AddScheduledJob: ' + ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @ScheduledJobId
END CATCH

GO
IF OBJECT_ID('usp_RunScheduledJob') IS NOT NULL
DROP PROC usp_RunScheduledJob

GO
CREATE PROC usp_RunScheduledJob
AS
DECLARE @ConversationHandle UNIQUEIDENTIFIER, @ScheduledJobId INT, @LastRunOn DATETIME, @IsEnabled BIT, @LastRunOK BIT

SELECT @LastRunOn = GETDATE(), @IsEnabled = 0, @LastRunOK = 0
-- we don't need transactions since we don't want to put the job back in the queue if it fails
BEGIN TRY
DECLARE @message_type_name sysname;
-- receive only one message from the queue
RECEIVE TOP(1)
@ConversationHandle = conversation_handle,
@message_type_name = message_type_name
FROM ScheduledJobQueue

-- exit if no message or other type of message than DialgTimer
IF @@ROWCOUNT = 0 OR ISNULL(@message_type_name, '') != 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
RETURN;

DECLARE @ScheduledSql NVARCHAR(MAX), @IsRepeatable BIT
-- get a scheduled job that is enabled and is associated with our conversation handle.
-- if a job fails we disable it by setting IsEnabled to 0
SELECT @ScheduledJobId = ID, @ScheduledSql = ScheduledSql, @IsRepeatable = IsRepeatable
FROM ScheduledJobs
WHERE ConversationHandle = @ConversationHandle AND IsEnabled = 1

IF @IsRepeatable = 0
BEGIN
END CONVERSATION @ConversationHandle
SELECT @IsEnabled = 0
END
ELSE
BEGIN
-- reset the timer to fire again in one day
BEGIN CONVERSATION TIMER (@ConversationHandle)
TIMEOUT = 6;
SELECT @IsEnabled = 1
END

-- run our job
EXEC (@ScheduledSql)

SELECT @LastRunOK = 1
END TRY
BEGIN CATCH
SELECT @IsEnabled = 0

INSERT INTO ScheduledJobsErrors (
ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, ScheduledJobId)
SELECT ERROR_LINE(), ERROR_NUMBER(), 'usp_RunScheduledJob: ' + ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @ScheduledJobId

-- if an error happens end our conversation if it exists
IF @ConversationHandle != NULL
BEGIN
IF EXISTS (SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = @ConversationHandle)
END CONVERSATION @ConversationHandle
END

END CATCH;
-- update the job status
UPDATE ScheduledJobs
SET LastRunOn = @LastRunOn,
IsEnabled = @IsEnabled,
LastRunOK = @LastRunOK
WHERE ID = @ScheduledJobId
GO

IF EXISTS(SELECT * FROM sys.services WHERE NAME = N'//ScheduledJobService')
DROP SERVICE [//ScheduledJobService]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = N'ScheduledJobQueue')
DROP QUEUE ScheduledJobQueue

IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = N'//ScheduledJobContract')
DROP CONTRACT [//ScheduledJobContract]

GO
CREATE CONTRACT [//ScheduledJobContract]
([http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer] SENT BY INITIATOR)

CREATE QUEUE ScheduledJobQueue
WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = usp_RunScheduledJob,
MAX_QUEUE_READERS = 20, -- we expect max 20 jobs to start simultaneously
EXECUTE AS 'dbo' );

CREATE SERVICE [//ScheduledJobService]
AUTHORIZATION dbo
ON QUEUE ScheduledJobQueue ([//ScheduledJobContract])

GO
DECLARE @ScheduledSql nvarchar(max), @RunOn datetime, @IsRepeatable BIT
SELECT @ScheduledSql = N'DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512);
SELECT @backupTime = GETDATE(),
@backupFile = ''c:\backup\Test_'' +
replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), '' '', ''_''), '':'', ''_'') +
N''.bak'';
BACKUP DATABASE test TO DISK = @backupFile; ',
@RunOn = dateadd(s, 1, getdate()),
@IsRepeatable = 1

EXEC usp_AddScheduledJob @ScheduledSql, @RunOn, @IsRepeatable
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources