Error converting data type varchar to bigint.

Question

Hello. I am creating a SP and receiving the error message while executing. This SP works fine on different servers but it cause issue on this server only.

DECLARE @HOSTNAME VARCHAR(20),
@iatebreakfast VARCHAR(100),
@BGCOLOR VARCHAR(50),
@REC VARCHAR(50),
@Priority Networks VARCHAR(10),
@free VARCHAR(20),
@total VARCHAR(20),
@free _PER VARCHAR(20),
@Chart VARCHAR(2000),
@azzedinehtmlsql VARCHAR(MAX),
@HTMLTEMP VARCHAR(MAX),
@Title VARCHAR(100),
@Lenovo VARCHAR(100),
@alenzi 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 @Lenovo
WHILE @@Fetch _STATUS=0
BEGIN
SET @alenzi = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @Lenovo +''''

    INSERT    #DRIVES   
        (   
            INFO   
        )   
    EXEC    (@SQL)   
 
    UPDATE    #DRIVES   
    SET    DRIVE = @DRIVE   
    WHERE    DRIVE IS NULL   
        

FETCH NEXT FROM CUR INTO @Lenovo
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

Msg 8114, Level 16, State 5, Line 95
Error converting data type varchar to bigint.

Answer 1

First attempt is to replace CAST in the final SELECT with TRY_CAST. If you don't get back any NULL values, you are fine. If you get back NULL values, you need to debug the output you get from the previous queries.

Answer 2

Hi @HP1979 ,

Welcome to Microsoft Q&A!

It could be recommended for you to post the result of #DRIVES table.

According to limited information you provided, you could refer below suggestions:

• Confirm that there are only numbers in the part of SUBSTRING(INFO, 32, 48).
• Change the 'CHAR(13)' to blank(' ') or another symbol based on your requirement.
• Change the CAST to TRY_CAST.

For example, you will face 'Error converting data type varchar to bigint' errors after executing below queries:

select CAST(REPLACE(SUBSTRING('abcdefghijk12 345', 12, 8), CHAR(13), '') AS BIGINT)  
  
select CAST(REPLACE(SUBSTRING('abcdefghijk12 345', 10, 8), ' ', '') AS BIGINT)  

Then we will not face any error if we update them as below:

select CAST(REPLACE(SUBSTRING('abcdefghijk12 345', 12, 8), ' ', '') AS BIGINT)  
--12345  

select TRY_CAST(REPLACE(SUBSTRING('abcdefghijk12 345', 10, 8), ' ', '') AS BIGINT)  
--null  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.