Enjoy Every Sandwich

Using Signed Assemblies for SQLCLR: Doing the Safety Dance.

Kent Tegels — Thu, 16 Feb 2006 18:23:00 GMT

You know that song. Yes, that song. The beeping. The arm flailing. The Safety Dance. I so wanted it stay in the 80s – along side the uncounted Wild Turkey inflicted hangovers. It just won’t. Every now and then that damn beeping rhythm creeps into my conscious thought when I least need it to. Like today. I’ve been trying to figure out how to use the last-minute-added ability to catalog signed assemblies that need External Access or Unsafe permission without having to set the database trustworthy bit. Getting frustrated with the interesting example in Books Online, the beeping started.

So here’s what you really need to know:

The first you need is a certificate that can establish a chain of trust to some trusted root certificate authority on the target machine. If you’ve already got one thanks to having Certificate Server on your network or you’ve purchased one, great. If not, you can make one for yourself as we’ll do there.
You need to understand the *interesting* inter-play of certificates, logins and signing assemblies. It is not hard once you understand that you can use a single certificate to do all of that.
You will have to comfortable using the Command Shell and a couple of tools in the .NET Software Development Kit (SDK), namely SignTool and MakeCert.

Continues here.

The first rule of Talk Club is not to talk about the rules of Talk Club

Kent Tegels — Wed, 15 Feb 2006 10:02:00 GMT

Offered without comment as none is needed:

The brain reacts in exactly the same way when a talk goes wrong.

You see an error on the screen that you’ve never seen before. Your demo has broken on a line that you don’t remember writing. For some reason, the error message is in Greek.

Your forehead tingles as blood floods the capillaries, flushing your face in seconds and turning it into an I’M A F***CKING IDIOT beacon for everybody to see. Sweat creeps out of glands in places you didn’t know you had ‘em, you suddenly become acutely aware of your own body odor, and then your eyes resolve the blurry thing that’s just beyond the monitor – yes, the audience – that’s what it is, and it’s staring at you, shocked, and sending you a telepathic message which goes something like “Yes, Rory, your I’M A F***CKING IDIOT beacon is working properly. We can all see you up there, being acutely aware of your own body odor. Please resolve this soon or we will eat you.”

More at http://neopoleon.com/blog/posts/17449.aspx

SmallDateTime Conversion

Kent Tegels — Fri, 10 Feb 2006 12:29:00 GMT

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        If String.IsNullOrEmpty(Row.Date.Trim()) Then
            Row.NewDate_IsNull = True
        Else
            Row.NewDate = DateTime.Parse(Row.Date)
        End If
        If String.IsNullOrEmpty(Row.Age.Trim()) Then
            Row.NewAge_IsNull = True
        Else
            Row.NewAge = Int32.Parse(Row.Age)
        End If
    End Sub

I, Sandwiches

Kent Tegels — Wed, 08 Feb 2006 22:39:00 GMT

Thanks for linking to me, Ken. At least somebody laughts at my jokes. Did I ever tell you one about the Panda...?

Nevermind, let's not go there. Instead, let's talk about Sandwiches. Yes, stuff (preferablly eatible) between one or more slices of bread. An Ice Cream sandiwich counts in my book, but I'm not so sure about my latest “Sandwich” addiction.

Uncrustables.

Yes, I eat them. Anybody that know me knows I'm addicted to Peanut Butter. Oh sure, the Jelly is important, but let's face it, it's a PEANUT BUTTER and Jelly sandwich. Its not robotic science here either: bread, butter, Peanut Butter, Jelly and another slice of bread. For the last two weeks in a row, my lunch has been 1 or 2 uncrustables, 1 or more fruit items (Apples, largely, Bananas last week, Oranges this) and a bottle of Water. The joys of living on the road. I try to stay in the classroom for lunch when I'm teaching and this combination proves durable enough to last. There's just one problem.

I don't really believe Uncrustables are Sandwiches.

Go find one. Examine it. Go CSI:Sandwich on it. I dare you to tell me that is anything more than a Pocket trying to pass itself off as Sandwich. The evidence speaks for itself: Uni-bread. Crimped like Ravoli. Machined together. Someplace is Smuckerland there's an a spot-welding robot who suffered a mid-life crisis and is now punching bread instead of metal. Next to it is other once-otherwise-employed 'bot sperting out the PB&J. Call them Spert and Stamp..

Why am I off on this rant? Four words: I, Robot. Will Smith. I watched less than ten minutes of it. I'm scared forever.

I'm now fully sure that there is a VIKI and she's got the really food people at Smuckers locked down so tight that the only way they can communicate with us via these abominations of the Sandwich name. The logic in this infallable: An Uncrustable may not injure a human being or, through inaction, allow a human being to come to harm. Since actually enjoying an Uncrustable as we might enjoy proper PB&J might cause us to eat too many of them, those sinister minions of the Pocket revolution -- good old Spert and Stamp -- actually make something unjoyable. I don't think yelling at Spert and Stamp about this is going to be any good. Damn second and third laws.

