Sql error getting instance id from name

Posted by blakhani on December 16, 2014

This entry was posted on December 16, 2014 at 3:30 AM and is filed under Error, SQL Server.
Tagged: Please uninstall then re-run setup to correct this problem, Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.

Sql error getting instance id from name

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I wish to download and test a sample database in SQL Demystified by Andy Oppel. McGraw Hill publishers.

Downloaded a free version of SQL server 2012 express and chose localdb as the easiest to configure, supposedly with least conpatibility issues. The download created a second Program files (x86) directory on my hard drive below Program files. Is this normal or could it cause the problem identifying an ‘instance’. SQL server appears in both directories, though the exe file is only in the original program files.

Path being:- program files/Microsoft SQL Server/110/localDb/Binn

It is SQL server for windows NT- 64 bit. My computer is running Windows.

On running SQL Server, I get the message.

SQL server is corrupt or has been tampered with. Error getting instance id from name. Uninstal and re-run setup to correct this problem.

Do I uninstal and delete the program files (x86), have already tried uninstal/setup without deleting the x86 directory.

Источник

Sql error getting instance id from name

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I wish to download and test a sample database in SQL Demystified by Andy Oppel. McGraw Hill publishers.

Downloaded a free version of SQL server 2012 express and chose localdb as the easiest to configure, supposedly with least conpatibility issues. The download created a second Program files (x86) directory on my hard drive below Program files. Is this normal or could it cause the problem identifying an ‘instance’. SQL server appears in both directories, though the exe file is only in the original program files.

Path being:- program files/Microsoft SQL Server/110/localDb/Binn

It is SQL server for windows NT- 64 bit. My computer is running Windows.

On running SQL Server, I get the message.

SQL server is corrupt or has been tampered with. Error getting instance id from name. Uninstal and re-run setup to correct this problem.

Do I uninstal and delete the program files (x86), have already tried uninstal/setup without deleting the x86 directory.

Источник

Sql error getting instance id from name

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I wish to download and test a sample database in SQL Demystified by Andy Oppel. McGraw Hill publishers.

Downloaded a free version of SQL server 2012 express and chose localdb as the easiest to configure, supposedly with least conpatibility issues. The download created a second Program files (x86) directory on my hard drive below Program files. Is this normal or could it cause the problem identifying an ‘instance’. SQL server appears in both directories, though the exe file is only in the original program files.

Path being:- program files/Microsoft SQL Server/110/localDb/Binn

It is SQL server for windows NT- 64 bit. My computer is running Windows.

On running SQL Server, I get the message.

SQL server is corrupt or has been tampered with. Error getting instance id from name. Uninstal and re-run setup to correct this problem.

Do I uninstal and delete the program files (x86), have already tried uninstal/setup without deleting the x86 directory.

Источник

Sql error getting instance id from name

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I got error while installing Sql Server Management Studio and connecting existing database.

Error getting instance ID from name

check using SQLcmd as mention above.

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

I want to my connect my current database. with SQL Server Management Studio..

Need to delete some data from current database.

USE [dbname]
GO
DELETE FROM [dbo].[employee]
WHERE city=’pune’

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

Источник

Sql error getting instance id from name

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I wish to download and test a sample database in SQL Demystified by Andy Oppel. McGraw Hill publishers.

Downloaded a free version of SQL server 2012 express and chose localdb as the easiest to configure, supposedly with least conpatibility issues. The download created a second Program files (x86) directory on my hard drive below Program files. Is this normal or could it cause the problem identifying an ‘instance’. SQL server appears in both directories, though the exe file is only in the original program files.

Path being:- program files/Microsoft SQL Server/110/localDb/Binn

It is SQL server for windows NT- 64 bit. My computer is running Windows.

On running SQL Server, I get the message.

SQL server is corrupt or has been tampered with. Error getting instance id from name. Uninstal and re-run setup to correct this problem.

Do I uninstal and delete the program files (x86), have already tried uninstal/setup without deleting the x86 directory.

Источник

So this happened.  In an attempt to give my SQL Server Instance access to more memory, I set the Max Memory to zero, expecting that to mean infinite.  No luck, SQL Server Management Studio (SSMS) set the max memory to 16MB instead and broke the instance.  I could not do anything more in SSMS because the instance did not have enough memory.

Note:  The screenshots in this article are from a SQL Server 2014 instance, which has apparently fixed this problem so that the Max Server Memory setting defaults to 128MB when you set it to zero and you can still connect with SSMS at 128MB.  In 2012 and prior versions, the setting defaults to 16MB, which is what causes all the trouble.

So I googled for: “accidentally set max server memory to 0”.  This turned up a ton of useful links, but since I had to piece my solution together from various posts, I have created this blog entry to hopefully help someone else get to the solution more quickly.

How to Increase SQL Server Max Memory in a Named Instance

1. First, you will need to be an administrator on the SQL Server Instance you want to fix.
2. Now, from the Start | Run menu in Win 7, or Start | Search in Win 10, look for CMD.
      
3. Now RIGHT-Click on cmd.exe or Command Prompt and select Run As Administrator.
4. Repeat steps 2 and 3, so you have two command windows open. Like so:
   
   In the left window, we will start the instance we need to repair.  In the right window, we will connect to that instance with SQLCMD and fix the memory setting.
5. In the both windows, you need to change to the Binn directory of the SQL Instance that you want to repair.  In my case, this instance is on the D: drive so I have to switch to that first.  Also, this instance is found in the D:Program FilesMicrosoft SQL ServerMSSQL12.DUMBO folder.  Lastly, the Binn folder is below the instance atInstanceMSSQLBinn path.  So I enter these two commands:

   D: <enter>

   Then,

   cd D:Program FilesMicrosoft SQL ServerMSSQL12.DUMBOMSSQLBinn <enter>

   

6. Now that I am in the Binn folder, I can start the SQL Server Instance.  Note:  This assumes the instance is stopped.  Go to Start | Control Panel | Administrative Tools | Services and find the SQL Server (InstanceName) Service and make sure the Status column is blank.  If it says ‘Started’, then right-click it and Stop the service.
   
