none
Exception thrown when opening Storage Utilization for a Managed Instance in Utility Explorer

    Question

  • I get an exception when opening the storage utilization tab as part of the utility explorer in multi-server management. I have a single managed instance and when I open the storage utilization tab I get the exception:

    ************** Exception Text **************
    System.InvalidCastException: Object cannot be cast from DBNull to other types.
       at System.DBNull.System.IConvertible.ToDouble(IFormatProvider provider)
       at System.Convert.ToDouble(Object value, IFormatProvider provider)
       at Microsoft.SqlServer.Management.Utility.FileGroupDatabaseUtilizationHierarchy..ctor(ManagedInstance managedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.TreeGridViewControl.LoadDataGroupedByDatabase(ManagedInstance selectedManagedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.TreeGridViewControl.LoadData(ManagedInstance selectedManagedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.UtilizationAndHistoryControl.LoadData(ManagedInstance selectedManagedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.StorageUtilizationTab.LoadData(ManagedInstance selectedManagedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.ManagedInstanceStorageUtilizationDetailsView.OnObjectChanged(ManagedInstance managedInstance)
       at Microsoft.SqlServer.Management.MultiServerMgmtUI.ObjectDetailsView`1.SetFocusedItem(IExplorerViewItem item)
       at Microsoft.SqlServer.Management.SqlStudio.Explorer.DetailsViewHost.ActivateDetailsView(IExplorerDetailsView detailsView)
       at Microsoft.SqlServer.Management.SqlStudio.Explorer.DetailsViewHost.set_CurrentDetailsView(IExplorerDetailsView value)
       at Microsoft.SqlServer.Management.SqlStudio.Explorer.DetailsViewHost.OnTabPageSelected(Object sender, TabControlEventArgs e)
       at System.Windows.Forms.TabControl.OnSelected(TabControlEventArgs e)
       at System.Windows.Forms.TabControl.WmSelChange()
       at System.Windows.Forms.TabControl.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    The other tabs all work as expected.

    The exact same exception is also thrown when accessing the Storage Utilization tab under 'deployed data-tier applications'.

    The instance name is SQL2008R2, and the utility is configured to send data to the management data warehouse on that same instance.

    This is on a Windows 2008 Enterprise x64 VM that also has a SQL server 2005 and 2008 (non-R2) instance installed. SQL2005 is the default instance.

    Not sure if this is a bug or just some bad configuration on my part.

    Thanks

    Wednesday, August 19, 2009 8:06 AM

Answers

  • Problem

    If a user clicks on the Storage Utilization tab in Utility Explorer Content, then the following exception will be generated:

     

    Unhandled exception has occurred in a component in your application.  If you click Continue, the application will ignore this error and atttempt to continue.

     

    Object cannot be cast from DBNull to other types.

     

    The exception occurs because a SQL Server DLL was not signed as part of the build process. 

    Workaround

    To workaround the exception, perform the following steps:   

     

    1.    From a command line run the following command on all the machines having SQL Server instances managed by the Utility.

    C:\Program Files\Microsoft SDKs\Windows\v7.0\bin\Sn.exe -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91
    or
    C:\Program Files\Microsoft SDKs\Windows\v7.0\bin\x64\Sn.exe -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91

    Make sure to use the right version of sn.exe depending upon the OS of the machines on which you are running SQL Server instances.

    Running this command disables strong name verification for the assembly which is causing the exception.

    Note that the exact file path in this step may be different depending on the version of Windows and the .NET SDK installed on your machine.

    2.    Restart the SQL Server service for each one of instances for the above step to take effect.


    3.   
    Wait for the next data collection job to run on the instance which may take upto 45 mins to upload the results to the Utility.


    4. Disconnect the Utility server connection from Utiltiy Explorer and connect it again.



     

     

     

    Strong Name Tool (Sn.exe)

     


    Sn.exe is a Strong Name Tool used in Step 1 of the workaround.  This tool is included with the .NET Framework SDK.

    If it's not already installed, it can be downloaded from: 

    http://www.microsoft.com/downloads/details.aspx?FamilyID=c17ba869-9671-4330-a63e-1fd44e0e2505&displaylang=en

    The installation option in Setup that contains Sn.exe is .NET Development Tools.

    Regards,
    Rajesh
    ----------------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by rwatkins Thursday, August 27, 2009 9:45 AM
    • Edited by Rajesh Nagpal - MSFT Friday, August 28, 2009 10:21 PM providing the download location of sn.exe
    Saturday, August 22, 2009 1:53 AM

All replies

  • Running profiler on this it seems to run the following query just before the exception is thrown:
    SELECT util.database_name, util.filegroup_name, util.databasefile_name, 
            util.under_utilization, util.current_utilization, util.over_utilization, util.used_space, util.available_space, 
            util.file_type, util.growth_type, dbhs.health_state as db_health_state
            FROM msdb.dbo.sysutility_mi_data_and_log_file_utilization_view as util, 
            msdb.dbo.sysutility_mi_database_health_states_view as dbhs 
            WHERE util.server_instance_name = N'SERVERNAME\SQL2008R2'
            and util.server_instance_name = dbhs.server_instance_name and util.database_name = dbhs.database_name
            ORDER BY database_name, file_type, filegroup_name, databasefile_name
    An subset of the rows/cols returned by this query for AdventureWorks is:

    database_name current_utilization
    AdventureWorks        0
    AdventureWorks        NULL
    AdventureWorks        NULL

    Guess that might be why the exception is thrown?
    • Edited by rwatkins Wednesday, August 19, 2009 8:31 AM mistake
    Wednesday, August 19, 2009 8:31 AM
  • Thanks for reporting this issue.  The product team is currently investigating this.
    Thursday, August 20, 2009 12:13 AM
  • Update: The root cause problem has been identified.  The workaround will be posted as soon as possible.

    Thursday, August 20, 2009 9:19 PM
  • Problem

    If a user clicks on the Storage Utilization tab in Utility Explorer Content, then the following exception will be generated:

     

    Unhandled exception has occurred in a component in your application.  If you click Continue, the application will ignore this error and atttempt to continue.

     

    Object cannot be cast from DBNull to other types.

     

    The exception occurs because a SQL Server DLL was not signed as part of the build process. 

    Workaround

    To workaround the exception, perform the following steps:   

     

    1.    From a command line run the following command on all the machines having SQL Server instances managed by the Utility.

    C:\Program Files\Microsoft SDKs\Windows\v7.0\bin\Sn.exe -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91
    or
    C:\Program Files\Microsoft SDKs\Windows\v7.0\bin\x64\Sn.exe -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91

    Make sure to use the right version of sn.exe depending upon the OS of the machines on which you are running SQL Server instances.

    Running this command disables strong name verification for the assembly which is causing the exception.

    Note that the exact file path in this step may be different depending on the version of Windows and the .NET SDK installed on your machine.

    2.    Restart the SQL Server service for each one of instances for the above step to take effect.


    3.   
    Wait for the next data collection job to run on the instance which may take upto 45 mins to upload the results to the Utility.


    4. Disconnect the Utility server connection from Utiltiy Explorer and connect it again.



     

     

     

    Strong Name Tool (Sn.exe)

     


    Sn.exe is a Strong Name Tool used in Step 1 of the workaround.  This tool is included with the .NET Framework SDK.

    If it's not already installed, it can be downloaded from: 

    http://www.microsoft.com/downloads/details.aspx?FamilyID=c17ba869-9671-4330-a63e-1fd44e0e2505&displaylang=en

    The installation option in Setup that contains Sn.exe is .NET Development Tools.

    Regards,
    Rajesh
    ----------------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by rwatkins Thursday, August 27, 2009 9:45 AM
    • Edited by Rajesh Nagpal - MSFT Friday, August 28, 2009 10:21 PM providing the download location of sn.exe
    Saturday, August 22, 2009 1:53 AM
  • Thanks for the replies. I don't have sn.exe on my server so I guess it's not part of the base .net 3.5sp1 framework. I installed the .NET framework SDK from the windows SDKs for 2008 (http://www.microsoft.com/downloads/details.aspx?FamilyId=F26B1AA4-741A-433A-9BE5-FA919850BDBF&displaylang=en) and registered the assembly.

    C:\Program Files\Microsoft SDKs\Windows\v6.1\bin\Sn.exe" -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91

    Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8
    Copyright (c) Microsoft Corporation.  All rights reserved.

    Verification entry added for assembly 'Microsoft.SqlServer.MpuSqlClrWrapper,8984
    5dcd8080cc91'

    Then I restarted the SQL Server service then left it for an hour or so to ensure that the new stats were collected.

    However this doesn't seem to have fixed the problem. I still get the issue when I click the 'Storage utilisation' tab inside SSMS and the exception is thrown again.

    Thanks

    Monday, August 24, 2009 11:21 AM
  • Can you disconnect the Utility server connection from Utility Explorer and connect it again to see if it resolves the issue?

    Regards,
    Rajesh
    Monday, August 24, 2009 11:36 PM
  • Hi Rajesh,

    I closed SSMS and reopened it but still get the exception thrown.

    Is it because there are still bad stats with nulls inside the MDW db?

    Thanks
    Tuesday, August 25, 2009 8:56 AM
  • Couple of other things to take care.

    Make sure you run the sn -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91 on all the machines which have SQL Server instances managed by the Utility and restart the SQL Server service for them.

    Also if your OS is 64 bit, make sure you run the 64 bit version of sn.exe

    Let me know if that resolves the issue.

    I'll update the workaround given above to include these cases.

    Regards,
    Rajesh
    ----------------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, August 27, 2009 7:10 AM
  • Hi Rajesh,

    It works!

    I wasn't running the 64bit sn.exe but the 32bit one. It seems that the version of the SDK I installed puts 32bit binaries into c:\program files\. Bit annoying.

    Instead I ran:

    "C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64\Sn.exe" -Vr Microsoft.SqlServer.MpuSqlClrWrapper,89845dcd8080cc91

    Had a cup of tea and waited for half an hour and I can now see storage stats.

    Thanks for your help
    Thursday, August 27, 2009 9:45 AM