And you think you've got issues. Don't even get me started on the Gummy Spider plants. I've already got the Roger Young on Speed Dial.

Tag, I'm it!

Kent Tegels — Tue, 24 Jan 2006 21:49:00 GMT

Yeah, I'm jumping on the Four-Things Tag-wagon.

Four jobs I’ve had in my life:

Developer (Geek)
System Analyst (Nerdy Geek)
Enterprise Software Architect (Business Nerdy Geek)
Instructor (Talking Head Geek)

Four movies I can watch over and over:

Patton -- “You want to know why this outfit got the hell kicked out of it? A blind man could spot it. They don't act like soldiers; they don't look like soldiers; why should they be expected to fight like soldiers? “
Shichinin no Samurai -- “Find hungry Samurai.“
A Christmas Story -- "Be sure to drink your Ovaltine. Ovaltine? A crummy commercial? Son of a bitch!"
Kentucky Fried Movie -- “What was that? This is not a chawade. We need total concentwation.”

Four places I have lived:

Fremont, Nebraska -- Harmless.
Lincoln, Nebraska -- Mostly harmless.
Omaha, Nebraska -- Still mostly harmless.
And thousands of nights in hotels world-wide

Four TV shows I love to watch:

Good Eats -- "Of course, we're not going to have any more fresh ingredients because we're all out of hand grenades."
Barefoot Contessa -- "Food is not about impressing people. It's about making them feel comfortable."
Mail Call -- “OOOOOOOO-RAH!“
Grey's Anatomy -- -- "I want facts and until I get them my pants are staying on."

Four places I have been on vacation:

