Month: May 2016

Useful Script for checking SQL job History from Query

USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(‘000000′ + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,’:’),3,0,’:’) RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then ‘failed’
when 1 then ‘Succeded’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
end as ExecutionStatus,
h.message MessageGenerated,*
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where j.name =’jobname’
ORDER BY j.name, h.run_date, h.run_time
GO

What is Stored Procedure (Part 1)

Stored Procedure

  • The Stored procedure is actually a piece of code that can be saved in order to reuse it and saves a lot of time instead of writing the queries over and over again.
  • Depending on the need , SQL stored procedure can also use parameters as Input and Output.
  • It is a secure place to write your code , for instance : A stored procedure can be encrypted which secures it from unauthorized access.
  • Permissions can be given to only particular users to access the stored procedure to have a better control of security .
  • When a stored procedure is executed  , it gets stored in the cache memory which increases performance and efficiency.
  • They are executed after writing execute statement.
  • Better error handling can be done in the stored procedure.