DaveWentzel.com            All Things Data

Dave Wentzel's blog

Fun With Transactions

Demo files can be found here

This is a short presentation on some of the confusing aspects of transaction handling in SQL Server.  We start with transaction naming:  

Transaction Names

Even on a case-insensitive database this will fail:  

But this will not fail:  

And even this will work because transaction names are totally ignored when issuing a COMMIT:

Autonomous Transactions

A sorely missed feature of SQL Server is native "autonomous transactions."  This is where you "pause" the current transaction and spin up a new transaction.  When the new transaction "completes" (commits or rolls back) then we continue the paused transaction.  Some use cases:

  • out-of-band logging/auditing
  • custom sequence generators

.NET and Java developers have the concept of .REQUIRES_NEW to "pause" an existing transaction and start a new, autonomous transaction.  Under the covers this is implemented as a new spid with potentially different transaction semantics.  I give some demos on how to use Linked Servers in SQL Server to generate your own autonomous transactions. 

Implicit Transactions

Implicit Transactions (IT) cause inordinate confusion.  At their worst they can cause what appears to be massive blocking events on your server.  Most developers and data access technologies do not use IT, the notable exception being the Java world (Spring JDBC, WebSphere, etc).  When SET IMPLICIT_TRANSACTIONS ON is set a transaction is started for you...if it is needed.  That "if" statement is what causes problems because the "if" assumptions are not properly documented by Microsoft.  If your driver forces you to use IT then ensure that whenever a connection is pulled from the pool that sp_reset_connection is being called.  This will reset the IT setting.  If this isn't done then you can "leak" connections where a developer might have turned OFF IT to get finer-grain control.  That's a big problem.  

I have lots of demos on the confusing aspects of implicit transactions as well as how to monitor your server for IT anomalies that may cause blocking or "abandoned spid" problems for you.  

Demo files can be found here



You have just read "Fun With Transactions" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  


Presenting at PSSUG next week

On Tuesday, December 2, 2014 beginning at 5:30 I'll be doing a short presentation on "Transaction Trivia" (I'll probably come up with a better title later) at the monthly PSSUG meeting at Microsoft's Malvern office.

Service Broker Demystified - Services

Services and the [DEFAULT] contract can be very confusing.  In the post I'll show you why that is and some simple ways to resolve the problems in your mind.  Then we'll look at how to model send-only and receive-only services, which is another constraint you can use in your SSB design. 

SSIS and data contracts

Have you ever tried to use a stored proc with a temp table in an SSIS package?  Did it work?  SSIS is severely hampered by the fact that this doesn't work seamlessly.  In this post I'll demo the problem and I'll give you the best fix for it.  The objective is to run a stored proc from SSIS and optionally send the output to a text file. 


  • Create a very simple stored proc.  You can download the repro script here.  Our stored proc simply builds a temp table, inserts a few rows and then reads those rows as a result set to the caller.  No parameters...very simple.  
  • Create a new SSIS package.  
  • Add a Data Flow Task 
  • Double-click it.  
  • Add an "OLE DB Source" to the designer
  • Connect to your instance/db where you have dbo.SampleDataContract installed
  • Change the "Data access mode" to SQL Command and enter the call to our stored proc
  • Click "Preview..."
  • You should immediately generate an error 
  • The error text is:  

Exception from HRESULT:0xC020204A
Error code: 0x80004005
Description: "Invalid object name ''.".
Unable to retrieve column information from the data source. Make sure your target table in the database is available.  

This error occurs because you decided to use a temp table (#temptable...the kind in tempdb) somewhere in the text of your procedure.  That's not allowed.  SSIS (and SSRS and a number of ORM tools, etc) attempt to read the metadata about your procedure to determine what the output will look like (number of columns, names, and data types).  The error is telling you that the metadata could not be found for the temp table that you are attempting to use.  

BTW, this will not happen if you usse @TableVariables.  However, there are performance ramifications of using table variables in some versions of SQL Server.  

Data Contracts to the Rescue

I've written about Data Contracts for Stored Procedures before.  Quick recap...in the Oracle world you CANNOT return a result set from a stored procedure.  Instead you create a package to do this.  In the package "header" you declare the "data contract" (inputs and outputs, including ordinal position, name, and datatype).  Then, in the package body you write the actual code and that code must adhere to the declared data contract.  In the example, emp_actions is the package and it implements 2 procedures, a cursor (which is nothing like a SQL Server cursor, it is more like an ADO cursor in that it is a "CURrent Set Of Records"...ie a result set) and the output format of the cursor, which is known as a record type.  Note that the package body actually outputs the data when someone queries for the desc_salary result set.  

When I first started working with Oracle I thought this syntax was EXTREMELY cumbersome.  Too much typing of redundant information (a CURSOR is declared over a TYPE and then the actual query has to be bound to the CURSOR and to the TYPE.  Too much can go wrong here.  

I thought this was cumbersome until I started working with SQL Server in the Nineties.  SQL Server then only partially declared the full data contract.  Stored procs declared the inputs but not the outputs.  In later releases M$ graciously gave us functions and table-valued parameters which FULLY implement data contracts.  But they never fixed procedures to fully support data contracts.  Why do we need data contracts for stored procs?  See this connect item for a list of reasons...but having a true data contract solves the SSIS problem I noted above.  In this post a blogger lists exactly what he would want to see in a fully implemented data contract for stored procedures.  That's a great list.  

While having full-blown data contracts would be nice, you don't have to wait for M$ to roll your own data contracts and solve some of these issues.  Here's how I do it.  At the top of your stored procedure add a little code block that can NEVER execute that defines your data contract.  When these "smart tools" like SSIS, SSRS, and most ORMs try to read your metadata they see that you have a SELECT statement and determine that is the "shape" of the result set.  Even though the SELECT code block is in a IF block that will never be executed (note the IF 1 = 0).  Just doing that little bit of work is enough to fool EVERY "smart tool" that looks for result set metadata.  

Mocking up a data contract really fixes this?

Yes.  Let's demo.  Go back to SSIS to your OLE DB Source component.  Click the "Preview..." button again.  Last time we got an error.  This time we get our result set as expected.  


Creating your own mocked-up data contract is an easy way to overcome tools that attempt to read the metadata of your procedures and fail because they contain references to a temp table.  If your SSIS packages require the IsSorted flag you can even put an ORDER BY clause on your "data contract" and your output will automatically have the IsSorted flag set.  


You have just read "SSIS and data contracts" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - Why do we need Services and Queues?

People claim that they don't want to use Service Broker because it is too complicated. I started a blog series called Service Broker Demystified because SSB really isn't that difficult if you understand some basic concepts. Folks don't understand why both a "Service" and a "Queue" are needed.  Why not just have one object?  In this post I'll show you why and give you a quick rule-of-thumb to avoid the confusion.  

Service Broker Demystified - Why is there no ALTER CONTRACT statement?

The concept of "contracts" in Service Broker is initially confusing to most data professionals.  I like to think of a contract as a constraint applied to a message.  Then why can't you ALTER a contract?  Because a contract is really more like a "legally-binding contract."  There are good reasons why contracts can't/shouldn't be altered.

Service Broker Demystified - Contracts and Message Types

Contracts and Message Types are the "table constraints" of the Service Broker world.  Like table constraints, they aren't required, but they keep you from doing stupid stuff with your Service Broker design.  In this post I'll cover some confusing aspects of contracts and message types.  

Service Broker Demystified - [DEFAULT] is not the DEFAULT

Contracts and Message Types have defaults, but the default is not [DEFAULT].  This leads to confusion to folks new to Service Broker.  In this post I'll clear up the confusion and give you some tricks to keep things clear.  

People tell me all the time that they don't want to use Service Broker because it is too confusing.  I started a blog series called Service Broker Demystified to dispel these misconceptions.  Today we are going to cover the [DEFAULT] objects, which are not really default objects...sometimes. 

Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER

Service Broker can be really confusing and causes data professionals to shy away from using it.  One example is setting up SSB.  It's easy, until you need to make a copy of your database.  Then the fun begins.  In this post I'll explain when you want to use ENABLE_BROKER vs SET NEW_BROKER.

Service Broker Demystified - Encryption Weirdness

Encryption is one of those things that makes Service Broker difficult to learn.  To set up encryption correctly is a lengthy process.  There are ways to safely not use encryption.  In this post I'll show you all of the issues with encryption in SSB and how to avoid and fix the problems.  


Subscribe to RSS - Dave Wentzel's blog