Mar 032014
 

It turns out that in System Center Operations Manager 2012 R2 (SCOM 2012 R2), there is a pretty nasty, but well documented bug that causes corruption and the following error:

SCOM Specified cast is not valid

After clicking OK a few times, you can attempt to view the Distribution tab, but will find it is now empty:

SCOM Empty Distribution Tab

The root cause of this is likely due to the uninstall of a previously managed agent. If you are in a large shop with multiple admins, which agent was it that is currently causing the problem? Since I’m a SQL guy, I took to Transact-SQL to find the answer. Log into your OperationsManager database instance, and run the following SQL to find the bad agent:

SELECT BME.DisplayName,
       BME.IsDeleted,
       CHS.SecureStorageElementId,
       CHS.HealthServiceId,
       CHS.LastModified
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
  JOIN [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    ON CMSS.SecureStorageElementId = CHS.SecureStorageElementId
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service

Objects with the IsDeleted column set to 1 are likely your problem agents.

So, how to clean this up? Well, there are a few options. One, if you are lucky you can re-approve the agent and after which you should be back to normal. Two, you can delete the offending row from the dbo.CredentialHealthService table. Lastly, and the option I’ll use is to call the same procedure that the SCOM Console uses in the GUI. To do that, run the following SQL:

DECLARE @SecureStorageElementId NVARCHAR(36);
DECLARE @HealthServiceIdList NVARCHAR(MAX);

SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
   /* or use CMSS.DisplayName, which is what the SCOM console shows */;

SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
  FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
   AND BME.IsDeleted = 0
 WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution 
            @SecureStorageElementId=''' + @SecureStorageElementId + N''', 
            @HealthServiceIdList=N''' + @HealthServiceIdList + N''', 
            @ApprovedHealthServicesResult=2;';

The output of which, you are free to inspect – no changes are made – until you are comfortable with running it. When you are, simply copy and paste the output and execute against your OperationsManager database. The output is exactly what the SCOM console would do if you were making changes to the distribution list. Running the output of the above script is done at your own risk. Changing data in the SCOM OperationsManager database is not officially supported. Contact Microsoft support if you are in an emergency. Feel free to mention the above solution to your support engineer, however.

  8 Responses to “SCOM 2012 R2 – Specified Cast Is Not Valid Error – Run As Accounts”

  1. Thanks for this post, saved me a lot time troubleshooting. Greatly appreciated.

    Dan

  2. Just for anybody else, this is not an R2 specific bug… I have had this many times in SP1 also :)

  3. The quieries run and I see the “is deleted” is set to “1” on the 2 I just screwed up, but the query that runs the stored procedures does not do anything to get rid of them. I even changed {AND BME.IsDeleted = 1}. Am I missing something…is the waiting game now for grooming to happen? I mean I can just delete the rows, but I am not getting what this console method does to remove the issue?

  4. nevermind! I get it now! You gotta take the GUIDs that are output and punch them into the SP…duh

  5. Gene – If you run the entire SQL statement, it outputs the command necessary to run the stored procedure. Just copy the output of the PRINT statement, and paste it in a new query window to execute it.

  6. My Problem now is that the output results HealthServiceIDList reached MAX per the Declare statement at the top and I have more than MAX allows so stuff would be cut-off. I suppose you would go into the SP and fix whatever it has MAX set to be allowed.

  7. NVARCHAR(MAX) supports up to two gigabytes of data, so it should work for you. The stored procedure also accepts NVARCHAR(MAX). If you don’t have a need to see the output before running it, you can replace the PRINT statement with the actual EXEC statement, which will pass things along correctly. See below for an example.


    exec dbo.p_SetApprovedHealthServicesForDistribution
    @SecureStorageElementId = @SecureStorageElementId,
    @HealthServiceIdList = @HealthServiceIdList,
    @ApprovedHealthServicesResult=2;

  8. That got it. Thanks for your replies!! For the record; I did take the PRINT statement off, but I must have had something wrong in the exec pattern (putting on my dunce cap for the day). This is a much needed thing to have for any SCOM admin and I appreciate you posting it up.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php