Tag Archives: Debugging

Articles related to Microsoft SQL Server debugging

"Default Cursor" scope set at the database level can be verified using the "Options" page of the "Database Properties" window in SSMS

#0386 – SQL Server – Cursor Scope – A cursor with the name ‘cursor name’ does not exist.; Msg 16916


It’s a well-known fact that using T-SQL cursors have a significant overhead on the query performance. At the same time, there are applications where one simply cannot use a set-based approach and use T-SQL cursors. Examples of such scenarios are when cascading complex calculations need to happen for each record (which are handled via CLR code or dedicated stored procedures) or when interfacing applications can only accept one record at a time.

Each T-SQL cursor has a specific scope. The cursor can be limited for use within the given stored procedure, trigger or batch making it a LOCAL cursor. If the context of the cursor is to be made available to multiple objects and scopes within the connection until explicitly deallocated, it is a GLOBAL cursor.

The cursor scope (GLOBAL/LOCAL) can be defined at the database level and can be overridden by the DECLARE CURSOR statement. Most implementations that I have seen rely on the database configuration. In such cases, if the cursor has been designed for GLOBAL access and someone changes the database configuration to a LOCAL cursor scope, the code will break.

Checking Default Cursor Scope: Database Configuration

Using T-SQL

A simple check on the [is_local_cursor_default] column of the [sys].[databases] will tell us if the default cursor scope is local or global.

--Confirm that Global cursors are set:
--        is_local_cursor_default = 0, i.e. GLOBAL cursor scope
--        is_local_cursor_default = 1, i.e. LOCAL cursor scope
--This is the default setting!
SELECT [sd].[is_local_cursor_default],
       [sd].* 
FROM [sys].[databases] AS sd 
WHERE [sd].[database_id] = DB_ID('AdventureWorks2012');
GO

As the column name suggests, a value of 0 indicates that the cursor is GLOBAL, whereas a value of 1 indicates that the cursor is LOCAL.

Using SSMS

In SSMS, we can check the value of default cursor scope by looking at the “Options” page of the Database Properties window.

"Default Cursor" scope set at the database level can be verified using the "Options" page of the "Database Properties" window in SSMS

Checking “Default Cursor” scope set at the database level

Reproducing the Problem

For the purposes of this demo, I have created a simple stored procedure in the AdventureWorks database that is called once per each Product Category Id to get the product listing of products in that category.

USE AdventureWorks2012;
GO

--Create the test stored procedure
IF OBJECT_ID('Production.ProductListing','P') IS NOT NULL
    DROP PROCEDURE Production.ProductListing;
GO
CREATE PROCEDURE Production.ProductListing
    @productCategoryId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ppsc.ProductCategoryID AS ProductCateogry,
           pp.ProductID,
           pp.Name,
           pp.ProductNumber,
           pp.MakeFlag,
           pp.FinishedGoodsFlag,
           pp.StandardCost,
           pp.ListPrice,
           pp.SellEndDate,
           pp.DiscontinuedDate
    FROM Production.Product AS pp
    INNER JOIN Production.ProductSubcategory AS ppsc 
            ON pp.ProductSubcategoryID = ppsc.ProductSubcategoryID
    WHERE ppsc.ProductCategoryID = @productCategoryId;
END
GO

I can now create a cursor, access values from the cursor and call the stored procedure in an iterative manner. One would use this pattern when working with situations where dynamic SQL may need to be used to build the cursor (e.g. when fetching data from different tables of the same structure depending upon the configuration/situation).

USE AdventureWorks2012;
GO
--Create a CURSOR via Dynamic SQL and then 
--use the values from the CURSOR to call the stored procedure
DECLARE @productListCategoryId INT;
EXEC sp_executesql @sql = N'DECLARE ProductListByCategory CURSOR
                                FAST_FORWARD READ_ONLY
                            FOR SELECT ppc.ProductCategoryID
                                FROM Production.ProductCategory AS ppc;
                           ';

OPEN ProductListByCategory;

FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXEC Production.ProductListing @productCategoryId = @productListCategoryId;

    FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
END

CLOSE ProductListByCategory;
DEALLOCATE ProductListByCategory;
GO

Normally the stored procedure calls will work just fine, as can be seen from the screenshot below.

