[ETL] A dynamic CSV ripper that's forwards/backwards compatible, in T-SQL

Had this problem coming, so wanted to get ahead of it:
Dev releases code, version 2.1, which has a CSV with 4 columns.
[2 days pass]
Dev releases code, version 2.1.1, which has a CSV with 5 columns.
[2 days pass]
Dev releases code, version 2.1.2, which has a CSV with 6 columns.

Since we relational DBAs have this fun thing called "schema", importing this gets hard.  SSIS will choke because it's different each time.  BULK INSERT will choke as the file type has changed.  Inserting via OPENROWSET and the text connector sucks because 64-bit servers require you to install the Excel pack. Modifying the import each time blows for obvious reasons.

So, time to use the DBAs secret weapon, Dynamic SQL.  (Which, yes, lots of potential problems, holes, etc.  See Erland's seminal paper "The Curse and Blessings of Dynamic SQL").

So what did I build?  Practically, it imports any delimited file with a header row, compares that to the end table, and inserts just those fields.  Need that field in your end table? Add it to the target table, and the next run will pick it up. 

Warning: it's SLOW. Not sure which part, but there's plenty of blame to go around on my part. Dynamic Shreds, Dynamic Pivots, Pivots, etc. You could replace the Pivot with a CLR Dynamic Pivot floating around out there (which is probably the majority of it), replace the comma parsing with Adam Machanic's, and that may get you to a happy spot.  For me, though, this is ideal. 

My next version of this process will probably be in Powershell, in case someone hasn't already done it.  That could be slick and really fast- import-csv, get the column list as a variable, match up to another variable with the columns from the final table, and use the resultant as a hash table for a select.  Next time, next time.

Dependency: right now, Jeff Moden's 8k splitter.

Order of operations for this code:

• get server list (naturally, this needs the file from multiple servers) 
• For each server...
• import first row (header has the field names) into a table
• split CSV into a list of columns.  
• import file into wide table
• split CSV to a keypair-style table, using Jeff Moden's awesome DelimitedSplit8K splitter (may need to change this to use Adam Machanic's CLR)
• update the table so that blank fields are now NULL (since blank fields become '', which converts to 0 if bigint, but fails converting to decimal.)
• build a pivot to go from the keypair into your target table
• compare the columns (sys.columns or information_schema.columns) between the header and the target table
• match the data and header to make a good insert statement
• use the pivot to insert into your target table

[Powershell] Getting uptime for a service, saved to a database

Needed this for someone in a different group. 

• Get a list of servers
• For every server on that list:
• use get-wmiobject to get the list of open processes that match my search term.
• Select relevant fields like computername, starttime, processid, and the exec path
• Include a rounded up/down number of hours the process has been up
• Write to a table.

Prereqs are the old standbys: out-datatable, write-datatable. 


Technical bits:
Powershell has some crazy syntax.  The $starttime is one example.
The most annoying part was having to get the times converted from the WMI format (MDTF) to something normal. 

[Code] Finding the lowest ID (identity) for a date when there's no index on the date, like Zeno

Needed this today.  I remember seeing someone at a SQLSaturday (which for me this year, doesn't narrow it down much) with this idea, but couldn't figure out who it was, so I wound up implementing my own.

Say you have, like most of us, a table with an "id INT IDENTITY PRIMARY KEY," so your PK is the ID, but the table also has a date column.  Nobody cares about the date.  Until they do, months later.  So now you have a busy table that you can't easily add an index on, and an ADHOC OMGWTFBBQ project comes in, where you need to query a date range, and no idea how to get there quickly. .

*raises hand* Yup, that'd be me.  So I wrote this. 

What it does: cycle down through a table, using the ID column.  Start by decrementing the max ID from the table by 10 million.  Does that go below the date you need?  Yes?  Okay, try 1 million. Yes? 100k.  No? How about 200k? Yes. 110k? No. 120k? Yes. 111k? No. 112k? Yes. 111100? (and so on). 

It'll pop down even a terabyte table pretty quickly, since it only does a handful of queries, and they're all against the PK.  Granted, I could make it faster by doing a (min+max)/2, then (newmin+max)/2 etc, but this works.  I've nicknamed it "Zeno's Arrow", although technically my code doesn't go halfsies - but it is fast and direct.

Also, (importantly!) it may not cope with missing rows (which could happen due to stuff like, for instance, a failed transaction).  I don't have a good fix for that, yet.  Maybe in V2

Hope this helps.

[System_Health] 2 dynamic and full parsers for the system_health session

I needed to look at some locking info in the system_health session recently, and realized nobody ever wrote one - all the ones I've seen are explicit calls. 
Everybody does stuff like '/event/data[3]', but there was no comprehensive shredding done.  I decided to fix that.  

While I've written something for SP_SERVER_DIAGNOSTICS a while ago (which will handle 2012+), I needed something specifically for 2008/2008R2, . 
 At it's core is a piece of code I picked up a while ago from stack_overflow and a couple other Xquery posts:

              SELECT  
                    A.B.value('@name[1]', 'varchar(128)') AS EventType,
                    A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
                    X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
                    X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
                    X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
             FROM    cte_healthsession
             CROSS APPLY EventXML.nodes('/*') AS A (B)
             CROSS APPLY EventXML.nodes('//*') AS X (N)



All this really does is shred out every node at the various levels, including names and values.  Which for system_health seems to work pretty well (not perfectly; there are some values I need to filter in my pivot)

But once it's gotten all the potential details, the code automatically goes through each group and shreds it.  As a bonus, I realized that by doing it slightly differently, I could return all the details in one table.  The table does have a ton of fields, since many are specific to each type of event - error_reported doesn't have duration or wait_type, for instance, whereas waitinfo doesn't include error or messsage.  However, it means that you can read them in time order quite easily, which makes it easier to correlate issues.



However, I also wound up with a version that returns multiple tables, one for each event type.  (This looks similar to the sp_server_diag parser I wrote)





 


Now, when you run this (if you're not already doing a ton of stuff with system_health) you may be wondering why you're not seeing anything recent.  There are two potential reasons:

1) events that are too large, most likely deadlocks.  There's no good way around it other than to modify the system_health session to also write to a file (which 2012 does do).  In order to make sure I'm not susceptible to it, I added deadlock monitoring via Event Notifications (add the event DEADLOCK_GRAPH).  I will probably modify the system_health session again and add a file.

2) a really nasty bug in system_health, which Jonathon Kehayias mentioned in http://www.sqlskills.com/blogs/jonathan/incorrect-timestamp-on-events-in-extended-events/ . I can easily tell it's happening due to crappy monitoring software - I show a new error message every 5 minutes, although the time is several days ago (and not exactly X days ago, either).  So, I added the system time, as suggested in the post. 
Here's the code, holler if you have any questions!

One table:




Multiple tables: