SQLblog.com - The SQL Server blog spot on the web

Manipulate XML data with non-xml columns and not using variable

Peter Larsson — Fri, 03 Jul 2009 09:57:15 GMT

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column.
Here Some XML search approaches and here Updated XML search (test case with variables).

Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference.
And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about something necessary.

Much of the XML manipulations I have made by trials and errors, because I haven’t found a single good source of information about these things. If you do know of such information, please comment.

First, we create a sample table to hold some important data, like this

CREATE TABLE    #Sample
                (
                    RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                    MemberID INT,
                    MemberData XML
                )

INSERT #Sample
SELECT 1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL
SELECT 2, '<meta><age>24</age></meta>'

CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)

CREATE XML INDEX IX_Element ON #Sample(MemberData)
USING XML INDEX IX_PrimaryXML FOR PATH

As you see, I also created the primary XML index and a secondary XML index on the XML column.
Now let’s see what is stored in the important table.

SELECT MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample

The exercise now is to add one or more elements to the XML column, both with using a variable but also, more importantly, using a column. Create a helper table like this below.

DECLARE @Sample TABLE
        (
            MemberID INT,
            ZipCode VARCHAR(5),
            Mosaic VARCHAR(200)
        )

INSERT @Sample
SELECT 1, '26737', 'SQLTeam' UNION ALL
SELECT 2, '12345', 'Smart woman in the countryside'

When an element doesn’t already exist, the element is added to the XML column. Beware that an additional element is created if one alerady exists!

-- New element for all
UPDATE      s
SET         MemberData.modify('insert <zipcode>{sql:column("x.ZipCode")}</zipcode> into (/meta)[1]')
FROM        #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID

Now do the SELECT again and you will see that a zipcode element was added to both records.

SELECT MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample

As I said before, if an element already exist a new elemtent with same name is added. This behaviour can be avoided by using a WHERE MemberData.exists clause. Do the updates first since it only touches existing records with matching elements and then do the inserts with the MemberData.exists clause.

-- Update existing element
UPDATE      s
SET         MemberData.modify('replace value of (/meta/mosaic/text())[1] with sql:column("x.Mosaic")')
FROM        #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID

-- Insert new element. Beware of duplicates.
UPDATE      s
SET         MemberData.modify('insert <mosaic>{sql:column("x.Mosaic")}</mosaic> into (/meta)[1]')
FROM        #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID

Now do the SELECT again and you will see that the element mosaic was updated for member 1 and added to member 2. This is also true for member 1 because now this member has two elements with same name.

SELECT MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample

And this was my reason for this blog post. How to add and replace elements with values from another column. So why not display how to do this with variables too?

DECLARE @Status VARCHAR(18)

SET         @Status = 'Shipped'

-- Update single record without prior element
UPDATE      #Sample
SET         MemberData.modify('replace value of (/meta/status/text())[1] with sql:variable("@Status")')
WHERE       MemberID = 1

-- Create new element for single record
UPDATE      #Sample
SET         MemberData.modify('insert <status>{sql:variable("@Status")}</status> as last into (/meta)[1]')
WHERE       MemberID = 2

As you can see now, same rules apply. Member 1 did not get a new Status element since we wanted to update previous value. But member 2 did get a new element.

SELECT MemberID,
        MemberData,
        MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
        MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
        MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
        MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
        MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM    #Sample

Don’t forget to do your housecleaning and drop the important table.

DROP TABLE #Sample

Besides doing update and insert, you can also use the delete syntax to remove elements if you jus don’t to clear them.
I hope you liked this post.

Cheers!

Leeds Area SQL Server User Group Meeting, Leeds - Monday 20th July

MartinBell — Fri, 03 Jul 2009 06:39:00 GMT

Managing a large SQL Server estate If size matters Martin Cairney would have the bragging rights!! Martin Cairney Martin Cairney has over 10 years experience as a SQL Server DBA. In his time he has worked for one of the largest IT services companies in the world deploying and supporting SQL Server installations from versions 6.5 through to 2005. Martin has supported some the SQL Server estate of some major Government Departments and Financial Institutions as well as a variety of occasional consultancy...(read more)

SQL Nuggets

MartinBell — Fri, 03 Jul 2009 06:15:00 GMT

What are SQL Nuggets?...(read more)

WCF-SQL Adapter fails while inserting rows to a table that has an identity column.

Randy Aldrich Paulo — Fri, 03 Jul 2009 03:58:25 GMT

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we've tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I'm configuring the mySAP adapter, I found out that license has expired, since we've downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can't find the 1.0 version that's why I've installed the Adapter pack 2.0. I was a little bit surprise to see that there's a new SQL Adapter included. So I've installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you're trying to add rows to a table that has identity column.

Database Programming: The Time Zone Conversion Beast, Once Comatose, Returns to Taunt Us a Second Time (or, An Answer for Iain)

Ward Pond — Thu, 02 Jul 2009 23:15:00 GMT

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...(read more)

DRY SQL

jamie.thomson — Thu, 02 Jul 2009 17:05:00 GMT

