Where the hell is Bill?

Trying to find me at Summit 2009? I'll be arriving on Sunday 1 Nov at Noon and I'll be staying at the Sheraton Seattle Hotel.

This is my tentative schedule. Highlighted rows are sessions I'll be at for sure, an asterisk in the Interested column means I'm interested in that session. How's that for counter-intuitive? It'll also be my reminder of the sessions I want to watch first once the DVD is available.

I'll make use of the #sqlbingo twitter hash (@billinkc) as best I can. I'm looking forward to meeting as many of you fine folks as I can.

It depends on how you measure it

A friend ran into an issue where len() was trimming the trailing space
and not returning the real length. Ah, I said to myself, he must not
realize he's working with varchar datatype. I coded up a quick
example to show him how he was "doing it wrong(tm)"

DECLARE
    @a as varchar(10)
,   @b as char(10)
SELECT
    @a = 'hi'
,   @b = 'hi'
SELECT
    len(@a) AS len_a
,   len(@b) AS len_b

Results are

len_a       len_b
----------- -----------
2           2

Well, it must not be really making that a char since it's a variable
but if it were a table column, then I'd know for sure it's a
character. (Just go with me on the logic)

DECLARE
    @TABLE TABLE
(
    col1 varchar(10) NOT NULL
,   col2 char(10) NOT NULL
)
INSERT INTO @table
SELECT
    'hi'
,   'hi'
INSERT INTO @table
SELECT
    'bye       '
,   'bye       '
SELECT
    len(T.col1) AS len_col1
,   len(T.col2) AS len_col2
,   T.col1 + 'X' AS col1
,   T.col2 + 'X' AS col2
FROM
    @TABLE T

Results were

len_col1    len_col2    col1        col2
----------- ----------- ----------- -----------
2           2           hiX         hi        X
3           3           bye       X bye       X

Hmmm, that's decidedly not what I expected. Time to give up and
consult BOL as there must be something I'm missing. And of course,
plain as day in the article on len() "Returns the number of characters
of the specified string expression, excluding trailing blanks."
Equally important was the Note "To return the number of bytes used to
represent an expression, use the DATALENGTH function".

DECLARE
    @a as varchar(10)
,   @b as char(10)
SELECT
    @a = 'hi'
,   @b = 'hi'
SELECT
    len(@a) AS len_a
,   len(@b) AS len_b
SELECT
    datalength(@a) AS actual_len_a
,   datalength(@b) AS actual_len_b

DECLARE
    @TABLE TABLE
(
    col1 varchar(10) NOT NULL
,   col2 char(10) NOT NULL
)

INSERT INTO @table
SELECT
    'hi'
,   'hi'

INSERT INTO @table
SELECT
    'bye       '
,   'bye       '

SELECT
    len(T.col1) AS len_col1
,   len(T.col2) AS len_col2
,   T.col1 + 'X' AS col1
,   T.col2 + 'X' AS col2
,   datalength(T.col1) AS actual_len_col1
,   datalength(T.col2) AS actual_len_col
2FROM
    @TABLE T

Take away from this: use len() to find out how many characters would
be used for a varchar representation. Use datalength() to find out
how many bytes (not chars as this works with unicode types) a string
takes. TODO: BOL indicates they work with binary datatypes, how does
that work?

Reference materials:
http://msdn.microsoft.com/en-us/library/ms190329.aspx and
http://msdn.microsoft.com/en-us/library/ms173486.aspx

SQLServerPedia Super Bingo at Summit 2009

Go to the Official SQLServerPedia Bingo to get playing cards.


This card not valid in SQLServerPedia Bingo. Using this card to play will earn you nothing but heartache. This page is for those who are not eligible to play actual bingo (vendors, bingo squares, etc).

