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,TOTALSIZE

    SQL 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 D

     

    DRIVE   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)

    • This reply was modified 2 weeks, 1 day ago by  homebrew01.
  • 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

    • This reply was modified 2 weeks ago by  homebrew01.
  • error_temp

     

    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

    https://santhoosql.blogspot.com/2016/12/script-to-display-drive-details-in-sql.html?showComment=1630675910524#c7538392216165528209

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply