Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,893,380 hits
  • Select GETDATE()

    June 2021
    M T W T F S S
     123456
    78910111213
    14151617181920
    21222324252627
    282930  

Archive for the ‘Error’ Category

Solution: Unable to launch SQL Server Configuration Manager – Invalid class [0x80041010]

Posted by blakhani on September 4, 2014


While launching SQL Server Configuration Manager on one of my machine, I got below error

image

Here is the text of the error message:

—————————
SQL Server Configuration Manager
—————————
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
—————————
OK  
—————————

There might be various reason for this error. In this case our actual problem is “Invalid class” which I have highlighted above. I have looked further and found that below is the solution for me. Same solution also works for Invalid namespace [0x8004100e] error also. 

image

C:\WINDOWS\system32>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof"
Microsoft (R) MOF Compiler Version 6.3.9600.16384
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository…
Done!

MofComp is a command line utility to compile MOF (Managed Object Format) files and store the data in WMI repository. The MOF Compiler is available in the %Windir%\System32\wbem directory. So if you are getting “‘mofcomp’ is not recognized as an internal or external command then try changing current directory from command prompt to %Windir%\System32\wbem

Also note that in mof file on my machine is under 120 folder. Depends on SQL version installed, you may have it in different folder. The value of that number depends on the version of SQL Server.

Microsoft SQL Server 2014

120

Microsoft SQL Server 2012

110

Microsoft SQL Server 2008 R2

100

Microsoft SQL Server 2008

100

Microsoft SQL Server 2005

90

 

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Troubleshooting | Tagged: , , , | 30 Comments »

    Help : Getting error – Cannot resolve the collation conflict

    Posted by blakhani on August 26, 2014


    Recently someone posted on MSDN forum about collation related error which I have seen multiple times. Today I am taking time to write notes about that. Here is the famous collation conflict error which every DBA would encounter at least once in their career.

    Msg 468, Level 16, State 9, Line 29

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

    Let’s understand the error first. There are two collation in the error message. If you read in a flick they look similar but they are not. First one has CS (stands for case sensitive) and second one has CI (stands for case insensitive). From the message it’s clear that SQL can’t convert value implicitly between these two collations. Next action would be to you capture a profiler trace or some other troubleshooting technique to identify offending query. In other words, we get this error when queries use two or more columns with different collations for join and comparison purposes. So there could be two possibilities of getting this error message:

    1. Columns are in two different databases that have a different default collation.
    2. Columns are in same database that have collation explicitly specified as different.

    In most of the cases, this error has been caused when database was moved from one server to another server and this falls under first category. Here is the quick script to get same error.

     

    CREATE DATABASE [CaseSensitiveCollation]
     COLLATE SQL_Latin1_General_CP1_CS_AS
    go
    Use [CaseSensitiveCollation]
    go
    Create table MyTableInCaseSensitiveDatabase (vc varchar(100))
    go
    insert into MyTableInCaseSensitiveDatabase
    values ('sysobjects')
    go
    SELECT
    * FROM master.sys.objects a INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b ON a.NAME = b.vc

    Here is the screenshot.

     

    image

     

    The collation of master database is SQL_Latin1_General_CP1_CI_AS and for column vc in the table its different and hence the error.  Now, how do you resolve this error? We have multiple options.

     

    • Help SQL Server in identifying which column we want to convert to different collation.
    SELECT * 
    FROM   master.sys.objects a 
           INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b 
                   ON a.name = b.vc   collate SQL_Latin1_General_CP1_CI_AS
    • If this is due to restore of the database from different server then check server collation of source server. In this situation, we might have to rebuild the target server to match the source server for the collation. This is as good as reinstalling SQL Server.
    • If we are getting this error for a database which is created as a part of a product installation then we should review the product documentation for details on supported collations.

    I have seen few DBA suggesting to change database collation. It is important to understand that column collation is specified during creation of table. We can find that using below

    image

    Even if we alter the database the collation of already created table would NOT change. Only way to change the collation of existing tables is

    • Move the data to a new table with new collation.
    • Get the script of the table and create same index, stats etc. on new tables.
    • Drop the old table
    • Rename new table as old table. 

    If error is due to temporary tables created in tempdb database then you need to give a thought to contained database feature. Other thing Another option would be to provide the column level collation while creating table as below.

    CREATE TABLE #SQLServerHelp
       (iPK int PRIMARY KEY,
        nCol nchar COLLATE SQL_Latin1_General_CP1_CS_AS
       );
    

    Here are other error which you might get.

    • Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator for %ls operation.
    • Collation conflict caused by collate clauses with different collation ‘%.*ls’ and ‘%.*ls’.
    • Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator occurring in %ls statement column %d.
    • Implicit conversion of %ls value to %ls cannot be performed because the resulting collation is unresolved due to collation conflict between "%ls" and "%ls" in %ls operator.
    • Implicit conversion of %ls value to %ls cannot be performed because the collation of the value is unresolved due to a collation conflict between "%ls" and "%ls" in %ls operator.
    • Cannot resolve the collation conflict between "%.*ls" and "%.*ls" in the %ls operation.

    Hope this would help you in troubleshooting and fixing collation errors.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Troubleshooting | Tagged: , , , , | 1 Comment »