Super
B	I	N	G	O
Not valid for game play
AndyLeonard Safe word:	AaronBertrand Safe word:	SQLvariant Safe word:	AdamMachanic Safe word:	sqlinsaneo Safe word:
sqlAndy Safe word:	whimsql Safe word:	billinkc Safe word:	billgraziano Safe word:	blythemorrow Safe word:
brento Safe word:	kbriankelley Safe word:	BenchmarkIT Safe word:	mrdenny Safe word:	lotsahelp Safe word:
SQLCraftsman Safe word:	GFritchey Safe word:	JeffRush Safe word:	peschkaj Safe word:	JoeWebb Safe word:
kensimmons Safe word:	SQLDBA Safe word:	leeannepedersen Safe word:	loriedwards Safe word:	Mike_Walsh Safe word:
SarasotaSQL Safe word:	SQLAsylum Safe word:	paschott Safe word:	Peter_Shire Safe word:	RossMistry Safe word:
rushabhmehta Safe word:	way0utwest Safe word:	stuarta Safe word:	bugboi Safe word:	sqlagentman Safe word:
Tim_Mitchell Safe word:	tjaybelt Safe word:	Todd_McDermid Safe word:	SQLRockstar Safe word:	SQLServerSleuth Safe word:
wendy_dance Safe word:	WesBrownSQL Safe word:
Not valid for game play

I've signed up to be a Bingo square and I want to take a moment to thank Stuart Ainsworth, Brent Ozar, SQLServerPedia, Quest Software, PASS and everyone involved for making this possible. The rules indicate I should not submit my bingo card but I still want to meet people and find the best Safe Words. I have shameless liberated the code from the official SQLServerPedia Bingo page and created a master reference card. Blogger wasn't happy with things like external style sheets so this is much uglier than the actual bingo card with

Scope and Identity

Most relational database management systems have the concept of an identity/auto-number attribute on an integer-like column. For the unwashed, visualize a "take a ticket" dispenser. Any time data is stored into the table, a number is pulled out and associated with that row. How do you capture that id? It's just the biggest number, right? Without knowing the built-in functionality of MSSQL, one may write a query on the order of "SELECT MAX(identity_column) FROM MyIdentTable" Depending on usage, that might work just fine but the possibility exists that a second record is inserted between the time the original record was inserted and the max query is run. In that case, you would receive the wrong id back from your query. That's a Bad Thing (tm).

An idiomatic way of capturing that within SQL Server is to use one of the three native features: SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT.

IDENT_CURRENT('TableName') will return the last identity value generated for a table. If none have been inserted, or it's been truncated, the function returns the seed value. This can be called at any time, even if the last identity was generated a year and a hundred powercycle ago. It is callable per table and if the table in question doesn't have an identity column, it will return NULL. Do not attempt to use this function to guess what the next identity value will be. You might get right 99 times out of 100 but when you are wrong, you will spend far too much of your life trying to rectify the problem of out of synch data.

@@identity returns the last identity value created in that session (connection). SCOPE_IDENTITY returns the last identity value created in that session. If an identity has not been generated in the session, both methods will return NULL. They are not synonyms however, as they will generate different values based on your scope. Just like in programming, the concept of variable scope applies. @@identity is a global variable, scope_identity captures locally scoped value. Using the imagery from above, imagine pulling the ticket also caused a different ticket dispenser to dispense a number. I pull 63 and dispenser B spits out 103. In a pre-SQL Server 2008 world, we rolled our own change data capture by creating audit tables that were inserted into whenever data changed. An address is created. Under the sheets, a record is inserted into dbo.ADDRESS and the address_id identity value, 63, is created. An insert trigger is also fired that writes all the data to the audit table along with metadata indicating it's an insert. The audit table also has an identity value, 103. SCOPE_IDENTITY() will return 63, while @@identity will yield 103.

Unless you have a reason to do it otherwise, stick with SCOPE_IDENTITY()

Tasty bits from BOL

"SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope."

"IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope."

Links to BOL
IDENT_CURRENT
@@IDENTITY
SCOPE_IDENTITY

DROP TABLE 
    dbo.MyIdentTable
DROP TABLE
    dbo.RedHerring
