If you come here regularly, you have probably noticed that my website 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.)
A number of smaller updates to the article Don't Let Your Permissions Be Hijacked!
Updated the section Using Extended Events to chapter seven in Part Three in the series Error and Transaction Handling in SQL Server with the links to Dave Mason's original blog posts about the idea, as he was kind to contact me to inform me of the new location.
Released version 2.015 of Win32::SqlServer, a module for accessing SQL Server from Perl. This release adds support for Perl 5.38 and 5.40.
Added a couple of upcoming items to my list of presentations. Most notable is that I will be speaking at the PASS Data Community Summit in Seattle. They've selected me for the seventh time consecutively!
Uploaded pictures from my vacation in April, which I mainly spent in the Basque Lands, but I also visitied Bordeaux and Paris. Lots of pictures from the beuatiful Basque landscape!
Added a section Using Extended Events to chapter seven in Part Three in the series Error and Transaction Handling in SQL Server to describe how you can use extended events to capture all error messages from for instance BACKUP/RESTORE and still be able to continue execution. Previously, I was linking to two blog posts from Dave Mason, but these posts are no longer available.
There is now a version of beta_lockinfo for Azure SQL Database.
An update to the section on Regular Expressions in my SQL Server Wishlist, since Microsoft now have announced a private preview for this feature.
Uploaded a brand-new article, Changing the Server and Database Collation in SQL Server. This relatively short (by my standards, that is!) article gives tips and provide scripts to help you with this very challenging task. Maybe not the article you would read for divertisment, but the day you need to do this, I hope you will find the article helpful.
Gee, it's been a lull, the first update in five months! But, no, no technical use. Just an announcement that I've uploaded this year's pictures. There are two sets: One for short travels to Verona, Edinburgh, Dublin and a few more places, and one set of summer pictures from the Stockholm area.
Very short updates to three articles: Part 2 of Error and Transaction Handling in SQL Server, Using the Bulk-Load Tools in SQL Server and Using Table-Valued Parameters in SQL Server and .NET.
Added a new item to the Video Section. There is now a recording of my most recent session, Don't Let Your Permissions Be Hijacked!.
A couple of updates to the article Packaging Permissions in Stored Procedures.
An update to the article Dynamic Search Conditions. From my fellow MVP Fabiano Amorim, I learnt that OPTION (RECOMPILE) works differently when you have variable assignment which I discuss in the section The History of Forced Recompilation.
Added the chapter Query Timeouts and INSERT-EXEC to Appendix 1 in the series Error and Transaction Handling in SQL Server.
I'm happy to present a new short story, The Power of Generate_series. This is a kind of a successor article to Using a Table of Numbers. In SQL 2022, Microsoft added the function generate_series which is a built-in table of numbers. The new article also covers the new functions datetrunc and date_bucket. The older article remains available for users on older versions, and has been slightly updated.
Updated the article Arrays and Lists in SQL Server, mainly to reflect changes in SQL 2022.
Added two new entries to The Video Section on my home page, as I have become aware of these videos being available on YouTube.
Updated my SQL Server Wishlist for changes in SQL 2022.
Updated the article Slow in the Application, Fast in SSMS, primarily with regards to new features in SQL 2022, but also because of input from Marshall Smith and Erwin Anema. There is also small update about PSP optimisation in the article Dynamic Search Conditions, but that section mainly refers to the new chapter on this feature in Slow in the Application.
A new version of beta_lockinfo with some modifications for SQL 2022 and Azure Managed Instance, and a few more adjustments.
Rewrote the section The Virtue of Getting All Result Sets in Part Three of Error and Transaction Handling in SQL Server, after having learnt about DataTable.Load(SqlDataReader) from Karen Payne. This call serves the same purpose as SqlDataAdapter.Fill(DataTable) but will not miss re-raised exceptions that occurs in a query.
Uploaded the pictures of 2022. Enjoy!
Much belated I have updated permission details about the SqlEventLog assembly for CLR Strict Security in Part Three of Error and Transaction Handling in SQL Server.
Daniel Adeniji pointed a few details in the short story Where Is that Table Used which prompted to review the article, and particularly the chapter Using the Plan Cache.
John Barrow was kind to supply a thorough proof-reading of the article The Curse and Blessings of Dynamic SQL, and I have corrected many spelling and grammar errors, of which some made the text somewhat unintelligible in places.
Released version 2.014 of Win32::SqlServer with support for MSOLEDBSQL19 and Perl 5.36.
Added the section When ;THROW Does Not Reraise All Errors to Part Two of the series Error and Transaction Handling in SQL Server.
The May issue of the SQLServerGeeks Magazine is out, and my article this month is A Tip about Linked Servers. Also, the article in the previous issue, A Tip about INTERSECT is now available as a blog post.
Eleventh public release of AbaPerls, label L1.0.0402. AbaPerls has not kept up well with all new features in SQL Server and does not fit into the age of Git, so with release AbaPerls enters a state of deprecation. Look at it for amusement and inspiration, but I don't recommend using AbaPerls for actual work.
An addition to the section Other Approaches in Part Three in the series Error and Transaction Handling in SQL Server with links to two blog posts by Adam Buckman with one more solution (well kludge) to get both error messages from BACKUP/RESTORE.
I have reworked the home page a little bit. There is now a section of Quick Links on top. I've added a section for the articles I've written for the SQLServerGeeks Magazine and which SQLServerGeeks later has republished on their blog. I have also added a video section where you find links to selected recordings of my presentations.
In other news, there is a small update (a deletion actually) in Part One in the series Error and Transaction Handling in SQL Server.
Earlier this month, the March issue of SQLServerGeeks Magzine was released, and I am contributing with the article A Tip about INTERSECT.
Two additions to Part Two in the series Error and Transaction Handling in SQL Server, and a very small clarification to Part Three.
The December issue of the SQLServerGeeks Magzine is out, and I am contributing with the article A Tip about Full Join.
Uploaded two sets of pictures from 2021: One set from short travels to Zagreb and Belgium, and one set with summer pictures from the Stockholm area.
Updated the short story Arrays and Lists in SQL Server to cover the third parameter recently added to string_split in Azure SQL Database.
I have refreshed all links to feedback items in my articles so that they point to the new feedback site on Dynamics 365 rather than the demised Uservoice site. In the process, I review my SQL Server Wishlist add removed some items and added some new ones. I also reviewed Appendix 1 about linked servers in the series Error and Transaction Handling in SQL Server with regards to SQL 2019 and MSOLEDBSQL. Finally, there are very small updates to Part Two in the same series and the article How to Share Data between Stored Procedures.
The November edition of the free commuity magainze SQL Server Geeks Magazine is out, and this month I'm contributing with A Tip about Packaging Permissions in Stored Procedures, which is a short introduction and teaser for the longer article.Packaging Permissions in Stored Procedures.
A small update to the article Packaging Permissions in Stored Procedures based on a suggestion from Michael K. Campbell.
A small update to Part Three of Error and Transaction Handling in SQL Server. (Related to the change on 2021-08-20 below.)
Extended the chapter Using the CLR in the article How To Share Data Between Stored Procedures with a description of the stored procedure ExecAndInsert which accepts an SQL batch which it executes and then saves the result sets into tables created from the metadata. Also added a brief mention of this procedure in the article The Curse and Blessings of Dynamic SQL.
Two updates to the series Error and Transaction Handling SQL Server. In Part Two, the section error_message() & co has been augmented to reflect changes in error_procedure() in SQL 2017 (which I did not discover until now). In Part Three, I have a loopback solution for Azure SQL Database that Daniel Verzellesi was kind to share with me.
The August edition of the free commuity magainze SQL Server Geeks Magazine is out, and this month I'm contributing with A Tip about Using Python for Regular Expressions in T-SQL.
Released version 2.013 of Win32::SqlServer.
There is a new edition of the free community magaine, SQL Server Geeks Magazine, and it includes a short article from me: A Tip on Writing UNION Queries.
Added a note to the section Advanced Options for Dynamic Searches in the article Dynamic Search Conditions with links to a presentation and a repository about framework to build advanced dynamic searches by Data Platform MVP Eitan Blumin.
A small update to the article Packaging Permissions in Stored Procedures related to application roles and EXECUTE AS WITH COOKIE.
I have new short article, A Tip to Optimise LIKE Searches, but for once it is not publised on my own site, but you find it in the SQL Server Geeks Magazine, which is a free community magazine. I encourage you to check it out!
I've made a general change, so files with code now have a final extension of .txt, preceded by the actual extension, and this the only one you see in the text of the article. For instaance, an article may have a link that says Northgale.sql, but the actual link goes to Northgale.sql.txt. Partly this is due to that my web host blocks access to .sql files (what I said on 2021-04-24 below turned out to be a mistake on their side), but I decided to do this on a general basis, since a link to for instance a .bat file could cause problems for some readers.
I moved my website to a new host earlier this month, and this resulted of an issue that I was unaware of until Christian Gray was kind to inform me. All attempts to reach the .sql files that accompany the articles were said to be missing and you got a 404 page. It turns that my new web host has a filter for this particular file type. However, this filter only applies to http, not https. For this reason, I have now set up the site so that http redirects to https, and all .sql files should again be available. My apologies for any inconvience.
Added the section The Effect of Transactions to the article Slow in the Application – Fast in SSMS? based on an experience that Daniel Lopez Atan had.
A small update to the series Error and Transaction Handling in SQL Server. There was a problem with the CLR loopback in SqlEventLog which I have corrected. There is a smaller update to Part Three (where I have removed the text referring to the code that was removed), and I have rewritten the subsection Invoking the Loopback from the CLR in the CLR Appendix where I discuss the issue a little more.
Corrected a small inaccuracy in the article How to Share Data between Stored Procedures.
Revised the section What If You Cannot Use a Function? in the short story Arrays and Lists in SQL Server after suggestions from Yithzak Khabinsky.
A small update to the article Packaging Permissions in Stored Procedures about a surprising situation with cross-database access and certificate signing.
There was a bug in two functions presented in my short story Arrays and Lists in SQL Server.
A small update to the article Using the Bulk-Load Tools in SQL Server to reflect that SQL 2019 supports UTF-8 collations.
A minor overhaul of the article Slow in the Application – Fast in SSMS to keep it up to datte
Published a brand-new article: Don't Let Your Permissions Be Hijacked! This is a security article about how a user with permissions to deploy triggers and stored procedures could get you to unknowingly execute malicious code that performs actions to the benefit of the attacker – and how you can defend yourself against these attacks. In conjunction with this there are also some smaller updates to the article Packaging Permissions in Stored Procedures and to the demo script in the chapter Letting Users Start Specific Jobs in the appendix in the aforementioned article.
Rewrote the Arrays and Lists in SQL Server, The Short Version, so it is now a little longer with more examples and solution for the situation where you cannot write a function. There are also some examples on how to build delimited lists from table data. The article has now been moved to the Short Stories section on my home page.
A fairly substantial overhaul of the article Dynamic Search Conditions with the prime objective to use the same version of Northgale as when I present on the topic and to renumber all stored procedures. In the process some material was shuffled around and I've added some new ideas and observations, and also removed sections I no longer find relevant.
One more addition to the article The Curse and Blessings of Dynamic SQL about the functions SanitizeObjectName and SanitizeObjectNameEx that John Smart was kind to share.
A small addition to the article The Curse and Blessings of Dynamic SQL about a script searchalldbases.sql which permits you to search for a string in all tables in all databases on an instance.
Added the section Catching a Misisng Table in the Same Scope to Part Two of Error and Transaction Handling in SQL Server.
Uploaded pictures from 2020. Due to the pandemic, mainly from the Stockholm area, but it also features Edinburgh.
Added a section on an alternate solution in the chapter on linked servers in the appendix to the article Packaging Permissions in Stored Procedures.
Published a new short story: Using a Table of Numbers that shows how of a one-column table of consecutive numbers (or dates or hours etc) can be a powerful asset in your database to help you to write certain type of queries
Version 26 of beta_lockinfo. A small adjustment to the update from yesterday.
Version 25 of beta_lockinfo. A small improvement to show the job name instead of a hexstring.
Minor updates to no less than four articles: Dynamic Search Conditions, The Curse and Blessings of Dynamic SQL, Slow in the Application, Fast in SSMS? and Using Table-Valued Parameters in SQL Server and .NET. The updates are on the same theme: the virtue of using two-part notation in dynamic SQL, not only with tables but all schema-bound objects, including user-defined types.
I've introduced a new section on my home page: SQL Short Stories. Eventually, that will be a page of its own, when I've written more of these short stories. These shorter articles will be small tips in a specific area. The first new article to appear here is Monitor Exceptions in Your Database, which was originally published in the PASS Insights newsletter (where the links to the scripts unfortunately were broken). The article Where Is That Table Used? has also been move to this new section.
The short article SearchCode has a new name: Where Is That Table Used? It has also morphed from mainly presenting a utility to build a full-text database to search for references, to a more general discussion of various ways to find references and thanks to input from Paul McMillan I now present the plan cache and Query Store as means to find references,
Released version 24 of beta_lockinfo. Nothing dramatic, just a few small enhancements.
It took one day... Jonathan Van Houtte was quick to come with a useful addition to the new version of The Curse and Blessings of Dynamic SQL.
I happy to announce a new article: The Curse and Blessings of Dynamic SQL. Yes, the title and the URL are old. But the article is new. The topic is the same as the old one, but it is not a rewrite or a revision, but essentially a new article. The chapter on SQL injection is a lot better. There is a chapter which is a style guide on writing dynamic SQL. And I've even written a brief introduction to dynamic pivot.
There is also a small update to the article Packaging Permissions in Stored Procedures.
Uploaded pictures from the summer in the Stockholm area and from shorter travels when going to SQL Server conferences.
An important update to the article Packaging Permissions in Stored Procedures. I have identified a few potential security issues in the script for granting server permissions permissions, and to eliminate those I have updated the script and also changed the recipe.
Added the section Database Settings to the article Slow in the Application – Fast in SSMS? to discuss how different database settings can affect the plan choice even when parameter sniffing is not involved.
Some minor modifications to Packaging Permissions in Stored Procedures to point a possible security risk with script GrantPermsToSP_Server.sql.
As an asmusement, added the section The Error that Can Only Be Caught to Part Two of Error and Transaction Handling in SQL Server.
Released version 2.012 of Win32::SqlServer with improved support for char and varchar, triggered by the introduction of UTF-8 collations in SQL 2019. There are now binaries for Perl 5.28 and Perl 5.30.
A small update to my SQL Server Wishlist about the status of the replacement message for String or binary data would be truncated.
Updated the section LINQ to SQL and Entity Framwork in the article Using Table-Valued Parameters in SQL Server and .NET.
Updated the statement to load the Lines table in the SearchCode utility for better performance and to exclude DDL triggers.
A small update to the section False Error Messges in Part Two of Error and Transaction Handling in SQL Server.
Some minor additions to the articles How to Share Data Between Stored Procedures and Dynamic Search Conditions.
Uploaded pictures from a two-week trip to Madiera and Lisbon.
A couple or minor corrections and additions to my article Slow in the Application, Fast in SSMS?
Uploaded pictures from year just gone by, ranging from April to December. Summer pictures as well as shorter trips for SQL Saturday and alike.
Updated my SQL Server Wishlist to reflect that two wishes have been granted in SQL 2019.
A new small utility SearchCode to store your SQL code in a full-text index table so you can search it efficiently.
An update to the section Could it Be MARS? in the article Slow in the Application, Fast in SSMS? It seems that we now have answer to why MARS can slow things down – network latency.
Updates to the articles Dynamic Search Conditions and Arrays and Lists and SQL Server, both The Short Version and The Long Version. For all three articles the changes were prompted by the introduction of interleaved execution of multi-statement functions and CLR Strict Security in SQL 2017. I also made a few more changes to them while I was at it, as detailed in the Revision section of each article.
Updated the text on the -q option for BCP in the article Using the Bulk-Load Tools in SQL Server to reflect that this option has a second behaviour of which I was not previously aware.
A new version of sp_sqltrace with two fixes for wait-stats information.
Made a revision of Appendix 1 in the series Error and Transaction Handling in SQL Server to cover input from Robin Otte and other findings.
Reviewed all articles with regards to links to the defunkt Connect site and replaced them with links to the new Uservoice site, or simply removed the links. I have not made entries into the Revision History section for the articles where this was the only change. However, for three articles there were also smaller updates to the contents as a consquence of the review:
Updated my SQL Server Wishlist so that all links to point to the new feedback site on Uservoice. In the process, I reviewed the wishlist and added and removed a few. I also replaced Connect links in the articles Ideas for SET STRICT_CHECKS ON and Why Read-only Table Parameters is Not Enough and also reviewed the contents. Particularly, I introduced a new idea how to deal with temp tables with strict checks.
Alexey Guzev has been kind to provide a Russian translation of Part One of Error and Transaction Handling in SQL Server.
Released version 2.011 of Win32::SqlServer with support for Perl 5.26 and the new MSOLEDBSQL provider.
Made some updates to Part Two of Error and Transaction Handling in SQL Server prompted by input from Nick Reilingh.
I'm back from New Zealand and the full story with all pictures from the trip is now available and finalised.
Uploaded the first set of pictures of the trip I started in Singapore, and of which the main focus is New Zealand. I hope to upload pictures as the trip goes on. The last stop on the trip will be SQL Saturday in Victoria and the MVP Summit in Seattle, but don't expect pictures from these places.
Some small modifications to the article Packaging Permissions in Stored Procedures.
Uploaded a set of pictures I took in 2017 in Copenhagen, Wrocław, Oslo, Sofia, Seattle, Ljubljana. And of course there is regular set of summer pictures from the Stockholm area.
Three minor updates to the article Slow in the Application – Fast in SSMS.
Replaced the old article Granting Permissions through Stored Procedures with a new called Packaging Permissions in Stored Procedures. The topic is the same, but I wrote the article from scratch and the style of presentation is entirely different. Rather than long pieces of demo code, you get it piece by piece. The article is about 60 % long, and I believe the biggest reason is that I hold a slower pace, so that the material is easier to digest. There is also an appendix to the article, where I showcase the use of certificates in specific situations, including special situations like activation procedures for Service Broker, contained databases and letting users to start a job. Speaking of the latter: this was also covered in the old article which included a bad idea to solve this with a jump database owned by sa. Bad, because I suggested that you could not create a user to impersonate sa. I was wrong, and this is covered in the new article in the section about cross-database ownership chaining.
The URL for the new article is the same as the old.
Added a section Could it be MARS? to the article Slow in the Application – Fast in SSMS.
Added one more Connect item to the section Get the Call Stack in My SQL Server Wishlist.
The tenth public release of AbaPerls, label L1.0.0360.
Corrected an error in the section Finding Information About Statistics in the article Slow in the Application, Fast in SSMS? pointed out by Gordon Griffin. You can only use a column name as an argument to DBCC SHOW_STATISTICS if there is auto-created statistics on it.
A minor addition to the article Using Table-Valued Parameters in SQL Server and .NET to cover a performance gotcha when passing a DataTable as a parameter, as discussed in a blog post from SQL Server MVP Dan Guzman.
Uploaded a new version of the article How to Share Data Between Stored Procedures. It's a general overaul to keep up with the times, so that features introduced in SQL 2012 are no longer presented as "new, but there is also some new and revised content. See the Revision section for details.
Added a caveat about column names with spaces to the section Generating a Format File in the article Using the Bulk-Load Tools in SQL Server.
Added one Connect item for BEFORE triggers to my SQL Server Wishlist.
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.
Two updates to the series Error and Transaction Handling in SQL Server.
A minor correction to two scripts in the article Giving Permissions through Stored Procedures.
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.
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.
Uploaded some pictures from Victoria, BC, Canada and Parma, Italy.
Changes to no less than three articles:
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.
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.
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.
Uploaded some summer pictures from Stockholm (and a few from elsewhere).
Add a new article to the website: 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.
Minor updates to the SQL Server Wishlist about auto-updated columns and built-in table of numbers.
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.
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.
Released version 2.010 of Win32::SqlServer.
Put up pictures from my trip to the zone around Chernobyl power plant when I was in Kiev for SQL Saturday.
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.
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.
One more small fix sp_sqltrace: it can now be created on a server with an SC collation.
Bugfix in sp_sqltrace: @snoop_time > 2147 resulted in an overflow error.
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.
Geovanny Hernandez have been kind to translate Part One of Error and Transaction Handling in SQL Server into Spanish.
Some small corrections to the article Dynamic Search Conditions.
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.
Added Devart's ODBC driver to the page about accessing SQL Server from Unix.
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.
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.
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.
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.
Version 19 of beta_lockinfo. Fixes for a potential overflow and a workaround for locked metadata in SQL 2014.
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.
Uploaded photos from my vacation in Ukraine and then there is also the traditional bag of summer pictures.
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.
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.
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.
Updates to three articles:
'.' and '(./text())[1]'.There was a small error in the function setoptions that's attached to the article Slow in the Application, Fast in SSMS.
The ninth public release of AbaPerls. Lots of new features and improvements, start with the Compact History.
Added a new item to the SQL Server wishlist: Add a Virtual Errors Table.
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.
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.
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.
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.
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.)
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.
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.
Released version 2.008 of Win32::SqlServer, an API to SQL Server for Perl. See the History file for changes and new features.
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.
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, which is 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.
Added a note about SQL Server Data Tools and sharing temp tables to the article How to Share Data Between Stored Procedures.
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.
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.
Triple release:
Refreshed the article Connecting to MS SQL Server from Unix to reflect that Microsoft has released a CTP of an ODBC driver for Linux.
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.
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.)
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.
Released version 2.007 of Win32::SqlServer.
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.
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.
There is a now a Russian translation available of Slow in the Application, Fast in SSMS. Thanks to Dima Piliugin for this work!
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.
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.)
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.
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.
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.
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.
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.
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.
Released version 13 of beta_lockinfo. Bugfixes, and added a column to show tempdb usage.
Released version 2.006 of Win32::SqlServer.
The seventh public release of AbaPerls. AbaPerls users can see the list of recent changes here.
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.
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.
Updated the section on the OVER clause in the SQL Server Wishlist, with a new Connect item from Iztik Ben-Gan. Votes are encouraged!
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.
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.
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).
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.
Added a request for scoped variables to my SQL Server Wishlist.
Added a request for QUALIFY to permit for more concise queries to my SQL Server Wishlist.
Uploaded various pictures from 2009, mainly from summer in Stockholm and around.
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.
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.
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:
Two new items:
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.
In my SQL Server Wishlist, added request for getting the position in an XML document.
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.
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.
Released version 2.005 of Win32::SqlServer.
Uploaded pictures from my trip to Japan.
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.
In the SQL Server wishlist, added an additional request with regards to domains.