A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.
Thursday, October 29, 2009
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.
Tuesday, October 27, 2009
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
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
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
Go to the Official SQLServerPedia Bingo to get playing cards.
This card 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).
|Not valid for game play|
|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
Tuesday, October 13, 2009
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
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]
Thursday, October 8, 2009
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
The easy ones are grabbing the user name associated with the connection account and the date when the insert is happening.
DF_DEFAULTS_last_user DEFAULT system_user FOR last_user
|ADONET SSIS||.Net SqlClient Data Provider|
|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|
|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.
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.
Wednesday, October 7, 2009
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.
Thursday, October 1, 2009
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.
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]
- ► 2012 (14)
- ► 2011 (35)
- ► 2010 (53)
- ▼ October (8)
- ► 2008 (15)