SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class

Recently I got this error message when I started the configuration manager on a SQL server that is used for development purposes. It is a SQL 2008 R2 standard 64-bit version and it had not been used up to now.

When starting the SQL server Configuration manager, the program halted immediately with this error message: “Cannot connect to WMI provider – Invalid class…".

Some sites suggested this may have been caused by an older installation of the 32-bit version, that had been removed after installation of the 64-bit version. The Configuration manager is a 32-bit tool used by both versions. so after uninstalling the 32-bit version the configuration manager might have been uninstalled as well. So I repaired the 64-bit installation in the hope that this would fix the problem. However, it did not.

After further searching on the net I found this article from Martin Poon

Open an elevated Command Prompt, and type the following…

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

His solution worked for me. I was able to open the SQL server configuration manager and reconfigure the SQL server services and network protocols.

No cubes can be found

Using VS2008 to create a new dataset in a reporting services project, based on a SSAS cube. The query designer responds with the error message: ‘No cubes can be found’

From this this article on Microsoft connect

“After connecting to SSAS in SQL Server Management Studio and attempting to browse a cube, the cube browser doesn't show any cubes. Attempting to open a dataset that uses an MDX query in Reporting Services results in the following error:
No cubes can be found“

“This was caused by the cube Visible property being set to False but what flips this property from True to False is unknown.“

Although the Visible property was set to true in Visual Studio 2008, I have had to explicitly reset it to True and then redeploy the cube. After that I could create a shared dataset based on this cube.

How to programmatically modify a foreign key value in a Lightswitch entity

Problem

Sometimes the business requirement of an application is such that a foreign key value in a database record or set of records needs to be changed into another value automatically. E.g. in the AdventureWorks database a selected set of products has to be given a new product subcategory.

In principle this means that the value ProductSubCategoryyId in the Products table needs to be changed from value A to B. In a Lightswitch entity based on the database table, the value of the Id is hidden, instead the entity propagates an instance of the ProductSubCategory entity.

Solution

To change the value of the foreign key programmatically you need to change the SubCategory not the Id.

Example

Suppose we have an editable grid screen base on the AdventureWorks Products and the product that is currently selected needs to have another default SubCategory. A button on the Datagrid command bar has execute code behind to do so.

The trick is to create a new instance of the subcategory with the Id you want and then assign this new instance to the product in question. In the example we want to assign a subcategoryId with value 1 to the selected product.

partial void ModifySubCategory_Execute()

{

    int NewSubCategoryId = 1;

    ProductSubcategory s = DataWorkspace.AdventureWorksData.ProductSubcategories_SingleOrDefault(NewSubCategoryId);

    Product p1 = this.Products.SelectedItem;

    p1.ProductSubcategory = s;

}

Once the data will be saved the SubcategoryId value will be written into the product table.

Linked dataset in report does not show new columns added in database

Using Visual Studio 2008 to develop the report, SQL server 2008 R2.to host the database and the reporting services.

Problem

The problem occurred in a report that uses a link to a shared dataset, which is based based on a stored procedure in SQL server. The stored procedure uses input parameters to select data from a view and returns the dataset. The table on which the view is based has been altered with some new columns. The view was altered to select those columns. The stored procedure used a ‘SELECT * FROM view’ and showed the new columns after testing in SQL server management studio. The shared dataset was updated with the Refresh Fields button to show the new fields. The fields did become available in the fields list of the shared dataset. But with the report in the designer of VS2008, and with after refreshing the fields of the linked dataset, no new fields were available.

Report –> linked dataset –> shared dataset –> stored procedure –> view –> table.

Solution

The select statement in the stored procedure was modified to select each column from the view by name.

Instead of using

SELECT * FROM view

The statement was altered to

SELECT field1, field2, field3, … FROM view

After saving the changes in the stored procedure, the refresh fields in the linked dataset design gave all new fields.

The target principal name is incorrect while trying to connect to Analysis Services server

A sql server had been setup for testing purposes but never been used with SSAS. The service was installed. Today when trying to connect to the AS server I received this error message:

The target principal name is incorrect.

The solution is to use th IP address of the server or the fully qualified domain name. e.g. instead of using ‘SERVER01’, use ‘SERVER01.somedomainname.com’
Thanks to Patrick Purviance who wrote about this problem in his blog: target principal is incorrect

Print alternating background colour in detail section of XtraReports for Lightswitch

Problem

The readability of a printed report with text and numbers can be improved by printing the details with and alternating colour, e.g. lines in white background alternate with lines in light grey background.

Solution

In a LightSwitch application with an XtraReports report, we can make the colours alternate by creating an EvenStyle and OddStyle in the Styles collection in the properties of the detail section. One of these styles will be given a different background colour from the other.

Prerequisites

The example is based on a view vEmployeeDepartment from the AdventureWorks database. The view has been added to the Datasource of the Lightswitch project. To create a report with grouping see my article about grouping with XtraReports 

How to set alternating background colours

• In the XtraReports design, select the detail section. Click on the grey Details band.
• In the properties window, expand the Styles Collection property
  
• The EvenStyle, OddStyle properties are displayed. Click on (none) near the EvenStyle.
  
• Click on the dropdown arrow, in the dropdown list, select (New)
  
• A new style is created with the name xrControlStyle1. Repeat the above step for OddStyle.
  
• A 2nd style is created , called xrControlStyle2.
  
• Now we have to set the background colour of these styles to a different colour. By default the background of both styles will be transparent. We will only modify the background of the OddStyle to WhiteSmoke. To modify click on (Collection) of the Styles collection, so that a button ‘…’ appears.
  
• Click on the button ‘…’. IN the Styles editor dialog window select ‘xrControlStyle2’.
  
• In the right-hand panel, click (Not set) near the BackColor property to open a dropdown with colours. Select the WhiteSmoke colour.
  
• Click Close.
• Now the background will be printed in alternating white and WhiteSmoke colour as shown in the start of this article.

Remark

In XtraReports, you have to fit your labels together horizontally, so that there is no white space. Because the details background only shows where there are controls. It is not quite as one would expect. If your labels don’t fit together the alternate colour will not show up.

Windows account has been assigned SQL sysadmin role but cannot connect to Integration services with SSMS

When a new Windows Account (user or a group) has been granted the sysadmin server role, it does not automatically follow that this account has full Integration Services administration privileges. To administer Integration Services you need a windows account, a SQL server account will not work.

• In the database engine the account has to be added to the Security Logins with Server Roles public and Sysadmin.
• Use SSMS to connect to the database engine of the server that also runs the integration services.
• Under Security, right-click on Logins, select New Login…
• In the Login-New dialog window
• leave the option Windows authentication
• enter the name of a Windows Account (can be a user or a group)
• Under Server roles, check public and sysadmin
• click OK to create the login.
• In the windows server on which the Integration Services are running the account has to be added to the local system administrators group.
• Log on to the windows server
• under Start / Administrative tools, select Computer management
• In the computer Management console
• System tools / Local User and Groups / Groups
• right click on Administrators in the right hand pane, select Add to group (or select properties)
• In the Adminstrators properties dialog
• click on the Add… button
• enter the Windows Account name (the same as was used in the SQL logins)

This Windows account will now be able to connect with SSMS to Integration services server and perform all tasks necessary to deploy and run packages.