Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 512 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 527 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 534 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 570 Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1244 Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1442 Strict Standards: Redefining already defined constructor for class wpdb in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/wp-db.php on line 306 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 103 Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 431 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/query.php on line 61 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/theme.php on line 1109 Strict Standards: Declaration of Walker_Comment::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Redefining already defined constructor for class WP_Dependencies in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/class.wp-dependencies.php on line 31 Strict Standards: Redefining already defined constructor for class WP_Http in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/http.php on line 61 SQL Tidbits » 2008 » December

You are currently browsing the SQL Tidbits blog archives for December, 2008.

How to keep a transaction from being escalated to the DTC

December 21st, 2008

When working on a project at a client, I some problems with a few functions in my code that wouldn’t work when we uploaded the code to our test environment. It turned out that the functions all contained transactions which were escalated to use the DTC (Distributed Transaction Coordinator) instead of plain old transactions. This made me quite confused, because all connections and transactions were against the same database, which should keep them out of the DTC. Anyways, to fix the problem in the short term, we started the DTC in the test environment and everything worked again. But I decided to take a closer look at the problem.

First, our setup. We wrote c# code in .Net 3.0, using a SQL Server 2005 Standard Edition database. Some actions in our business layer needed transactions, so we used TransactionScopes, developed just for this scenario. As we were using SQL Server 2005, which supports light-weight transactions (2000 does not), we shouldn’t ever need the DTC since we were using only one data source. But apparently, this wasn’t the case. So what was happening?

Here’s a snippet showing our situation:


using (TransactionScope ts = new TransactionScope())
{
    // First database connection, might happen in Business Logic -> Data Access Layer or anywhere
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    // Second database connection, might happen in Business Logic -> Data Access Layer or anywhere. This is escalated to DTC. But why?
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    ts.Complete();
}

When running this code in debug mode, reaching the second using-scope triggered an escalation to the DTC. But why? Apparently, we have two connections running at the same time, but that seems weird, since we’ve already left the using-scope so that connection should be closed in the dispose()-part of the using-scope.

It’s obvious when you think about it, really. When leaving the first using-scope, the connection should be closed. But that doesn’t happen, since we are in a transaction. The transaction scope needs to keep the connection open when we either commit or rollback. So when we reach the second using-scope, a second connection is opened and we are escalated to the DTC.

So, what can we do about this? There are at least two solutions:

If your are using SQL Server 2008 and Visual Studio 2008, this is implemented from the beginning and you don’t need any wrappers or overloads.

Recommended reading:

SP3 out now

December 19th, 2008

SP3 for SQL Server has been released! Microsoft has a list of what’s new and a list of the bug fixes. Go on and download SP3 now!

The perils of not specifying a variable or parameter length

December 12th, 2008

When declaring a variable or parameter as varchar or nvarchar, you might get into some nasty surprises if you don’t specify the number of characters in the data. If you are declaring a variable like DECLARE @string AS VARCHAR, you are going to end up with a string that only holds 1 character. That’s probably not what you intended, but rather DECLARE @string AS VARCHAR(50) or similar.

The behaviour is similar when using CAST and CONVERT. Then, the default is to limit the data to 30 characters.

Some examples of this:

DECLARE @string AS VARCHAR
SET @string = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN('Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [varchar],
CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR) AS [Casted varchar],
LEN(CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR)) AS [Length of casted varchar]
GO

DECLARE @string AS NVARCHAR
SET @string = N'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [nvarchar],
CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR) AS [Casted nvarchar],
LEN(CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR)) AS [Length of casted nvarchar]

Results:

Original string                                          Length of original string varchar Casted varchar                 Length of casted varchar
-------------------------------------------------------- ------------------------- ------- ------------------------------ ------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L       Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)

Original string                                          Length of original string nvarchar Casted nvarchar                Length of casted nvarchar
-------------------------------------------------------- ------------------------- -------- ------------------------------ -------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L        Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)

Introduction to SQL Tidbits

December 10th, 2008

Hello everyone!

SQL Tidbits is a place for me to ramble on about SQL and post tips on how to do weird or complicated stuff in SQL, T-SQL and SQL Server.

My name is Jonas Lincoln, I am currently employed at Netlight AS in Oslo, Norway. I’m usually located in Stockholm, Sweden, but I’m doing a wonderful year in Oslo starting up our business here.

I started using SQL Server 2000 back in 2002, while working on a large online game called Hattrick, one of the largest games in the world actually. As time passed, I got more and more involved and since we were on a tight budget, we had to do some really cool optimizations do get everything running. And that was when my interested in SQL started for real.

Now, I’m a senior consultant, mostly coding in C# and using the .Net framework.

If you wish to contact me, check out my personal page.