Black River Falls, Wisconsin
Atlanta, Georgia
Yankton, South Dakota
(I really don't go places on Vacation...)

Four websites I visit daily:

Four of my favorite foods:

Peanut Butter (with anything)
BBQ, preferably pulled Pork
Sushi and most things Japanesse
Ribeye Steak

Four places I would rather be right now:

Home
On a flight home
Driving to the airport for a flight home
Black River Falls, Wisconsin

Four bloggers I am tagging

I'm going to add a new set of four. The the four best things in my life:

My Sunshine Janell
Jack and Claire
My extended families -- Kevin, Lizzie, John, Rita and Paul
A kick-ass Job (despite the travel)

See -- Beer, SQL Server and XML didn't even make the list.

Shreding XML with XQuery

Kent Tegels — Fri, 20 Jan 2006 19:02:00 GMT

Over on the Microsoft.Public.SqlServer.XML newsgroup, one Chris Kilmer asked a good question about how to shred a single XML document into multiple tables using multiple stored procedures. Chris's goal with this was to have each stored procedure update one table and pass the XML remaining nodes of the first document off to the next stored procedure. This is actually fairly easy except for one thing: maintaining referential activity between inserted elements.

The XML involved is fairly simple:

<Invoice>
<InvoiceID>1</InvoiceID>
<Customer>
    <FName>Bill</FName>
    <LName>Gates</LName>
    <Address>
      <Street>1 Microsoft Way</Street>
      <City>Redmond</City>
      <Zip>98052</Zip>
    </Address>
</Customer>
</Invoice>

We want to shred this data using SQL Server 2005's XML datatype's Nodes method when and where we can. The database schema has three tables scripted as such.

create table dbo.invoices(pkid tinyint identity(10,1) primary key,InvoiceID int)
create table dbo.customers(pkid tinyint identity(20,1) primary key,invoicePKID tinyint constraint fkCustomersInvoices foreign key references dbo.invoices(pkid) on delete cascade on update cascade,fname varchar(20),lname varchar(20))
create table dbo.addresses(pkid tinyint identity(30,1) primary key,customerPKID tinyint constraint fkAddressesCustomers foreign key references dbo.customers(pkid) on delete cascade on update cascade,street varchar(50),locale varchar(50),postalCode varchar(25))

So no big deal with that. While we could argue that there's better designs, this one works for now. Our first stored procedure really needs to do three things:

Extract the InvoiceID and insert that info the dbo.invoices table
Get the identity value for the just inserted row as we'll use that we'll need that when we insert the customer's detail into dbo.customers
We need to get the Customer element as a fresh XML document to pass on the next stored procedure

Here's the code for a procedure that does that.

create procedure dbo.shred1(@in xml)
as
begin
begin try
begin tran
declare @id tinyint
declare @c table(pkid tinyint)
declare @cust xml
-- Get the invoiceID from the XML and insert that into the invoices table,
-- capturing the PKID for row just inserted.
insert into dbo.invoices(invoiceid)
output inserted.pkid into @c
select c.cust.value('InvoiceID[1]','int')
from @in.nodes('/Invoice') as c(cust)
-- Get the Customer sub-element
select @cust = @in.query('/Invoice/Customer')
-- Get that PKID and insert it into the the Customer element
-- so we can pass that along to the next procedure.
select @id = pkid from @c
set @cust.modify('insert <invoiceID>{sql:variable("@id")}</invoiceID> into /Customer[1]')
exec dbo.shred2 @cust
commit
end try
begin catch
rollback
end catch
end

A few comments need to be made about this example as there's some interesting things being done.

The inbound and outbound instances really should be bound to an XML Schema collection to make sure they valid. That's not shown here to save time, space and detailing of schema collection behavior.
This method works great if the inbound instance (@in) contains a single invoice only. If it doesn't, things get more complicated in terms of getting the host row identity value into the outbound XML instance.
We didn't have to embed the hosting row's identity value in the passed-down instance. We've done that here to keep in-line with Chris's request to pass XML instance between the procedures. However, a positive from this is that we could somewhat more easily resolve the issue above since the we could pass a single XML instance containing all of the customers.
I wouldn't normally choose to use the .Nodes method to shred a single XML instance, however, Chris said that this is what he does so I'm showing that for the sake of consistency. Here there's no need to do that, per se, as we could just as easily and performantly pick element values out resorting to a .Nodes call.
Although you can shred XML using what we're looking at here, once you get more than four or five XQuery method calls in a T-SQL Server, you're probably better to have put the instance in a table with a primary XML index on the column holding the instance. Otherwise, you're generating a Node Table for each method call which isn't cheap.

The real meat of Chris's question in the post was this: “However, I do not understand how to retreive the Customer node from the Invoice xml as a new xml doc and then send the new xml doc to the next sproc.“ Getting the Customer element is fairly easy -- declare a variable of type XML and then use a SET query calling the .Query method on the source instance to select the desired elements. Ala:

select @cust = @in.query('/Invoice/Customer')

Since we already have our desired instance as an XML instance, we can simply pass it to the next Stored Procedure as parameter thusly:

exec dbo.shred2 @cust

Our next stored procedure is similar. It takes in the customer's data, parses out their name dumps that into the dbo.customers table. The address portion of the data is extracted as passed along to the next procedure. Again, this has some of the same considerations in terms of performance as noted for the first procedure.

create procedure dbo.shred2(@cust xml)
as
begin
declare @c table(pkid tinyint);
declare @addr xml;
declare @id tinyint;

-- Shred out the customer information and
-- insert that into the customers table,
-- capturing the PKID.
insert into dbo.customers(fname,lname,invoicePKID)
output inserted.pkid into @c
select c.x.value('FName[1]','varchar(20)'),
c.x.value('LName[1]','varchar(20)'),
c.x.value('invoiceID[1]','tinyint')
from @cust.nodes('/Customer') as c(x);
-- Get the ID for that just inserted row and embed it into Address Data.
select @id = pkid from @c
select @addr = @cust.query('/Customer/Address')
set @addr.modify('insert <customerID>{sql:variable("@id")}</customerID> into /Address[1]')
-- Shred the Address data
exec dbo.shred3 @addr
end
go

Our last stored procedure is pretty straight forward, and the same comments apply:

create procedure dbo.shred3(@addr xml)
as
begin
insert into dbo.addresses(street,locale,postalCode,customerPKID)
select a.f.value('Street[1]','varchar(50)'),
   a.f.value('City[1]','varchar(50)'),
   a.f.value('Zip[1]','varchar(25)'),
   a.f.value('customerID[1]','tinyint')
from @addr.nodes('/Address') as a(f)
end
go

The only real pain here was figuring a good way to capture the identity value for the just inserted row. Sure, saving off SCOPE_IDENTITY() is option, but this example also demonstrates that can get the identity using the new OUTPUT clause. More, SCOPE_IDENTITY wouldn't have worked well enough if there had been multiple Invovices serialized into the first Stored Procedure's input XML as it returns the last identity generated, not all of the identities generated.

Emeril seems, at all times, dangerously close to actually copulating with the food he prepares...

Kent Tegels — Fri, 20 Jan 2006 02:58:00 GMT

And you'd thought you'd find *IT* Geek content here? You will... eventually. Promise. But now that I have your attention¹...

Welcome to the new home for my Techno-Geek Foodie blog “Enjoy Every Sandwich.” I'm Kent Tegels, the Database Curriculum Lead for DevelopMentor. I've been blessed by Microsoft as a Most Vaulable Professional for SQL Server since 2004. So as you can guess, this blog is mostly going to be about .NET, SQL Server and the communities involved with those tools. Well that and stuff about my “Domestic Partner” Janell, our Cats Jack and Claire, our love of all things food and drink as well as whatever I feel the need to blab on about.

And yes, you probably do recognize my name or at least the name of this blog. While I've been a proud member and supporter of the SQLJunkies community almost since day one, I decided -- after CMP took that site over -- that it was time to find a new home. Nothing against them, but I wanted to get back more control over the blog and what gets aggregated. Thankfully, fellow MVP and midwesterner Jeff Julian set me up with space on GWB. Thanks Jeff! As for the name of the blog, yes, it is from Warren Zevon's “famous last words“ on the David Letterman show shortly before his death.

Let's whip this Llama up...

¹:I only wish I would have said it first. http://flakmag.com/tv/flay.html