Hello Krystian,
With the exception of changing @recipients and @profile_name, I ran your script just as you posted it. I ran the first part creating sp_DetectLongRunningTransactions. I didn't run the part creating the job I just tried running the script as such EXEC sp_DetectLongRunningTransactions @OpenTransactionTreshold = 30
I'm using a test server so there is no activity on it at all. It is 2008 R2 Datacenter OS and SQL 2012 SP1 Enterprise. When I run the script with nothing running in the instance, it comes back with successful completion. I then created a test table and kicked off a query against it that will run awhile, that way I could test different thresholds etc. Anytime I executed the SP, whether before the threshold or after, it didn't matter, I would get that same conversion error. I don't have a lot of experience with coding at this point but this link seems to be related to what I'm seeing here.
That's it. Here is the code I used to create a test table and query:
--create table
/*CREATE TABLE dbo.TestTableSize
(
MyKeyField VARCHAR(10) NOT NULL,
MyDate1 DATETIME NOT NULL,
MyDate2 DATETIME NOT NULL,
MyDate3 DATETIME NOT NULL,
MyDate4 DATETIME NOT NULL,
MyDate5 DATETIME NOT NULL
)*/
--execute long running query
DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME
SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0
WHILE @RowCount < 3000000
BEGIN
SET @RowString = CAST(@RowCount AS VARCHAR(10))
SELECT @random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SET @InsertDate = DATEADD(dd, @random, GETDATE())
INSERT INTO TestTableSize
(MyKeyField
,MyDate1
,MyDate2
,MyDate3
,MyDate4
,MyDate5)
VALUES
(REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
, @InsertDate
,DATEADD(dd, 1, @InsertDate)
,DATEADD(dd, 2, @InsertDate)
,DATEADD(dd, 3, @InsertDate)
,DATEADD(dd, 4, @InsertDate))
SET @RowCount = @RowCount + 1
END