InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 10:20:49 GMT20http://www.sqlservercentral.com/Forums/Topic1405305-23-1.aspx[quote][b]PritamSalvi (1/10/2013)[/b][hr]I have one more script also to find current running job on the server which i need to insert in the temp table and with using IF else loop i can set email alert notification -SELECT top 1 j.name as Running_Jobs, ja.Start_execution_date As Starting_time, datediff(ss, ja.Start_execution_date,getdate())/60 as [Has_been_running(in Min)]FROM msdb.dbo.sysjobactivity jaJOIN msdb.dbo.sysjobs jON j.job_id=ja.job_idWHERE job_history_id is null AND start_execution_date is NOT NULL and start_execution_date > GETDATE() -1 and datediff(ss, ja.Start_execution_date,getdate())/60 > 60 --and j.name like '%gdc%'ORDER BY start_execution_date Can somebody help me.Thanks in advance.[/quote]If this is inserting into a temp table, once done create a sp_send_dbmail script underneath it to check if a row exists in the table and if so email the people who need to knowINSERT INTO #TempSELECT TOP 1 ............... IF @@RowCount > 0BEGINEXEC MSDB.dbo.sp_send_dbmail.....Thu, 10 Jan 2013 03:41:30 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1405305-23-1.aspx[quote][b]PritamSalvi (1/10/2013)[/b][hr]Hello,I have one script which displays current running job on the server.I want to insert this result into a temp table and wants to setup job running out of SLA but i am getting error as below -Msg 257, Level 16, State 3, Line 9Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.Script is as below -CREATE TABLE #CURRENTJOBS (SERVER_NAME VARCHAR(200), JOB_NAME VARCHAR(200), JOB_RUN_DATE_TIME VARCHAR(200), CURRENT_RUN_TIME VARCHAR(200), JOB_RUN_SECONDS INT);INSERT INTO #CURRENTJOBSSELECT SERVERPROPERTY('MachineName') AS SERVER_NAME,job.Name AS JOB_NAME , CONVERT(Varchar(20),activity.run_requested_Date,109) AS JOB_RUN_DATE_TIME, RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))/3600)),2) + ':' + RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))%3600)/60),2) + ':' + RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))%60)),2) AS CURRENT_RUN_TIME, (DATEDIFF(ss, activity.Run_requested_Date, GETDATE())) JOB_RUN_SECONDS FROM msdb.dbo.sysjobs_view job INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id) WHERE run_Requested_date IS NOT NULL AND stop_execution_date IS NULL and Run_requested_Date > GETDATE()-2; Can you help me.[/quote]Wrap all your SERVERPROPERY call up into a convert so that it is explicitly casted to VARCHAR, as SQL will not implicitly case it for you.Thu, 10 Jan 2013 03:38:15 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1405305-23-1.aspxI have one more script also to find current running job on the server which i need to insert in the temp table and with using IF else loop i can set email alert notification -SELECT top 1 j.name as Running_Jobs, ja.Start_execution_date As Starting_time, datediff(ss, ja.Start_execution_date,getdate())/60 as [Has_been_running(in Min)]FROM msdb.dbo.sysjobactivity jaJOIN msdb.dbo.sysjobs jON j.job_id=ja.job_idWHERE job_history_id is null AND start_execution_date is NOT NULL and start_execution_date > GETDATE() -1 and datediff(ss, ja.Start_execution_date,getdate())/60 > 60 --and j.name like '%gdc%'ORDER BY start_execution_date Can somebody help me.Thanks in advance.Thu, 10 Jan 2013 03:26:32 GMTPritamSalvihttp://www.sqlservercentral.com/Forums/Topic1405305-23-1.aspxHello,I have one script which displays current running job on the server.I want to insert this result into a temp table and wants to setup job running out of SLA but i am getting error as below -Msg 257, Level 16, State 3, Line 9Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.Script is as below -CREATE TABLE #CURRENTJOBS (SERVER_NAME VARCHAR(200), JOB_NAME VARCHAR(200), JOB_RUN_DATE_TIME VARCHAR(200), CURRENT_RUN_TIME VARCHAR(200), JOB_RUN_SECONDS INT);INSERT INTO #CURRENTJOBSSELECT SERVERPROPERTY('MachineName') AS SERVER_NAME,job.Name AS JOB_NAME , CONVERT(Varchar(20),activity.run_requested_Date,109) AS JOB_RUN_DATE_TIME, RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))/3600)),2) + ':' + RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))%3600)/60),2) + ':' + RIGHT('0'+CONVERT(VARCHAR(2),((DATEDIFF(ss, activity.Run_requested_Date, GETDATE()))%60)),2) AS CURRENT_RUN_TIME, (DATEDIFF(ss, activity.Run_requested_Date, GETDATE())) JOB_RUN_SECONDS FROM msdb.dbo.sysjobs_view job INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id) WHERE run_Requested_date IS NOT NULL AND stop_execution_date IS NULL and Run_requested_Date > GETDATE()-2; Can you help me.Thu, 10 Jan 2013 03:23:43 GMTPritamSalvi