Failed to convert varchar to int using substring function

Question

Post reply

Failed to convert varchar to int using substring function

mssqlsrv

SSCrazy

Points: 2375
More actions
February 7, 2013 at 4:43 am

#288722

Hi I am using following query but unable to get the result at the end, it gives me error " The conversion of the varchar value '83886075904' overflowed an int column"
I used cast function to convert it but it failes by giving an error that conversion failed
Please help me to modify the query so that I can get result value (i.e. drive size value)
Below is my original query:
declare @sz varchar (60)
DECLARE @drive TINYINT,
@sql VARCHAR(100)
SET @drive = 97
-- Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @drive <= 122
BEGIN
SET @sql = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC(@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @drive = @drive + 1
END
delete from @drives where info not like '%Total # of%'
delete from @drives where info = 'Total # of avail free bytes :'
select Drive,SUBSTRING(info,32,30)/1024/1024 from @Drives where INFO like 'Total # of bytes%' order by drive

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

The simple answer is this: -

SELECT Drive, CAST(SUBSTRING(info, 32, 30) AS BIGINT) / 1024 / 1024

FROM @Drives

WHERE INFO LIKE 'Total # of bytes%'

ORDER BY drive;

Be aware that you are losing precision in your sum. If you change 1024 to 1024.0 then you'll get the fraction as well as the whole number.

--EDIT--

The less simple answer would be this: -

DECLARE @DriveSpace AS TABLE(DriveLetter CHAR(1), FreeSpace VARCHAR(10));

DECLARE @DriveInformation AS TABLE(DriveLetter CHAR(1), TotalSpace BIGINT, FreeSpace BIGINT);

DECLARE @DriveTemp AS TABLE(OutputInfo VARCHAR(MAX));

INSERT INTO @DriveSpace

EXEC master.dbo.xp_fixeddrives;

DECLARE curDriveLetters CURSOR FOR SELECT driveletter FROM @DriveSpace;

DECLARE @DriveLetter CHAR(1), @sql NVARCHAR(MAX);

OPEN curDriveLetters;

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @sql = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + @DriveLetter + ':'''

INSERT INTO @DriveTemp

EXECUTE sp_executesql @sql;

INSERT INTO @DriveInformation

SELECT @DriveLetter,

MAX(CASE WHEN OutputInfo LIKE 'Total # of bytes%' THEN CAST(SUBSTRING(OutputInfo, 32, 30) AS BIGINT) / 1024 / 1024 END),

MAX(CASE WHEN OutputInfo LIKE 'Total # of free bytes%' THEN CAST(SUBSTRING(OutputInfo, 32, 30) AS BIGINT) / 1024 / 1024 END)

FROM @DriveTemp;

DELETE FROM @DriveTemp;

FETCH NEXT FROM curDriveLetters INTO @DriveLetter;

END

CLOSE curDriveLetters;

DEALLOCATE curDriveLetters;

SELECT *

FROM @DriveInformation;

I'm losing precision in the same way that you are, but there is less work done than in your version due to less looping around non-existent drives.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mssqlsrv SSCrazy Points: 2375 More actions · Answer 2

Hey, I have tried this already hence i told in my post that error is in conversion.

I have tried your query but gives me same error "Error converting data type varchar to bigint."

Please provide me another solution.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

It works here. . .

What is the result of this: -

DECLARE @DriveSpace AS TABLE(DriveLetter CHAR(1), FreeSpace VARCHAR(10));

DECLARE @DriveTemp AS TABLE(OutputInfo VARCHAR(MAX));

INSERT INTO @DriveSpace

EXEC master.dbo.xp_fixeddrives;

DECLARE curDriveLetters CURSOR FOR SELECT driveletter FROM @DriveSpace;

DECLARE @DriveLetter CHAR(1), @sql NVARCHAR(MAX);

OPEN curDriveLetters;

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @sql = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + @DriveLetter + ':'''

INSERT INTO @DriveTemp

EXECUTE sp_executesql @sql;

SELECT @DriveLetter,

MAX(CASE WHEN OutputInfo LIKE 'Total # of bytes%' THEN SUBSTRING(OutputInfo, 32, 30) END),

MAX(CASE WHEN OutputInfo LIKE 'Total # of free bytes%' THEN SUBSTRING(OutputInfo, 32, 30) END)

FROM @DriveTemp;

DELETE FROM @DriveTemp;

FETCH NEXT FROM curDriveLetters INTO @DriveLetter;

END

CLOSE curDriveLetters;

DEALLOCATE curDriveLetters;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mssqlsrv SSCrazy Points: 2375 More actions · Answer 4

i need drive name & disk size in MB. & for that i need to convert it from varchar to int.

your query is working but i need values in MB

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

mssqlsrv (2/7/2013)
i need drive name & disk size in MB. & for that i need to convert it from varchar to int.
your query is working but i need values in MB

I know that, I want to see the result of the above query so that I explain why it won't convert.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mssqlsrv SSCrazy Points: 2375 More actions · Answer 6

Hi please find attachement.

In picture Part.1 shows result of your query. & Part.2 shows result of my query

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

mssqlsrv (2/7/2013)
Hi please find attachement.
In picture Part.1 shows result of your query. & Part.2 shows result of my query

That looks suspiciously like white spaces. . . could you try this -

SELECT Drive,

CAST((REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(Info, 32, 30),

CHAR(0), '') COLLATE Latin1_General_BIN,

CHAR(1), '') COLLATE Latin1_General_BIN,

CHAR(2), '') COLLATE Latin1_General_BIN,

CHAR(3), '') COLLATE Latin1_General_BIN,

CHAR(4), '') COLLATE Latin1_General_BIN,

CHAR(5), '') COLLATE Latin1_General_BIN,

CHAR(6), '') COLLATE Latin1_General_BIN,

CHAR(7), '') COLLATE Latin1_General_BIN,

CHAR(8), '') COLLATE Latin1_General_BIN,

CHAR(9), '') COLLATE Latin1_General_BIN,

CHAR(10), '') COLLATE Latin1_General_BIN,

CHAR(11), '') COLLATE Latin1_General_BIN,

CHAR(12), '') COLLATE Latin1_General_BIN,

CHAR(13), '') COLLATE Latin1_General_BIN,

CHAR(14), '') COLLATE Latin1_General_BIN,

CHAR(15), '') COLLATE Latin1_General_BIN,

CHAR(16), '') COLLATE Latin1_General_BIN,

CHAR(17), '') COLLATE Latin1_General_BIN,

CHAR(18), '') COLLATE Latin1_General_BIN,

CHAR(19), '') COLLATE Latin1_General_BIN,

CHAR(20), '') COLLATE Latin1_General_BIN,

CHAR(21), '') COLLATE Latin1_General_BIN,

CHAR(22), '') COLLATE Latin1_General_BIN,

CHAR(23), '') COLLATE Latin1_General_BIN,

CHAR(24), '') COLLATE Latin1_General_BIN,

CHAR(25), '') COLLATE Latin1_General_BIN,

CHAR(26), '') COLLATE Latin1_General_BIN,

CHAR(27), '') COLLATE Latin1_General_BIN,

CHAR(28), '') COLLATE Latin1_General_BIN,

CHAR(29), '') COLLATE Latin1_General_BIN,

CHAR(30), '') COLLATE Latin1_General_BIN,

CHAR(31), '') COLLATE Latin1_General_BIN,

CHAR(127), '') COLLATE Latin1_General_BIN

) AS BIGINT) / 1024 / 1024

FROM @Drives

WHERE Info LIKE 'Total # of bytes%'

ORDER BY Drive;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/