Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,153,602 hits

  • Select GETDATE()

    December 2025
    M T W T F S S
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

Archive for the ‘Troubleshooting’ Category

« Previous Entries
Next Entries »

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

    • Posted in Error, Troubleshooting | Tagged: , , , , | 1 Comment »

    Troubleshooting : %1 is not a valid win32 application

    Posted by blakhani on August 19, 2014


    As part of my job, a significant part of my working hours are all about troubleshooting SQL related issues. They could range from SQL installation, performance, high availability, T-SQL query and pretty much any area related to SQL Server.

    Few days back one of my friend pinged me and told that he is not able to start “Reporting Service” service. I asked if he is seeing any error message anywhere like event logs. He shared below with me.

    image

    Here is the text of the message.

    Service cannot be started. System.Exception: Default appdomain failed to initialize.

       at Microsoft.ReportingServices.Library.ServiceAppDomainController.Start()

       at Microsoft.ReportingServices.Library.ReportService.OnStart(String[] args)

       at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)

    That was not a good message to tell us what’s wrong. I researched further and found that similar to ERRORLOG in SQL Server, there are logs for Reporting Services as well. Here is the more detailed messaged in Reporting Services log.

    image

    configmanager!DefaultDomain!e10!08/16/2014-01:52:29:: e ERROR: Error loading configuration file: %1 is not a valid Win32 application

    library!DefaultDomain!e10!08/16/2014-01:52:29:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.  —> System.ComponentModel.Win32Exception: %1 is not a valid Win32 application

       at Microsoft.ReportingServices.Diagnostics.SafeLibraryHandle.LoadLibrary(String libName)

       at Microsoft.ReportingServices.Diagnostics.SqlInstallation.GetSkuFromSqlBoot(String instanceId, Int32& daysLeft)

       at Microsoft.ReportingServices.Diagnostics.Sku.<>c__DisplayClass6.<GetSkuFromSqlBoot>b__5()

       at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c__DisplayClass1.<Run>b__0(Object state)

       at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state)

       at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback)

       at Microsoft.ReportingServices.Diagnostics.Sku.GetSkuFromSqlBoot(String instanceId)

       at Microsoft.ReportingServices.Diagnostics.Sku.GetInstalledSku(String instanceId)

       at Microsoft.ReportingServices.Diagnostics.RSConfiguration.AdjustProperties(ConfigurationPropertyBag properties)

       at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Validate(ConfigurationPropertyBag properties)

       at Microsoft.ReportingServices.Diagnostics.RSConfigurationFileManager.LoadDocument()

       at Microsoft.ReportingServices.Diagnostics.RSConfigurationFileManager.LoadConfiguration()

       — End of inner exception stack trace —;

    appdomainmanager!DefaultDomain!e10!08/16/2014-01:52:29:: e ERROR: Appdomain:1 DefaultDomain failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.  —> System.ComponentModel.Win32Exception: %1 is not a valid Win32 application.

    appdomainmanager!DefaultDomain!352c!08/16/2014-01:52:29:: e ERROR: Windows service failed to start. Exception: System.Exception: Default appdomain failed to initialize.

       at Microsoft.ReportingServices.Library.ServiceAppDomainController.Start()

    If you are a developer, you would know what a stack is. It goes from bottom to top and shows the section which caused the error. From the highlighted pieces it’s easy to make sense. Reporting Service is trying to get SKU (edition) which is installed on this machine using function GetInstalledSku. After that we are seeing function GetSkuFromSqlBoot which indicates that we will get information using this function. Later, we are seeing LoadLibrary and that function is raising error. Now the question is why! If we do a search on internet using Bingoogle. In general, the most possible cause of the error is corruption of the files which are needed. If we capture ProcMon while starting SSRS Service, it would be easy to find last loaded DLL and then we may need to find if it’s a correct DLL by comparing with another machine where things are working fine.

    Interesting, here is what I saw under “C:\Program Files\Microsoft SQL Server\110\Shared”

    image

    As we can see that someone has renamed the file and original file is renamed as sqlboot.dll.x64.

    When I captured ProcMon

    image

    And after this I saw “exit” of the threads and process.

    Another symptom on the same problem is that when he was running SQL Setup to add some component, he was getting below error

    There was a failure to calculate the default value of setting DIGITALPRODUCTID.

    and this is what we see in setup logs.

    (01) 2014-08-15 10:46:44 Slp: The following is an exception stack listing the exceptions in outermost to innermost order

    (01) 2014-08-15 10:46:44 Slp: Inner exceptions are being indented

    (01) 2014-08-15 10:46:44 Slp:

    (01) 2014-08-15 10:46:44 Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.CalculateSettingValueException

    (01) 2014-08-15 10:46:44 Slp:     Message:

    (01) 2014-08-15 10:46:44 Slp:         There was a failure to calculate the default value of setting DIGITALPRODUCTID.

    (01) 2014-08-15 10:46:44 Slp:     HResult : 0x85640001

    (01) 2014-08-15 10:46:44 Slp:         FacilityCode : 1380 (564)

    (01) 2014-08-15 10:46:44 Slp:         ErrorCode : 1 (0001)

    (01) 2014-08-15 10:46:44 Slp:     Data:

    (01) 2014-08-15 10:46:44 Slp:       SettingId = DIGITALPRODUCTID

    (01) 2014-08-15 10:46:44 Slp:       WatsonData = Microsoft.SqlServer.Chainer.Infrastructure.CalculateSettingValueException@1

    (01) 2014-08-15 10:46:44 Slp:     Stack:

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.Setting`1.CalculateValue()

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Deployment.PrioritizedPublishing.PublishingQueue.CallQueuedSubscriberDelegates()

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Deployment.PrioritizedPublishing.PublishingQueue.Publish(Publisher publisher)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.Setting`1.set_Value(T value)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.Setting`1.SetValue(Object newValue, InputSettingSource source)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.SetSettingValue[T](String settingName, T value, InputSettingSource source)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Configuration.Property`1.SetValueAndSource(Object value, InputSettingSource source)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Configuration.InstallWizard.InstallTypeController.SaveData()

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Configuration.InstallWizardFramework.InstallWizardPageHost.PageLeaving(PageChangeReason reason)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.set_SelectedPageIndex(Int32 value)

    (01) 2014-08-15 10:46:44 Slp:         at Microsoft.SqlServer.Configuration.WizardFramework.NavigationButtons.nextButton_Click(Object sender, EventArgs e)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Control.OnClick(EventArgs e)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Control.WndProc(Message& m)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.ButtonBase.WndProc(Message& m)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Button.WndProc(Message& m)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

    (01) 2014-08-15 10:46:44 Slp:         at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    (01) 2014-08-15 10:46:44 Slp:     Inner exception type: System.ComponentModel.Win32Exception

    (01) 2014-08-15 10:46:44 Slp:         Message:

    (01) 2014-08-15 10:46:44 Slp:                %1 is not a valid Win32 application.

    (01) 2014-08-15 10:46:44 Slp:                

    (01) 2014-08-15 10:46:44 Slp:         HResult : 0x80004005

    (01) 2014-08-15 10:46:44 Slp:         Error : 193

    (01) 2014-08-15 10:46:44 Slp:         Stack:

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Configuration.Sco.SqlbootModule.get_Handle()

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Configuration.Sco.EditionInfo.GetEditionInfo(String RegistryPath, RegistryView view, UInt32& daysLeft)

    (01) 2014-08-15 10:46:44 Slp:                at Microsoft.SqlServer.Configuration.Sco.EditionInfo.GetEditionInfo(String RegistryPath, RegistryView view)

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Configuration.SetupExtension.SqlEditionSetting`1.GetDefaultSqlEditionInfoValue()

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Configuration.SetupExtension.DigitalProductIdSetting.DefaultValue()

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Deployment.PrioritizedPublishing.PublishingQueue.CallFunctionWhileAutosubscribing[T](SubscriberDelegate subscriberDelegate, Int32 priority, AutosubscribingFunctionDelegate`1 function)

    (01) 2014-08-15 10:46:44 Slp:                 at Microsoft.SqlServer.Chainer.Infrastructure.Setting`1.CalculateValue()

    So we can clearly see that someone has messed around with the files related to SQL. Here also we are seeing SqlbootModule.

    RESOLUTION

    In this situation, I went ahead and named the files correctly and things were fixed. BUT if you get this error “%1 is not a valid Win32 application” you might need to remove and install SQL Server. It’s not always possible to find the cause with the corrupted file and reinstallation would be a faster approach.

    Hope this helps!

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

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

    Tips and Tricks : Error: 5171 – tempdb.mdf is not a primary database file

    Posted by blakhani on August 12, 2014


    If you are getting same error for database other than tempdb then there is a serious issue with the file. Primary file is a database file which contains information about database itself like location, size of other files and other information about the database. Error 5171 means that SQL Server is attempting to get the information for a database from a file that is not the primary file.

    While doing some testing with TempDB database I started getting below errors in ERRORLOG and SQL Server was not getting started.

    2014-08-12 05:08:24.91 spid9s      Clearing tempdb database.

    2014-08-12 05:08:28.20 spid9s      Error: 5171, Severity: 16, State: 1.

    2014-08-12 05:08:28.20 spid9s      F:\TEMPDB\tempdb.mdf is not a primary database file.

    2014-08-12 05:08:28.26 spid9s      Error: 1802, Severity: 16, State: 4.

    2014-08-12 05:08:28.26 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    2014-08-12 05:08:28.26 spid9s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

    2014-08-12 05:08:28.29 spid9s      SQL Server shutdown has been initiated

     

    This started happening after I moved TempDB to new location using my own earlier blog. Here is the command which I have run 

    USE master; 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
GO

    If you notice closely, I have made mistake in extension of the files and due to which both files are same. This can easily be corrected by starting SQL in minimal configuration using parameter f and correcting the path.

    When I tried the same in SQL Server 2014, I got below error message, which is amazing.

    Msg 12106, Level 16, State 1, Line 6

    The path name ‘F:\TEMPDB\tempdb.mdf’ is already used by another database file. Change to another valid and UNUSED name.

    If this is happening for database other than TempDB after moving then you may want to check if move command was proper or not. You need to check logical name and the physical file path. If this is after some crash then you may need to restore from a last known good backup. If you don’t have backup then … you need to find a new assignment! Take this as a new lesson and move on. There are data recovery tools available but I have not worked with them and can’t recommend anyone.

    Hope this helps.

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

    • Posted in Tips and Tricks, Troubleshooting | Tagged: , , , | 1 Comment »

    « Previous Entries
    Next Entries »