Error converting data type varchar to bigint.

Question

Error converting data type varchar to bigint.

HP1979 26

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.

Viorel 126.9K Reputation points

2021-06-14T16:18:58.327+00:00

Probably SUBSTRING cannot extract the numeric parts. Show some details about the INFO values from #DRIVES and make sure that SUBSTRING has correct arguments.
MelissaMa-msft 24,246 Reputation points Moderator

2021-06-16T01:18:23.587+00:00

Hi @HP1979 ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

2 answers

Your answer

Viorel 126.9K Reputation points

2021-06-14T16:18:58.327+00:00

Probably SUBSTRING cannot extract the numeric parts. Show some details about the INFO values from #DRIVES and make sure that SUBSTRING has correct arguments.
MelissaMa-msft 24,246 Reputation points Moderator

2021-06-16T01:18:23.587+00:00

Hi @HP1979 ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Answer 1

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.

Dandge, Ashwajeet 0 Reputation points

2023-06-16T04:03:44.1433333+00:00

Total # of free bytes : 1029300682752 (958.61GB)

Answer 2

Erland Sommarskog 133.9K MVP Volunteer Moderator

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.

0 comments

Share via

Error converting data type varchar to bigint.

2 answers

Your answer