Celebrating the first day of Summer with the first technical post on the blog since mid-Winter (apologies for my absence).. Jens has a great post regarding the internal behavior of various forms of sp_-named programmability objects. Some SQLRAP-related research led me to take a different tack, and...

A bit of old news, as I return to the blog in earnest after some time away.. Last summer, in the posts Database Programming: What I’ve Learned About SQL Server 2008 (with a little on SQL Server 2005 thrown in) and Database Programming: Why I Like MERGE , I commented that I’d been able to build a MERGE...

UPDATED 11:11p 7 September 2009: Based on my latest exchange with Adam (see the comments for this post), compound operators should be avoided for string concatenation due to the ORDER BY bug discused in KB 287515 . The XML variant below is the preferred approach for string concatenation, but the compound...

Courtesy of Jens Suessmeyer comes this little gem purloined from an internal discussion; cut-and-paste this into SQL Server Management Studio and see if you can answer the questions in the comments before you execute the script: --   create and populate a table variable DECLARE @Demonstration TABLE...

Hot on the heels of yesterday’s MERGE discussion comes a complementary (and far more detailed) take from Adam Machanic .  It’s a nifty technical discussion, under the banner of an outstanding Dr. Strangelove pun. To see some of the true power of MERGE (and to share Adam’s joy in releasing his MERGE...

In the wake of last month’s post on my new development efforts in SQL Server 2008, several of you have asked me to go into further detail.  This post is my first effort to answer those questions; right now, we’ll focus on these two , which relate to this statement: By using a CTE with a MERGE and...

With SQLRAP 2.5 out the door a couple of weeks ago, I’ve been free to turn to another project, SIPA, an internal effort to automate storage and retrieval of our group’s diverse intellectual property catalog. I’m acting as both the database architect and one of the database implementers on the project...

Now go away, or we shall taunt you a second time. - John Cleese as a French knight (picture at left); Monty Python and the Holy Grail One of the reasons I started blogging over four years ago was my conviction that the coding challenges with which my employer was presenting me, while stimulating and...

Make a note..  here’s my first useful programming tip for SQL Server 2008 (and yes, there will be more to come; I’m about a week and a half into my first SQL Server 2008 development project).. Way back in February of aught-seven, I shared Jerry’s solution for running the .modify() method against...

UPDATED 13 April 2009: The SAPWD parameter sets the sa password; use BLANKSAPWD if you don’t wish to set one.  I’ve updated the seventh paragraph below accordingly. One of our responsibilities in the ITOE IP Architects group (the artists formerly known as the Exchange, SQL Server, and Windows Centers...

One of the most popular posts in the history of this little corner of the Internets is one from August, 2005, which describes a method for accessing stored procedure output in a SELECT statement which I’ve come to refer to as “ the OPENROWSET trick .” On the occasion of this blog’s 750th post(!),...

And I remember what she said to me How she swore that it never would end I remember how she held me, oh so tight Wish I didn't know now what I didn't know then Bob Seger, Against the Wind All you need is love Love is all you need John Lennon and Paul McCartney, All You Need is Love My pal Jimmy May apparently...

As we progress towards the Ides of March, one of my best friends-who-I’ve-never-met, Jimmy May , notes our annual transition to Daylight Savings Time , in a post he generously concludes with a link to one of this very blog’s oldies but goodies . In his blog’s title, Jimmy humbly refers to himself...

Jens Suessmeyer is a Microsoft Consultant in Germany who frequently shares his useful techniques and insights both inside Microsoft and in the community at large.  In his latest post , he answers a colleague’s question with a simple yet thorough repro which proves the titular point. Here’s the money...

Never let it be said that Adam Machanic lacks style.. Adam left a comment on yesterday’s revisiting of the XML String Concatenation Trick, announcing his T-SQL Challenge: Grouped String Concatenation contest. Read Adam’s post to get all of the particulars for the event.  I hope to be able to play...

UPDATED 27 February 2009 for spelling I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my blog roll shortly after I get this posted (second attempt; first thwarted by a laptop hang. I have suspended unit testing of SQLRAP 2.5 changes until I’m...

In light of the current state of the economy (not to mention recent news in my own back yard (I can still call it that since we continue, against our deeply held wishes, to own a house in Redmond)), I make it a point to count my blessings every day.  A loving and supportive family is primary among...

This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function. I learned this from Dirk Gubbels when he sent me a follow-up email to the seasonal T-SQL he passed along (the posting of which spawned a small flood ). This “trick” is actually a SQL Server 2005 enhancement, so it...

Bert left a comment on this post pointing out a limitation of “the other OPENROWSET trick” (the one which loads an XML file into a single XML column): I tried this, but had an error because my XML file is 6gB. The SINGLE_BLOB seems to be limited to 2gB Bert is correct; fortunately, this is easily explained...

I had a couple of extra minutes today and found this post on Linchi Shea’s blog ; I wanted to commend to all of you who’ve taken in the programming methodology and set-based thinking discussions in this little corner. While I disagree some with the point in the main post about views (JOINing views...

This is another one of those tricks that’s been available since the earth was cooling, but I just discovered it recently. So, here’s a history lesson in the form of a small T-SQL script, with the moral contained in the title of this post: set nocount on use master select object_id ( 'sysobjects...

Fulfilling my promise at TechEd , I’ve finally completed a self-directed demonstration of the database versioning techniques I first presented at last year’s TechEd and which was alluded to during this year’s TechEd SQL Tricks presentations. It’s published here on my resource page at MSDN Code Gallery...

Kyle Schoonover, a former colleague in MSIT, posed the following questions in a comment on October’s cross-version database attachments post : Ward, I'm currently working in MySQL and writing conversion scripts to convert a MySQL database to a SQL 2005 database.  I have also been using OpenQuery...

Binh Cao's customer left a comment on yesterday's post on global temp tables. Here's the money quote: My research confirms that global temp table does allow the plan to be shared among processes. However now we are forced to deal with the problem of ownership of data within the global temp table...

One of the great things about my involvement with the SQL Ranger community is that I occasionally hear from past Candidates asking for help with issues in the field. So it was earlier this week when Binh Cao, an MCS Senior Consultant II based in San Diego, looped me in on a question his customer was...