Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,832 hits

  • Select GETDATE()

    June 2023
    M T W T F S S
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  

Archive for the ‘Troubleshooting’ Category

« Previous Entries
Next Entries »

Solution : Cannot add new node – Rule "SQL Server Database Services feature state" failed.

Posted by blakhani on September 11, 2014


While deploying SQL Server 2014 cluster in my lab I ran into this problem when I was trying to add second node (Node 2) to SQL cluster.

—————————
Rule Check Result
—————————
Rule "SQL Server Database Services feature state" failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
—————————
OK
—————————

Above error means that when I installed SQL Server on first cluster node, it didn’t install properly. There were some errors while creating cluster and due to that AddNode is blocked. In my case also this is true because I had an issue with SQL Server Network Name Resource and it didn’t come online earlier. Below was the original error on Node 1

Cluster network name resource ‘SQL Network Name (Balmukund)’ cannot be brought online. The computer object associated with the resource could not be updated in domain ‘MyDomain.com’ for the following reason:
Unable to update password for computer account.

The text for the associated error code is: Access is denied.

The cluster identity ‘WinCluster$’ may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

I fixed above error by giving proper permission to cluster computer object on Balmukund (which was the network name of SQL) After fixing that I created SQL Server and SQL Agent manually. Sometime it may also happen that SQL Agent didn’t come online on Node 1 due to some failure and it was fixed after setup was completed.

So, if we see above rule failure then we should go to the first node and check setup log files to see if the installation succeeded or failed. If the first node installation had some failures which were fixed later then we should do a repair of the installation. Repair option can be found under the "Maintenance" page in SQL Server Installation Center (setup).  This action will clear the failure state and allow the node addition action.

If you are lazy like me to do repair then you can also check registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\ConfigurationState

If you see value as 2 for any component then there were some failures of those components. In most of the cases since issue would be with SQL Server or SQL Agent, we might see 2 for MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

Note: that we MUST fix the error on first node before taking shortcut of registry value. Once error is fixed, we can make the value of those component to 1 (means success)

The highlighted value MSSQL12.SQL2014 might vary based on SQL version and Instance name. The first piece for SQL 2008 it would be MSSQL10, for SQL 2008 R2 it would be MSSQL10_50, for SQL 2012 it would be MSSQL11 and for SQL 2014 it would be MSSQL12. Second piece is instance name (for me instance name is SQL2014)

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
    • Advertisement

    Posted in Installation, Troubleshooting | Tagged: , , , | 2 Comments »

    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 AlwaysOn – Paperback, Kindle

    • Posted in Error, Troubleshooting | Tagged: , , , | 31 Comments »

    Troubleshooting : Error – Incorrect syntax near ‘GO’

    Posted by blakhani on August 28, 2014


    Sometimes we are very comfortable and used to with certain things that if they change, we become nervous and uneasy. I have a lovely daughter and she always greets me when I come back home from office. That one “hello” takes away all my worries and I feel alive. Yesterday she didn’t do that and I was worried. I checked with my wife and she told that there was a mild fever and her mood is little different today. That made me little nervous.

    Same feeling happened when I saw below in management studio of my colleague:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near ‘GO’.

    image

     

    and management studio intellisense feature was also complaining about syntax. “Incorrect syntax near ‘End Of File’. Expecting ‘=’. As per documentation “GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor”

    I went to Tools > Option in Management studio and found that there was a customization done on batch separator. SSMS> Tools > Options >Query Execution > SQL Server > General > Batch Separator.

    Which means if I run with “come” it should work and as expected.

    image

    Perfect! This mystery is solved now. This made me think that can we customize SQLCMD as well. If we look into help of SQLCMD we can see parameter -c cmdend

    image

    Here is the usage of parameter –c . I have used hello as command end parameter and hello works same as go in true sense.

    image

    I hope this clears some confusion about batch separator.

    If you are getting this error while using ExecuteNonQuery in .net program and running script then refer http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx which has a workaround.

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SSMS, Troubleshooting | Tagged: , , , , , , | 2 Comments »

    « Previous Entries
    Next Entries »