locked
SCOM Exchange Microsoft.Exchange.15.MailboxStatsSubscription.Rule Failed to store data in the Data Warehouse RRS feed

  • Question

  • I am gettting the below error, only for exchange in SCOM. Anyone having any idea why?

    Failed to store data in the Data Warehouse. The operation will be retried.
    Exception 'InvalidOperationException': The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. 

    One or more workflows were affected by this.  

    Workflow name: Microsoft.Exchange.15.MailboxStatsSubscription.Rule


    Brody Kilpatrick

    Wednesday, August 26, 2015 1:01 PM

Answers

  • This error is caused by the rule Exchange 2013: Mailbox Statistics Collection:

     

     

    This rule does collect information about statistics of mailboxes. Within a second step, the management server does write these information into the database and does fail with this error:

     

    Log Name:      Operations Manager
    Source:        Health Service Modules
    Date:          20.01.2016 09:36:58
    Event ID:      31551
    Task Category: Data Warehouse
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      WinterSCOM1.winter.com
    Description:
    Failed to store data in the Data Warehouse. The operation will be retried.
    Exception 'InvalidOperationException': The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.
    One or more workflows were affected by this. 
    Workflow name: Microsoft.Exchange.15.MailboxStatsSubscription.Rule
    Instance name: WinterSCOM1.winter.com
    Instance ID: {466DF86F-CC39-046A-932D-00660D652716}
    Management group: WINTER1


     

    This does happen if a property collected by the rule “Exchange 2013: Mailbox Statistics Collection” does not fit into the columns in the related table in the datawarehouse.

     

    Usually this does happen if there is a mailbox with a lot of emailaddresses. The length of emailadresses is limited to 1024 in the datawarehouse:

     

     

    However, there might be another property that does not fit into the length defined in the DW. There are two options:

     

    -          Disable the Exchange 2013: Mailbox Statistics Collection

    -          Or, my preferred one, check the mailboxes if there are mailboxes with a lot of emailaddresses that break the limit of 1024 chqaracters

     

    The powershell command get-mailbox might help to identify this. An example from my testing:

     

     

    The mailbox dashboard has a property EmailAddresses with more than 1024 characters which does lead to this error.

    Hope this helps.....  

    • Proposed as answer by Ken Knicker Monday, August 29, 2016 7:15 PM
    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    Wednesday, January 20, 2016 8:48 AM
  • For people with more than one mailbox this approach could be a little cumbersome ;-)

    The following PS should help

    get-mailbox | where-object { $_.EmailAddresses.ProxyAddressString.ToCharArray().Length -ge 1024 } | foreach-object {write-host "$_"}

    Hope that helps

    Dan Rawlings


    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    Friday, October 7, 2016 8:47 AM
  • Ok, well, I created a blog post for a workaround until MS fixes. Thank you Stefan Wuchenauer, Ken Knicker and Dan Rawlings for the direction.

    http://www.brodykilpatrick.com/2016/12/scom-exchange-microsoftexchange15mailbo.html 


    Brody Kilpatrick


    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    • Edited by zuldera7 Friday, December 16, 2016 9:39 PM
    Friday, December 16, 2016 9:37 PM

All replies

  • We are getting the exact same error. Its the only event that says something about failed to store data in the Data Warehouse. This event id (31551) repeats every 10 minutes.
    Monday, August 31, 2015 7:39 AM
  • Hi Sir,

    Please refer to the article regarding the same error :

    http://blogs.technet.com/b/silvana/archive/2014/04/11/exception-39-invalidoperationexception-39-the-given-value-of-type-string-from-the-data-source-cannot-be-converted-to-type-nvarchar-of-the-specified-target-column.aspx

    Hope it is helpful to you .

    Best Regards,

    Elton Ji


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com .

    • Proposed as answer by Elton_Ji Monday, September 7, 2015 5:11 PM
    • Marked as answer by Elton_Ji Tuesday, September 8, 2015 4:07 PM
    • Unmarked as answer by zuldera7 Wednesday, September 9, 2015 5:14 PM
    • Unproposed as answer by zuldera7 Wednesday, September 9, 2015 5:14 PM
    Sunday, September 6, 2015 4:23 AM
  • That is not a resolution. This is specific to the one rule, and the errors outlined in the blog post are not present. Has anyone found a resolution to the Exchange 2013 specific issue?

    Brody Kilpatrick

    Wednesday, September 9, 2015 5:15 PM
  • I am getting the same also.  This only started happening since I upgraded to UR7.  From what I have seen online, one of the fields is larger then the set character length in the DW.   

    I have found the following article but not got very far with it as of yet. 

    http://blogs.technet.com/b/mihai/archive/2014/05/03/some-alerts-are-not-appearing-in-reports-in-operations-manager-and-we-see-31551-events-in-the-event-log.aspx


    Friday, October 9, 2015 2:57 PM
  • This is still an issue. Any chance they are going to fix the Exchange 2013 MP? Is anyone else experiencing this and have you found a fix?

    Brody Kilpatrick

    Thursday, October 22, 2015 12:47 PM
  • I see this issue as well. Has there been an update to fix this?
    Tuesday, December 8, 2015 9:09 PM
  • I am getting Same error also.

    Failed to store data in the Data Warehouse. The operation will be retried.

    Exception 'InvalidOperationException': The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. 

    One or more workflows were affected by this.  

    Workflow name: Microsoft.Exchange.15.MailboxStatsSubscription.Rule 
    Instance name: < MS Server Name >
    Instance ID: {5F2FD4F9-0A2C-E8C1-8B26-20E595A873D2} 
    Management group: < MG Name >

    Wednesday, December 16, 2015 2:25 PM
  • This error is caused by the rule Exchange 2013: Mailbox Statistics Collection:

     

     

    This rule does collect information about statistics of mailboxes. Within a second step, the management server does write these information into the database and does fail with this error:

     

    Log Name:      Operations Manager
    Source:        Health Service Modules
    Date:          20.01.2016 09:36:58
    Event ID:      31551
    Task Category: Data Warehouse
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      WinterSCOM1.winter.com
    Description:
    Failed to store data in the Data Warehouse. The operation will be retried.
    Exception 'InvalidOperationException': The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.
    One or more workflows were affected by this. 
    Workflow name: Microsoft.Exchange.15.MailboxStatsSubscription.Rule
    Instance name: WinterSCOM1.winter.com
    Instance ID: {466DF86F-CC39-046A-932D-00660D652716}
    Management group: WINTER1


     

    This does happen if a property collected by the rule “Exchange 2013: Mailbox Statistics Collection” does not fit into the columns in the related table in the datawarehouse.

     

    Usually this does happen if there is a mailbox with a lot of emailaddresses. The length of emailadresses is limited to 1024 in the datawarehouse:

     

     

    However, there might be another property that does not fit into the length defined in the DW. There are two options:

     

    -          Disable the Exchange 2013: Mailbox Statistics Collection

    -          Or, my preferred one, check the mailboxes if there are mailboxes with a lot of emailaddresses that break the limit of 1024 chqaracters

     

    The powershell command get-mailbox might help to identify this. An example from my testing:

     

     

    The mailbox dashboard has a property EmailAddresses with more than 1024 characters which does lead to this error.

    Hope this helps.....  

    • Proposed as answer by Ken Knicker Monday, August 29, 2016 7:15 PM
    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    Wednesday, January 20, 2016 8:48 AM
  • For people with more than one mailbox this approach could be a little cumbersome ;-)

    The following PS should help

    get-mailbox | where-object { $_.EmailAddresses.ProxyAddressString.ToCharArray().Length -ge 1024 } | foreach-object {write-host "$_"}

    Hope that helps

    Dan Rawlings


    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    Friday, October 7, 2016 8:47 AM
  • -          Disable the Exchange 2013: Mailbox Statistics Collection

    -          Or, my preferred one, check the mailboxes if there are mailboxes with a lot of emailaddresses that break the limit of 1024 characters

    Disabling the Mailbox statistics collections is a shame as this is important data.

    The problem in my case is a user that has a lot of e-mail addresses and needs them all, the user has 1187 characters in use.

    At least I would expect to see a form of truncation/error trigger before the data is written to the Datawarehouse.

    If this error is encountered frequently by multiple users why isn't the field in this MP extended to 2048 characters?

    Thursday, October 13, 2016 2:52 PM
  • Ok, well, I created a blog post for a workaround until MS fixes. Thank you Stefan Wuchenauer, Ken Knicker and Dan Rawlings for the direction.

    http://www.brodykilpatrick.com/2016/12/scom-exchange-microsoftexchange15mailbo.html 


    Brody Kilpatrick


    • Marked as answer by zuldera7 Friday, December 16, 2016 9:37 PM
    • Edited by zuldera7 Friday, December 16, 2016 9:39 PM
    Friday, December 16, 2016 9:37 PM
  • Hey Brody, This workaround only goes halfway to correcting the issue as the target table also needs updating. I've posted a comment on your blog to explain. In short, you also need to update the column length in the target table (mailboxproperties) and ideally also the table def in the standarddatasetaggregation table. 

    Any direct modification to the DB is done at your own risk and is not supported by MS etc. etc. 

    HTH 

    Dan Rawlings


    • Edited by Danraw Thursday, May 18, 2017 4:04 PM
    Thursday, May 18, 2017 2:44 PM