7. So, back to the command window.  On the left side, we need to start the SQLSERVR service in single user mode, being sure to name the correct instance, like so:

   sqlservr -f -sDUMBO (where DUMBO is the name of the instance to fix)

   You should see SQL Server display a ton of messages.
   

   If you get this error message:

   SQL Server installation is either corrupt or has been tampered with. Error getting instance id from name.

   Then check the instance name and try again.

8. Now that SQL Server is running in the left window, go to the right window and start SQLCMD.  We need to use a trusted connection (-E parameter) and we need to specify the server so we can pick the right instance.  Like so:

   sqlcmd -E -sSHQBT0084DUMBO   (where SHQBT0084DUMBO is the serverinstance to repair)

9. The SQLCMD prompt 1> should appear:
   
10. Now enter the following SQL Server commands, pressing <enter> at the end of each line:

    1> sp_configure 'show advanced options', 1;
2> go
1> reconfigure;
2> go
1> sp_configure 'max server memory', 64000;
2> go
1> reconfigure;
2> go

    Your screen should look like this:
    
11. Assuming that there were no errors when you ran the reconfigure commands, you have fixed the server.  Now we need to clean up a bit.  If you did get an error, let me know via comment below.
12. At the 1> prompt, type exit and press <enter>.  You can now close the window on the right.
13. On the left, press Ctrl-C to stop the instance and enter Y when prompted.  You can now close the left window.
14. Finally, restart the service in the Administrative Tools | Services window by right-clicking it and selecting Start.
15. The End.

Read time 4 minutes

MS SQL Server is the most powerful, reliable and quick RDBMS (Relational Database Management System) which is used for storing data in the form of tables. But sometimes installation of this useful application is unsuccessful and results into following error message:

“Windows could not start the SQL Server ([myinstancename]) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 10022.”

Ways to Trace the Root Cause of the Error – 10022

When you receive the above error, you can find the cause of the error message.

1. You can find the root cause easily with Configuration Manager. For this, start the SQL Server from the Windows Services applet.
2. Another way of starting the SQL server is through command prompt. It may happen that after running the command, you get the following error message:

   Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem

   This error message is an indication that you are in big trouble, but anyhow it needs to be fixed if you want to work on SQL Server. As stated in the error message, reinstall the application but re-installing the SQL Server may or may not solve the problem. It might lead to your database corruption or damage. The MDF files may corrupt as the installation of SQL Server has been damaged. This is the condition when you need SQL repair software to repair corrupt SQL database.

3. Another solution you can go for is the editing of registry entries. If you are technically thorough with the registry concepts, then locate the following:
   HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server.

   There may be some permission problems, so correct it. If this does not work, then uninstall the SQL Server so that all the SQL entries can be successfully removed from the registry. Now install the application again. After installation, all the related problems of installation will get clear.

Solution to Resolve SQL Error- 10022

Databases corruption issues are not that easy to resolve but could be with an efficient SQL Database Recovery. Kernel for SQL Database Recovery software is equipped with advanced repair engine that instantly reacts and fixes corrupt SQL database MDF files.

Follow the simple process to repair and recover MDF file data easily:

Step 1: SELECT

Start the software and click the Browse button to select corrupt database file, select recovery mode and SQL database version as shown in the screenshot below.

Step 2: PREVIEW

You can view and analyze the recovered database objects from the corrupt SQL MDF file before saving the objects to the desired location.

Step 3: SAVE:

Select the saving mode and click OK to proceed. The database file will be saved successfully.

Final Words:

You can remove the error by uninstalling the existing SQL Server and install it again more cautiously. But if the error is due to the corruption in the MDF file then you will face same error after installing the SQL Server. You should repair the MDF file using Kernel for SQL Database software and retrieve the healthy files. The healthy database file will be more acceptable towards the new installation of SQL Server.

Demystifying What “sqlservr -q” Actually Does

(last updated: 2019-01-07 @ 02:25 EST / 2019-01-07 @ 07:45 UTC )

For various reasons, people sometimes find themselves in the unfortunate (and unenviable) situation of having an Instance of SQL Server configured with the wrong Collation. This can often lead to unexpected errors and/or sorting and comparison behavior.

People sometimes try to “fix” this problem by adding the COLLATE DATABASE_DEFAULT to string columns of temporary tables, and/or COLLATE {collation_name} to WHERE / JOIN predicates. While this might work in some situations, it is not a true fix since it does not address areas controlled by the Instance-level Collation: resolving names of variables, cursors, and GOTO labels. It also does not affect the behavior of any Microsoft-provided feature that is contained within msdb and might use the system Collation (possibly including SQL Server Agent, Database Mail, Central Management Server (CMS), Maintenance Plans, Policy Management, etc).

For situations where adding the COLLATE {collation_name} keyword is inadequate or undesirable, there is the option of changing the Instance-level Collation. There are two ways to accomplish this: one “official” and the other “unofficial”. But first, before seeing how to change an Instance’s Collation, it is important to understand exactly what the total impact of that change could be.

General Concepts

The following should be a mostly-complete list of affected areas. It assumes that the Collation will be changing across all levels (Instance, Database, and column) since one of the two methods for changing the Instance-level Collation will change all levels.

1. Simplistically, changing any Collation, even to the same Collation of a newer version (e.g. Latin1_General_CI_AS to Latin1_General_100_CI_AS) can easily change sort orders as well as what equates to what:
   1. Since the Database default Collations are changing, IF / WHILE / etc conditions for variable and input parameters can change behavior. For example, consider the following code:

      IF (@Variable = N'some string')

      Passing in yes for @Variable would evaluate to True in a case-insensitive Database. But if the Database’s Collation changes to be binary or case-sensitive, then that same value would evaluate to False.

   2. Since the Instance default Collation is changing, this impacts resolution of names of variables / parameters, cursors, and GOTO labels. For example, consider the following code:

      DECLARE @Var INT;

      SET @var = 1;

      This will work just fine on an Instance having a case-insensitive Collation. But, if that Instance changes to either a binary or case-sensitive Collation, then that code will break due to the difference between @Var and @var.

   3. JOINs and WHERE predicates can change behavior based on columns having a new Collation:
      • More rows might match when moving from a binary or case-sensitive Collation to a case-insensitive Collation. This could result in a Cartesian product.
      • Conversely, fewer rows than before might match when moving from a case-insensitive Collation to either a binary or case-sensitive Collation. This could result in missing rows.
   4. GROUP BY and DISTINCT behavior could change in the same manner.
   5. FOREIGN KEYS: Rows that equated to the Primary Key reference due to using a Collation with one or more insensitivities (case, accent, etc.) might no longer equate to the PK when moving to a binary or case-sensitive Collation.
   6. ORDER BY behavior could change.
   7. CHECK CONSTRAINT behavior.
2. Data-loss potential for non-Unicode string columns: these rely upon the Code Page used by the Collation of the column to determine which characters can be stored in the column. Data loss can occur only if all 3 of the following conditions are true:
   1. Data is stored in a column using a non-Unicode type: CHAR / VARCHAR / TEXT (FYI: the TEXT type has been deprecated starting with the release of SQL Server 2005, so don’t use it, but it might exist in some places)
   2. Characters with underlying values of 128 (0x80) – 255 (0xFF) are being used.
   3. The old and new Collations use different Code Pages. It does not matter if the old and new Collations use different LCIDs (i.e. Locales); only a change in the column’s Code Page matters. For example, the following cultures (and several others) all use Code Page 1252, and hence switching between them will not be a Code Page conversion: French, Finnish_Swedish, German, Latin1_General, Spanish, Norwegian, etc.

   Even if all three conditions are met, that does not guarantee that there will be data loss. Many Code Pages have many of the same characters in the 0x80 – 0xFF range. Whether or not there will be data loss depends on the specific characters being used, what the new Code Page supports, and which method of updating the Collations is being used:

   • The documented method does an actual conversion: if switching to a Collation that uses a different Code Page than the current one, it is possible that some characters might not be available in the new Code Page and would be converted to either a “best fit” mapping, if one can be found, or else to a “?”. But if the characters are available, then the underlying byte value will be changed if necessary.
   • The undocumented method simply changes the Collation: the bytes of the source data will remain the same, but what characters they represent might change if the new Code Page has a different character with that same underlying byte value.
3. Similar to #2, if a Database’s new Collation uses a different Code Page than the previous Collation, then non-Unicode string literals in that Database (i.e. those not prefixed with an upper-case “N”) can have one or more characters changed to either a best-fit mapping or into a “?” if not available in the Code Page of the new Database default Collation. This can be PRINT statements, RAISERROR messages, literals used for INSERT or UPDATE statements. PLEASE NOTE: this conversion will take place during parsing and hence will not be visible to you. Please see the following two part series for a full explanation: “Which Collation is Used to Convert NVARCHAR to VARCHAR in a WHERE Condition?” (Part A of 2: “Duck”) and (Part B of 2: “Rabbit”).
4. All Indexes containing string-type columns in key fields (INCLUDE columns shouldn’t matter unless one or more of them are non-Unicode and the old and new Collations use different Code Pages) need to be fully rebuilt (or more likely dropped and recreated):
   1. Their ordering might have changed
   2. If using a filter expression (and this applies equally to filtered statistics) that filters on a string column, then the rows that have been included / excluded from the index (or statistic) might change!
   3. For non-Unicode string columns, if the old and new Collations use different Code Pages, then it is possible that characters might change (especially if their underlying value is between 0x80 (128) and 0xFF (255)). See item #2 above regarding data-loss.
5. Potential breaking of code: similar to #1, but here the code will actually break (not just silently behave differently) in any of the following situations if a column in a User Table (i.e. Collation will be changing) is used in combination with a column from a System Table that is NOT based on the Instance default Collation or the Database default Collation (some Collations are hard-coded and are the same between all systems) such that the Collations between the columns is no longer the same:
   • JOIN or WHERE predicate
   • String concatenation (e.g. column1 + column2 )
   • UNION / UNION ALL
   • COALESCE (but ISNULL is fine)
   • CASE statements returning the columns in question
   • CONCAT function
   • possibly some other situations
6. Some columns might not be desirable to change. Some (perhaps many) applications use the same Collation for all string columns. But, for any columns that are set to a different Collation for a specific reason, it might not be desirable to change that to the same Collation as all other columns. On the other hand, it might be less work to change a few columns back to non-standard Collations than it would be to change most of them manually. At the very least you need to do an audit to make sure that you know where all of your “special” columns with differing Collations are, and what Collation they are using so that they can be set back to that after the mass-update.
7. System objects might be in conflict and/or code might break if object names can no longer be resolved, or resolve to duplicates. If using inconsistent casing between object definition and object reference in code (e.g. Table name = “Customers”, Table reference in stored procedures / functions / views = “customers”) and moving to a case-sensitive or binary Collation, that code will fail. If currently using a binary or case-sensitive Collation and having objects named both “Customers” and “customers”, moving to a case-insensitive Collation will fail on a unique constraint violation for what is the internal table holding what we see in sys.objects.
8. What about Full Text Search? Not sure if that is impacted or not, and if so, how…

Methods

IMPORTANT: Before making any changes to your system, be sure to have a complete (and working) set of backups, just in case something goes wrong. “Working” here means that you have tested them by restoring them (somewhere).

Documented Approach

Below is a general overview of this approach that only mentions the parts that actually change the Collation of some part of the system. This is not a full, step-by-step guide. Please see the “Set or Change the Server Collation” link below for additional details.

Rebuild System Databases

Do this to change the Instance-level default Collation, as well as the Collation of the system Databases: master, model, msdb, and tempdb (which is just a copy of model).

• Rebuild System Databases
• Set or Change the Server Collation

Note: the ^ character is for line-continuation. Otherwise, hitting Enter executes the command.

SETUP.EXE /QUIET /ACTION=REBUILDDATABASE ^
/INSTANCENAME=InstanceName ^
/SQLCOLLATION=CollationName ^
/SQLSYSADMINACCOUNTS=accounts  [ ^
/OptionalSwitches ]
 

PLEASE BE AWARE of the following warning in the documentation:

RebuildDatabase scenario deletes system databases and installs them again in clean state. Because the setting of tempdb file count does not persist, the value of number of tempdb files is not known during setup.

This step does not do anything more than change the Collation of the four system Databases, Instance-level meta-data, and the Instance itself. User Databases, as well as the string columns of the User Tables within them, are ignored.

You can find SETUP.EXE in the C:Program FilesMicrosoft SQL Server{INT_version_number}Setup Bootstrap{product_common_name} folder. Some examples are:

• C:Program FilesMicrosoft SQL Server110Setup BootstrapSQLServer2012
• C:Program FilesMicrosoft SQL Server120Setup BootstrapSQLServer2014
• C:Program FilesMicrosoft SQL Server130Setup BootstrapSQLServer2016
• C:Program FilesMicrosoft SQL Server140Setup BootstrapSQL2017
• C:Program FilesMicrosoft SQL Server150Setup BootstrapSQL2019CTP2.1

Change Database-level Collation of User Databases

Do this to change the Database’s default Collation, as well as the Collation of Database-level meta-data:

ALTER DATABASE { database_name | CURRENT } COLLATE {new_collation_name} ;

For example:

ALTER DATABASE [TestDB] COLLATE Latin1_General_100_CI_AS_SC;

or:

ALTER DATABASE CURRENT COLLATE Latin1_General_100_CI_AS_SC;

PLEASE NOTE: You cannot change the Collation for any of the system Databases. Attempting to do so will result in the following error:

Msg 3708, Level 16, State 5, Line 271

Cannot alter the database ‘model’ because it is a system database.

Also, there are certain conditions which can prevent this command from completing. For example, if there are any objects that were created with the SCHEMABINDING option and that use the Database’s default Collation. For more details, please see the documentation for “ALTER DATABASE: Changing the Database Collation“.

This step does not do anything more than change the Collation of Database-level meta-data, and the Database itself. String columns of the User Tables within the Database are ignored.

Change Collation of Columns

Do this to change a column’s Collation:

ALTER TABLE { schema_name }.{ table_name }
   ALTER COLUMN { column_name } { datatype }
   COLLATE { new_collation_name }
   { [ NOT ] NULL } ;

If you do not specify the current NULL / NOT NULL setting, it might change! The default is usually NULL.

Certain conditions may prevent you from being able to alter the column. For example, if you have a Check Constraint defined that references that column, you will get the following error:

Msg 5074, Level 16, State 1, Line XXXXX

The object ‘CK_test’ is dependent on column ‘col1’.

Msg 4922, Level 16, State 9, Line XXXXX

ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.

You should also get an error if either a Primary Key or Foreign Key exists that references the column. However, having a Default Constraint on the column does not cause an error.

Undocumented Approach

How to Do it

The following approach has some advantages over the documented approach described above, especially being one step instead of three, but it is undocumented and hence unsupported, so if you run into any problems, Microsoft is not likely to help, nor will they fix any buggy or unexpected behavior. The -q switch of SQLSERVR.EXE is not found in the Microsoft documentation, nor is it listed when passing in the -? switch to get the help info. However, it can be used as follows:

sqlservr -c -m -T4022 -T3659 -s"{instance_name}" -q"{new_collation_name}"

For example:

sqlservr -c -m -T4022 -T3659 -s"CHANGECOLLATION" -q"Estonian_100_CS_AS_SC"

You must execute this command in an “Administrator” Command Prompt, not a regular Command Prompt. Attempting to do this in a regular Command Prompt will result in the following error messages:

2018-06-10 16:08:19.06 Server Error: 17058, Severity: 16, State: 1.

2018-06-10 16:08:19.06 Server initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.).

… {same error repeated 9 more times)

2018-06-10 16:08:22.08 Server SQL Server shutdown has been initiated

You will also need to make sure that the Instance is not currently running before executing that command-line. You can run the following in the Command Prompt:

NET STOP MSSQL${InstanceName}

Trace Flag 4022 instructs SQL Server to not execute any Stored Procedures marked as “execute at startup”.

Trace Flag 3659 instructs SQL Server to log all errors to the error log during server startup.

Once it completes, you should see the following in the Command Prompt window:

2018-06-10 16:10:45.97 spid6s   The default collation was successfully
                                changed.
2018-06-10 16:10:46.12 spid6s   Recovery is complete. This is an
                                informational message only. No user action
                                is required.

However, the process is still running and does not self-terminate.

Hit Control-C.

You will then see:

Do you wish to shutdown SQL Server (Y/N)?

Hit y.

You should then see the following:

2018-06-10 16:10:56.13 spid6s   SQL Server shutdown due to Ctrl-C or
                                Ctrl-Break signal. This is an informational
                                message only. No user action is required.
2018-06-10 16:10:56.13 spid6s   SQL Server shutdown has been initiated
2018-06-10 16:10:56.13 spid6s   SQL Trace was stopped due to server
                                shutdown. Trace ID = '1'. This is an
                                informational message only; no user action
                                is required.

Now you can start the Instance again. You can run the following in the Command Prompt:

NET START MSSQL${InstanceName}

You can find sqlservr.exe in the Binn folder within the main directory for the Instance (not the SQL Server main directory for that version of SQL Server). If you need help finding it, just run the following from a Command Prompt (does not need to be an Administrator Command Prompt):

REG QUERY "HKLMSOFTWAREMicrosoftMicrosoft SQL Server" ^
 /f "Setup" /s /v "SQLBinRoot" /k /e | FINDSTR SQLBinRoot
 

You will get back entries such as:

... C:Program FilesMicrosoft SQL Server110LocalDBBinn

... C:Program FilesMicrosoft SQL Server120LocalDBBinn

... C:Program FilesMicrosoft SQL Server130LocalDBBinn

... c:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBinn

... c:Program FilesMicrosoft SQL ServerMSSQL14.SQL2017EXPRESSMSSQLBinn

... C:Program FilesMicrosoft SQL Server140LocalDBBinn

... C:Program FilesMicrosoft SQL ServerMSSQL15.SQL2019MSSQLBinn

What it Does

1. Converts one Database at a time (appears to be in database_id order):
   1. master
   2. tempdb
   3. model
   4. msdb
   5. User DBs
2. Will roll back a DB if it does not complete fully, but DBs that have completed will remain converted if an error occurs. This behavior holds true for the system Database’s also. This could lead to the process leaving the Instance with inconsistent Collations for the system DBs if they do not all complete successfully. Meaning, if less than all 4 system DBs complete successfully, there will be a mismatch between the system DBs. And if they do complete successfully but a User DB fails, then the system DBs won’t match the Instance-level Collation. This is not terribly bad, though, since you can fix the problem and restart the operation.
3. Conversion bypasses restrictions imposed on documented method (ALTER DATABASE, and ALTER TABLE…ALTER COLUMN). The following do not cause an error with this method:
   1. Schema-bound objects
   2. Check constraints that use the Database’s Collation
   3. Computed columns that use the Database’s Collation
   4. Table-Valued Functions (TVFs) that pass back string columns that did not specify the COLLATE keyword.
   5. Indexes on string columns
4. Indexes containing string columns are dropped and recreated. If the Clustered Index contains at least one string column, then all Indexes on the Table are dropped and recreated (even if they do not contain any string columns).
5. Conversion bypasses checks meant to prevent leaving the data in an invalid state:
   1. NTEXT columns do not error when being set to a Collation that supports Supplementary Characters. This leaves the column as effectively read-only: you can select from it, but attempting to modify it will get the following error:

      Msg 4189, Level 16, State 0, Line XXXXX

      Cannot convert to text/ntext or collate to ‘{collation_name}’ because these legacy LOB types do not support the Unicode supplementary characters whose codepoints are U+10000 or greater. Use types varchar(max), nvarchar(max), or a collation which does not have the _SC flag.

      However, it is easy enough to manually change the Collation of these columns to one that is not Supplementary Character Aware.

   2. Foreign Key rows that referenced a PK value due to relying upon an insensitivity (e.g. “Y = y” due to case-insensitivity) might no longer reference any PK value. There is no constraint verification, so the FK will be left as “enabled” and “trusted” (assuming it was both “enabled” and “trusted” prior to the operation), yet these same values can no longer be added due to now getting the expected FK violation error.
