Tag Archives: Development

Articles on Microsoft SQL Server development

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

Leave a reply

Recently, I was reviewing a T-SQL script and saw a ALTER DATABASE statement with the CURRENT keyword. It seems redundant at first glance. After all, if you are already connected to a database, why would you want to refer to it as CURRENT?

In this post, I try to uncover the purpose, possible use cases and potential mistakes that one can make when using the CURRENT keyword.

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:

  1. Automated Scripts: Avoid hard-coding database names when deploying changes across multiple databases.
  2. Cross-Environment Compatibility: Whether you’re running the script on dev, test, or prod, CURRENT targets the database that you are connected to.
  3. 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:

  1. 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.
  2. Permissions: CURRENT doesn’t bypass permission checks and the scripts would fail if your user is not allowed to ALTER the database
  3. 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.
  4. Mixing with Dynamic SQL: Using CURRENT with dynamic SQL is unnecessary and can be confusing or redundant (unless it’s a code generator)
  5. 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.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

#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.

#0424 – SQL Server – Null value is eliminated by an aggregate or other SET operation. – Why? How to fix?

Leave a reply

I recently ran into a forum post where the poster wanted to know why they were getting the following warning during query execution:

"Null value is eliminated by an aggregate or other SET operation."

Having been asked this question a few times by a few of my office colleagues as well, I thought to write up a quick post on the reason behind this warning.

A quick test

The test below is simple – I am creating a sample test table which allows NULL values to be inserted. I am then trying to perform a simple aggregate function (SUM) over the NULL-able column. Upon checking the “Messages” tab, we see that no warning is returned.

USE [tempdb];
GO
SET NOCOUNT ON;

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

--Create the test table
CREATE TABLE [dbo].[NULLAggregation] 
    ([Id]          INT           NOT NULL IDENTITY (1,1),
     [Value]       INT               NULL,
     [ValueString] VARCHAR(50)   NOT NULL,
     CONSTRAINT [pk_NULLAggregation] PRIMARY KEY ([Id])
    );
GO

--Insert the test data
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( 1, 'One'),
       (10, 'Ten'),
       (22, 'Twenty-Two');
GO

--Perform the aggregation
--NOTE: No NULL values are in the table at this point
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
****************************************/

Now, I add a single record with a NULL value in the [Value] column and repeat the aggregation. While the result is the same, we have a warning in the “Messages” tab.

INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( NULL, 'One');
GO

SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
Warning: Null value is eliminated by an aggregate or other SET operation.
****************************************/
A screenshot showing the warning encountered in SSMS when an aggregation operation is performed on a NULL value.
Warning encountered when aggregating on NULL values

The reason for the warning

To begin – this is just a warning and not an error. If your script/job is failing it is probably failing due to some other data condition OR as an indirect result of operating on NULL values.

The warning simply suggests that an aggregation operation is being done on a NULL value. If no NULL values are present in the dataset being evaluated, then the warning is not encountered.

Solutions

The important thing is to consider what is important for the business/domain.

  • If processing on NULL values are okay for the business/domain, then one of the following two (2) workaround can be applied:
    • The warning can either be ignored OR
    • Use the “SET ANSI_WARNINGS OFF” option for your query/procedure
  • If processing on NULL values is not acceptable for your business/domain, then
    • Either use a simple WHERE clause to remove the records with NULL values from the aggregation
    • Use input validations in the application code and NOT NULL checks in the database to stop the NULL values from being entered by the users

NOTE: If you do decide to use the “SET ANSI_WARNINGS OFF” option, please do so with caution. It can have unintended consequences with string operations as well (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15)

I trust you found this post useful.

Until we meet next time,

Be courteous. Drive responsibly.

#0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code

Leave a reply

One of the main reasons I value the interactions with the SQL Server community on various forums is because it often inspires me to explore alternate paths to doing the same task.

With SSIS, there are multiple patterns to achieve a particular outcome – some more prevalent than others. A task that we need to do often in SSIS is to perform file manipulations (e.g. rename or delete files after a data ingest is complete, etc). This is typically achieved by using a script task – but using a script task involves writing code. Therefore, a question that came up was:

Can we perform file operations (move, rename, delete or any other operations) without writing a script or a line of code?

