SQL Server CLR Int64 to SQLInt64 Specified cast is not valid

Question

I am trying to write a CLR that allows me to run a WMI Query on a SQL Server.

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Management;

public class WMIQuery
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod()
    {      
        ManagementScope scope = new ManagementScope();    
        scope = new ManagementScope(@"\\localhost\root\CIMV2");
        scope.Connect();    
        SelectQuery query = new SelectQuery("SELECT Name, Capacity, Freespace FROM Win32_Volume WHERE DriveType=3");
        ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

        ManagementObjectCollection retObjectCollection = searcher.Get ( );    
        return retObjectCollection;
    }

    public static void FillRow(Object obj, out SqlString Name, out SqlInt64 Capacity, out SqlInt64 Freespace)
    {
        ManagementObject m = (ManagementObject)obj;           

        Name = new SqlString((string)m["name"]);
        Capacity = new SqlInt64((Int64)m["Capacity"]);
        Freespace = new SqlInt64((Int64)m["Freespace"]);
    }  
 }

When running that table valued function i get the following error:

An error occurred while getting new row from user defined Table Valued Function : System.InvalidCastException: Specified cast is not valid. System.InvalidCastException: at WMIQuery.FillRow(Object obj, SqlString& Name, SqlInt64& Capacity, SqlInt64& Freespace) .

I already found out that the problem is the the conversion:

Capacity = new SqlInt64((Int64)m["Capacity"]);
Freespace = new SqlInt64((Int64)m["Freespace"]);

I hope that someone has an idea how to solve the above problem?

My code to test this CLR is:

CREATE FUNCTION [dbo].[WMIQuery]()
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Capacity] [bigint] NULL,
    [Freespace] [bigint] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyFirstAssembly].[WMIQuery].[InitMethod]
GO


select * from WMIQuery()

Answer 1

You should use and check whether that row and column has proper values which can be converted to the Int64 or not. Try how to check this Here.

Please do the following before casting

bool success = Int64.TryParse(Convert.ToString(m["Capacity"]), out long number);
if (success)
{
   Capacity = new SqlInt64((Int64)m["Capacity"]);
}
else
{
   Capacity = 0;
}

Answer 2

The type of Disk m["Capacity"] is UInt64. I've used this function to find out what data type was used.

m["Capacity"].GetType().ToString();

I've modifided the CLR to output just the datatype for that purpose.

After knowing the type i've done some research how to convert a UInt64 to Int64 and finally found the soultion:

Int64 int64cap;
Int64.TryParse(m["Capacity"].ToString(), out int64cap);

I don't know if this is the proper solution, but it works for me.

Here's the complete code

public class WMIQuery
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod()
    {


        ManagementScope scope = new ManagementScope();

        scope = new ManagementScope(@"\\localhost\root\CIMV2");
        scope.Connect();

        SelectQuery query = new SelectQuery("SELECT Name, Capacity, Freespace FROM Win32_Volume WHERE DriveType=3");
        ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);


        ManagementObjectCollection retObjectCollection = searcher.Get ( );

        return retObjectCollection;
    }

    public static void FillRow(Object obj, out SqlString Name, out SqlDecimal Capacity, out SqlDecimal Freespace)
    {
        ManagementObject m = (ManagementObject)obj;

        Name = new SqlString((string)m["name"]);


        Int64 int64cap;
        Int64.TryParse(m["Capacity"].ToString(), out int64cap);
        decimal decCap;
        decCap = int64cap / 1073741824; // to GB
        decCap = Math.Round(decCap, 2);
        Capacity = new SqlDecimal(decCap);

        Int64 int64Free;
        Int64.TryParse(m["Freespace"].ToString(), out int64Free);
        decimal decFree;
        decFree = int64Free / 1073741824; // to GB
        decFree = Math.Round(decFree, 2);
        Freespace = new SqlDecimal(decFree);




    }


}

The SQL to run this stuff:

CREATE ASSEMBLY [MyFirstAssembly]
FROM 'C:\MyFirstAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE FUNCTION [dbo].[WMIQuery]()
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Capacity] decimal(18,2) NULL,
    [Freespace] decimal(18,2) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyFirstAssembly].[WMIQuery].[InitMethod]
GO

select * from WMIQuery()

Answer 3

As you found out, the error is due to m["Capacity"] being an unsigned, not signed, Int64. To fix, just use the Convert class as follows:

        Capacity = new SqlInt64(Convert.ToInt64(m["Capacity"]));
        Freespace = new SqlInt64(Convert.ToInt64(m["Freespace"]));

I tested this with your code, got the same error before making any changes, and then made the change recommended above and I now get the correct output.

While not part of the problem here but just in general (initially there was a String Querytext input param): For input parameters / return types, please use Sql* types instead of native types for most datatypes (object for SQL_VARIANT and DateTime for DATETIME2 are notable exceptions). So, use SqlString instead of String for the Querytext parameter (or just remove the input parameter as it is not being used). You can get the native .NET string out of the parameter by using the Value property (e.g. Querytext.Value), which all Sql* types have (which returns the expected native type in each case).

For more info on using SQLCLR in general, please visit: SQLCLR Info

HOWEVER, and possibly more importantly: looking at exactly what you are querying via WMI, it looks like you are getting info that there is already a DMV for, sys.dm_os_volume_stats. You could get the exact same info, for all drives / volumes that you already have files on by using the following query:

SELECT DISTINCT vol.[volume_mount_point], vol.[volume_id], vol.[logical_volume_name],
                vol.[file_system_type], vol.[total_bytes], vol.[available_bytes],
                (vol.[total_bytes] - vol.[available_bytes]) AS [used_bytes]
FROM sys.master_files files
CROSS APPLY sys.dm_os_volume_stats(files.[database_id], files.[file_id]) vol