GO
CREATE TABLE 
    dbo.MyIdentTable
(
    myIdent int identity(100, 10) NOT NULL PRIMARY KEY CLUSTERED
,   col1 varchar(50) NOT NULL
)
GO
CREATE TABLE
    dbo.RedHerring
(
    herring_id int identity(10, 1) NOT NULL PRIMARY KEY CLUSTERED
,   col2 varchar(50) NOT NULL
)
GO
-- Seed value of 100
SELECT
    ident_current('dbo.myidenttable') AS current_ident
GO
-- Add 2 rows, show output
INSERT INTO
    dbo.MyIdentTable
OUTPUT INSERTED.myIdent AS [INSERTED_myIdent], INSERTED.col1 [INSERTED_col1]
SELECT 'A' AS col1
UNION ALL
SELECT 'B'
GO
-- Note same value regardless of which function is called
SELECT
    @@identity AS [identity]
,   scope_identity() AS [scope_identity]
,   ident_current('dbo.myidenttable') AS [ident_current]
GO
SELECT * FROM dbo.MyIdentTable
GO
-- Insert fails, but identity still claimed
INSERT INTO
    dbo.MyIdentTable
SELECT NULL AS col1
GO
-- Note same value regardless of which function is called
SELECT
    @@identity AS [identity]
,   scope_identity() AS [scope_identity]
,   ident_current('dbo.myidenttable') AS [ident_current]
GO
INSERT INTO dbo.RedHerring SELECT 'z'
GO
-- Note same value regardless of which function is called
SELECT
    @@identity AS [identity]
,   scope_identity() AS [scope_identity]
,   ident_current('dbo.myidenttable') AS [ident_current]
GO

[edit date="2009-10-27"]PSS has a good article discussing how to handle identity values pre-SQL 7. I didn't realize you can chain a variable assignment and an update together ala "set @iVal = iVal = iVal + 1" I had always assumed it was one or the other, but both works.[/edit]

Defaults

I need some cheesy title for this post

At any rate, we have some semi-standard fields on our tables that correspond to who and when was a record last updated/inserted.  One of the challenges I have is these are self-reporting fields.  We have to play by SOX rules so there's not as much opportunity to do things that won't be noticed but there is opportunity for people to be lazy and or ignorant of what should be populated.  To address this, I suggested we use some defaults in our database.  I know, a trigger would ensure it's done right but there is this misguided perception that trigger=MASSIVE PERFORMANCE HIT.  I'd wrap that sucker in a blink tag if I thought browsers still supported it.  So baby steps.

Given this table let's examine how we can create defaults to populate when, who and what is acting on a table.


CREATE TABLE dbo.DEFAULTS
(
col1 varchar(50) NOT NULL
, last_user sysname NOT NULL
, last_mod_date datetime NOT NULL
, program_name sysname NOT NULL
)
GO


The easy ones are grabbing the user name associated with the connection account and the date when the insert is happening.


ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_last_user DEFAULT system_user FOR last_user

ALTER TABLE

dbo.DEFAULTS

ADD CONSTRAINT

DF_DEFAULTS_last_mod_date DEFAULT getdate() FOR last_mod_date

GO

The interesting one is what is the name of the program that is talking to SQL Server.  sp_who2 shows that info, but how do you actually get it out?  sys.dm_exec_sessions is part of the answer.  Great DMV by the way, lots of good stuff in there about the current sessions.  For me, that program_name field was what I wanted.  All I needed to know was what my connection's session id was and I could filter the DMV down to one row and acquire the correct program name.  I am embarrassed to say, I finally had to call our DBA and ask how I determine my session id.  Google results had things like people calling the non-existant get_sid() method.  The answer, elementary dear Watson.  @@spid

ALTER TABLE

dbo.DEFAULTS

ADD CONSTRAINT

DF_DEFAULTS_program_name DEFAULT (SELECT top 1 E.program_name from sys.dm_exec_sessions E WHERE E.session_id = @@SPID) FOR program_name

GO

