Stored procedure Error converting data type varchar to bigint
-
I have been using below stored procedure which works very well for SQL Server 2012 but in SQL Server 2019 it throws an error. I tried to post the code here but it was showing an error while posting.
Msg 8114, Level 16, State 5, Line 65 Error converting data type varchar to bigint
Any workaround would be great.
If you have other solution or a T-SQL script with e-mail alert for disk monitoring and can share would be nice as well.
Thank you.
CREATE proc [dbo].[USP_Send_DiskSpace]
(
@To varchar(200) ,
@CRITICAL int = 10 -- if the freespace(%) is less than @alertvalue, it will send message
)
as
Begin
DECLARE @HOSTNAME VARCHAR(20),
@HEAD VARCHAR(100),
@BGCOLOR VARCHAR(50),
@REC VARCHAR(50),
@PRIORITY VARCHAR(10),
@FREE VARCHAR(20),
@TOTAL VARCHAR(20),
@FREE_PER VARCHAR(20),
@CHART VARCHAR(2000),
@HTML VARCHAR(MAX),
@HTMLTEMP VARCHAR(MAX),
@TITLE VARCHAR(100),
@DRIVE VARCHAR(100),
@SQL VARCHAR(MAX)
CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500))
INSERT INTO #MOUNTVOL
EXEC XP_CMDSHELL 'MOUNTVOL'
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%VOLUME%'
DELETE #MOUNTVOL WHERE COL1 IS NULL
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%MOUNTVOL%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%RECYCLE%'
SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
CREATE TABLE #DRIVES
(
DRIVE VARCHAR(500),
INFO VARCHAR(80)
)
DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
OPEN CUR
FETCH NEXT FROM CUR INTO @DRIVE
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @DRIVE +''''
INSERT #DRIVES
(
INFO
)
EXEC (@SQL)
UPDATE #DRIVES
SET DRIVE = @DRIVE
WHERE DRIVE IS NULL
FETCH NEXT FROM CUR INTO @DRIVE
END
CLOSE CUR
DEALLOCATE CUR
-- SHOW THE EXPECTED OUTPUT
SELECT DRIVE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE
INTO #DISKSPACE FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE 'TOTAL # OF %'
) AS D
GROUP BY DRIVE
ORDER BY DRIVE
SET @TITLE = 'DISK SPACE REPROT : '+ @@SERVERNAME
SET @HTML = '<HTML><TITLE>'+@TITLE+'</TITLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2>
<TR BGCOLOR=#0070C0 ALIGN=CENTER STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:WHITE''>
<TD WIDTH=40><B>DRIVE</B></TD>
<TD WIDTH=250><B>TOTAL</B></TD>
<TD WIDTH=150><B>FREE SPACE</B></TD>
<TD WIDTH=150><B>FREE PRECENTAGE</B></TD>
</TR>'
DECLARE RECORDS CURSOR
FOR SELECT CAST(DRIVE AS VARCHAR(100)) AS 'DRIVE', CAST(FREESPACE/1024/1024 AS VARCHAR(10)) AS 'FREE',CAST(TOTALSIZE/1024/1024 AS VARCHAR(10)) AS 'TOTAL',
CONVERT(VARCHAR(2000),'<TABLE BORDER=0 ><TR><TD BORDER=0 BGCOLOR='+ CASE WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 < @CRITICAL
THEN 'RED'
WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 > 70
THEN '66CC00'
ELSE
'0033FF'
END +'></TD>
<TD><FONT SIZE=1>'+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 AS INT) AS CHAR(10) )+'%</FONT></TD></TR></TABLE>') AS 'CHART'
FROM #DISKSPACE ORDER BY ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0
OPEN RECORDS
FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART
WHILE @@FETCH_STATUS = 0
BEGIN
SET @HTMLTEMP =
'<TR BORDER=0 BGCOLOR="#E8E8E8" STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:#0F243E''>
<TD ALIGN = CENTER>'+@DRIVE+'</TD>
<TD ALIGN=CENTER>'+@TOTAL+'</TD>
<TD ALIGN=CENTER>'+@FREE+'</TD>
<TD VALIGN=MIDDLE>'+@CHART+'</TD>
</TR>'
SET @HTML = @HTML + @HTMLTEMP
FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART
END
CLOSE RECORDS
DEALLOCATE RECORDS
SET @HTML = @HTML + '</TABLE><BR><B>THANKS,</B>
<B>DBA TEAM</B>
</HTML>'
--PRINT
PRINT @HTML
--save data
if(object_id('DBA.dbo.diskdrive_stats') is null)
Begin
create table DBA.dbo.diskdrive_stats (
Drive varchar(100) ,
FreeSpace float null,
TotalSize float null,
Free_per float,
date_time datetime)
insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
select Drive,convert(float,freespace),convert(float,totalsize),
convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE
--insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
--select *,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0,getdate() from #DISKSPACE
End
Else
Begin
insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
select Drive,convert(float,freespace),convert(float,totalsize),
convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE
End
--############################Send Mail#############################
set @head = '<RED> Disk Space report from SQL Server : '+@@servername
--SELECT * FROM #DISKSPACE
IF EXISTS(SELECT * FROM #DISKSPACE WHERE CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT) <= @CRITICAL)
BEGIN
SET @PRIORITY = 'HIGH'
print @head
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = @To,
@subject = @head,
@importance = @Priority,
@body = @HTML,
@body_format = 'HTML'
END
ELSE
BEGIN
print''
END
DROP TABLE #MOUNTVOL
DROP TABLE #DRIVES
DROP TABLE #DISKSPACE
END -
that likely has different output on the server with sql 2019 - you should be able to figure out yourself what the issue is as the convert is happening on a string manipulation - try the code manually up to the point where you do the conversion, then look at the contents of #drives to see why the substring/convert is being messed up.
even a locale change can affect that output and cause it to fail.
-
Thank you for your time to look into this question. I believe this part of the code is throwing an error.
SELECT DRIVE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE INTO #DISKSPACE FROM
Have any thoughts?
-
So what does the following (w/o the casts) return?
It's probable that one or more of those INFO values still contains non-numeric characters, is decimal, or is even bigger than a bigint (not likely), even after the replace.
SELECT DRIVE,
CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') ELSE 0 END AS TOTALSIZE,
CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') ELSE 0 END AS FREESPACE
FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE 'TOTAL # OF %'
) AS D
ORDER BY DRIVE,TOTALSIZESQL Server 2012 and SQL Server 2019 instances are not on the same server, correct?
What version of Windows server are the instances on?
Are the # and specs of the drives identical?
-
did you look at the contents of the string as I stated?
did you count the characters and see if your substring matches that output?
are the separaters a "," or a "." on that server?
only you can see that - and unless you give us the output/contents of #DRIVES we can't help you but only do as I did (twice) and point you to where you have to look at.
- SQlLogic wrote:
Thank you for your time to look into this question. I believe this part of the code is throwing an error.
SELECT DRIVE, SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE, SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE INTO #DISKSPACE FROM
Have any thoughts?
you might be right. It's likely the error is there.
First thing is you're replacing CHAR(13), but ignore CHAR(10),
And second, run the query without aggregations and conversions:
SELECT DRIVE, REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '')
and see if you can find any non-numeric entries.
_____________
Code for TallyGenerator -
Thank you Ratbak,
SQL 2012 and 2019 is totally different. I am testing this solution on SQL 2019 LAb/Test Machine.
I ran the query I received below error after words. Looks like it fixed the previous error but when it went to 'DECLARE RECORDS CURSOR ' it error out.
Msg 8134, Level 16, State 1, Line 99
Divide by zero error encountered.
Msg 16917, Level 16, State 2, Line 101
Cursor is not open.
Msg 16917, Level 16, State 1, Line 120
Cursor is not open.
-
Why is your substring length 48 ?
select *,REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') as 'Cast as BIGINT ?'
FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE 'TOTAL # OF %'
) AS DDRIVE INFO CAST as BIGINT ?
----- -------------------------------------------- ----------------------
D:\ Total # of free bytes : 154540326912 (143.93GB) 154540326912 (143.93GB)
D:\ Total # of bytes : 429493579776 (400.00GB) 429493579776 (400.00GB)
D:\ Total # of avail free bytes : 154540326912 (143.93GB) 154540326912 (143.93GB)
E:\ Total # of free bytes : 177932451840 (165.71GB) 177932451840 (165.71GB)
E:\ Total # of bytes : 214745214976 (200.00GB) 214745214976 (200.00GB)
E:\ Total # of avail free bytes : 177932451840 (165.71GB) 177932451840 (165.71GB)
C:\ Total # of free bytes : 150011650048 (139.71GB) 150011650048 (139.71GB)
C:\ Total # of bytes : 214170595328 (199.46GB) 214170595328 (199.46GB)
C:\ Total # of avail free bytes : 150011650048 (139.71GB) 150011650048 (139.71GB) -
Thank you for all suggestions. The solution got fixed by this change.
SELECT DRIVE, SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(INFO, 32, 13), CHAR(13), ''),'(',''))) AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE, SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(LTRI(RTRIM(REPLACE(REPLACE(SUBSTRING(INFO, 32, 13), CHAR(13), ''),'(',''))) AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE -
If you run the code in a SQL Window, not as a stored procedure, then when you get an error message, double-click the error and it jumps to the area of code that generated it.
That isolated to this area.
SELECT DRIVE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE
INTO #DISKSPACE FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE 'TOTAL # OF %'
) AS D
GROUP BY DRIVE
ORDER BY DRIVE -
I apologize for the late reply. But This is what I get from the code I ran as per @homebrew01.
So the solution with new changes working for some 2016/ 2019 servers and some servers throwing same error.
-
you need to parse the output - and ensure that you deal with the quirks of different versions of servers/locales as you are now finding out
also fsutil needs administrator privs which is not advisable that the SQL Server execution account or sqlcmdProxy have
It may be an option that you use wmic instead which will give you the output for all drives in one go.
WMIC LOGICALDISK GET Name,Size,FreeSpace
gives
FreeSpace Name Size
85407141888 C: 483927257088
25197436928 D: 26843541504
- SQlLogic wrote:
So the solution with new changes working for some 2016/ 2019 servers and some servers throwing same error.
I did not get an error on a 2008 SQL Server, but looks like there were no records selected on 2008, so no reason to get an error.
Those XP_CMDSHELL commands may give different results in different versions.
-
Different OS locales might cause different formatting of the output of "FSUTIL VILUME".
you need to "teach" your script to identify the anchor points in the returned sets of string and parse them accordingly.
_____________
Code for TallyGenerator -
Thank you everyone for valuable input.
way back I found the script TechNet Gallery but not seems it's got moved or removed. I will try to make it work and post it here.
-- Available here
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply