Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert error - convert failed Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 4:23 AM
Points: 4, Visits: 40
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.
Post #1405305
Posted Thursday, January 10, 2013 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 4:23 AM
Points: 4, Visits: 40
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 ja
JOIN msdb.dbo.sysjobs j
ON j.job_id=ja.job_id
WHERE 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.
Post #1405308
Posted Thursday, January 10, 2013 3:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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.


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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1405315
Posted Thursday, January 10, 2013 3:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
PritamSalvi (1/10/2013)
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 ja
JOIN msdb.dbo.sysjobs j
ON j.job_id=ja.job_id
WHERE 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.


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 know


INSERT INTO #Temp
SELECT TOP 1 ...............

IF @@RowCount > 0
BEGIN
EXEC MSDB.dbo.sp_send_dbmail
.....




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1405318
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse