Author Archives: nakulvachhrajani

About nakulvachhrajani

Nakul Vachhrajani is a SQL Server MCTS, TOGAF certified Technical Architect with Capgemini having a total IT experience of more than 16 years. Nakul is an active blogger, and has been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students. Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

#0429 – SQL Server – Working with JSON using WITHOUT_ARRAY_WRAPPER

1 Reply

When working on system integrations, JSON is often the format of choice used to exchange data. As with any data contract, the clarity and structure of the schema matter. Default behaviors (like wrapping JSON into arrays) can complicate things when you’re expecting a single object. This is especially true when processing feeds or doing asynchronous operations.

I dabble a lot in data operations and often use SQL Server queries to build my JSON payloads. As we know, we can use the FOR JSON PATH clause to generate the output as a JSON.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] BETWEEN 1600 AND 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	},
	{
		"FirstName": "David",
		"LastName": "Robinett",
		"PhoneNumber": "238-555-0100",
		"City": "Solingen"
	}
]

If you observe carefully, the output is a JSON array. This is true even if one (1) object is being returned – which then requires me to edit the payload manually. Here’s the example:

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	}
]

Introducing WITHOUT_ARRAY_WRAPPER

Starting SQL Server 2016, a new option (WITHOUT_ARRAY_WRAPPER) is now available. This option can be used with a single-row result to generate a single JSON object as output instead of an array with a single element.

Here’s the same example as above, but with the WITHOUT_ARRAY_WRAPPER clause applied.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
GO

{
	"FirstName": "Rebecca",
	"LastName": "Robinson",
	"PhoneNumber": "648-555-0100",
	"City": "Seaford"
}

Practical use-cases

Below are some practical use-cases where the WITHOUT_ARRAY_WRAPPER option has been very handy for me:

Creating Samples – for API requests, responses and writing documentation!
Nesting/embedding JSON in another JSON
Logging or auditing single events

Word of caution

If you use this option with a multiple-row result, no error will be returned. However, the resulting output is not valid JSON because of the multiple elements and the missing square brackets.

Call to Action

I trust you found this quick nugget helpful. Do let me know if you already knew about it before. I would be interested to read about the various cases in which you have used the FOR JSON PATH, WITHOUT_ARRARY_WRAPPER option.

Until we meet next time,

Be courteous. Drive responsibly.

Disclaimer: The [AdventureWorks2022] sample database (https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver17&tabs=ssms) has been used for all test data used in this post.

#0428 – SQL Server – Demystifying the CURRENT keyword: Why it exists and when to use it

The Purpose

First, let’s understand the purpose of the CURRENT statement. Put very simply, the CURRENT keyword in the ALTER DATABASE statement refers to the database that you are connected to. Instead of hard-coding the database name, you can use CURRENT to make your script more portable and dynamic.

-- Instead of this:
ALTER DATABASE [SQLTwins] SET COMPATIBILITY_LEVEL = 150;

-- You can write:
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

This is especially useful in deployment pipelines, maintenance scripts, or reusable procedures where the database context may vary.

Typical Use-cases

Here are some practical scenarios where CURRENT keyword is very useful:

Automated Scripts: Avoid hard-coding database names when deploying changes across multiple databases.
Cross-Environment Compatibility: Whether you’re running the script on dev, test, or prod, CURRENT targets the database that you are connected to.
Contained Databases: In Azure SQL Database or Managed Instances, where scripts run within a specific database context, CURRENT ensures the command targets the right scope.

CURRENT v/s DB_NAME()

You might wonder: why not dynamically fetch the database name using the very familiar DB_NAME() and build the statement?

DECLARE @dbName NVARCHAR(128) = DB_NAME();
EXEC('ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = 150');

While DB_NAME() works, it’s great for dynamic SQL. Dynamic SQL is often complicated to manage (esp. in deployment pipelines) and introduces potential quoting issues. CURRENT is cleaner, safer, and easier to read – more so for simple configuration changes.

Please consider

Before you start using CURRENT in your scripts, be on the lookout for the following common mistakes:

Context Awareness: If your script runs in a multi-database environment, double-check that you’re in the right context before executing. Remember: CURRENT refers to the database you’re connected to, not necessarily the one you intend to modify.
Permissions: CURRENT doesn’t bypass permission checks and the scripts would fail if your user is not allowed to ALTER the database
Forgetting Test Coverage: Because CURRENT abstracts the database name, it’s easy to overlook edge cases in testing—especially when deploying across environments. Always validate that your script behaves as expected in all environments & configurations that you intend to use.
Mixing with Dynamic SQL: Using CURRENT with dynamic SQL is unnecessary and can be confusing or redundant (unless it’s a code generator)
The CURRENT keyword is fully supported for Azure SQL Database and Azure SQL Managed Instances. However, for on-premise installations SQL Server 2012 (11.x) and above (Why are you on a lower version anyway?)

In conclusion

The CURRENT keyword may seem trivial. However, it plays a subtle role in making T-SQL scripts more portable. It also contributes to scripts being context-aware and cloud-friendly. Whether you’re managing databases in Azure or on-prem, using ALTER DATABASE CURRENT can streamline your operations. It also reduces hard-coded dependencies.

#0427 – SQL Server – Msg 8152, Level 16: Which column is causing “String or binary data would be truncated.”?

Leave a reply

As a database administrator or developer, I’m quite sure that you’ve encountered the following error when working with your database queries in SQL Server.

Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.

The statement has been terminated.

As is quite obvious by the text, this error occurs when you try to insert or update data that exceeds the maximum length of a column. While this error is common and quite straightforward to understand, it can be frustrating to troubleshoot – especially when you’re not sure which column is causing the issue (e.g. in the case of integrations or data imports).

In newer versions of SQL Server (2019 and above), the error message has become much more descriptive and I have found it very helpful to quickly identify and resolve the problem.

Let’s check it out with a code example. I am creating a test database and then changing the compatibility level of that database to 130 to simulate SQL 2016 equivalent behaviour. Then I will attempt to insert data (~42 characters) that exceeds the maximum length of the [SomeColumn1] column (25 characters).

SET ANSI_WARNINGS ON;
GO

USE [master];
GO
CREATE DATABASE [SqlTwinsDB];
GO
USE [master];
GO
ALTER DATABASE [SqlTwinsDB]
SET COMPATIBILITY_LEVEL = 130; --Simulating SQL 2016 on my SQL 2022 instance
GO


USE [SqlTwinsDB];
GO
--Safety Check
BEGIN
	IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
	BEGIN
		DROP TABLE [dbo].[stringLengthError];
	END
END

--Declarations
BEGIN
	CREATE TABLE [dbo].[stringLengthError]([Id]          INT         NULL,
	                                       [SomeColumn1] VARCHAR(25) NULL,
										   [SomeColumn2] VARCHAR(25) NULL
						    		      );
END

--Run the test
BEGIN
	INSERT INTO [dbo].[stringLengthError] ([Id],
	                                       [SomeColumn1],
										   [SomeColumn2]
										  )
	VALUES (1, 
	        REPLICATE('nav',14), --14*3 = 42 characters, can't fit into [SomeColumn1]
			REPLICATE('nav',5)   --14*3 = 42 characters,  will fit into [SomeColumn2]
		   );
END

/* Expectation: Get the following error:

Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.
The statement has been terminated.

Problem is: We don't know which column is generating the error, 
            unless we know the data and do some calculations.
*/

--Cleanup
BEGIN
	IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
	BEGIN
		DROP TABLE [dbo].[stringLengthError];
	END
END
GO

The error message we get is quite generic and doesn’t provide any information about which column is causing the issue. This would mean that we would have to dump the data into another temporary table/file and begin the tedious task of comparing the lengths and finding which field/column is being hit with the offending data.

Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.

The statement has been terminated.

Let us repeat this test with Compatibility Level set to 150 or higher (I will go with the default (160) for my version. Since the database is already created, I will simply change the compatibility level and try again.

SET ANSI_WARNINGS ON;
GO

USE [master];
GO
ALTER DATABASE [SqlTwinsDB]
SET COMPATIBILITY_LEVEL = 160;
GO

USE [SqlTwinsDB];
GO
--Safety Check
BEGIN
	IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
	BEGIN
		DROP TABLE [dbo].[stringLengthError];
	END
END

--Declarations
BEGIN
	CREATE TABLE [dbo].[stringLengthError]([Id]          INT         NULL,
	                                       [SomeColumn1] VARCHAR(25) NULL,
										   [SomeColumn2] VARCHAR(25) NULL
						    		      );
END

--Run the test
BEGIN
	INSERT INTO [dbo].[stringLengthError] ([Id],
	                                       [SomeColumn1],
										   [SomeColumn2]
										  )
	VALUES (1, 
	        REPLICATE('nav',14), --14*3 = 42 characters, can't fit into [SomeColumn1]
			REPLICATE('nav',5)   --14*3 = 42 characters,  will fit into [SomeColumn2]
		   );
END

--Cleanup
BEGIN
	IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
	BEGIN
		DROP TABLE [dbo].[stringLengthError];
	END
END
GO

This time, we still get the “String or binary data would be truncated” error, but with:

With a different message code (2628 instead of 8152)
A more verbose error which shows:
- The field that is causing the error
- The value that is being truncated

Msg 2628, Level 16, State 1, Line 31
String or binary data would be truncated in table 'SqlTwinsDB.dbo.stringLengthError', column 'SomeColumn1'. Truncated value: 'navnavnavnavnavnavnavnavn'.
The statement has been terminated.

Personally speaking, I find this to be a significant enhancement & a real time-saver when troubleshooting large data imports. Tip: If you’re working on an integration project and frequently encounter these types of errors, it may be worth considering upgrading your database engine to take advantage of this feature.

Sometimes, you may not even get this error and the truncation would happen silently. Do read my previous post https://nakulvachhrajani.com/2014/08/04/0341-sql-server-random-string-or-binary-data-would-be-truncated-errors-during-bulk-data-loads/ that talks about connection configuration parameters (more specifically, ANSI_WARNINGS) that may affect this behaviour.

Untill we meet next time,

Be courteous. Drive responsibly.

#0426 – Microsoft Teams – Quick fix for highly reduced/attenuated audio in Teams when sharing videos

#0425 – SQL Server – Backup exists but doesn’t display on the restore window in SSMS. Why? How to fix?

2 Replies

Recently, I ran into a forum post where the ask was to figure out why a perfectly valid backup was not visible when attempting to restore it via the wizard in SSMS. Today, I will reproduce the issue, explain the root cause and provide the solution for the same.

Building the scenario

In one of the my test SQL Servers, I have a copy of the [AdventureWorks2019] sample database, which I have backed up using the following simple script.

USE [master];
GO
BACKUP DATABASE [AdventureWorks2019]
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2K17\MSSQL\Backup\AdventureWorks2019.bak'
    WITH COMPRESSION;
GO

Now, to simulate the movement of the backup to a different machine, I created a new folder under my default “Documents” folder and placed the backup there.

Screenshot showing the placement of the target folder where the backup is placed

Reproducing the symptom

Restoring via SSMS

Connect to the target SQL Server using SSMS
Right-click on the “Databases” folder in the Object Explorer
Choose to Restore a database
Under “Source”, select the radio-option for restoring from a “Device”
Use the ellipsis to open the “Select Backup Devices” window and open the File explorer by choosing “Add”
Navigate to the folder where the backup has been placed
1. Expected Result: We should be able to see the folder and the backup file
2. Actual Result: The backup file is not seen (the folder may or may not be seen)

Screenshot showing that the backup exists, but it is not seen in the "Locate Backup File" window — Screenshot showing that the backup exists, but it is not seen in the “Locate Backup File” window

Restoring via T-SQL

While the UI keeps things a bit mysterious, attempting to restore via T-SQL does point us to the right direction.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2019_Copy]
    FROM DISK = 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak';
GO

Here’s the error that we run into:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Notice that the error clearly says – “Access is denied.“

Root Cause

As highlighted by the results of the T-SQL script, SQL Server is actually running into a security problem. The operation is done under the context of the SQL Server instance service user (i.e. the user under which the SQL Server service runs).

Because the user doesn’t have access to the folder we just created, the service cannot see the files underneath.

Solution

The solution is to use the SQL Server Configuration Manager to figure out the user under which the SQL Server service runs.

Once the user is identified, provide access to the target folder to the user and the files should now be visible – both to SSMS and to T-SQL.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.