SQL SERVER – Msg 8152, Level 16, State 14 – String or binary data would be truncated

Earlier this week, I have blogged about how to suppress Warning: Null value is eliminated by an aggregate or other SET operation SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation.

If you read that blog, I mentioned during closure that this setting might cause unexpected behavior if not used properly.  First, let’s understand the error which I am talking about:

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

I am sure that many developer might have seen this error at least once in their lifetime. This particular error message is raised by SQL Server when we try to insert long literal sting is longer than the defined table field datatype.  For example, if we try to insert a varchar with more than 100 characters into a varchar(50) field, we will get the following error. Here is an example script to reproduce the error:

USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO

I have put the same column name as the value which I am inserting so that you can easily see the cause of the error. Since I am inserting ‘Pinal’ in varchar(3) and ‘SQLAuthority’ in varchar (10), we are getting the error.

One of the problems with this message is that SQL Server doesn’t tell you which table or column has the problem. The correct way to fix the issue is to find the column causing error and correct the data or column length.

Coming back to set option, if we use ansi_warnings as OFF, the error would be suppressed and whatever can fit in the column, would be inserted, the rest would be truncated.

SET ansi_warnings OFF
GO
USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO
SELECT * FROM MyTable
GO

As we can see that the error was suppressed in the above code, but we lost part of the actual data which we were supposed to have on the table. So be cautious when working with such SET options.

Conclusion – Use ANSI_WARNING OFF with care, it might have effect on data insertion.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation

Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL.

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

In future blogs, I would try to explain another one called ANSI_WARNINGS. Let’s look at the various warnings which can be suppressed when working with NULL.

Warning: Null value is eliminated by an aggregate or other SET operation

Let us run below script in SQL Server Management Studio to see the effect.

