Job Scheduling in SQL Server

Schedule Jobs:

Jobs are scheduled objects which will execute a sequence of stored statements at a particular time or at regular intervals. Considering a practical scenario like, I need to take a backup of my database at regular intervals (Daily at 12AM). How can I achieve it?

This can be achieved through jobs in sql server. I will take a very simple scenario of inserting a record in a table. This insert should happen on weekly basis (One time) with no end date. Let's see the step by step procedure to ahieve it.



Below is the table (I need to insert into this table),



Check your SQL Server agent or else start your agent (The easier way is go to your services window and search for your sql server agent and start it) . Under SQL Server Agent -> jobs -> right click and create the job. Provide the Name of your job and possible description. You can specify the owner the job too.

Next Stage:

Got to steps tab, and click New to create the steps. A job can have any number of steps. We can arrange the step. So that, it will execute on the prescribed order.

On clicking the new button in the above window, we will get the steps window. You can provide the step name and command to be executed.

After providing the command, you can check the syntax by clicking the parse button.

Go to the scheduled tab and you can schedule your job. A job may have multiple schedules.

Now, I am creating the job which recurrent/weekly once /only on sunday /Occurs at 12AM /Start date is today / No end date.

Goto the next tab "Alert" -> You can customize the message for the job failure or else you can take the existing alerts.

Go to the Notification tab -> You can specify the notification details. How you want the notification through email or through page or net send or just write it in windows app event log.

That's it. Your job is ready to go. On clicking the Jobs, you can see your new job.

Considering, I need to execute the job now. Just right click the job and click start job.

To check the activity of the job in the servers. Right click job activity monitor and check it.

In SSMS, you can check the job details in the msdb tables.

select * from sysjobs -> List down all the jobs available in the server. It holds all the information like job name/description/owner/job failure details/versionnumber/job creation date/job modification date.

select * from dbo.sysjobschedules - >This table will show the scheduled job details. Job name/next run date.

OOPS!!! That's a very lengthy article and I would like to close it. Will talk about more on Jobs in my future articles.

Cheers,

Venkatesan Prabu .J

Peculiar nature of identity insert in a table

Peculiar nature of identify column in sql server:
I have tried to insert some records in an identity column and got confused on the below scenario. It seems to be a bug in sql server :0 I think so,

DROP TABLE Venkattemp
-- Am creating a table
create table Venkattemp (id int identity(1,1),VAL INT)
INSERT INTO Venkattemp(VAL) VALUES (1)
Truncate table Venkattemp
-- Reseeding the identity column
DBCC CHECKIDENT ('Venkattemp', RESEED, 1)
INSERT INTO Venkattemp(VAL) VALUES (1)
INSERT INTO Venkattemp(VAL) VALUES (1)
-- Checking the current identity value on the table
select IDENT_CURRENT('Venkattemp')+1
DELETE Venkattemp WHERE ID =3
select IDENT_CURRENT('Venkattemp')+1
-- Here, why its inserting value 3 in the identity column. This is an unexpected behaviour of sql server
INSERT INTO Venkattemp(VAL) VALUES (5)
SELECT * FROM Venkattemp
select IDENT_CURRENT('Venkattemp')
INSERT INTO Venkattemp(VAL) VALUES (5)
INSERT INTO Venkattemp(VAL) VALUES (5)
INSERT INTO Venkattemp(VAL) VALUES (5)
select IDENT_CURRENT('Venkattemp')
DELETE Venkattemp WHERE ID =6
select IDENT_CURRENT('Venkattemp')
-- Here, why its not inserting 6 in the below insert statement. This is an expected behaviour of sql server
INSERT INTO Venkattemp(VAL) VALUES (5)
SELECT * FROM Venkattemp

Thanks and Regards,
Venkatesan Prabu .J

SQL Query challenge

Hi Readers,

Have you tried inserting a record in the table like below,

create table Venkattemp (id int identity(1,1))

Any inputs????????

Thanks and Regards,
Venkatesan Prabu .J

Search string in the stored procedure

Searching a string in the stored procedures:

Am writing after a very long break :-) I got a peculiar question in one of the forum and wish to start my article from the question started.

The query is, "Am having hard coded ip address values in the stored procedure and I need to find all the stored procedures" How to find it?

Let's see, How can we address the above said issue.

Am creating the stored procedure

CREATE PROCEDURE Venkatproc
AS BEGIN
DECLARE @IP VARCHAR(100)
SET @IP ='12.34.56.78'
END

All stored procedures/function details can be fetched from a system related view information_schema.routines

Let's see the same,

select * from information_schema.routines

The above query will return all the stored procedures and functions available in the database + properties and details of the objects like owner of the object, which database, when its created, whats the definition etc..,

Let move the problem, We can use the like operator to identify any hard coded IP address and it needs to be searched in the routiine definition column.

select x.ROUTINE_DEFINITION,x.specific_name,x.* from information_schema.routines x
where (x.ROUTINE_DEFINITION LIKE '%.[0-9].%' or x.ROUTINE_DEFINITION LIKE '%.[0-9][0-9].%' or x.ROUTINE_DEFINITION LIKE '%.[0-9][0-9][0-9].%')

Happy Learning!!!

Thanks and Regards,

Venkatesan Prabu .J