PritamSalvi (1/10/2013)

---

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 9

Implicit 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 #CURRENTJOBS

SELECT 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.