SET NOCOUNT ON
GO
DECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULL
GO
SELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)
-- no warning
GO
SELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO
SELECT AVG(Qty) 'Avg' FROM @ItemSale WHERE ID IN (1,2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO

Here is the output

As the error says, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we can use “set ansi_warnings off” before the script. Here is the modified script.

SET ANSI_WARNINGS OFF
GO

Here is the output after adding the set option:

First of all we should remember that default value of the setting is OFF, which is actually good. In subsequent blogs, we would cover an error which can be suppressed using same set option. Tuning it ON can be dangerous as well. Stay tuned!

What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful? Let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the Execute Package Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Learning how to configure and use the SQL Server Integration Services (SSIS) Execute Package Task gives you a great starting point for understanding SSIS package orchestration. I advocate writing small, functional SSIS packages that perform a unit of work. That’s a great idea for many reasons. But it begs the question: “How do I string together the execution of these packages?” Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS Execute Package Task, shown in Figure 1:


Figure 1: SSIS Execute Package Task

The Execute Package Task provides one way to implement an SSIS Design Pattern for SSIS package execution known as the Parent-Child pattern. When an SSIS package uses the Execute Package Task to start another SSIS package, the package with the Execute Package Task is called the Parent and the package started by the Execute Package Task is called the Child.

The Execute Package Task changed between SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The changes support the SSIS 2012 (and 2014) Catalog. There is a new property called ReferenceType which defaults to “Project Reference” in packages executing in Project Deployment Mode (the default mode for building SSIS 2012 and SSIS 2014 SSIS packages), as shown in Figure 2:


Figure 2: Options for the Execute Package Task ReferenceType Property

Project Reference is used to execute an SSIS package – a Child package – in the same SSIS project with the package that contains the Execute Package Task – the Parent package. When Project Reference is selected, the next property in the property grid is PackageNameFromProjectReference, a dropdown containing a list of all the SSIS package in the SSIS project.

Setting the ReferenceType property to External Reference is a way to execute SSIS packages that are stored in the file system or the msdb database. When External Reference is selected the next properties in the property grid change to reflect this backwards-compatible functionality, as shown in Figure 3:


Figure 3: Setting the ReferenceType Property to External Reference

The Location and Connection properties are used to specify an OLE DB Connection (to the msdb database) or a File Connection (to the location of the dtsx file). SQL Server locations also require the name of the SSIS package; the File System option does not because the SSIS package is the only thing in the file. The External Reference ReferenceType setting is useful for importing SSIS solutions that contain earlier versions of the Execute Package Task because External Reference behaves exactly like previous versions of the Execute Package Task.

The remaining properties in the property grid – Password and ExecuteOutOfProcess – are shared between ReferenceType options. Password is used if the SSIS package is password-protected (if the ProtectionLevel property of the SSIS package is set to either EncryptSensitiveWithPassword or EncryptAllWithPassword). ExecuteOutOfProcess is a setting that determines whether the package will be executed as part of the current process or a new process.

When executing SSIS packages in Project Deployment Mode (using the Project Reference ReferenceType), Parameter Bindings are enabled and allow values to be passed from the Parent package to the Child package, as shown in Figure 4:


Figure 4: Binding a Parent Package Parameter to a Child Package Parameter

Parameter Bindings are disabled when the ReferenceType property is set to External Reference.

Once configuration is complete, click the OK button to close the Execute Package Task Editor. You can test execution by pressing the F5 key or selecting “Start Debugging” from the SSIS dropdown menu. A successfully-executed Execute Package Task will appear as shown in Figure 5:


Figure 5: A Successful Execution!

The Execute Package Task drives a powerful data integration architecture pattern: Parent-Child execution. Using SSIS Precedent Constraints and Sequence Containers with the Execute Package Task, a data integration developer can develop SSIS “driver” packages that call Child SSIS packages in any combination of parallel and serial orders of execution.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script to Find Leap Year

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as a variable to @year but you can change it to any year which you want to check for leap year.

DECLARE @YEAR SMALLINT
SET @YEAR=2000
SELECT @YEAR AS YEAR,
CASE
DAY
(EOMONTH(DATEADD(DAY,31,DATEADD(YEAR,@YEAR-1900,0))))
WHEN 29 THEN 'YES' ELSE 'NO'
END AS LEAP_YEAR
GO

The result is

YEAR LEAP_YEAR
 ------ ---------
 2000 YES

The logic is from the given year create a Date which results for Feb 01 of that year. You can do this by adding 31 (DATEADD(YEAR,@YEAR-1900,0) results for Jan 01 of that year). Using EOMONTH finds the last day of the month. Using DAY function find out day value. If it is 29, it is a leap year otherwise it is non Leap year.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error: Fix for Error Msg 3906 – Failed to update database because the database is read-only

Some error messages are a great source of troubleshooting when things go wrong. I always request my blog readers to read what the error messages are conveying. Most of the solutions are very much documented as part of the message. I must agree that over the years the error messages have become more descriptive in SQL Server.

On a similar note, recently one of the DB’s sent me a mail stating they were getting the below error message and they thought I had something on the blogs to help them solve the same.

Msg 3906, Level 16, State 1, Line 10

Failed to update database “ProdDB” because the database is read-only.

On careful search, I haven’t seem to have written about the resolution. When I looked at the error message, I thought it was quite explanatory and didn’t need any mention about solution. Your database is marked as ReadOnly and we are not able to write into them. So why the fuss, just mark your database as read-write.

After that incident, I thought of writing this blog to show how this can be solved with a simple code. Let us get into the script next. Let us create our database for testing.

CREATE DATABASE [ReaOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReaOnlyDB', FILENAME = N'C:\Temp\ReaOnlyDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReaOnlyDB_log', FILENAME = N'C:\Temp\ReaOnlyDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
USE ReaOnlyDB
GO
CREATE TABLE tbl_SQLAuth (id INT)
GO

We have gone ahead and created the table which we will use for inserting later in the code. Let us mark the just created database as ReadOnly.

USE MASTER
GO
ALTER DATABASE [ReaOnlyDB] SET READ_ONLY
GO

With the above code successfully executed. Let us go ahead and try to insert some value in the table.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

You will be greeted with the below error message inside SQL Server Management Studio.

Msg 3906, Level 16, State 1, Line 20

Failed to update database “ReaOnlyDB” because the database is read-only.

We have successfully replicated this error message. It is as easy as it gets. If you encounter this error message, we need to remove the ReadOnly attribute from our database. This can be achieved using the following TSQL:

USE [master]
GO
ALTER DATABASE [ReaOnlyDB] SET READ_WRITE WITH NO_WAIT
GO

As simple as it gets. Now the database is available for Write operations.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

Now the insert statement would succeed without any errors. With the error message and resolution discussed. Let me give the cleanup script for this blog.

-- Clean up time
USE MASTER
GO
DROP DATABASE ReaOnlyDB
GO

As I wrap up this blog, would love to hear from you if you have every marked and used ReadOnly databases in your environments? What are the scenario’s you have used these options in databases? Or is this the first time you read about ReadOnly databases? Let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Viewing User Configurable Info in SQL Server

Most of the blog post as I say is revisiting something I wrote a while back in this blog. Recently one my blog readers asked me about user defined counters and he seems to have bumped into the blog: How to use Procedure sp_user_counter1 to sp_user_counter10 and was thinking if there is a way to play with these counters for some debugging purposes.

Well, the debugging capabilities of SQL Server are completely awesome since SQL Server 2012, this particular question got me thinking. So I thought let me write something simple to show you how some of these hidden gems are worth a look again.

Introducing sp_trace_generateevent

This is a simple command that allows to create user defined events inside SQL Server. We can raise an event with event id between 82 and 91 (total of 10 events). The usage of the same is simple – use the TSQL:

SELECT 1
GO
EXEC MASTER..sp_trace_generateevent
@event_class = 82, @userinfo = N'This is post SELECT 1 :)';

In the above code block, we execute the SELECT and post it executes – we want to raise an event. So your next obvious question is how do we capture the same? We will turn up to our old friend SQL Server Profiler.

So as part of event selection, we will go ahead and select the appropriate event:

Since we have used Event ID of 82 – it gets collected under UserConfigurable:0. So the guess is simple:

Event ID: 82 -> UserConfigurable:0

Event ID: 83 -> UserConfigurable:1

Event ID: 84 -> UserConfigurable:2

Event ID: 91 -> UserConfigurable:9

Now let us next look at our event output from Profiler.

Now, we can use the various event ID to raise a user configured event. This is not the ONLY way to capture user configurable events. We can also use Extended Events to capture the same. If you go through the Extended Events Wizard, make sure to select the “user_event” Event name just like below:

Go through the wizard and finish. Or feel free to use the below TSQL:

CREATE EVENT SESSION [User-Generated-Event] ON SERVER
ADD EVENT sqlserver.user_event
ADD TARGET package0.event_file(SET filename=N'C:\Temp\User-Defined-Event.xel',max_file_size=(20))
WITH (STARTUP_STATE=ON)
GO

Once this is created, we can use the Extended Events node to right click and “Watch Live Data”.

These are simple yet powerful ways to watch data when it comes to using User Configurable Events. Have you ever tried using them in your environments? Would be interesting in learning the same from you if you can share via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

Here is the most debated Interview Question – Is Shrinking Database Good or Bad?

I will try to answer this in a single statement – “Shrinking Database is bad practice for performance as it increases fragmentation. It should be used in rare cases of running out of space on drive.”

I believe above statement is not enough to explain the various details associated with it. Here are few reference blog posts, I strongly suggest to read with regards to this subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)