I recently inherited some SQL that someone else had written and had the job of “tidying it up” before it gets pushed out to production. Here’s a slightly simplified (yes, simplified) version of that SQL:

   1: --options

   2: select    asset_class

   3: ,        case    when volume > 0 then 'profit'

   4:                 else 'loss'

   5:         end as profit_or_loss

   6: ,        customer

   7: ,        sum(volume) as total_volume

   8: from    t1

   9: where    asset_class = 'options'

  10: group    by

  11:         asset_class

  12: ,        case    when volume > 0 then 'profit'

  13:                 else 'loss'

  14:         end

  15: ,        customer

  16: union    all

  17: --swaps

  18: select    asset_class

  19: ,        case    when volume > 0

  20:                     then 'profit'

  21:                 else 'loss'

  22:         end as profit_or_loss

  23: ,        customer

  24: ,        sum(volume) as total_volume

  25: from    t2

  26: where    asset_class = 'swaps'

  27: group    by

  28:         asset_class

  29: ,        case    when volume > 0 then 'profit'

  30:                 else 'loss'

  31:         end

  32: ,        customer

The SQL basically takes data from two tables (t1 & t2), aggregates each, carries out some inline expressions (for profit_or_loss) and finally unions it all together. On the surface it looks fine but there are a few problems here, namely that there is a lot of repeated code; it violates the principle of don’t repeat yourself (DRY) which preaches “single point of maintenance” and “deduplication” of code. If you take the time to check it out you’ll see that identical aggregations are carried out on the two datasets (lines 7 & 24) as are the same conversions for profit_or_loss (lines 3-5 & 19-22). Not only that but we have expressions appearing in both the SELECT clause and GROUP BY of both halves of the query (lines 12-14 & 29-31), another violation of DRY.

A bit of refactoring is called for. First job, eliminate those expressions for profit_or_loss which appear in the GROUP BY clauses:

   1: --options

   2: select    asset_class

   3: ,        profit_or_loss

   4: ,        customer

   5: ,        sum(volume) as total_volume

   6: from    (

   7:         select    asset_class

   8:         ,        customer

   9:         ,        case    when volume > 0 then 'profit'

  10:                         else 'loss'

  11:                 end as profit_or_loss

  12:         ,        volume

  13:         from    t1

  14:         where    asset_class = 'options'

  15:         )t1

  16: group    by

  17:         asset_class

  18: ,        profit_or_loss

  19: ,        customer

  20: union    all

  21: --swaps

  22: select    asset_class

  23: ,        profit_or_loss

  24: ,        customer

  25: ,        sum(volume) as total_volume

  26: from    (

  27:         select    asset_class

  28:         ,        customer

  29:         ,        case    when volume > 0 then 'profit'

  30:                         else 'loss'

  31:                 end as profit_or_loss

  32:         ,        volume

  33:         from    t2

  34:         where    asset_class = 'swaps'

  35:         )t2

  36: group    by

  37:         asset_class

  38: ,        profit_or_loss

  39: ,        customer

Ok cool, expressions have gone from our GROUP BY clauses but we’ve still got DRY violations. The aggregation (lines 5 & 25) and expression for profit_or_loss (lines 9-11 & 29-31) still appear in two places. More refactoring….

   1: select    asset_class

   2: ,        customer

   3: ,        sum(volume) as total_volume

   4: from    (

   5:         select    asset_class

   6:         ,        case    when volume > 0 then 'profit'

   7:                         else 'loss'

   8:                 end as profit_or_loss

   9:         ,        customer

  10:         ,        volume

  11:         from    (

  12:                 --options

  13:                 select    asset_class

  14:                 ,        customer

  15:                 ,        volume

  16:                 from    t1

  17:                 where    asset_class = 'options'

  18:                 union    all

  19:                 --swaps

  20:                 select    asset_class

  21:                 ,        customer

  22:                 ,        volume

  23:                 from    t2

  24:                 where    asset_class = 'swaps'

  25:                 )t

  26:         )q

  27: group    by

  28:         asset_class

  29: ,        customer

There, much better; the aggregation only occurs once as does the expression for profit_or_loss (which in the code that I inherited occurred four different times). Our SQL is DRY and its going to be a lot easier to maintain for whomever picks the code up from me.

You’ll notice we’ve got 2 levels of nested subqueries (aka derived tables). I make no apologies for that - derived tables are a great mechanism for eliminating repeated code and if you take but one bit of advice away from this blog post it would be this: derived tables are your friend.

Would you have refactored this the same way? Maybe you might have moved the derived tables into a dedicated view. Perhaps you might even have put the derived tables into a WITH clause like so:

   1: with    t as (

   2:         --options

   3:         select    asset_class

   4:         ,        customer

   5:         ,        volume

   6:         from    t1

   7:         where    asset_class = 'options'

   8:         union    all

   9:         --swaps

  10:         select    asset_class

  11:         ,        customer

  12:         ,        volume

  13:         from    t2

  14:         where    asset_class = 'swaps'

  15: ),

  16: q as    (

  17:         select    asset_class

  18:         ,        case    when volume > 0 then 'profit'

  19:                         else 'loss'

  20:                 end as profit_or_loss

  21:         ,        customer

  22:         ,        volume

  23:         from    t

  24: )

  25: select    asset_class

  26: ,        customer

  27: ,        sum(volume) as total_volume

  28: from    q

  29: group    by

  30:         asset_class

  31: ,        customer