Successful execution of cursor when Global cursor scope is configured

Successful execution of cursor when Global cursor scope is configured

Now, let us change the cursor scope to LOCAL at the database level.

--Now, enable local cursors by default
--for the AdventureWorks2012 database
ALTER DATABASE [AdventureWorks2012] SET CURSOR_DEFAULT LOCAL;
GO

Running the same query as above now results in errors! The errors simply state that the cursor with the name “ProductListByCategory” does not exist.

USE AdventureWorks2012;
GO
--Create a CURSOR via Dynamic SQL and then 
--use the values from the CURSOR to call the stored procedure
DECLARE @productListCategoryId INT;
EXEC sp_executesql @sql = N'DECLARE ProductListByCategory CURSOR
                                FAST_FORWARD READ_ONLY
                            FOR SELECT ppc.ProductCategoryID
                                FROM Production.ProductCategory AS ppc;
                           ';

OPEN ProductListByCategory;

FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXEC Production.ProductListing @productCategoryId = @productListCategoryId;

    FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
END

CLOSE ProductListByCategory;
DEALLOCATE ProductListByCategory;
GO
Msg 16916, Level 16, State 1, Line 75
A cursor with the name 'ProductListByCategory' does not exist.

Learning the hard way

The error message above is exactly what we received after we restored a copy of our development database on a loaned SQL Server instance to facilitate a ramp-up activity.

After conducting an impromptu code review to confirm that there were no other obvious issues (like deallocating the cursor without closing), we set out to compare the server and database settings with a working environment to rule out environmental issues. That’s when we saw that the DBA on the loaned server had changed the cursor scope setting of our database.

The Solution

The ideal solution to this issue has two parts – a configuration piece and a failsafe embedded into standard coding practice.

Set and document database level cursor scope requirements

One part of the ideal solution is to define and document the cursor scope requirements during database design. This should then be part of the documentation and checklists that are referenced when deploying the databases in production.

Database deployment and setup scripts should also have scripts to set the default scope, similar to the one below.

ALTER DATABASE [AdventureWorks2012] SET CURSOR_DEFAULT GLOBAL;
GO

Ensure that cursor scope is defined when declaring a cursor

The fail-safe aspect of the solution is to ensure that when declaring a cursor, the code explicitly specifies whether the cursor is GLOBAL or LOCAL.

As can be seen from the example below, if scope is defined during cursor definition, it continues to work even if the cursor scope at the database level is not as expected.

--Set the cursor scope as GLOBAL when defining the cursor
--(as part of the DECLARE CURSOR statement)
--This overrides the default database configuration
USE AdventureWorks2012;
GO
DECLARE @productListCategoryId INT;
EXEC sp_executesql @sql = N'DECLARE ProductListByCategory CURSOR GLOBAL
                                FAST_FORWARD READ_ONLY
                            FOR SELECT ppc.ProductCategoryID
                                FROM Production.ProductCategory AS ppc;
                           ';

OPEN ProductListByCategory;

FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXEC Production.ProductListing @productCategoryId = @productListCategoryId;

    FETCH NEXT FROM ProductListByCategory INTO @productListCategoryId;
END

CLOSE ProductListByCategory;
DEALLOCATE ProductListByCategory;
GO
If the GLOBAL scope is defined when declaring a cursor, the code works as expected even though the database configuration is set to default cursor scope of LOCAL

If the Global scope is defined when declaring a cursor, the code works as expected irrespective of database configuration

Further Reading

The same error message (Msg. 16916) will be thrown due to a simple coding error – when we code deallocates a cursor before closing it first. This can easily be caught by implementing a code review practice and is a lesson for sustenance teams. That is what I recommend as a further reading into this topic.

Until we meet next time,

Be courteous. Drive responsibly.

A Blank string is treated as a zero (0) when used in a query filter on an integer value due to implicit conversion.

#0384 – SQL Server – Basics – Implicit Conversion – Blank strings (”) are treated as integers


On one of the forums, I encountered an interesting question the other day. The person asking the query had a table which had integers, null values and blank strings. In order to clean up this data, it was required to distinctly identify each of these combinations, but the simple queries below seemed to return the same results.

USE tempdb;
GO
SELECT sv.RowValue,
       sv.RowValueInWords
