Category: Errors and Exceptions

DataTypes, Errors and Exceptions

Curious case of varchar to uniqueidentifier in SQL Server

Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).

There are two types of conversions – implicit and explicit.

Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.

Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.

SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:

Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier
Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98'

Set @uniqueidentifier = @varchar
Select @uniqueidentifier

Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.

The word of caution

Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.

Errors and Exceptions

“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:

Error Message:
“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Reason:

The error message is self explanatory one as this database does not meet a requirement to add AG as this database is protected with a master key. We may need to provide the master key while configuring the database into AG.Please note that this is not due to TDE, but due to master key.

One observation is that this is recently added as a validation in SQL Server 2016 version. Earlier version, you would be able to add seamlessly.

Solutions:

There are two solutions to add these databases into AG:

1. Use T-SQL to add the database into AG


ALTER AVAILABILITY GROUP AGName ADD DATABASE DBName

2. Provide the password and use GUI with few extra steps

In GUI, this is not so clear for the user that the password can be provided directly and configure AG.

Double click in the Password column
Type the password in the column
Click Refresh (To enable check box for the database)
Check the checkbox for the database.
Click Refresh again (To enable the Next button)
Click Next to progress

Errors and Exceptions

Length of LOB data to be replicated exceeds configured maximum 65536 in SQL Server

Problem Statement:

Sometimes When we do a DML operation in SQL Server, we end up with the below error –

“Length of LOB data to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type. The statement has been terminated.”

From the error message, its clear that the operation has been terminated by SQL Server.

Analysis:

I would say, this is a clear error message that means it contains the error information, steps to resolve the exception and its implication. Let us expand the error message a bit more than what its provided for our clear understanding with this blog post.

Details:
Length of LOB data to be replicated exceeds configured maximum 65536.

This means that the length of a LOB data is exceeding the configured value of 65536 which defaults to 65536. There is a configuration “max text repl size option” in SQL Server which can be viewed by sp_configure system procedure or querying sys.configurations system table. By default, the value of the configuration has been set to 65536 in SQL Server.This has an impact on systems configured with replication, Change Data Capture. The configuration “max text repl size (B)” is not an advanced configuration, so you do not need to reconfigure with “show advanced options” to see this setting.

From the above picture, the description of max text repl size (B) configuration is clear that it denotes the maximum size of a text field in replication. However, we need to understand the value is also applicable for LOB datatypes as well. The issue can be raised whenever a LOB data that contains more length than the specified value for this configuration. The operation gets terminated for the same reason.

What do we do when we get this error?

The simple solution is to set the configuration “max text repl size (B)” to “-1”. This will ensure there is no limit other than imposed by the datatype of the column. However, this may have some detrimental impact on replication as the size increases we need to make sure the replication has no other side effects due to the size of the data to be replicated over the network etc.

1. Identify the LOB datatype column in the table
2. Assess the size or length of the data in the table and confirm that the size of the data is exceeding the configured value of “max text repl size (B)”
3. Identify if there are any replication/CDC configured with the table/database
4. If Change data capture does not require the LOB column to be tracked, avoid the column from capture by specifying ONLY required columns in captured_column_list for the table while configuring CDC for the table
5. Assess your data and conclude the maximum size of data it can reached up to as per the business requirement. As an example, a photo field can be restricted with a specified size through application that can be uploaded etc.
6. Set the required or expected size for max text repl size (B) configuration if possible instead of maximum -1

Note: We recently observed similar issue with a database configured AlwaysOn-Availability Group.

Change Data Capture, Errors and Exceptions

Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

While configuring Change Data Capture on a database that is restored from different environment, we might end up with an error message as below:

Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

To resolve the issue, we need to change the owner of database and then enable CDC for the database as below. Please note that, the database would have restored irrespective of the error. You just have to run the below statement to avoid similar issues in future.

EXEC sp_changedbowner 'sa'
EXEC sys.sp_cdc_enable_db ;

Ref: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql?view=sql-server-2017

DBA Utilities, Errors and Exceptions, SCRIPT GALLERY

Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

Solution:
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

/*
	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'xp_cmdshell',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'show advanced options',0
	RECONFIGURE WITH OVERRIDE
*/

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null
DBA Utilities, Errors and Exceptions

SQL Server Configuration Manager – The remote procedure call failed. [0x800706be]

Problem Statement:

Recently, I encountered an issue as below with SQL Server Configuration Manager which is a very common issue for most of us. Most of the time, this type of error is related to not having enough permission to see the SQL Server Configuration Manager. However, the case was different for me as I am the administrator for my system.

Observations:

In my laptop, I have multiple SQL versions like 2008 R2, 2014, 2016 and 2017.For some reason, SQL Server Configuration Manager (by default it was pointing to oldest one, nothing but 2008 R2 version) was not able to correctly get the information for all the installed versions. This was causing an issue.

To resolve the issue,

1. we can identify the file location as “C:\windows\syswow64\” OR “C:\Windows\System32\”.
The SQL Server Configuration manager file name should be similar to SQLServerManager**.msc where ** denotes the version of the SQL Server.

2. Open the latest version of SQL Server configuration manager file. This will work as expected.

However, the above is not a complete solution while we open the configuration manager from the start window. This is because, the default configuration manager will still be pointing to the older version of the SQL Server. To change this behavior, we need to do the following steps.

1. Open the “SQL Server Configuration” in start and right click on the icon.


2. Right Click on “SQL Server Configuration Manager” and change the Target file to the latest mmc file.(You should have administrator privilege to do so)


3. Close the Window

From then, you will be successfully able to see the SQL Server Configuration manager that fetch the data as expected.