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()

Is one of your row values NULL? You may want to switch to using Nullable<Int64> just in case. And if one of the values is NULL converting to SqlInt64 will fail as it expects a valid Int64. If you could provide some sample data from your database table it would be helpful. — Ryan Wilson
– Ryan Wilson, Commented Jan 17, 2019 at 13:10
Please post the schema of your database table and provide some sample data. — Ryan Wilson
– Ryan Wilson, Commented Jan 17, 2019 at 13:42
@PanagiotisKanavos i'm using the win32_volume class as an expample for future wmi queries that i have in mind. thanks for pointing me to the dmv anyways. — Niklas
– Niklas, Commented Jan 18, 2019 at 11:09

Suraj Kumar · Accepted Answer · 2019-01-17 13:17:40Z

2

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;
}

answered Jan 17, 2019 at 13:17

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Suraj Kumar Over a year ago

Because it is needed string as parameter. It need parameter in the format as - 'public static bool TryParse (string s, out long result);' Here s is string.

Ryan Wilson Over a year ago

Or you could use Nullable<Int64>

Suraj Kumar Over a year ago

That might be or conditional operator or something also like string.IsNullOrEmpty....

Ryan Wilson Over a year ago

If the column holds Int64 values it should either be a valid Int64 or DBNull.

Suraj Kumar Over a year ago

Yes, But we do not know datatype here. As the name here Capacity it might be other thing some text values or something else.

|

Niklas · Accepted Answer · 2019-01-18 15:01:57Z

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()

good job figuring out that the underlying type is UInt64 / ulong. I posted a simplified version in my answer :) (no need for the expensive convert to string only to do TryParse / convert again).

Solomon Rutzky · Accepted Answer · 2019-01-18 15:18:23Z

1

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

edited Jan 18, 2019 at 15:18

answered Jan 17, 2019 at 13:46

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

3 Comments

Niklas Over a year ago

I've tried your suggested solution, but still getting the error Msg 6260, Level 16, State 1, Line 36 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)

Niklas Over a year ago

I'm aware of sys.dm_os_volume_stats, but this is more an expample CLR to be a template for other wmi queries that i want to run.

Solomon Rutzky Over a year ago

@Niklas I just updated my answer. The solution is simpler (and more efficient) than the answer you just accepted. And it does work with your code.

Collectives™ on Stack Overflow

SQL Server CLR Int64 to SQLInt64 Specified cast is not valid

3 Answers 3

8 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related