FROM dbo.SomeValues AS sv
WHERE sv.RowValue >= 0;
GO

USE tempdb;
GO
SELECT sv.RowValue,
       sv.RowValueInWords
FROM dbo.SomeValues AS sv
WHERE sv.RowValue >= '';
GO

Behind the scenes

This behaviour is not a bug, but is by design of SQL Server. What is happening is that when faced with conflicting data types, SQL Server tries to perform implicit conversions to arrive at a common data type which can be used to compare both the values.

Implicit conversion is done by following the rules of data type precedence, which dictates the sequence of implicit conversion, i.e. a data type with lower precedence is converted to a data type that has higher precedence. If such implicit conversion is not possible, and the user has not specified an explicit conversion, an error is returned.

Per the rules outlined by data type precedence, character data types (VARCHAR/NVARCHAR/TEXT/NTEXT) have a precedence that is lower than integers (INT). Hence, when faced with comparing characters and numbers, SQL Server will always implicitly convert the string to a number. A blank string (”) is therefore treated as a zero (0) when implicit conversion takes place.

Allow me to present this with a demo.

A demo

In the example below, I recreate the scenario referenced in the forum post – I have a table with numbers, zero and NULLs.

USE tempdb;
GO
SET NOCOUNT ON;
GO
--Safety Check
IF OBJECT_ID('dbo.SomeValues','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SomeValues;
END
GO

--Creating the test table
CREATE TABLE dbo.SomeValues 
        (RowId           INT         NOT NULL IDENTITY(1,1),
         RowValue        INT             NULL,
         RowValueInWords VARCHAR(50) NOT NULL
        );
GO

--Inserting values in the test table
INSERT INTO dbo.SomeValues(RowValue, RowValueInWords)
VALUES (0,    'Zero'),
       (NULL, 'NULL'),
       (1,    'One'),
       (2,    'Two'),
       (3,    'Three'),
       (4,    'Four'),
       (5,    'Five');
GO

Now let me run the query presented in the forum post.

USE tempdb;
GO
SELECT 'Query with Zero' AS QueryCondition,
       sv.RowValue,
       sv.RowValueInWords
FROM dbo.SomeValues AS sv
WHERE sv.RowValue >= 0;
GO

USE tempdb;
GO
SELECT 'Query with Blank' AS QueryCondition,
       sv.RowValue,
       sv.RowValueInWords
FROM dbo.SomeValues AS sv
WHERE sv.RowValue >= '';
GO

SQL Server returned me the same result because the blank string was implicitly converted to a zero (0).

A Blank string is treated as a zero (0) when used in a query filter on an integer value due to implicit conversion.

Implicit Conversion may cause query filters to behave unexpectedly

Now, I will try and insert a blank string into the test table. We can see that the value will be inserted successfully.

--Let's try to insert a blank value
USE tempdb;
GO
INSERT INTO dbo.SomeValues (RowValue, RowValueInWords)
VALUES ('','Blank');
GO
/***********
RESULTS
***********/
--Command(s) completed successfully.

The question now is – what did SQL Server insert? If a blank value was indeed stored, it would violate the rules of the data type enforced by the column definition. Hence, I will now select the values from the table to see what was inserted.

USE tempdb;
GO
--See what was stored
SELECT sv.RowValue,
       sv.RowValueInWords
FROM dbo.SomeValues AS sv;
GO
A blank string is inserted into the table as a zero due to implicit conversion

A blank string is inserted into the table as a zero due to implicit conversion

We can see from the results above that SQL Server performed an implicit conversion during the insert and stored a zero into the table. If the calling application tries to validate the data stored, it can continue to detect a mismatch between the expected and the actual data stored.

Summary

Implicit conversion is a boon if used wisely, but in most cases it can (and will) catch poorly written code and unsuspecting developers by surprise. Almost all operations in SQL Server are affected by implicit conversion as I have explored in the past with the following posts:

  • Best Practices – Avoid Implicit conversions as they may cause unexpected issues (Part 01) [Blog Link]
  • Best Practices – Avoid implicit conversions (Part 02) – Implicit conversions, COALESCE and ISNULL [Blog Link]
  • Msg 206; Operand Type Clash; Return type of a CASE expression follows datatype precedence [Blog Link]
  • Data Type Precedence in SQL Server [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.

Msg 1013 indicating duplicate exposed names when the same object is referenced again in the query without an alias.

#0383 – SQL Server – Basics – Msg 1013 – The objects in the FROM clause have the same exposed names. Use correlation names to distinguish them.


Working with complex queries (those that have more than a handful of joins), one of the things that is difficult for novice developers is to keep track of where they are and which objects have already been referenced on the query.

One of the developers just walked up to me the other day with the following error (formatted for the sake of brevity):

Msg 1013, Level 16, State 1, Line 3
The objects "Person.Person" and "Person.Person" in the 
FROM clause have the same exposed names. 
Use correlation names to distinguish them.

Now, I did not recollect if I had ever seen the error before, so my first response was to take a look at the query. As soon as I looked at the query, I immediately realized the problem – the query had a self join and no object aliases were used to distinguish between the two instances of the same object!

USE AdventureWorks2014;
GO
SELECT *
FROM Person.Person 
INNER JOIN Person.Person ON BusinesEntityID = BusinessEntityID;
GO
Msg 1013 indicating duplicate exposed names when the same object is referenced again in the query without an alias.

Msg 1013 indicating duplicate exposed names when the same object is referenced again in the query without an alias.

Lesson Learned

Once the objects in the query were given aliases, the error was resolved.

However, this incident enforces what I have always practiced and believed to be a best practice – always ensure that object names, queries and other literals used in a query have proper aliases.

Using proper aliases ensures that as a developer, reviewer or as a support engineer, we always know exactly which instance of the object is being referred.

Until we meet next time,
Be courteous. Drive responsibly.

Showing how to debug a DML trigger by stepping into a DML trigger during a SSMS T-SQL Debugging session

#0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger


I often receive questions related to debugging code using SSMS. Based on these questions, I have written a lot of blog posts in the past, however I was recently asked the question:

How can I debug a DML trigger in SSMS?

I realized that while I had encountered the same question and researched it in my initial days (when I worked on SQL Server 2000), I had never written about it. So, here goes.

In order to demonstrate how to debug a trigger, all we need to do is debug the statement that initiates the trigger.

For this demonstration, I will fire an update on the [Sales].[SalesOrderHeader] table of the AdventureWorks database.

USE AdventureWorks2012;
GO
UPDATE soh
SET soh.SubTotal += 2
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber = 'SO43697';

When we debug this query (using F10), the SSMS debugger launches and we can step through the code using the same key combinations as we have in Visual Studio.

Demonstrating how to debug DML triggers using SSMS

Debugging T-SQL script using SSMS

Executing the update should fire the DML trigger [uSalesOrderHeader] which can be debugged like any other T-SQL code by stepping into the trigger (press F11 when executing the update).

Showing how to debug a DML trigger by stepping into a DML trigger during a SSMS T-SQL Debugging session

Stepping into a DML trigger during a SSMS T-SQL Debugging session

That’s it. Debugging a trigger is no different than debugging a stored procedure or any other T-SQL script.

In case you would like to learn more about debugging in SSMS, please do refer my previous posts (links below). If there is anything specific that you would like to know more about, do drop in a line.

Further Reading

  • T-SQL Debugger – Underappreciated Features of Microsoft SQL Server [Blog Link]
  • Enabling T-SQL Debugger in SQL Server Management Studio (SSMS) [Blog Link]
  • SSMS – Tutorial – Part 03 – The T-SQL Debugger (L200) [Blog Link]
  • T-SQL Debugging – Connection Errors & Firewall settings [Blog Link]
  • T-SQL Debugging – SSMS errors – MSDBG2.DLL [Blog Link]
  • Setting, Labeling, Using & Searching Conditional Breakpoints [Blog Link]
  • Export/Import Breakpoints [Blog Link]
  • Using the “When Hit, Do Something” breakpoint configuration [Blog Link]
  • Using Data-Tips [Blog Link]
  • Editing, Exporting and Importing Data Tips [Blog Link]

Until we meet next time,
Be courteous. Drive responsibly.

When we multiply a Decimal (20.16) with another number (100) using the calculator, the result is as expected (2016)

#0380 – SQL Server – Basics – Specify Scale and Precision when defining Decimal and Numeric datatypes


I had some interesting conversation during a code review that I was asked to conduct for a simple query that a team had written to support their project monitoring. The team was specializing in quality assurance and had minimal development experience. The team had used variables of decimal data types in their script, but they were declared without any precision or scale. When I gave a review comment on the declaration of variables, I was asked the question:

Does it make a difference if we do not specify scale and precision when defining variables of decimal or numeric datatypes?

I often look forward to such encounters for two reasons:

  1. When I answer their questions, the process reinforces my concepts and learnings
  2. It helps me contribute to the overall community by writing a blog about my experience

When the question was asked, I honestly admitted that I did not have a specific answer other than it was the best practice to do so from a long-term maintainability standpoint. Post lunch, I did a small test which I showed the team and will be presenting today.

The Problem

In the script below, I take a decimal variable (declared without a fixed scale or precision) with value (20.16) and multiply it by a constant number (100) and then by another constant decimal (100.0). If one uses a calculator, the expected result is:

  • 20.16 * 100 = 2016
  • 20.16 * 100.0 = 2016
When we multiply a Decimal (20.16) with another number (100) using the calculator, the result is as expected (2016)

Expected results when we multiply a Decimal with another number using the calculator

However, when we perform the same test via SQL Server, we are in for a surprise:

DECLARE @dVal1 DECIMAL = 20.16;

SELECT (@dVal1 * 100)   AS DecimalMultipliedByAnInteger, 
       (@dVal1 * 100.0) AS DecimalMultipliedByADecimal;
GO

As can be seen from the seen from the results below, we do not get the expected results, but we find that the decimal value was rounded off before the multiplication took place.

Although the test input value is declared as a decimal, the result appears to be based only on the significand, not the mantissa part of the input.

Although the test input value is declared as a decimal, the result appears to be based only on the significand, not the mantissa part of the input.

Root Cause

The reason behind this behaviour is hidden in the following lines of the SQL Server online documentation on MSDN (formerly known as “Books-On-Line”) for decimal and numeric data-types available here: https://msdn.microsoft.com/en-us/library/ms187746.aspx.

…s (scale)
The number of decimal digits that will be stored to the right of the decimal point….Scale can be specified only if precision is specified. The default scale is 0…

The real reason however is a few lines below – rounding.

Converting decimal and numeric Data

…By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale….

What SQL Server appears to be doing here is that when a variable of DECIMAL datatype is declared without a precision and scale value, the scale is taken to be zero (0). Hence, the test value of 20.16 is rounded to the nearest integer, 20.

To confirm that rounding is indeed taking place, I swapped the digits in the input value from 20.16 to 20.61 and re-ran the same test.

DECLARE @dVal1 DECIMAL = 20.61;

SELECT (@dVal1 * 100)   AS DecimalMultipliedByAnInteger, 
       (@dVal1 * 100.0) AS DecimalMultipliedByADecimal;
GO

Now, the result was 2100 instead of 2000 because the input test value of 20.61 was rounded to 21 before the multiplication took place.

Because the test input value was declared as a decimal without precision and scale, rounding took place, resulting in a different result.

Because the test input value was declared as a decimal without precision and scale, rounding took place, resulting in a different result.

By this time, my audience was struck in awe as they realized the impact this behaviour would have had on their project monitoring numbers.

The Summary – A Best Practice

We can summarize the learning into a single sentence:

It is a best practice for ensuring data quality to always specify a precision and scale when working with variables of the numeric or decimal data types.

To confirm, here’s a version of the same test as we saw earlier. The only difference is that this time, we have explicitly specified the precision and scale on our input values.

DECLARE @dVal1 DECIMAL(19,4) = 20.16;

SELECT (@dVal1 * 100)   AS DecimalMultipliedByAnInteger, 
       (@dVal1 * 100.0) AS DecimalMultipliedByADecimal;
GO

When we look at the results, we see that the output is exactly what we wanted to see, i.e. 2016.

Because the test input value was declared as a decimal with precision and scale, no rounding took place and we got the expected result, i.e. 2016.

Because the test input value was declared as a decimal with precision and scale, no rounding took place and we got the expected result.

Further Reading

Until we meet next time,
Be courteous. Drive responsibly.