The answer is that this is certainly do-able. In fact, some of my previous posts have used the same mechanism that I am proposing today.

The Solution

Assume that I have a set of files in a folder (following the pattern – SQLTwins*.txt) and I wanted to delete them. The Control Flow is quite procedural:

  • A ForEach Iterator is used to iterate through files in the directory
    • The iterator is completely configurable – allowing the user to specify the folder name and the file name pattern
  • A FileSystem task is used to perform the actual file operation
Control Flow of a package that manipulates files without a script or code!

Now, allow me to walk-you through the package configuration:

Variables

The package is dependent upon the following variables:

Variable NameDataTypeExpression / Default ValueRemarks
SourceFolderString(My source folder path)
FileNamePatternStringSQLTwins*.txt
CurrentFileStringVariable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileNameStringSourceFolder + CurrentFileFully-Qualified file name to be used by the FileSystem task
List of User Variables on the SSIS pacakge

ForEach Iterator

The configuration of the Foreach Iterator is quite simple:

  • Collection
    • The “Descriptions” and “FileSpec” expressions are set with the user variables – “SourceFolder” and “FileNamePattern” respectively
  • Variable Mappings
    • This allows the package to capture the output of the iterator
    • The variable “CurrentFile” will be used to capture the current file name
“Collection” tab of the ForEach Iterator showing “Descriptions” and “FileSpec” expressions set with the user variables – “SourceFolder” and “FileNamePattern” respectively
Variable Mappings showing the output of the ForEach Iterator setting the “CurrentFile” variable

File System Task

The configuration of the FileSystem task is even simpler! Other than the Name, the only configuration I did was to set the “Operation” and the “SourceVariable” variables.

Screengrab showing the configuration of the File System task

That’s it! We are all set to give the package a spin and did not write a single line of code!

When we run the package, we can see right away that the files have been deleted.

Prior to execution, we can see that the files are still present.
Once the package is executed, the files are deleted!

The intention of the post was to demonstrate that with Microsoft SQL Server and related services, there are tools and components available which allow one to get started extremely quickly. If you have never worked with SSIS before, do explore the components available in the SSIS toolbox before getting into some serious scripting!

Further Reading

  • Adding date and time to a file name after processing [Blog Link]
  • Moving and Renaming a File [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

#0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory

11 Replies

I recently answered a question on a forum which I believe will be useful to many of the readers in the audience.

SSIS packages are widely used for data import from and export to files. One of the main tasks in this situation would be to check if files with certain kinds of names exist in a particular folder or directory, i.e. basically perform a wild-card search in a directory.

The solution

This can be achieved by using the EnumerateFiles() method of the System.IO.Directory class in the SSIS Script task. Here’s the sample package:

In a folder, I have a set of files, some with similar names (which we will search from the SSIS package).

Files existing in the directory to be searched

The SSIS package has two (2) variables:

Variable NameConfiguration on the Script TaskDescription
SearchPathReadOnly, InputThis is the path to be searched
SearchPatternReadOnly, InputPattern to be searched
FileExistsReadWrite, OutputA boolean indicating downstream processes whether files were found or not
Table 1: Variables on the SSIS package
Screenshot showing the variables and their configuration on the script task

The script is a quite simple implementation as below:

public void Main()
{
    // SQLTwins: SSIS: Blog #0421

    string searchPath = Dts.Variables["User::SearchPath"].Value.ToString();
    string searchPattern = Dts.Variables["User::SearchPattern"].Value.ToString();

    System.Collections.Generic.List<string> searchResults = System.IO.Directory.EnumerateFileSystemEntries(searchPath, searchPattern).ToList();

    if (searchResults.Any())
    {
        Dts.Variables["User::FileExists"].Value = true;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

Ensure that you have the following directive in the “namespaces” section of your script:

using System.Linq;

Here’s the script in action:

Screenshot showing the script in debug mode indicating that files were found matching the pattern.

As you can see, the script can help perform a wild-card search in a given folder or directory.

Further Reading:

  • File System errors when trying to move and rename a file [Blog Link]
  • Adding Date & Time to file names after processing [Blog Link]
  • VSTA Errors when working with SSIS packages [Blog Link]
  • System.IO.Directory.EnumerateFiles [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.