Erland Sommarskog's Web Log

If you come here regularly, you have probably noticed that my web site is not one that is updated frequently. Essentially, I make changes when I have a change to make: an update to an article, a new version of a tool or somesuch. If you want to see if I have any updates, this is the page you should bookmark. If I make additions to the site, or changes to existing items you will see it here. (Unless it's just a trivial fix like a correction of a spelling or a grammar error.)

2017-05-23

Added one Connect item for BEFORE triggers to my SQL Server Wishlist.

2017-04-08

Released version 22 of beta_lockinfo. I'm making use of the new DMV sys.dm_exec_query_statistics_xml so it is possible to see actual plans from beta_lockinfo. This only concerns the version for SQL 2016, and it requires SP1 CU2.

2017-03-26

Two updates to the series Error and Transaction Handling in SQL Server.

  1. In Part Two, I have refreshed chapter 8 on In-Memory OLTP to reflect that SQL 2016 is now a released and established product.
  2. In Part Three, I have added a section The Virtue of Getting All Result Sets last in chapter 9, Error Handling in Client Code.

2017-03-05

A minor correction to two scripts in the article Giving Permissions through Stored Procedures.

2017-03-04

I'm now back in Stockholm after eight weeks in Argentina and Chile, and the collection of pictures from that trip is now complete and finalised.

2017-01-04

Uploaded my first set of pictures from, my on-going trip in Argentina and Chile. I will hopefully upload pictures regularly during the trip, which lasts until the end of February. You need to watch this page for updates from the trip, because I will not announce each new set here.

2016-12-27

Uploaded some pictures from Victoria, BC, Canada and Parma, Italy.

2016-12-18

Changes to no less than three articles:

2016-11-16

A fairly critical patch to Win32::SqlServer to address the problem that it not read the SQL Server version correctly when @@version includes information about CU or GDR. There is no version number, but the patch is labelled 2.010b, as it only affects the Perl part of the module.

2016-11-01

Made an addition to the section Other Approaches in the chapter on administrative commands in Part 3 of the series Error and Transaction Handling in SQL Server that covers a solution from Dave Mason on the problem with trapping all error messages from commands like BACKUP/RESTORE.

2016-10-29

A small correction to the article Dynamic Search Conditions: a NOT was missing in the code sample in the section Optional Tables for dynamic SQL.

2016-10-23

Uploaded some summer pictures from Stockholm (and a few from elsewhere).

2016-10-20

Add a new article to the web site: Using the Bulk-Load Tools in SQL Server, an article about how to use BCP, BULK INSERT and OPENROWSET(BULK) with a focus on function, that is how to use these tools with different formats – and also when they will not work for you.

2016-10-09

Minor updates to the SQL Server Wishlist about auto-updated columns and built-in table of numbers.

2016-08-28

Added a section Having XACT_ABORT ON by default? to Part Two of the Error and Transaction Handling series to discusses the new option in SSMS that permits you to do just that.

2016-08-21

Rearranged and modernised the Arrays and Lists series. What used to be only a content listing is now a short introduction for people who need to know how to crack their comma-separated list. The article Arrays and Lists in SQL Server 2005 has been renamed to Arrays and Lists in SQL Server (The Long Version). I have added material on the new functions string_split and OPENJSON in SQL 2016 and made a general overhaul of the text to reflect that SQL 2005 is not really a new version any more. I have renamed the article Arrays and Lists in SQL Server 2008 to Using Table-Valued Parameters in SQL Server and .NET so that it better reflect the contents and decoupled it from the Arrays and Lists moniker. As a consequence of this, I have taken out some material that not directly related to TVPs. There are also some minor updates suggested by readers.

On a general note: there is now a Copyright notice on all articles on the site.

2016-07-15

Relased version 2.010 of Win32::SqlServer.

2016-06-26

Put up pictures from my trip to the zone around Chernobyl power plant when I was in Kiev for SQL Saturday.

2016-06-15

Updated my SQL Server Wishlist after the release of SQL 2016.

The release of SSMS 2016 has prompted an update to the section Line Numbers in SSMS in Part Two in the series Error and Transaction Handling in SQL Server.

2016-06-03

Version 21 of beta_lockinfo. There are now four versions for different versions of SQL Server: SQL 2016, SQL 2012/14, SQL 2008 and SQL 2005. The version for SQL 2016 makes use of some new features in SQL 2016. Beware that if you upgrade from an earlier version of SQL Server and/or beta_lockinfo, you should drop any guest.beta_lockinfo as the table definition has changed. (Does not apply to earlier versions fo SQL Server.) For full information, see the Revision history.

2016-03-29

One more small fix sp_sqltrace: it can now be created on a server with an SC collation.

2016-02-17

Bugfix in sp_sqltrace: @snoop_time > 2147 resulted in an overflow error.

2016-02-07

Somewhat belated, uploaded pictures from my vacation in Germany in the end of summer 2015, and a set of pictures which reaches from autumn to autumn in the Pacific Northwest with London and summer in Stockholm in between.

2016-02-02

Geovanny Hernandez have been kind to translate Part One of Error and Transaction Handling in SQL Server into Spanish.

2015-11-15

Some small corrections to the article Dynamic Search Conditions.

2015-07-11

A couple of small updates to Parts Two and Part Three of Error and Transaction Handling in SQL Server prompted by the release of SQL 2014 SP1, the availabilty of SQL 2016 CTP2 and tips from the community.

2015-05-26

Added Devart's ODBC driver to the page about accessing SQL Server from Unix.

2015-05-24

Released version 2.009 of Win32::SqlServer. This version adds binaries for Perl 5.18 and 5.20. I am now supporting Strawberry Perl! I have dropped support for SQL Server 6.5 and Perl 5.8 and Perl 5.10.

2015-05-03

Finally! Uploaded the three-part series Error and Transaction Handling in SQL Server. Part One is a short jumpstart which is for everyone. Part Two goes into detail what commands you can use for error and transaction handling how SQL Server can behave when an error occurs, a very bewildering topic. Part Three discusses how to implement error handling, and includes a facility, SqlEventLog to raise and log errors. Part Three also includes comprehensive examples for error handling. As the three parts should not be enough, there are also three appendixes: one for linked servers, one for the CLR and one for Service Broker. Start with Part One, and you will find links to the other articles. As part of the endeavour, I have also brushed up the old articles for SQL 2000, but mainly to make them fit stylistically with the other. Contentwise, they are largely the same.

2015-04-14

Jim Higgins was very kind to point out a small but important error in the section on linked servers in the article on dynamic SQL. A missing dot rendered the recommendation incorrect.

2015-03-15

Version 6 of sp_sqltrace. Wait-stats information now also includes external waits. That is, waits that are recorded because the process performs work outside SQL Server, for instance in an OS operation or accesses a linked server.

2015-02-01

Version 19 of beta_lockinfo. Fixes for a potential overflow and a workaround for locked metadata in SQL 2014.

2014-12-27

I have given the article on Dynamic Search Conditions on overhaul. The text have been reviewed from start to end, and there are some new content. I'm now giving examples with multi-valued parameters as well examples on how to handle dynamic sort columns. There is information about a new bug with OPTION (RECOMPILE) uncovered in the autumn of 2014. I've toned down the discussion on service packs and CUs for SQL 2008, since by now you should be on a later service pack – or have upgraded to SQL 2012/2014. Finally, I have relegated the older article for SQL 2005 and earlier to be a pure legacy article and it is on long directly visible from my home page.

2014-10-19

Uploaded photos from my vacation in Ukraine and then there is also the traditional bag of summer pictures.

2014-08-12

Version 18 of beta_lockinfo. Bugfix: beta_lockinfo would produce an overflow error if the instance had been up for 115 days or more. The format of the columns last_since and trn_since has been enhanced. Note that if you have used Archive Mode, you must drop the table guest.beta_lockinfo.

2014-02-01

Version 16 of beta_lockinfo. Lots of changes. The biggest novelty is archive mode to permit you to save the output to a table. beta_lockinfo now also displays information about memory grants and locking partition. I've also taken measures to remove noise from the default output, for instance Service Broker process that are waiting for the next message to arrive are not displayed by default, as long as they hold no locks. See the revision section for a complete list.

2014-01-01

Picture time again. Photos from my vacation the Carolinas and Georgia, as well as the regular set of summer pictures from Stockholm, this year extended thanks to a new camera with 50X zoom.

2013-11-02

Updates to three articles:

2013-08-30

There was a small error in the function setoptions that's attached to the article Slow in the Application, Fast in SSMS.

2013-06-14

The ninth public release of AbaPerls. Lots of new features and improvements, start with the Compact History.

2013-05-18

Added a new item to the SQL Server wishlist: Add a Virtual Errors Table.

2013-03-14

Updated the article Slow in the Application, Fast in SSMS to reflect an import change in SQL 2012 SP1 with regards to the issue with linked servers. Also added a note to the section The Story so Far.

2013-03-24

Added a good suggestion from Wayne Bloss about sharing temp tables in the article How to Share Data Between Stored Procedures.

Added a caveat to the documentation of the column tempdb in the output of beta_lockinfo. The procedure itself is unchanged.

2012-12-26

A small update to the SQL Server Wishlist about the old Connect items with numbers in the 12xxxx range. They are visible, but you can currently not vote for these items.

2012-12-24

Uploaded two suits of pictures: one from my vacation in Romania,where the top draw was the Danbue delta. Then there is a smaller suite with the regular pictures from summer in the area Stockholm, this time also augmented by pictures from Kalmar and Portland and a little more.

2012-11-22

Added one more item to my Strict Checks proposal: columns must be prefixed by an alias or the table source when there are more than one table source visible, to avoid that adding a column in the future results in an ambiguity error.

There was a problem with some very old Connect items mentioned in my SQL Server wishlist. The Connect team have fixed this problem, and these Connect items should now be visible. (At least I can see them when I'm signed in, and I couldn't in the past.)

2012-11-18

Version 15 of beta_lockinfo. Added display of non-default ANSI and locking settings as well a progress column (which applies to only some commands). beta_lockinfo now displays tasks without session ids that have interesting wait types. This can help you to see THREADPOOL waits that occurs when you run out of worker threads and your users cannot log in.

2012-10-27

There is a new version of Lee Tudor's sp_sqltrace available. Lee have made a major overhaul and the procedure is now more powerful than ever. You can now snoop another spid instead of analysing your own process. The procedure now collects and presents wait-stats information per statement on SQL 2008 and up. There are several new parameters to control the procedure, and you can reanalyse a trace if you find that wanted to see the data in a different way. Finally, it has a new name, so you have it in master and run it from anywhere.

2012-09-29

Released version 2.008 of Win32::SqlServer, an API to SQL Server for Perl. See the History file for changes and new features.

2012-07-18

Corrected a few errors and mumblings in the paragraph about using the CLR for linked servers in the article How to Share Data Between Stored Procedures.

2012-07-01

Rewrote most parts of the article Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters. I found that was extremely easy to write a class that parses a comma-delimited string and feed the constructor of that class as the parameter value to a TVP, why I have replaced the previous examples with this one. I have also added two examples on how to load a file through a TVP, one non-streaming and one streaming example. I've also added information on how you can specify that the data you pass to the TVP is already sorted. As a consequenc, I have also updated the section on TVPs in the performance appendix, but I have not run any new performance tests.

2012-05-11

Added a note about SQL Server Data Tools and sharing temp tables to the article How to Share Data Between Stored Procedures.

2012-05-07

Updated my SQL Server Wishlist after the release of SQL 2012. I've rearranged the list, and I put focus even more robustness, devoting an entire section to wishes aimed to make development in SQL Server more robust. All new wishes on the wish list are in that area. I have also updated my document Ideas for SET STRICT_CHECKS ON with some interesting ideas for strict checks, for instance I suggest checks for cardinality errors at compile-time. I've withdrawn some wishes from the wishlist – those that were fulfilled and a few more. If you wonder what happened the old wishes, there is an annonated version of the old wishlist.

2012-02-12

The eighth public release of AbaPerls. Big news! There is now support for Team Foundation Server. AbaPerls users can see the list of recent changes here.

2011-12-31

Triple release:

2011-12-15

Refreshed the article Connecting to MS SQL Server from Unix to reflect that Microsoft has released a CTP of an ODBC driver for Linux.

2011-11-27

Several of the links in the section Further Reading in the article Slow in the Application, Fast in SSMS? were broken. This has been fixed.

2011-11-20

Uploaded pictures from my vacation in Spain earlier this year, as well as the usual set of summer pictures from the Stockholm area (although you will need sturdy clothing to look at some of the pictures at the end, as they are from elsewhere.)

2011-08-26

When I updated of the article article on Dynamic Search Conditions for SQL 2008 to reflect the release of SP1 of SQL 2008 R2, I missed in one place. This has now been corrected.

2011-08-09

Released version 2.007 of Win32::SqlServer.

2011-08-01

Updated the article on Dynamic Search Conditions for SQL 2008 to reflect the release of SP1 for SQL 2008 R2. I've also updated the script for Northgale to avoid use of some deprecated syntax and data types.

2011-07-13

I've made a thorough revision of the article Granting Permissions through Stored Procedures. Partly this is due to that the article was originally written with SQL 2005 being a brand-new release which it no longer is. But I also cover some new ground with counter-signatures and show how you can use counter-signing to make it possible for plain users to start a certain job. The article now also includes a script to easily grant server-level permissions to a user-database procedure, using throw-away passwords for the certitficate. And the most important of all: the article includes a detailed discussion of what it means to make a database trustworthy, and how this can be a security vulnerability if done causually.

2011-07-02

There is a now a Russian translation available of Slow in the Application, Fast in SSMS. Thanks to Dima Piliugin for this work!

2011-06-25

Added section An Issue with Linked Servers to the article Slow in the Application, Fast in SSMS that covers one more reason why may see different performance in the application and from SSMS. Also, got a whim and clean up the article list one the home page to only include the date for the most recent revision of each article.

2011-06-23

A minor update in the article on Dynamic SQL to reflect that the bug with OPTION(RECOMPILE) has been fixed. (It was over a year ago, but I had failed to update this article.)

2011-02-25

Made an addition to the article Arrays and Lists in SQL Server 2008, Using Tabled-Valued Parameters: you need EXECUTE permission to use a table type.

2011-02-20

Published a brand-new article: Slow in the Application, Fast in SSMS? Understanding Performance Mysteries, which takes its starting point in the confusing situation when a query is slow in your application, yet runs quickly when you investigate it in SQL Server Management Studio. In many of these situations parameter sniffing is part of the equation, and I explain what parameter sniffing is, and what measures you can take to avoid to be tripped by what in most situations is a good feature, but which sometimes backfires.

2011-01-28

Uploaded a new version of beta_lockinfo. The new version displays temp tables with their logical name only. Moreover, if there is more than one temp table with the same name, they aggreagated into a single line per lock.

2011-01-23

On a cold dark winter's day, what could be better than to dream back on the summer that was? Pictures from the summer of 2010 uploaded.

2011-01-11

Imran Mohamed pointed out that there was an error in the article Granting Permission through Stored Procedures, the expression to decode context_info() would include trailing NUL characters in the character data. The expression has been improved.

2011-01-05

I used to host a tool call Query Plan Tree, developed by Ivan Arjetinski. However, I have dropped this, since SQL Sentry have made their SQL Sentry Plan Explorer available for free. It has all the functionality of Ivan's tool and a lot more bells and whistles.

2010-11-21

Released version 13 of beta_lockinfo. Bugfixes, and added a column to show tempdb usage.

2010-10-30

Released version 2.006 of Win32::SqlServer.

2010-10-24

The seventh public release of AbaPerls. AbaPerls users can see the list of recent changes here.

2010-10-16

Updated the article Dynamic Search Conditions in SQL 2008  to reflect the release of Serivce Pack 2 for SQL 2008. Also, I have corrected a couple of grammar and spelling errors in the same articleDuff Browne was kind to point out.

2010-10-10

I have rewritten the XML section in the article Arrays and Lists in SQL Server 2005. During the summer I became aware of several things about best practice and performance for XML, which I have addressed. I have performed a rerun of the performance tests for SQL 2008, since they were flawed in several aspects for XML. I have also uptaded the article for the original performance tests in 2006 to point out these flaws. I've also made some smaller updates to the section about fn_nums and added note that perforamance of INSERT VALUES with many rows may improve in SQL 11.

2010-09-18

Updated the section on the OVER clause in the SQL Server Wishlist, with a new Connect item from Iztik Ben-Gan. Votes are encouraged!

2010-08-21

Fixed an issue in sqltrace. Behzad Sadeghi pointed out that plans were missing for dynamic SQL invoked through sp_executesql. due to an inconsistency in SQL Server, which I have reported on Connect.With help of Behzad I have implemented a workaround for the issue.

2010-07-26

Uploaded pictures from my spring vacation in the south of France and Paris. Also uploaded very old (and generally very poor) pictures from my vacation in 2000.

2010-05-23

A new update for Dynamic Search Conditions in SQL 2008: There is now a Cumultative Update for SQL 2008 R2 which includes the fix for OPTION (RECOMPILE).

2010-05-13

An update on the article for Dynamic Search Conditions in SQL 2008: the RTM version of SQL 2008 R2 does not include the fix to make OPTION (RECOMPILE) behave as desired.

2010-02-27

Added a request for scoped variables to my SQL Server Wishlist.

2010-02-11

Added a request for QUALIFY to permit for more concise queries to my SQL Server Wishlist.

2010-02-01

Uploaded various pictures from 2009, mainly from summer in Stockholm and around.

2010-01-17

Further changes to Arrays and Lists in SQL Server 2005 and Beyond, When TVPs Don't Cut it: some variations on fn_nums and some performance observations had I failed to include originally.

2010-01-10 

I've extended the section XML in the article How to Share Data Between Stored Procedures and made some other minor revisions to that article.

2010-01-06

I've reworked the article on Arrays and Lists to cover a couple of new methods that people has to be kind to suggest to me. I've also run a new suite of performance tests on SQL 2008 to test the new methods together with the old ones. This time I have also tested call overhead, and made an attempt to test multi-thread performance. I've also added a new article for SQL 2008 that is devoted entirely to table-valued parameters. In total there are now three articles and two appendixes:

2009-11-29

Two new items:

2009-11-22

Started this web log, and added items back to 2009-04-01.

Updated the articles on Dynamic Search Conditions. With the release of SQL 2008 SP1 CU5, OPTION (RECOMPILE) now works like it did in RTM, but without the serious bug that caused Microsoft to revert to the old behaviour in SP1. Therefore I've restored the new article for SQL 2008, and the old one is again labelled to be for SQL 2005 and earlier.

2009-07-09

In my SQL Server Wishlist, added request for getting the position in an XML document.

2009-06-29

In the article How to Share Data Between Stored Procedures, added a brief discussion on performance about INSERT-EXEC with dynamic SQL, and a reference to a blog post from SQL Server MVP Adam Machanic.

2009-06-25

Released version 12 of beta_lockinfo. I've added three columns to show information about the current transaction of the process. Also added one more spid column. I've also fixed textmode that was broken, and added better checks to the creation script to alert you when you don't meet the prerequisites.

2009-06-21

Released version 2.005 of Win32::SqlServer.

2009-06-20

Uploaded pictures from my trip to Japan.

2009-05-18

The section on INSERT-EXEC in How to Share Data... said that it does not work with table variables, which is right on SQL 2000 only.

2009-05-16

In the SQL Server wishlist, added an additional request with regards to domains.

Back to my home page.