Let me know in the comments!

@Jamiet

The next SQLbits

simonsabin — Wed, 01 Jul 2009 20:43:08 GMT

We'll be announcing the date and venue for the next SQLBits on Monday

So keep posted, we've got some really exciting news.

I am really excited about the whole event. Its going to be bigger and better than the others.

The Best Thing I Learned At PASS

noreply@blogger.com (Kendal Van Dyke) — Wed, 01 Jul 2009 19:00:00 GMT

Note: I'm writing this as part of the "Best Thing I Learned At PASS" contest. Why? Because if I win it's worth a free registration or hotel stay for the 2009 Summit, and I like free!

Let me start by making a small confession: I've never actually been to the PASS summit in person. So how in the world can I write about what I've learned? Because I've been there vicariously through the blogs and Twitter streams of people who were there last year. Thank goodness, because before the rise of blogging\social networking if you had asked me what happens at the PASS summit I would have said…I honestly don’t know what happens at PASS besides a bunch of technical presentations given by people who write books and magazine articles for a living. My ignorance about what PASS is became clear to me last November as I read about the daily keynotes, the community sessions, and the nighttime parties. I came to realize that PASS is about so much more than just sitting in technical presentations. It’s about learning from people just like you who are doing the same things as you do every day. It’s about uniting with your peers and meeting new people who share a common interest. It’s about escaping the daily workload to reenergize and reinvigorate the passion to do our jobs as best as we possibly can when we return. So even though I haven’t been to PASS in person, I’ve learned one important thing: For the sake of my career, I need to go to PASS. If I can figure that out without actually having been to PASS, just imagine what I’ll learn when I do go this year.

ROW Constructors are dead, long live Table Value Constructors!

MartinBell — Wed, 01 Jul 2009 17:48:00 GMT

New documentation on ROW CONSTRUCTORS indicate the feature has had a name change to Table Value Constructors...(read more)

When in doubt, Reboot!

psssql — Wed, 01 Jul 2009 17:12:13 GMT

I tend to get quite a bit of Kerberos related cases. These are related across the box, from the Engine, to Reporting Services to just straight connectivity with custom applications. I had one given to me yesterday because the engineer had gone through everything we normally go through and wasn’t getting anywhere.

The situation was an 8 node cluster with multiple instances across the nodes. These were running Windows 2008 with SQL 2008. One node in particular was having an issue when they were issuing a Linked Server Query from a remote client.

When trying to hit the linked server from within Management Studio on the client machine, we received the following message:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Kerberos Configuration:

