question

HP1979 avatar image
0 Votes"
HP1979 asked MelissaMa-msft commented

Error converting data type varchar to bigint.

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),
@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


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



sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Probably SUBSTRING cannot extract the numeric parts. Show some details about the INFO values from #DRIVES and make sure that SUBSTRING has correct arguments.


0 Votes 0 ·

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.