May 20, 2021 at 9:11 pm
Below is the step which is failing due to "Error converting data type varchar to int. [SQLSTATE 42000] (Error 8114)". Please let me know if any suggestions to help fix it.
SET NOCOUNT ON;
TRUNCATE TABLE StatsAdminTest.dbo.tbactiverole;
insert into StatsAdminTest.dbo.tbactiverole
exec sp_who2;
Set DATEFORMAT YMD;
DECLARE @kill varchar(8000) = '';
SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), SPID) + ';'
FROM StatsAdminTest.dbo.tbactiverole
Where HostName IN ('WINDOWS / HOST NAME HERE')
AND DATEDIFF(hour,Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', '')),getdate()) > 8
EXEC(@kill);
May 21, 2021 at 6:58 am
If I am reading this correctly, you are trying to kill sessions that has been inactive for more than 8 hours.
What is the table defintion of StatsAdminTest.dbo.tbactiverole? Is your SPID column defined as an int?
The output in the SPID column from sp_who2 is actually a char(5) and not an int - so I am guessing the error is likely in the "insert into StatsAdminTest.dbo.tbactiverole" line.
[snip from sp_who2]
--------Output the report.
EXEC(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
,REQUESTID = convert(char(5),request_id)
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
order by spid_sort
SET nocount on
'
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply