none
Error converting data type nvarchar to numeric.

    Question

  • Hello Everyone..

    I run the following script on Central Management Studio to identify the disk space. It returns accurate results for few servers but for the other few it is throwing error message "Error converting data type nvarchar to numeric." and does not show any results.

    Any help is appreciated.

    DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;
    INSERT INTO @psinfo
    EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;
    DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';
    update @psinfo set data = REPLACE(data,' ',',');


    ;With DriveSpace as (


    select SUBSTRING(data,1,2)  as [Drive],
    replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
    (substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
    as [FreeSpace]
    ,
    replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%',
    (substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
    as [Size]
    from @psinfo
    )
    SELECT
     Drive,
     convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as TotalSize_GB,
     convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) as FreeSpace_GB,
     (convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024))/convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)))*100  as Percentage_Free
    FROM DriveSpace;
    GO


    Monday, June 11, 2012 4:15 PM

All replies

  • Instead of your last query just do a  Select * from Drivespace

    That will show you the data that is causing your issues

    DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;
     INSERT INTO @psinfo
     EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;
     DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';
     update @psinfo set data = REPLACE(data,' ',',');
     
    
    ;With DriveSpace as (
     
    
    select SUBSTRING(data,1,2)  as [Drive], 
    replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
     (substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
     as [FreeSpace]
     , 
    replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%',
     (substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
     as [Size]
     from @psinfo
     ) 
    
    SELECT * FROM DRIVESPACE


    Chuck Pedretti | Magenic – North Region | magenic.com


    Monday, June 11, 2012 4:18 PM
  • I imagine the decimal value you are specifing is not large enough. Try using something larger like DECIMAL(20,2).  If this is not the issue, you may have a character or something in your text.  You have to select the data to see.

    http://jahaines.blogspot.com/


    Monday, June 11, 2012 4:36 PM
  • My guess is that the powershell is returning an error message for one of the drives in the data field.  Probabally lack of permission

    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, June 11, 2012 4:39 PM
  • Actually it may be better looking at the raw data captured in the first part:

    select * from @psinfo

    after the update as you may not be getting a numeric value back from one the drives.

    Monday, June 11, 2012 4:41 PM
  • Thanks Chuck, Chris.

    Powershell was not installed in those few servers that throws error message.

    Monday, June 11, 2012 8:01 PM