However, one cannot use a query in a constraint.  SQL Server will balk and say

Msg 1046, Level 15, State 1, Line 5

Subqueries are not allowed in this context. Only scalar expressions are allowed.

That's easy enough to rectify, wrap it into a scalar function and assign the function to the default.

CREATE FUNCTION

    dbo.ProgramNameGet()

RETURNS

    sysname

AS

BEGIN

    DECLARE @program_name sysname

    SELECT

        @program_name = E.program_name

    FROM

        sys.dm_exec_sessions E

    WHERE

        E.session_id = @@spid

    RETURN @program_name

END

GO

ALTER TABLE

dbo.DEFAULTS

ADD CONSTRAINT

DF_DEFAULTS_program_name DEFAULT dbo.ProgramNameGet() FOR program_name

GO

[edit]Or, I could just notice that program_name turned pink when I was typing it and have simply used the following but then I wouldn't have known about the DMV so not all is wasted

ALTER TABLE

dbo.DEFAULTS

ADD CONSTRAINT

DF_DEFAULTS_program_name DEFAULT dbo.ProgramNameGet() FOR program_name

GO

[/edit]

I then set about inserting rows into my table in as many different ways as I could think of.  Attached are the things I knocked out while I was listening to people debate the merits of handling orphan accounts from advisors using one trading platform vs another.

Method	program_name
ADONET SSIS	.Net SqlClient Data Provider
osql	OSQL-32
dataflow SSIS	Microsoft SQL Server
ODBC mdac driver from VS	Microsoft (R) Visual Studio (R) 2005
ODBC mdac driver from console app	SimpleDatabaseWriter
ODBC native SQL driver from VS	Microsoft (R) Visual Studio (R) 2005
ODBC native SQL driver from console	SimpleDatabaseWriter
OLEDB driver from VS	Microsoft (R) Visual Studio (R) 2005
OLEDB driver from console	SimpleDatabaseWriter
SSMS	Microsoft SQL Server Management Studio - Query
sqlcmd	SQLCMD
OLEDB SSIS	Microsoft SQL Server
WebPage OLEDB	Microsoft (R) Visual Studio (R) 2005
WebPage OLEDB	Microsoft® .NET Framework

I know isql and things like BCP are still outstanding as means of getting data into a database but otherwise, I'm getting low on ideas.  Perhaps it's just the lateness of the hour.

You got XML in my database

You got your database in my XML! And if you haven't tried the dark chocolate Reese's peanut butter cups, I highly suggest you try them. They're delicious.

In SQL Server 2005, Microsoft introduced the XML datatype and while I'm still unconvinced that it's a needed datatype, it does present some interesting possibilities. I have a Post It note on my work computer that simply says "FOR XML 2009-04-24" We had to provide an XML file to a third party that contained a list of all of our advisors and their reporting hierarchy. I knew there was something in SQL server for handling that type of work and my quick look at BOL turned up the FOR clause you can add to a query to transform the resultset into an XML format. It's really that easy. Take your favorite SELECT statement and at the end, add "FOR XML AUTO" and run it. My query generated

<defaults col1="Hello" last_user="tom thumb"/>
<defaults col1="Hola" last_user=""/>
<defaults col1="Hai" last_user="ANGBANDVM\bfellows"/>

That may or may not be what you are interested in. It was not, the vendor had some rules on how the data needed to be formatted and my coworker wasn't interested in exploring this fun new feature. I am slowly getting around to tearing that Post It down and as I work through the power of FOR and OPENXML, I"ll be writing posts on it. Another coworker has a project where they'll be importing hierarchical-type data (NSCC) and it seems like this might be a good project to work with the XML possibilities of SQL Server as SSIS isn't going to be very practical due to the input file being in a painful format.

Validation and OnTaskFailed

Here's an interesting tidbit: an SSIS package can pass package level validation and fail task level validation. A quick way to jigger that up is to break a connection string. Why you would care is if you are using SSIS to report on errors. Where this gets interesting is that if you are looking for interesting situations via OnTaskFailed a validation error does not get caught by that event handler.

