Adding condition to sql task - 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

Adding condition to sql task

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-30 : 18:02:26

Hi

I am creating an SSIS package which gets data from msdb.dbo.sysjobhistory to a temp table where
run_duration is > 18000(which is 5hrs), when the data greater than run_duration > 18000 enters
into temp table i have created an identity row_num in temp table which increases by 1,
So i have created a stored procedure which generated an email to notify with an alert saying the Job has hanged for more than 5hrs,

Here is the structure of my SSIS Packages
1.First package gets data from sysjobhistory table , load into temp table
2.Need to write a condition if row_num > 1 to
execute the sql task to run the store procedure which sends an email,

So i have created stored procedure but dont understand where to give the condition if row_num is > 1 ..

Another Option I have tried is by creating a Job and writing the T -SQL Code as below, but cannot understand where and how to give 'if' condition?

SELECT * FROM sysjobhistory
WHERE run_duration >= '18000'
and run_date = (convert(varchar,getdate(),112))-2
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'qctreport',
@recipients = xyz@xyz.com',
@subject=N'ALERT :: Job Hanged for More than 5hrs.....' ;
END
GO

Could anybody please help...

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 18:30:00

If you only need to know whether there's any records in the temp table, and send an email if there is, then you can use this.


IF EXISTS (SELECT TOP 1 * FROM #TempTable)
BEGIN
	-- INSERT YOU CODE HERE 

END

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:23:00

didnt understand the purpose behing check row_num > 1? it will always be >1 after the first insertion

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources