locked
'DTSStep_ExecuteSQLTask_SC_EventFact_View_1_Insert' - 14 Day History not filling in RRS feed

  • Question

  •   I have a single W2K8 SP1 Server with FCS and WSUS on a single server load.  I followed the info located here, http://technet.microsoft.com/en-us/library/cc901471.aspx, and the install went very smooth.  I also read the info at this links http://blogs.technet.com/b/fcsnerds/archive/2008/09/25/fcs-with-mom-2005-database-guidance.aspx on Db sizing.  My environment is all windows XP, Win 7, W2K3, and W2K8 with approximately 700 Desktops and 50 Servers.  So my OnePoint DB should be approximately 3 GB and its transaction logs should be 1GB.  The SystemsCenterReporting DB should be 94GB and it transaction logs should be 15GB.

      That is where I started and everything appeared to running well.  About three days ago the stats in the 14 day history stopped reporting and still shows no data for the last three days.  If I run the either manually or via its sked taks there is no difference, the data still does not show up.

    Thanks

     

    Jim

    Friday, October 1, 2010 5:01 PM

Answers

  • Hi,

    Thanks for the post.

    There are two possible causes for this issue:

    1- The user opening the Client Security console has not been granted Client Security Report Viewer permissions.

    2- The SQL Server Reporting Services site needs to be added to the list of trusted sites.

    To troubleshoot this issue, please do the following:

    To add the SQL Reporting Services site to the list of trusted sites
    1. In Internet Explorer, on the Tools menu, click Internet Options.
    2. Click the Security tab, and then click the Local intranet zone.
    3. Click the Sites button.
    4. In the Add this website to the zone box, type the URL of the SQL Server Reporting Services site. For example, http://servername.
    5. Click Add, click Close, and then click OK for all subsequent dialog boxes.
    6. Refresh the Client Security console.

    Before using Client Security, you must grant additional permissions to the service accounts.

    To grant the correct permissions for the service accounts

    1. On the Client Security server, add the action account to the Administrators group.
    2. Grant the reporting account db_owner permissions on the SystemCenterReporting database.
    3. If you used different accounts for the DAS account and the action account, grant the action account db_owner permissions on the OnePoint database.
    4. If you used different accounts for the DAS account and the reporting account, grant the reporting account db_owner permissions on the ReportServer database.

    To grant permissions to SQL Server databases

    1. On the server with the appropriate database (OnePoint or SystemCenterReporting), start SQL Server Management Studio.
    2. In the console tree, expand Security.
    3. Right-click Logins, and then click New Login on the shortcut menu.
    4. In the Login dialog box, type the appropriate service account (domain\username) in the Login name box.
    5. Under Select a page, click User Mapping, and then in the Map column, select the check box for (OnePoint, ReportServer and SystemCenterReporing) databases.
    6. In the Database role membership box, select the db_owner check box, and then click OK.

    Does it work?

    Thanks,

    Miles

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Miles Zhang Tuesday, October 26, 2010 6:47 AM
    Monday, October 4, 2010 2:22 AM

All replies

  • Hi,

    Thanks for the post.

    There are two possible causes for this issue:

    1- The user opening the Client Security console has not been granted Client Security Report Viewer permissions.

    2- The SQL Server Reporting Services site needs to be added to the list of trusted sites.

    To troubleshoot this issue, please do the following:

    To add the SQL Reporting Services site to the list of trusted sites
    1. In Internet Explorer, on the Tools menu, click Internet Options.
    2. Click the Security tab, and then click the Local intranet zone.
    3. Click the Sites button.
    4. In the Add this website to the zone box, type the URL of the SQL Server Reporting Services site. For example, http://servername.
    5. Click Add, click Close, and then click OK for all subsequent dialog boxes.
    6. Refresh the Client Security console.

    Before using Client Security, you must grant additional permissions to the service accounts.

    To grant the correct permissions for the service accounts

    1. On the Client Security server, add the action account to the Administrators group.
    2. Grant the reporting account db_owner permissions on the SystemCenterReporting database.
    3. If you used different accounts for the DAS account and the action account, grant the action account db_owner permissions on the OnePoint database.
    4. If you used different accounts for the DAS account and the reporting account, grant the reporting account db_owner permissions on the ReportServer database.

    To grant permissions to SQL Server databases

    1. On the server with the appropriate database (OnePoint or SystemCenterReporting), start SQL Server Management Studio.
    2. In the console tree, expand Security.
    3. Right-click Logins, and then click New Login on the shortcut menu.
    4. In the Login dialog box, type the appropriate service account (domain\username) in the Login name box.
    5. Under Select a page, click User Mapping, and then in the Map column, select the check box for (OnePoint, ReportServer and SystemCenterReporing) databases.
    6. In the Database role membership box, select the db_owner check box, and then click OK.

    Does it work?

    Thanks,

    Miles

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Miles Zhang Tuesday, October 26, 2010 6:47 AM
    Monday, October 4, 2010 2:22 AM
  • Miles,

      Thanks for the information.  I performed the above steps and the error has cleared however, the 14 day history is still not populating data. It just seems very odd that is was working great and then just stopped.  So when the job runs now I see this error 

    Step StepInvokeInnerPackage failed.

    Step Error Source: Microsoft OLE DB Provider for SQL Server

    Step Error Description: (1:SC_Inner_DTS_Package) SubStep 'DTSStep_ExecuteSQLTask_SC_EventParameterFact_View_1_Insert' failed with the following error:

    Transaction (Process ID 89) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Execution was canceled by user.

    Step Error Code: -2147220441

    Step Error Help File:

    Step Error Help Context ID:0

      I think I have see this before and will look.

    Thanks

    Jim

    Monday, October 4, 2010 3:09 PM
  •  Ok, an update.  Using this KB http://support.microsoft.com/kb/899158 more specifically the latency switch I was able to get all the old data to show in the 14 dy history.  Now the scheduled task which runs a 1am has these switches

    MOM.Datawarehousing.DTSPackageGenerator.exe /silent /srcserver:FOREFRONT /srcdb:OnePoint /dwserver:FOREFRONT /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager"

    which looks correct to me.  While looking upthe error above I found this link http://blogs.technet.com/b/clientsecurity/archive/2010/04/28/event-id-81-and-fcs.aspx and was thinking of setting the /maxconn switch.

     

    Thoughts?

     

    TIA

    Jim

     

    Monday, October 4, 2010 4:12 PM
  •   Update, the scheduled tas ran fine and reported no errors however, the 14 day history is missing the latest day, so I guess even if the task ran it didn't move the data over.  Does anyone have another idea?

    Jim

     

    Tuesday, October 5, 2010 3:45 PM