At my current engagement, in addition to using native SSIS logging, we capture runtime information and send an email with the pertinent bits included. We use a custom component but as the SSIS Madman displayed in his Error Handling Template, a script solution will work just as well. We notify on both success and failure and so the current solution has our custom component as the second to last step in the control flow as well as in the OnError task as we want to capture the ErrorDescription and ErrorCode. This has been working well, we receive notification when the packages run and when they fail. Well, we did run into one situation where the package failed to validate, some strange gremlin with the deploy but we corrected that by having SQL Agent send an alert on failure as well.

Fast forward seven months later and one of the developers is mucking about and creates a situation the current solution doesn't handle. He breaks a connection string which causes a validation error but it does not send an error notification. The OnError event is raised, it is populating the object we use for notification but the next step in the chain, the actual transmission of that message, does not take place. The package will error out so it knows that it's in a bad state but the OnTaskFailed event does not fire in the event that it fails to validate. Now, with our current solution of the SQL Server Agent watching return codes we handle the situation but I'm not satisfied with it. It should be an easy thing to hook the failed validation event and report on that.

Obviously if I'm posting this, I either haven't hit the dead simple solution or it is as painful as I've made it out to be.

It's been an interesting forray into SSIS to try and figure out what is going on behind the scenes. The first solution would be to simply send notification in the OnError task but that will result in an email for each error raised. One error results in four messages for the way we have our packages configured, your mileage may vary. What won't vary is the OnError getting called multiple times. Will messing with the Propogate, fail parent, etc flags help? Maybe, this is a work in progress. The best solution I've come up, with an assist from the madman, is to parse the error description and look for failed validation. When I encounter that, Dts.Events.FireError will very nicely help me fail the task and thus get me into the OnTaskFailed event handler. The problem I have with this solution is two-fold: it cannot exist in the base package's OnError event handler as when it fires the error event, that will be sent to one level higher. The second is that I don't know that the error description will always say "failed validation." What if it says, failed to validate or some other permutation.

There must be a hook somewhere.

What I can say doesn't work. OnPostValidate I thought maybe I'd get lucky and if it failed to validate, this event wouldn't fire. From there, I could just set a variable to indicate that it did validate and conditionally not send an email. No such luck, failure to validate does not cause the OnPostValidate event to fire.

This post has been in a draft state since May 2008. It hasn't been a pressing concern since then but at some point, I'll have to dig back in and see what I can learn. As it stands now, we have two error notification chains. The first is the package fails and OnTaskFailed fires and we receive email. The second is the invoking SQL Agent job is also listening for failure and that also sends notification.

SSIS package viewer

The idea is simple, I'd like to be able to export a picture of what my SSIS packages look like. If they're small enough, I can just take a screenshot. If they're sizable though, I have to take lots of shots, stitch them together and it really shouldn't be that hard. I can build packages programatically. I have a basic comprehension of the object model but I'll be dogged if I can figure out how to determine any of the presentation "crap." UI stuff has never been my strong suit but this is worse than usual. What I know: it's all XML. The root element is a Package and then it specifies all this stuff about what the objects are and where they are on the canvas. Maybe I'm blind, but I'm not seeing anything in Microsoft.SqlServer.Dts.Runtime that indicates any object has a clue about coordinates. Not the Package, not the Executable, none of that. I'll keep digging but it might be faster for me to get to SQL PASS and try to talk to a MS developer about it. Maybe it'd be easier to try and write a Visual Studio plugin that does nothing more than expose and Export method. BIDS Helper might be a good starting point for that.

At any rate, blog rate has slowed but there are still plenty of ideas rolling about in the noggin and this is one of them.

[edit]Bingo! BIDSHelper, ExpressionHighlighterPlugin.cs looks like it's got code in there to deal with the canvas. Microsoft.DataWarehouse.Design.EditorWindow has stuff for display-centric stuff.[/edit]