Important to know:
This posting is based on this article Kevin Holman posted. Based on the information provided in that article I wrote this step by step guide.
When one has imported the latest version of the Server OS MP (version 6.0.6958.0) with the additional Reports MP (Microsoft.Windows.Server.Reports.mp) one can bump into a nagging error when running the Report Performance By Utilization.
How to solve it
And this puzzled me since the related SQL Server is based on SQL Server 2008 R2 SP1. So no issues there. So it was time for some Bing actions (no more Google for me since Google has decided to wreck the search results with Google+ information…). And soon I found this posting written by Kevin Holman.
But first I needed to enable Remote Errors on the related SSRS instance in order to make sure I experience the issue described in the posting. How? Easy and also to be found here: http://technet.microsoft.com/en-us/library/aa337165.aspx.
- Start a RDP session with the SSRS server;
- Log on and start SQL Server Management Studio and logon to the SSRS instance (NOT the DB engine!);
- Right click on the SSRS instance, select Properties and go to Advanced and set EnableRemoteErrors item to True. No restart needed. Just click OK and close SQL Server Management Studio.
- When you run the problematic report again, you’ll get more information about the CAUSE of the error and BINGO! Exact the same error as described: Procedure or function Microsoft_SystemCenter_Report_Performance_By_Utilization has too many arguments specified.
OK, so now I know this issue matches exactly the one Kevin has described. Now it’s time for some action in order to restore the functionality of this Report. In this case I have decided to run the procedure high lighted in purple since that one is straight forward and easy to do:
- Download the MP for the server OS and unzip it by either installing it on your workstation or running the QWERTY.msi tool;
- Make sure the MP Authoring Console is installed on the same workstation, start this tool and open the file Microsoft.Windows.Server.Reports.mp;
- Go to Reporting > Data Warehouse Scripts;
- Select the Data Warehouse script Microsoft.Windows.Server.Reports.PerformancebyUtilization.Script and select in the left pane the option Properties;
- Go to the Install tab and select the WHOLE SQL script and copy it:
- Backup the Data Warehouse database before you run this SQL script!!!
- Right click the Data Warehouse database (OperationsManagerDW) and select the option New Query;
- Paste the query which you copied in Step 5:
- Run the query. It will run fast:
- Close SQL Server Management Studio. Go to the SCOM R2 Console and run the Report again. BINGO!
So within a few minutes a nagging problem was solved! Nice one!
Here is the required SQL script which you must run against the Data Warehouse database when you have above mentioned issues. If not, contact CSS!
Now you can start from Step 6 :).
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Report_Performace_By_Utilization')
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_Performace_By_Utilization]
SET NOCOUNT ON
DECLARE @ExecError INT
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId
-- create table to hold all relationship types found
CREATE TABLE #RelationshipType (
-- use table-valued function to build relationship list
-- create table to hold all contained objects
-- prepare recursion: put group into contained objects
DECLARE @RowCount int
-- recursively walk down containment hierarchy
CREATE TABLE #PerfObjectCounters (
SET @Error = @@ERROR
CREATE TABLE #ManagementGroups(ManagementGroupGuid UNIQUEIDENTIFIER)
EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @PerfObjectCounterListXml
INSERT INTO #PerfObjectCounters (PerfObject, PerfCounter, PerInstance)
DECLARE @Pos INT,
SET @Pos = 0
WHILE @NextPos > 0
IF @ExecError <> 0 GOTO QuitError
INSERT INTO #ManagementPacks(ManagementPackSysName, ManagementPackId)
IF @DataAggregation = 1
WITH ComputerId AS
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_Report_Performace_By_Utilization] TO OpsMgrReader