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.