When we see this type of error, it is typically Kerberos related as the Service we are using (ServerA) is unable to delegate the client’s credentials to the backend server (ServerB – Linked Server). The first thing we do is go through our regular kerberos checklist – SPN’s and Delegation settings. Both SQL Servers were using the same Domain User Service Account (SNEAKERNET\SQLSvc). We can use SetSPN to check what SPN’s are on that account. NOTE: There are numerous ways to look for SPN’s but SetSPN is one of the easier command line tools available. You could also use LDIFDE (http://support.microsoft.com/kb/237677), ADSIEdit (http://technet.microsoft.com/en-us/library/cc773354(WS.10).aspx) and other tools. You will see us use an in house tool called DHDiag to collect SPN’s. This is just a wrapper that calls LDIFDE to output the results.

So, here are the SetSPN results:

C:\Users\Administrator>setspn -l sqlsvc
Registered ServicePrincipalNames for CN=SQL Service,OU=Service Account,DC=sneakernet,DC=local:
        MSSQLSvc/SQL02:26445
        MSSQLSvc/SQL02.sneakernet.local:26445
        MSSQLSvc/SQL01.sneakernet.local:14556
        MSSQLSvc/SQL01:14556

Why do we see SQL01 and SQL02 when our machine names are ServerA and ServerB? This is because SQL01 and SQL02 are the virtual names for the cluster. This name will move to whatever the active node is for that given instance. Where as ServerA and ServerB are the physical machine names and may or may not be actually hosting that instance. We can also see from this that we have two distinct instances because of the ports (14556 & 26445). If you look at some of our documentation (i.e. http://msdn.microsoft.com/en-us/library/ms189585(SQL.90).aspx), it indicates that for clusters, you need to also add a SQL SPN that does not include the port number. I have yet to see where this is actually needed. Every cluster I’ve seen has never had one. Typically if it is needed, you will receive a KRB_ERR_S_PRINCIPAL_UNKNOWN error if you enable Kerberos Event Logging. If you do see that and it lists that SPN, then go ahead and add it. But, from my experience, you won’t see it.

Ok, our SPNs look good. Lets look at our Delegation Settings. In this case we really care about the SQL Service Account, because that is the context that will be performing the delegation.

We can do this by going to the properties for that account within Active Directory Users and Computers. You will see a Delegation tab on the account. If you don’t see the delegation tab, then the account does not have an SPN attached to it. In this case we have “Trust this user for delegation to any service (Kerberos only)”. This is what I call Full or Open Delegation as opposed to Constrained Delegation (which is more secure). We are good to go here. Nine times out of ten, the SPN or Delegation setting is going to be the cause of your issue. In this case it isn’t. What can we do now?

Kerberos Event Logging and Network Traces:

We can enable Kerberos Event Logging (http://support.microsoft.com/default.aspx?scid=kb;EN-US;262177) which will give us errors within the System Log for Kerberos. This can sometimes be very helpful in diagnosing what may or may not be happening. This produced the following results on ServerA:

Error Code: 0x1b Unknown Error
Error Code: 0x19 KDC_ERR_PREAUTH_REQUIRED
And KDC_ERR_BADOPTION

These are not uncommon and when we looked at these, they didn’t really relate to our issue. Which means we had nothing here. Of note, doing a linked server query from ServerB to ServerA worked, and it also produced the same events listed above. So, nothing to gain here.

The next thing we can look at is getting a network trace as this will show us the communication between Service in question and the Domain Controller. I usually end up at this level if the SPN’s and Delegation settings check out. This is really where some customers can have issues, because typically these are hard to interpret and will require a call to CSS. We grabbed a trace in the failing and working condition to see what was different. We saw the following:

Failing:
525355 2009-06-30 15:55:39.468865 10.0.0.90 10.0.0.10 KRB5 TGS-REQ
KDC_REQ_BODY
KDCOptions: 40810000 (Forwardable, Renewable, Canonicalize)
Realm: SNEAKERNET.LOCAL
Server Name (Enterprise Name): ServerA$@SNEAKERNET.LOCAL

Working:
353115 23.437037 10.0.0.20 10.0.0.11 KRB5 TGS-REQ
KDC_REQ_BODY
KDCOptions: 40810000 (Forwardable, Renewable, Canonicalize)
Realm: SNEAKERNET.LOCAL
Server Name (Service and Instance): MSSQLSvc/SQL02.sneakernet.local:26445

You’ll notice that we are hitting different DC’s here, but that wasn’t the issue as we also saw the failing one hitting different DC’s as we continued. The other item that is different is the working one requested the right SPN, where as the failing one is requesting the physical machine account context. This is what was forcing us into NTLM and causing the Login failed error. But why was that happening? So far we have zero information to indicate what could be causing it.

SSPIClient:

We then used an internal tool called SSPIClient which makes direct calls to the InitializeSecurityContext API call which is how we do impersonation. This tool allowed us to take SQL Server out of the picture and focus on the Kerberos issue directly. We could see that we were failing back to NTLM which really confirmed what we saw in the network trace.

2009-07-01 16:34:24.577 ENTER InitializeSecurityContextA
2009-07-01 16:34:24.577 phCredential              = 0x0090936c
2009-07-01 16:34:24.577 phContext                 = 0x00000000
2009-07-01 16:34:24.577 pszTargetName             = 'MSSQLSvc/SQL02.sneakernet.local:26445'
2009-07-01 16:34:24.577 fContextReq               = 0x00000003 ISC_REQ_DELEGATE|ISC_REQ_MUTUAL_AUTH
2009-07-01 16:34:24.577 TargetDataRep             = 16
2009-07-01 16:34:24.577 pInput                    = 0x00000000
2009-07-01 16:34:24.577 phNewContext              = 0x0090937c
2009-07-01 16:34:24.577 pOutput                   = 0x0017d468
2009-07-01 16:34:24.577 pOutput->ulVersion        = 0
2009-07-01 16:34:24.577 pOutput->cBuffers         = 1
2009-07-01 16:34:24.577 pBuffers[00].cbBuffer   = 52
2009-07-01 16:34:24.577 pBuffers[00].BufferType = 2 SECBUFFER_TOKEN
2009-07-01 16:34:24.577 pBuffers[00].pvBuffer   = 0x02c99f90
2009-07-01 16:34:24.578 02c99f90 4e 54 4c 4d 53 53 50 00 01 00 00 00 97 b2 08 e2   NTLMSSP.........
2009-07-01 16:34:24.578 02c99fa0 03 00 03 00 31 00 00 00 09 00 09 00 28 00 00 00   ....1.......(...
2009-07-01 16:34:24.578 pfContextAttr             = 0x00001000 ISC_RET_INTERMEDIATE_RETURN
2009-07-01 16:34:24.578 ptsExpiry                 = 0x0017d43c -> 2009-07-01 10:39:24 *** EXPIRED *** (05:55:00 diff)
2009-07-01 16:34:24.578 EXIT InitializeSecurityContextA returned 0x00090312 SEC_I_CONTINUE_NEEDED (The function completed successfully, but must be called again to complete the context)

NOTE: We purged all of the Kerberos Tickets before we did this to make sure we would request the ticket from the KDC. This was done using KerbTray which is part of the Windows Resource Kit.

This tells us that we were requesting a given SPN for the Target, but the buffer shows NTLMSSP. This means we fell down to NTLM instead of getting Kerberos. This still doesn’t explain why.

End Result:

Unfortunately, this was one of those issues that just escaped us. This tends to happen with odd Kerberos cases. We had the Directory Services team engaged as well and they did not know what else we could do in terms of data collection outside of a Kernel Dump to see what may be going on. We noticed that the nodes had not been rebooted since April 5th which is a while. The SQL Service was recycled on June 25th. We decided to fail over to another node and reboot ServerA. After we rebooted, we tried SSPIClient again and we saw a proper response come back which also didn’t list EXPIRED. The issue at this point it was resolved. We don’t have hard data to indicate what exactly the issue was, but the thought is that something was cached and invalid causing the issue. Rebooting cleared that out and allowed us to work as expected.

Which leads me to my motto: When in doubt, Reboot!

Adam W. Saxton | Microsoft SQL Server Escalation Services

Microsoft MVP

Peter Larsson — Wed, 01 Jul 2009 12:33:00 GMT

Today it happened. I received the Microsoft MVP Award for my contributions to the Microsoft SQL Server community.
I am very honored by the award and I will continue to work hard for the community to keep their trust in me.

I will continue to update this blog and help users out in the SQLTeam forums, SqlServerCentral forums, SQL Server Magazine forums and SQL Server Developer Center forums among a few other.

Thank you to everyone who reads this blog and leave comments. I still learn something new every day.

Best Regards,
Peter Larsson

July Vacation

paul.nospam@nospam.sqlskills.com (paul) — Wed, 01 Jul 2009 11:12:00 GMT

Well - the first half of the year is over and it's been a pretty busy time for Kimberly and I. I only managed to get one day at home in June but I'm at home for whole month of July, and this year I'm taking a proper break. I'm going to be essentially dark through July - no blogging, no Twitter, no forums or newsgroups - so don't expect to hear from me until August.

Hope you have a great summer, and Happy July 4th for those in the US.

Cheers

Looking for security vulnerabilities in database code

kimberly.nospam@nospam.sqlskills.com (kltripp) — Tue, 30 Jun 2009 11:34:00 GMT

I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters (fyi - using QUOTENAME can be a fantasic solution to protectng identifiers as input parameters but it can't protect more complex strings).

Having said that, what if I'm looking at a database for the first time... just poking around trying to see if there's anything that needs further attention? I've come up with a quick query... And, while it's not going to "solve" your problem (as that's going to take some re-writing of code) or even truly verify if you're vulnerable, it gives you a "quick list" of where you should look first! If your code uses dynamic strings AND it's elevated - then start there!

SELECT OBJECT_NAME(object_id) AS [Procedure Name],
CASE
    WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' THEN 'WARNING: code contains EXEC'
      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' THEN 'WARNING: code contains EXECUTE'
END AS [Dynamic Strings],
CASE
    WHEN execute_as_principal_id IS NOT NULL THEN N'WARNING: EXECUTE AS ' + user_name(execute_as_principal_id)
      ELSE 'Code to run as caller - check connection context'
END AS [Execution Context Status]
FROM sys.sql_modules AS sm
ORDER BY [Procedure Name]

Is this enough? Anything else you'd check? What do you think?

THANKS!
kt

Cambridge usergroup meeting on Monday 6th - SQL 2008

simonsabin — Tue, 30 Jun 2009 09:19:11 GMT

Don't forget if you are in the Cambridge area on Monday (6th July 2009) we are doing another usergroup meeting.

This one is going to be on SQL Server 2008 features.

We are holding this at the head quarters of Redgate software who are kindly sponsoring the event.

To register go to http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx

I look forward to seeing you there.

Don't destory your certificate if you dabble with SQL 2008 Encryption

simonsabin — Tue, 30 Jun 2009 09:11:37 GMT

Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty one.

http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/29/After-disabling-tde-you-still-requires-certificates-to-restore-the-database.aspx

If you have enabled TDE on a database and encrypted some data then remove the TDE and backup, you may not be able to restore that backup if you don't have the certificate.

I am guessing that this is to do with the encryption when you turn TDE on being a background task, I expect the same is true in reverse, i.e. pages stay encrypted until a background process decrypts them.

I wonder if the database ever gets back to a totally unencrypted version

How to stop that annoying beep on your server

simonsabin — Tue, 30 Jun 2009 05:45:37 GMT

Many years ago I was working on a project that had some large batch processing in it. To do this processing we had some dedicated servers, one of which sat under the desk next to me.

Apart from the noise of the fan soudning like we had actually bought a jet fighter the most annoying thing about this processing was that it resulted caused the servers beep to continually sound. Something in the code caused it to fire.

Boy it was annoying.

We were never able to find out how to stop it, I think in the end we disconnected it. Well last week I found out and it was so annoyingly simple.

net stop beep

Yep thats it, thanks to the trainer that pointed this out last week. As you can imagine, having 12 training machines all beeping is not something you really want

To permanently stop it you have to disable the service using

sc config beep start= disabled

Whats really odd is that beep doesn't appear in the list of services

After disabling TDE you still requires the certificate to restore the database

MartinBell — Mon, 29 Jun 2009 18:46:00 GMT

Missing certificates can cause problems when restoring databases even if they are not being used!...(read more)

DTLoggedExec 0.2.2.0: Here comes CSV Logging!

Davide Mauri — Mon, 29 Jun 2009 17:40:38 GMT

In these last days I've released the latest version of DTLoggedExec. Now it has reached version 0.2.2.0.

I planned the release to be done in May, but I added a l lot of stuff and then I realized that logging so much information without being able to automatically import it into SQL Server for further analsys has no clue.

So I decided to hold on a little bit and also improve the DTLoggedExec db that I firstly released with version 0.2.1.4 just as an example of what can be done with the Data Profiling featture, and that news has grown to be official repository of all the data the DTLoggedExec can log.

Of course with the added capability to log to CSV file, this feature was really needed.

This new feature brought also the need to be able to associate CSV Logging with DataFlow Profiling, so I had to change the code a little bit so that everything can get a consistent and unique Execution ID, which allows the data to be tied all togheter, not matter if it comes from DataFlow Profiling or CSV Logging.

Add this with some bug fixed and a very handy feature that allows you to get rid of the complex SET syntax to make yout package parametric and you can imagine that some work had to be done.

Plus, add that before releasing any new release I want to test it for a while on myself, just to be sure that very basic bug does get unnoticed, at voilà, some months of delay.

Anyway, now its there, and I'm sure you'll enjoy the new features:

Added logging of expression used by variables for which the "EvaluateAsExpression" property is true.
Fixed the bug the prevented to log properly Custom Events (CodePlex id# 20513)
Fixed a bug that prevented to log properly all the container properties in case of error
Added support for "Params" commandline options
Released CSV log provider
Handled exceptions when loaded configuration tries to configure non-existing objects
Changed the ILogProvider interface to allow Log Provider to get the ExecutionGUID value from the main program
Changed the Console and Null log providers to support the new ILogProvider interface
Changed the header of the *.dtsDataFlowProfile in order to store package, version and execution GUIDs
Changed .sql files to import Profiled DataFlow data in SQL Server
Updated DTLoggedExecDB database

Released scripts to load CSV data proceduced by CSV Log Provider into SQL Server

I'm now in the process of updating the documentation. I plan to be able to do it during this week and weekend, but using the new feature is quite easy and as usual I have included in released code some samples to show how to use them.

So, if you cannot wait, go and play, otherwise just hold on a while, while I update the documentation here:

http://dtloggedexec.davidemauri.it/MainPage.ashx

One last notice about the new CSV Log Provider. This is completely functional and you can start to use it right now, but is just the first release. I plan to improve it more and more. I have to deep test it for performance impact, so play with it freely but be warned that performance may be lower then the Console Log Provider. Inside it pack data into XML format so that it can be stored in SQL Server easily, but this has some costs (You know, XML is flexble, standard, whatever you want, but surealy is not performant).

In future I plan to change a little bit the Log Provider interface again so that everything can be redirected to the Log Provider, also the initial messages, so that in the CSV log can be put everything can currently be found in the Console Log Provider, event the initialization messages, so inside SQL Server you can have the complete picture of what happened, right from the initialization of DTLoggedExec, just as it now happens with the Console Log Provider. Anyway, and I wanted to share it with you so that you can have an idea of future improvements, but for now just playing with the CSV log provider can solve a lot of nice problems. For example Auditing packages :).

Interesting issue with Filtered indexes.

mssqlisv — Mon, 29 Jun 2009 15:56:00 GMT

Recently, an ISV I work with ran into an interesting problem with Filtered Indexes.

The application does all SQL INSERT, DELETE, and UPDATE operations using individual stored procedures. To improve performance, they decided to create a Filtered Index (new in SQL 2008) to restrict the data such that it does not contain NULL values, in this case drastically reducing the number of rows in the index. See documentation here: http://msdn.microsoft.com/en-us/library/ms175049.aspx

This all worked fine, until months later, when the ‘UPDATE’ stored procedure was updated as part of a routine application upgrade. The stored proc was replaced with a newer version using a TSQL script. After adding the ‘new’ stored proc the application was tested and the following was observed;

· the SELECT statements accessing the table continued to use the filtered index

· the ‘INSERT’, and ‘DELETE’ stored procs continued to work

· However, the modified ‘UPDATE’ stored proc returned the following error:

InnerException: System.Data.SqlClient.SqlException: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Note that the error message is very explicit and actually points us to the source of the problem. Can you guess what it is?

You are correct! The TSQL script used SET options that were set differently for the new ‘UPDATE’ stored proc than they were originally, and they don’t conform to the rules required to utilize Filtered Indexes. In this case, the SET QUOTED_IDENTIFIER was set to an invalid setting.

The solution to the problem was to recreate the stored proc using the ‘correct’ settings required to use Filtered indexes, documented here: http://msdn.microsoft.com/en-us/library/ms188783.aspx

The following simplified TSQL example shows the problem and the solution.

NOTE: the fact that Stored Procedures were used is important, because they ‘inherit’ the SET statement values they were created with, and NOT the values they are executed with.

USE USE master

CREATE DATABASE FI_Test

USE FI_Test

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [FactSalesQuota](

[SalesQuotaKey] [int] IDENTITY(1,1) NOT NULL,

[EmployeeKey] [int] NOT NULL,

[DateKey] [int] NOT NULL,

[CalendarYear] [smallint] NOT NULL,

[CalendarQuarter] [tinyint] NOT NULL,

[SalesAmountQuota] [money] NOT NULL,

CONSTRAINT [PK_FactSalesQuota_SalesQuotaKey] PRIMARY KEY CLUSTERED

([SalesQuotaKey] ASC) ON [PRIMARY]

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX FI_FactSalesQuota

ON FactSalesQuota(Employeekey,CalendarQuarter)

WHERE CalendarYear = 2009 -- <---- This makes it a Filtered index

INSERT FactSalesQuota values(53,20090101,2009,4, 37000.00)

SELECT * FROM FactSalesQuota

CREATE PROCEDURE UPDATE_FactSalesQuota

@EmployeeKey int,

@CalendarYear smallint,

@CalendarQuarter tinyint,

@SalesAmountQuota money

UPDATE FactSalesQuota

SET SalesAmountQuota = @SalesAmountQuota

WHERE EmployeeKey = @EmployeeKey and

CalendarYear = @CalendarYear and

CalendarQuarter = @CalendarQuarter

EXECUTE UPDATE_FactSalesQuota 53,2009,4,50000.00

SELECT * FROM FactSalesQuota

--- this was the script to update the sp

use FI_Test

sp_rename UPDATE_FactSalesQuota , UPDATE_FactSalesQuota_V1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF -- <------ HERE is what caused the error

--SET QUOTED_IDENTIFIER ON -- <------ it will work if this is set correctly

CREATE PROCEDURE UPDATE_FactSalesQuota

@EmployeeKey int,

@CalendarYear smallint,

@CalendarQuarter tinyint,

@SalesAmountQuota money

UPDATE FactSalesQuota

SET SalesAmountQuota = @SalesAmountQuota,

DateKey = cast((CONVERT (char(8) ,getdate(), 112)) as int)

WHERE EmployeeKey = @EmployeeKey and

CalendarYear = @CalendarYear and

CalendarQuarter = @CalendarQuarter

--this fails

EXECUTE UPDATE_FactSalesQuota 53,2009,4,52000.00

SELECT * FROM FactSalesQuota

-- Now go back fix the script, and retry the sript and now it will work

Cross Posted from http://blogs.microsoft.com/mssqlisv

SQL Server version has a known compatibility issues with Windows 7 RC. This warning is genuine and applies to all SQL Server editions, including all Express editions.

ssqa.net — Mon, 29 Jun 2009 06:35:00 GMT

There is no need to guess about the rootcause of the above error message, as it clearly mentions about compatibility issues as a warning.... read more !...(read more)

Hyperlinks in custom reports

simonsabin — Mon, 29 Jun 2009 03:31:50 GMT

One feature introduced in SQL Server 2005 is reporting in management studio. You have a set of standard reports that allow you to see in a nice report format output from dmvs and the default system background trace. Whats neat is that you can write your own custom reports. For those that long after the taskpad view of a database from SQL 2000 Enterprise manager with the help from Jasper Smith I created this http://sqlblogcasts.com/blogs/simons/archive/2007/03/28/Update-to-the-taskpad-custom-report.aspx

The custom report feature is almost fantastic, there are 3 main features I think make them awkward to use.

1. You can select text from the report and copy it
2. You can't add additional parameters to allow additional filtering, i.e. filter audit changes for today.
3. You can't have hyperlinks.

The last one I raised a connect item for https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240407.

If we had this you could write reports that had links to KB articles, blog posts, or internal KB systems. Similar to what most monitoring systems provide.

If you think this is a good idea then please vote on the connect item. I've been asked by the tools team to see if it is something people want.

Some other connect items that you might be interested in supporting if you think they are worthwhile.

Use expressions for data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238233

Be able to combine data from different data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240268

Be able to run reports against SQL 2000 database - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

Microsoft Connect - Enhanced Syntax For Insert Into Statement

Peter Larsson — Sat, 27 Jun 2009 16:06:54 GMT

I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax.
Especially for INSERT INTO ... EXEC ...

Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets.
And it's only possibly to fetch and store the first resultset.

What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this

INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ)
EXEC usp_MyStoredProcedure @Param1, @Param2

In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first.
First resultset has two columns, and second resultset has three columns.

Let Microsoft know what you think about this suggestion.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=470881

Microsoft Connect - SSMS Debugger Issue

Peter Larsson — Sat, 27 Jun 2009 16:01:42 GMT

I've found an issue with the Debugger for SQL Server 2008 Management Studio a while ago.
This is my way to ask you to endorse a fix
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=374183

Please let Microsoft know what you think about this suggestion.

Forced Parameterization: A Turbo Button?

Dan Guzman — Sat, 27 Jun 2009 10:47:17 GMT

I never had the need to turn on the PARAMETERIZATION FORCED database option until this week. We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused. This practice of using parameterized stored procedure calls, together with attention to detail in query and index tuning, allows us to comfortably handle several thousand requests per second on commodity hardware without taking special measures.

The Perfect Storm

We acquired a third-party application which had to sustain thousands of batch requests per second in order to keep up with our peak demand. Our first attempt to use the application out-of-the box failed miserably when the 16-core database server quickly hit 100% CPU and stayed there. An examination of the most frequently run query soon revealed why CPU was so high. Not only was the moderately complex query not parameterized, each invocation required a full table scan. The schema (EAV model, missing primary keys and indexes), application code (ad-hoc, non-parameterized queries) and inattention to indexing seemed the perfect storm to guarantee failure.

Our hands were tied in what the vendor could/would do to address our performance concerns. We worked with the vendor to optimize indexes and this brought the CPU down to about 65% but the batch requests/sec rate and slow response time was still unacceptable. We needed to increase performance by at least an order of magnitude to meet SLAs.

The Perfect Fix

I then recalled an experience that SQL Server MVP Adam Machanic shared not long ago:

CPU was 95%+ at peak time (several thousand batch requests/second, via an ASP (classic) front end), and the peak time lasted 8+ hours every day. The server was one of the big HP boxes -- not sure if it was a Superdome or some other model -- with something like 56 cores and 384 GB of RAM. The database itself was only 40 or 50 GB, as I recall, so the entire thing was cached. Long story short, I logged in during peak load, did a quick trace and noticed right away that none of the queries were parameterized. I decided to throw caution to the wind and just go for it. Flipped the thing into Forced Parameterization mode and held my breath as I watched the CPU counters *instantly* drop to 7% and stay there. I thought I'd broken the thing, but after checking my trace queries were running through the system same as before, and with the same number of errors (another story entirely <g>). Luckily the head IT guy happened to be watching his dashboard right as I made the change, and after seeing such an extreme result thought I was a god...

I knew of PARAMETERIZATION FORCED but never realized how big a difference the option could make until I learned of Adam's experience. I'm not quite as adventuresome as he is so I restored the production database to a separate environment for some cursory testing. To my amazement, I watched the rate of my single-threaded test jump from a few dozen batch requests/sec to several hundred immediately after I executed "ALTER DATABASE...SET PARAMETERIZATION FORCED". CPU dropped by half even with the tenfold increase in throughput.

The production improvement was even more impressive - the 16 core Dell R900 hasn't exceeded 8% CPU since the change. Response time is excellent, we have happy users and plenty of CPU headroom to spare.

A Turbo Button?

Despite anecdotal success with PARAMETERIZATION FORCED, I wouldn't turn it on indiscriminately. When the PARAMETERIZATION FORCED database option is on, all queries are parameterized, including complex ones. This is good in that compilation costs are avoided due to cache hits. The bad news is that a single plan might not be appropriate for all possible values of a given query. Worse overall performance will result when higher execution costs (due to sub-optimal plans) exceed compilation savings so you should understand the query mix before considering the option.

In contrast, SQL Server parameterizes only relatively simple "no brainer" queries in the default PARAMETERIZATION SIMPLE mode. This behavior promotes reuse of plans for queries that will yield the same plan anyway regardless of the literal values in the query. Complex queries are not parameterized automatically so that the optimizer can generate the optimal plan for the values of the current query in the event of a cache miss. The downside with simple parameterization, as Adam and I observed, is that complex queries not already in cache will incur costly compilation costs that are a CPU hog in a high-volume OLTP workload.

There is also middle ground between PARAMETERIZATION SIMPLE and PARAMETERIZATION FORCED. One can use plans guides with PARAMETERIZATION SIMPLE to avoid compilation for selected queries while other complex queries are compiled as normal. In my case, a plan guide may have been a better option because the culprit was a single query rather than many different unpredictable ones.

In my opinion, the best solution is to use stored procedures and/or parameterized queries in the first place. These methods provide the performance benefits of PARAMETERIZATION FORCED and add other security and application development benefits. Unfortunately, third-party vendors are notorious for not following parameterization Best Practices so DBAs need to keep PARAMETERIZATION FORCED and plan guides in their tool belt.

Scripting schema AND data with SSMS in SQL 2008

paul.nospam@nospam.sqlskills.com (paul) — Fri, 26 Jun 2009 13:16:00 GMT

It's very commonly known that you can use the Script Wizard in Management Studio to script out tables or even an entire database. It's NOT commonly known that in SQL Server 2008, the wizard was upgraded and you can now script out the data too. The feature isn't enabled by default, which is why not many people know about it, and I couldn't find it described in Books Online either, further contributing to it's obscurity - but it's definitely there.

If you go into the wizard, and go down to the Table/View Options, there's a setting 'Script Data'. Set that to True and you'll get a bunch of INSERT statements in the resulting script too. Very cool.

Here's a screenshot showing what I mean (click it for a 1024x768 version).

Enjoy!