Stored procedure Error converting data type varchar to bigint

Question

Stored procedure Error converting data type varchar to bigint

Viewing 4 posts - 16 through 18 (of 18 total)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

The thing is:

The command EXEC XP_CMDSHELL 'FSUTIL VOLUME DISKFREE E:\' returns the following recordset on my laptop:

Total free bytes : 1,716,875,264 ( 1.6 GB)
Total bytes : 16,503,533,568 (15.4 GB)
Total quota free bytes : 1,716,875,264 ( 1.6 GB)
NULL

There is no " # of " in it at all, so the script you're trying to use will return zeros for all my drives.

And when you extract REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') from the string, you're getting "10,968,320 (118.0 GB)", which is not right, and cannot be converted to bigint.

The script must be way smarter than this.

_____________
Code for TallyGenerator

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 2

@homrebrew01

you are right. Even same 2019 versions giving me an error on some servers and some servers it's working fine and sending an alert. something fishy going with the script.

Let me know if you have any thoughts or idea to change the script.

anyway thank you and best ahead.

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Once again, it's not about SQL Server versions, it's about the formatting returned by OS commands.

Which depends on OS versions and locales.

_____________
Code for TallyGenerator

frederico_fonseca SSCoach Points: 16034 More actions · Answer 4

SQlLogic wrote:

@homrebrew01
you are right. Even same 2019 versions giving me an error on some servers and some servers it's working fine and sending an alert. something fishy going with the script.
Let me know if you have any thoughts or idea to change the script.
anyway thank you and best ahead.

I gave you an alternative command - did you tried it? and did you try parsing its output?

I'm assuming it works regardless of drive type - but needs to be tested by yourself on your servers.

one thing you need to do regardless - stop using hardcoded substrings and search for the position of the delimiters - : and ( and parse the data inbetween.

and the strings to search for also vary with FSUTIL - contrary to the alternative I gave