6. Data loss can occur in VARCHAR / CHAR / TEXT columns if the new Collation uses a different Code Page than was being used previously, and the new Code Page does not have the same character having the same numeric value. This is a different type of data loss than what happens with the other method. The documented method does an actual Code Page conversion, which will both (a) adjust the byte value to a different one for characters that exist in both Code Pages but with different underlying values, and (b) attempt to find a similar looking character, known as a “best fit” match, if a “best fit” mapping exists. This undocumented method does neither of those character conversions; no attempt is made to maintain consistency of the character / glyph itself. The underyling byte values remain the same, but the character that they map to might change between Code Pages. For example, in Code Page 1252 (Latin1_General), byte value 0xC6 equates to “Æ” (Latin Capital Letter AE), but using this method to change to Code Page 1257 (Baltic Rim), the data would show “Ę” (Latin Capital Letter E with Ogonek) because that is what 0xC6 maps to in that Code Page, even though Code Page 1257 contains “Æ”, but having an underlying value of 0xAF instead of 0xC6. A true Code Page conversion would have changed the underlying byte value from 0xC6 to 0xAF so that the data would have shown the same character, “Æ”, after the operation.

   This alone indicates that the operation is not doing any actual string conversions, but instead is merely updating the meta-data for string columns directly to the system catalog tables.

   (please also see related item #15 below regarding In-Memory OLTP)

7. User-Defined Table Types (UDTTs) are completely ignored! Collations for string columns of UDTTs, whether explicitly set using the COLLATE keyword in the CREATE TYPE statement or not (hence using the Database’s default Collation) are not changed by this process. It is unclear whether this behavior is intentional (i.e. by design) or an oversight (i.e. nobody has looked as this code since it was first introduced, prior to the existence of UDTTs), but at the very least it is inconsistent with the “change-everything” approach of this process. If you need the Collations of the string columns in UDTTs to be the same as the Collation you are updating everything else to, then you will need to manually drop and recreate the UDTTs (there is no ALTER TYPE).
8. The result set meta-data for User-Defined Table-Valued Functions (TVFs) is updated to the new Collation, even if the column’s Collation is being explicitly set with the COLLATE keyword. This happens for both Multi-Statement TVFs and Inline TVFs, though the effect of this change is different between them:
   • Multi-Statement TVFs use the Collation as recorded in the meta-data. Even if a column is using the COLLATE keyword in the RETURNS @{table_name} TABLE () definition in the CREATE FUNCTION statement to explicitly set the Collation, the Collation shown in sys.columns is what will be used. In this case, the behavior of the function will be inconsistent with its definition, and the output might be incorrect (especially for CHAR / VARCHAR fields).
   • Inline TVFs use the Collation as recorded in the definition of the function or source table. Regardless of the Collation shown in sys.columns, a Collation that is either explicitly set using the COLLATE keyword in the SELECT statement in the function, or specified for a column when selecting from a table, is what will be used. In this case, the behavior of the function will be inconsistent with its meta-data, but the output should be correct.

   In both cases, executing sys.sp_refreshsqlmodule for the TVF will correct the meta-data shown in sys.columns. There will be no behavioral change for Inline TVFs. But, for Multi-Statement TVFs, because the output was based on the meta-data, the behavior will change to be consistent with the function definition.

9. Computed Columns that set the output Collation by using the COLLATE keyword, whether PERSISTED or not, will have their meta-data updated in sys.columns, but the actual Collation used will be whatever was specified in the definition. This is very similar to how Inline TVFs behave, except:
   • Selecting from the Table will display the following warning in the “Messages” tab:

     Metadata stored on disk for computed column '{column_name}' in table '{schema_name}.{column_name}' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column.

     This warning will appear when the query referencing the table is not in the plan cache. Once the query is cached, the warning will not be displayed again, for that query, until the plan is evicted from the cache, or DBCC FREEPROCCACHE is executed, etc.

   • There is no “refresh” system stored procedure that will fix the meta-data. Executing sys.sp_recompile on the table does not help. Correcting the meta-data requires dropping and recreating the Computed Column.
10. Contained Databases (i.e. “Partially Contained Database”, CONTAINMENT = PARTIAL) are handled correctly:
    • Instance-level meta-data is changed as expected
    • Database-level meta-data (name column in system catalog views) is still Latin1_General_100_CI_AS_KS_WS_SC (i.e. CATALOG_DEFAULT)
    • System lookup values (_desc columns in system catalog views) are still Latin1_General_CI_AS_KS_WS (coming from the hidden mssqlsystemresource DB)
    • User data is changed (this includes: filter_definition in sys.indexes ; collation_name in sys.columns ; collation_name and definition in sys.computed_columns ; definition in sys.sql_modules ; clr_name in sys.assemblies ; etc)
11. Instance-level default Collation will not change until all DBs have successfully converted.
12. This operation will make no changes at all if the current Instance-level Collation is the same as the new Collation being requested by this operation. This means that if the process converts some Databases but then fails while converting another Database (hence not completing and not changing the Instance-level Collation), then you cannot “revert” the operation by going back to the original Collation. If the Collation you request using the -q switch is the same as the Instance-level Collation, then running this command-line will simply start the Instance in Single-User mode instead of making any Collation changes. However, you will still be able to make changes to the Database that the error occurred in and repeat the operation for the new Collation.
13. While both documented and undocumented methods use the Transaction Log for the dropping and recreating of Indexes, this method sometimes uses less Tran Log space due to not doing Code Page conversions of VARCHAR / CHAR / TEXT columns. Of course, if the old and new Collations use the same Code Page, then the documented method won’t be doing Code Page conversions anyway.
14. Unlike the documented “SETUP.EXE /ACTION=REBUILDDATABASE” method, this method does not drop and recreate the system Databases (requiring a bit of extra work to get the instance back to its original state minus the change in Collation). This could save a good bit of work in terms of restoring Databases, recreating Instance-level objects, SQL Server Agent configuration and jobs, re-applying patches, etc.

15. For memory-optimized tables containing string columns, there are a few issues that might come up:

    1. The existence of any auto-created statistics (i.e. names starting with _WA_Sys_*) on string columns will most likely cause an error that will stop the Collation update process. Please see next section on “Errors you Might Encounter” (below) for details.
    2. For any characters with values of 128 or above, it is quite likely that those same bytes will refer to different, or possibly even invalid, characters. This is pretty much the same data loss issue as noted above in item #6. For example, if the original Collation is Korean_100_CI_AS, then the byte sequence of “0xB15A” will produce “켣”. If you then use this method to switch to any “_UTF8” Collation, you will end up seeing “�Z” because “0xB1” is an invalid byte sequence in UTF-8, and “0x5A” is valid and is the “Z” character.
    3. What is different with memory-optimized tables (regarding data loss) is that even when there is no data loss, indexes on string columns can easily produce non-obvious, “delayed-reaction” errors (meaning: they do not cause the Collation-update process to fail, and you might not notice them immediately). For memory-optimized tables that persist their data and have data to persist, the Collation update process does not drop and recreate their indexes, which leads to two issues:
       1. The indexes could, quite likely, be out of order. For example (using a VARCHAR column):
          

          Column Collation	ORDER BY	Data	Notes
          SQL_EBCDIC297_ CP1_CS_AS	column	ab ➞ aA ➞ ba ➞ Aa ➞ Ab	Initial order
          French_CI_AS	column	ab ➞ aA ➞ ba ➞ Aa ➞ Ab	Same order
          French_CI_AS	column COLLATE French_CI_AS	ab ➞ aA ➞ ba ➞ Aa ➞ Ab	Same order
          French_CI_AS	column COLLATE French_100_CI_AS	aA ➞ Aa ➞ ab ➞ Ab ➞ ba	Correct order

          As you can see, after the Collation update, ordering by the column (no COLLATE clause) uses the original sort order because the index hasn’t been rebuilt for the new Collation. And even if you specify the COLLATE clause with the new Collation in an attempt to force the intended new sort order, it will still return in the original order because specifying the Collation that is defined for the column has no effect at all. But, specifying a Collation that is neither the original Collation nor the new Collation, will return in the order for that Collation because SQL Server knows that it doesn’t have the rows sorted by the new ordering rules, so it applies the specified Collation.

          Also, you aren’t allowed to manually REBUILD or REORGANIZE these either.

          ALTER INDEX [IX_ShoppingCart_Name2i] ON dbo.ShoppingCart REBUILD; -- REORGANIZE

          /*

          Msg 10794, Level 16, State 12, Line XXXXX

          The operation 'ALTER INDEX' is not supported with memory optimized tables.

          */

          ALTER TABLE dbo.ShoppingCart REBUILD;

          /*

          Msg 10794, Level 16, State 134, Line XXXXX

          The operation 'ALTER TABLE REBUILD' is not supported with memory optimized tables.

          */

          DROP INDEX [IX_ShoppingCart_Name2i] ON dbo.ShoppingCart;

          /*

          Msg 10794, Level 16, State 114, Line XXXXX

          The operation 'DROP INDEX' is not supported with memory optimized tables.

          */

          In these cases you will need to, at the very least, delete and re-insert the data, or drop and recreate the table(s) and then re-insert the data.
       2. Queries appear to execute correctly, until you add more data. Then, when you use WHERE or ORDER BY, you will likely get one of the following errors:

          Msg 9100, Level 21, State 2, Line XXXX

          Possible index corruption detected. Run DBCC CHECKDB.

          (disconnected!)

          Msg 701, Level 17, State 153, Line XXXXX

          There is insufficient system memory in resource pool 'default' to run this query.

          These errors will disappear once the Instance is resarted (there might be a more elegant way of getting the system to rebuild memory-optimized indexes, but I am not aware of any at the moment). The errors might come back if more rows are added to these tables.

PLEASE NOTE

To avoid unnecessary time, disk I/O, tran log space, etc spent (i.e. wasted) on DBs that are already set to the desired Collation (in terms of both Database-level and all columns that are desired to be using the new Collation), or to skip updating one or more Databases for any reason: detach the Database(s) before shutting the Instance down. Then, after the update, re-attach the Database(s). Unfortunately, you cannot skip a Database by setting it to OFFLINE since it will be treated as being “read-only”, which causes an error before any Database is converted (though the error message never says which Database(s) are causing the problem).

Errors You Might Encounter

1. Due to read-only DB (documented in link #4 at the bottom, and can be caused by a Database being OFFLINE):

   Error: 5804, Severity: 16, State: 1

   Character set, sort order, or collation cannot be changed at the server level because at least one database is not writable.

   Make the database writable, and retry the operation.

2. Due to files being read-only (documented in link #4 at the bottom, and can be caused by a Database being OFFLINE):

   Error: 3416, Severity: 20, State: 1

   The server contains read-only files that must be made writable before the server can be recollated.

3. Due to In-Memory DB (documented in link #4 at the bottom):

   Error: 41317, Severity: 16, State: 4

   A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

   (please see “Fix…” immediately below this list)

4. Due to In-Memory DB (documented in link #4 at the bottom) and Unique Constraint Violation:

   Error: 3434, Severity: 20, State: 1

   Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL

   Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.

   (please see “Fix…” immediately below this list)

5. Due to Unique Constraint Violation:

   Error: 1505, Severity: 16, State: 1.

   The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.UniqueIndexViolation’ and the index name ‘CUIX_UniqueIndexViolation’. The duplicate key value is (a ).

6. Due to specifying an invalid Collation for the -q option (including Collations not available in the version of SQL Server being updated; e.g. trying to use a version “100” Collation with SQL Server 2005):
   • SQL Server 2005, 2008, and 2008 R2 error (specifying -q"Latin1_General_200_CI_AS"):

     2018-08-02 14:24:00.78 Server Error: 17112, Severity: 16, State: 1.

     2018-08-02 14:24:00.78 Server An invalid startup option q was supplied, either from the registry or the command prompt. Correct or remove the option.

   • SQL Server 2017 error (specifying -q"Latin1_General_200_CI_AS"):

     An invalid startup option ‘qLatin1_General_200_CI_AS’ was supplied, either from the registry or the command prompt. Correct or remove the option.

     2018-08-02 14:07:30.11 Server SQL Server shutdown has been initiated

Fix for In-Memory OLTP Errors

Errors related to memory-optimized tables are due to non-indexed columns (specifically, columns that are not the leading column in any index) that have auto-created statistics on them. All you need to do is remove these auto-created statistics by using the following query:

DECLARE @DropStats NVARCHAR(MAX) = N'';
SELECT 
       @DropStats = @DropStats +
       N'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(st.[object_id]))
       + N'.' + QUOTENAME(OBJECT_NAME(st.[object_id]))
       + N'.' + QUOTENAME(st.[name]) + N';' + NCHAR(0x000D) + NCHAR(0x000A)
FROM   sys.stats st
INNER JOIN sys.objects so
        ON so.[object_id] = st.[object_id]
WHERE  st.[auto_created] = 1
AND    so.[is_ms_shipped] = 0
PRINT @DropStats; -- DEBUG
EXEC (@DropStats);

NOTE: This query only fixes the problem that prevents the Collation update process from completing. It does not fix any instances of data loss or potentially corrupted indexes. To fix those problems you still might need to drop/recreate any affected tables or maybe just the data (in any affected tables).

Posts Dealing With the Undocumented sqlservr.exe -q Option

1. Changing Server Collation ( 2011-05-26 )
2. Changing SQL Server Collation After Installation ( 2015-02-19 )
3. SQL Server – Changing Sql Instance Collation – via sqlservr/-q ( 2016-11-04 ; based on post linked directly above)
4. SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps ( 2017-01-08 ; follow-up to post linked directly above)

Misc.

SQL Server Express LocalDB

Changing the Instance-level Collation for LocalDB is not possible. It will always be SQL_Latin1_General_CP1_CI_AS (quite unfortunately!):

1. SETUP.EXE for LocalDB will not be able to find the LocalDB instance. Using something like (LocalDB)v12.0 for the Instance name will result in the following error:

   Instance Name (LOCALDB)V12.0 for product Microsoft SQL Server doesn’t exist. Specify a valid instance name.

   Error result: -2068578302

   Result facility code: 1204

   Result error code: 2

   Nor does using “LocalDB” or “v12.0” by themselves work. And yes, I do have a v12.0 Instance of LocalDB.

2. Attempting to use the sqlservr.exe -q option results in one of the following errors, depending on how you specify the Instance name:
   • Using just the instance name:

     Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem

   • Using (LocalDB)InstanceName:

     Your SQL Server installation is either corrupt or has been tampered with (Error: Instance name exceeds maximum length.). Please uninstall then re-run setup to correct this problem

There is a UserVoice suggestion, however, to allow it to be user-settable: Allow collation to be set for LocalDB at Instance level when creating an instance.

Compatibility Collations

You probably have never heard of “compatibility” Collations, and that is fine (full post on them coming in early 2019). They only show up when migrating from pre-SQL Server 2000. Most likely nobody outside of myself would ever try this, and I’m only doing it for research / documentation. The “sqlserver -q” method allows you to use a compatibility collation at the Instance-level. Attempting to do this using the documented method results in the following error:

The collation Compatibility_52_409_30002 was not found.

Error result: -2061893630

Result facility code: 1306

Result error code: 2

In-Memory OLTP and UTF-8 Collations (new in SQL Server 2019)

Even though In-Memory OLTP was mentioned in item # 15 in the “Undocumented Approach” section above, this is a special case.

The new UTF-8 Collations, introduced in SQL Server 2019, are not supported for using in memory-optimized tables. Attempting to CREATE or ALTER tables to use UTF-8 Collations will result in one of the following errors:

Msg 12356, Level 16, State 157, Line XXXXX

Comparison, sorting, and manipulation of character strings that use a UTF8 collation is not supported with memory optimized tables.

or:

Msg 12357, Level 16, State 158, Line XXXXX

Indexes on character columns that use a UTF8 collation are not supported with indexes on memory optimized tables.

Using the “sqlservr -q” approach will by-pass those restrictions and update memory-optimized tables to use UTF-8 Collations (assuming you specified a UTF-8 Collation). For memory-optimized tables that either have no data or are only persisting the schema (i.e. DURABILITY = SCHEMA_ONLY), this might work. I have not done extensive testing. But, even if testing does seem to indicate that it works as expected, this is still an unsupported configuration (so don’t expect any help from Microsoft if there is odd / incorrect behavior with a UTF-8 Collation in a memory-optimized table).

Testing

I tested on SQL Server 2017 CU6 and SQL Server 2019 CTP 2.1, but the behavior should be consistent across versions.

General

{ When I have time I will post the test cases }

LocalDB

SETUP

sqllocaldb c TestChange
CD C:Program FilesMicrosoft SQL Server140LocalDBBinn

TEST 1

.sqlservr -c -m -T4022 -T3659 -s"TestChange" -q"Hebrew_100_CI_AS"
REM Your SQL Server installation is either corrupt or has been tampered
REM with (Error getting instance ID from name.).  Please uninstall then
REM re-run setup to correct this problem

TEST 2

.sqlservr -c -m -T4022 -T3659 -s"(LocalDB)TestChange" -q"Hebrew_100_CI_AS"
REM Your SQL Server installation is either corrupt or has been tampered
REM with (Error: Instance name exceeds maximum length.).  Please
REM uninstall then re-run setup to correct this problem

TEST 3

CD C:Program FilesMicrosoft SQL Server120Setup BootstrapSQLServer2014
.SETUP.EXE /ACTION=REBUILDDATABASE /INSTANCENAME="(LocalDB)v12.0" ^
/QUIET /SQLCOLLATION=Latin1_General_100_CI_AS
REM Instance Name (LOCALDB)V12.0 for product Microsoft SQL Server
REM doesn't exist. Specify a valid instance name.
REM
REM Error result: -2068578302
REM Result facility code: 1204
REM Result error code: 2

Compatiblity Collations

TEST 1

SETUP.EXE /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL2019 ^
/SQLCOLLATION=Compatibility_52_409_30002
REM The collation Compatibility_52_409_30002 was not found.
REM
REM Error result: -2061893630
REM Result facility code: 1306
REM Result error code: 2

TEST 2

.sqlservr -c -m -T4022 -T3659 -s"{InstanceName}" ^
-q"Compatibility_55_409_30002"
REM Success!!

In-Memory OLTP and UTF-8 Collations

SETUP

CREATE DATABASE [Hekaton] COLLATE Japanese_XJIS_140_CI_AS_VSS;
ALTER DATABASE [Hekaton] SET RECOVERY SIMPLE;
ALTER DATABASE [Hekaton]
   ADD FILEGROUP [InMemoryStuff]
   CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [Hekaton]
   ADD FILE (name = N'InMemoryStuff', filename =
   'C:Program FilesMicrosoft SQL ServerMSSQL15.SQL2019MSSQLDATAInMemoryStuff.idf')
   TO FILEGROUP [InMemoryStuff];
USE [Hekaton];

TEST 1

Specify a UTF-8 Collation when creating the table (no index specified for the column attempting the UTF-8 Collation):

CREATE TABLE dbo.UTF8
(
    [ID] INT IDENTITY(1, 1)
        NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400),
    [Name] VARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC_UTF8
        NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
/*
Msg 12356, Level 16, State 157, Line XXXXX
Comparison, sorting, and manipulation of character strings that use
   a UTF8 collation is not supported with memory optimized tables.
*/

TEST 2

Create the table with a non-UTF-8 Collation, then attempt to change the Collation via ALTER TABLE:

CREATE TABLE dbo.UTF8
(
    [ID] INT IDENTITY(1, 1)
        NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400),
    [Name] VARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC -- NOT UTF8
        NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
-- Success
ALTER TABLE dbo.[UTF8]
    ALTER COLUMN [Name]
    VARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL;
/*
Msg 12356, Level 16, State 157, Line XXXXX
Comparison, sorting, and manipulation of character strings that use
   a UTF8 collation is not supported with memory optimized tables.
*/

However, changing the Collation to a non-UTF-8 Collation does work.

TEST 3

Specify a UTF-8 Collation when creating the table (include an index for the column attempting the UTF-8 Collation):

CREATE TABLE dbo.UTF8i
(
    [ID] INT IDENTITY(1, 1)
        NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400),
    [Name] VARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC_UTF8
        NOT NULL INDEX [IX_UTF8i_Name] NONCLUSTERED
)
WITH (MEMORY_OPTIMIZED = ON);
/*
Msg 12357, Level 16, State 158, Line XXXXX
Indexes on character columns that use a UTF8 collation are not
   supported with indexes on memory optimized tables.
*/

More…

For more info on Collations / encodings / Unicode / Extended ASCII (especially as they relate to Microsoft SQL Server), please visit:

Collations.Info