TVP decimal values appears as integer in Profiler

This could be just me doing something wrong, but it sure looks like there's something weird going on with invoking TVPs (table valued parameters) from .NET code where there are decimal data types and how profiler is listening to the call. I have no idea honestly, but I wanted to get this example posted so I can enlist my mighty twitter-brethren. Assume you have the following user-defined table type in SQL Server 2008, R2 and a procedure that consumes the that type as a TVP

CREATE TYPE 
    dbo.TEST_TYPE AS TABLE
(
    some_rate decimal(5, 4) NULL
)
GO
CREATE PROCEDURE 
    dbo.TVPTest
(
    @tvp dbo.TEST_TYPE READONLY
) 
AS 
BEGIN
    SET NOCOUNT ON 
    SELECT 
        T.* 
    FROM 
        @tvp T
END

This section of C# shows an example of how to pass a dataset into the proc

/// <summary>
/// A minimal reproduction of the pain I do not want to have
/// </summary>
public static void TVPTest()
{
    string connectionString = @"Data Source=localhost;Initial Catalog=master;Integrated Security=True";
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
    System.Data.DataSet dataSet = null;
    System.Data.DataTable dataTable = null;

    decimal someRate = 0.0m;

    // Spin up our dataset
    dataSet = new DataSet("repro");
    dataTable = new DataTable("TEST_TYPE");
    dataTable.Columns.Add("some_rate", System.Type.GetType("System.Decimal"));

    // Add some values
    someRate = 0.1000m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0200m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0030m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0004m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0096m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 1.0096m;
    dataTable.Rows.Add(new object[] { someRate });

    System.Data.SqlClient.SqlConnection connection = null;
    System.Data.DataSet messages = null;
    System.Data.SqlClient.SqlCommand command = null;
    System.Data.SqlClient.SqlDataReader dataReader = null;
    connection = new System.Data.SqlClient.SqlConnection(connectionString);
    try
    {
        connection.Open();

        command = new System.Data.SqlClient.SqlCommand("TVPTest");
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Connection = connection;

        System.Data.SqlClient.SqlParameter tvp = command.Parameters.AddWithValue("@tvp", dataTable);
        tvp.SqlDbType = System.Data.SqlDbType.Structured;
        tvp.TypeName = "dbo.TEST_TYPE";

        dataReader = command.ExecuteReader();
        if (dataReader.HasRows)
        {
            messages = new System.Data.DataSet();
            messages.Tables.Add();
            messages.Tables[0].Load(dataReader);
            PPrint(messages);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
}

/// <summary>
/// Pretty print a dataset
/// </summary>
/// <param name="ds">dataset that should be rendered to console</param>
public static void PPrint(System.Data.DataSet ds)
{
    System.Data.DataTable dt = null;
    if (ds == null)
    {
        Console.WriteLine("Dataset is null, n00b");
        return;
    }

    for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
    {
        dt = ds.Tables[tableIndex];

        // Determine header
        // Console.WriteLine(dt.Namespace);
        Console.WriteLine("==========================================");
        Console.WriteLine(dt.TableName);
        Console.WriteLine("==========================================");

        foreach (System.Data.DataColumn dc in dt.Columns)
        {
            System.Console.Write(string.Format("{0}\t", dc.ColumnName));
        }

        Console.WriteLine();

        foreach (System.Data.DataRow row in dt.Rows)
        {
            for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
            {
                Console.Write(string.Format("{0}\t", row[columnIndex]));
            }

            Console.Write("\n");
        }

        Console.WriteLine("{0} rows in table", dt.Rows.Count);
        Console.WriteLine("\n\n");
    }
}

Results of running that are

==========================================
Table1
==========================================
some_rate
0.1000
0.0200
0.0030
0.0004
0.0096
1.0096
6 rows in table

Completely unremarkable. Where it gets interesting is when profiler is turned on. Doing a TSQL trace, the TextData that is captured says this statement is what SQL Server is doing when the call to command.ExecuteReader is made

declare @p1 dbo.TEST_TYPE
insert into @p1 values(0.1000)
insert into @p1 values(200)
insert into @p1 values(30)
insert into @p1 values(4)
insert into @p1 values(96)
insert into @p1 values(1.0096)

exec TVPTest @tvp=@p1

That statement, copy and pasted into SSMS will generate these errors

(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

(1 row(s) affected)

The middle values which were all decimals when passed to passed to SQL Server show as integers values in SQL Profiler, at least as far as my profiler was concerned. What's really queer about it is that it didn't truncate the values which I'd have expected---as far as one can have expectations for abnormal behaviour. Instead, profiler just disregarded the decimal point. To generalize the behaviour, decimal values with leading-zeros may show up as integers in a SQL trace for calls to table valued parameters. I don't have a non-R2 image to test against and maybe I'm just not well-versed enough in the intracies of Profiler but it smells like a bug to me. As always, comments welcomed

SELECT
    SERVERPROPERTY('productversion') AS product_version
,   SERVERPROPERTY ('productlevel') AS product_level
,   SERVERPROPERTY ('edition') AS edition

product version	product level	edition
10.50.1600.1	RTM	Developer Edition (64-bit)

SQL Saturday 35 experience #sqlsat35

This overly long and rambling post attempts to encapsulate my experience at SQL Saturday 35, SQL Saturday Dallas, 22 May 2010.

Friday night

Arrived at Love Airfield courtesy of Southwest airlines right on time. My cab cost 32 bucks from Cowboy cab company and the ride was fine out there but I would have preferred the cabbie not listen to the ultra-hardcore convert to Jesus station. I've forgotten the name of the town now but apparently there is a town within the borders of Dallas that has all of its own public services and it's where all the rich people live. The houses I could see looked elegant without being McMansions. Traffic seemed busy but it was rush hour on an 8 lane highway. Arrived at the Marriott in Richardson by 5ish and had easily one of the friendliest front desk people I've seen in some time. I can't recall the gentleman's name but a very buoyant personality. The hotel Internet sucked, timeouts and just poor performance. The rooms only had cabled lines and fortunately they provided cables. I had set a cord out but walked off without it. I went for a run after I got there as I've heard to fight jet-lag one should not eat on the airplane, drink lots of water and then exercise once you arrive. Seems like a healthy enough approach so I go with it for as little as I travel.

Let me just say, it was freaking hot outside. Kansas City has been running mid 60s that week and had been having a pretty cool May. The weather map I pulled up said it was 88 degrees and felt like the low 90s. And this fat bastard ran 2.5 miles in it. Richardson is/was the headquarters for Texas Instruments. There's a very large TI Boulevard that Sherman spit me out onto. That's some history there so I made sure to run perpendicular far enough for it register on my Garmin. During that detour, I managed to step on a nail that easily slipped through my Vibrams but fortunately only grazed my arch. Still thinking about the near catastrophe of my foot lead to me being assaulted by a tree. Shaved head + branch = inch long gash. I completed the run with a few moments of walking because it was so mercilessly hot. I could not live that far south.

Back to the Marriott and an icy cold shower helped me cool off. I hadn't seen any response to my suggestion of dining out but the night was early so I tweeted that I'd be in the lobby. That time was well spent working through some fun SQL PowerShell play. That will be a post on all it's own but Mmmmmm PowerShell is quickly moving some "something I should learn" to "something I am learning"

After an hour or so, I gave up on meeting up with SQL tweeps and headed for some food. My waitress at Pappadeaux seafood kitchen didn't read me well as she was pushing some generic spinach dip. I can get that anywhere. I should have gone with the mud bugs but the oysters on the halfshell were just fine. Unremarkable salad but the crab legs were fantastic. Skip the pasta though, I shouldn't have wasted the stomach space for the one strand I tasted. An hour and a half later, I was channeling my wife's spirit and eating some bread pudding for her. She wouldn't have liked it though, it had raisins. I managed to get a chapter read in my SQL MVP deepdive book. The chapter on LINQ to SQL and how the query plans, while not award winning, should at least be consistent and well cached. I buy that argument. I knocked around until about midnight and finally gave up.

tl;dr

Friday was fine, crab is good, Dallas is hot

#sqlsat35

The stellar internet connection in the hotel led to tweetdeck not refreshing well until Saturday morning when I learned that I missed an opportunity to mix with the folks at Humperdinks the previous night. Dagnabbit! I checked out by 6:35 and walked over to Region 10 in time to meet the 7 a.m. deadline.

I spent first 2 hours Saturday helping with registration. It was a little disorganized at first, no one was really sure what to do with the dozen or so volunteers that showed up at first but Sri and Dave got things sorted out. Oh look, there's Sean of MidnightDBA fame. I of course go over and talk to him like we've had all these conversations and oh yeah, I guess we've never been physically introduced. Awkward... Oh well, back to stuffing bags with the mugs (see swag below) with one of the Houston SSUG leaders, @NancyHidyWilson, Nancy Hidy Wilson. The next familiar face was @sqlinsaneo aka Allen Kinsel. Hooray, someone I'd actually met, courtesy of SQL Pass 2009. We spent the rest of the time stuffing bags, talking about work and whatever else came up. I was also happy to see Tim Mitchell, another friendly face from SQL PASS but he was pretty busy with other volunteer activities.

The conference space was well laid out. All the rooms had name placards on them and the agenda listed on the wall. The conference center itself was in an L pattern with the awesome, kick ass sponsors lined up about the way. If you needed any help, the volunteers and speakers had swanky red shirts with the SQL Saturday logo. I wish I had planned my attendance better rather than figuring it out last minute so I could have gotten one of those in an XL *cough* *cough* I missed the opening remarks and the early bird drawing as we were helping to pack up the registration tables and bring them inside. Oh well, I wasn't there for goodies. I was there to absorb SQL Server knowledge and get an understanding of what all a SQL Saturday involves.

There was still plenty of breakfast to be had and even though by the label I'd have never purchased it, I liked the Chai tea they offered. In fact, I have to give full marks to the #sqlsat35 crew for their food vendor who's name I'll have to backfill into this as I can't find it. It's something like "little kickass food treasures of Dallas with a side of hell yeah." Breakfast was muffins, quick breads, pastries, fruit, coffee, tea, etc. Well laid out, kept stocked and overall full of win. [update]Jen says it's Guess Who's Coming to Dinner Caterers but I think they should consider a name change. [/update]

My first session was with Kal Yella on "Data Compression in SQL Server" He spent a good bit of time on vardecimal (variable length) which, had it not been deprecated in SQL 2008 RTM, would have been a good idea. He dug into the actuals of how they achieved it, not just how to turn it on. I liked that he pointed out it wasn't compression so much as efficient storage. With column prefix something, they store prefix values in page header and then references dupes within page dictionary. It only works on duplicated leading values and it's goodfor IO bound apps, bad for CPU bound. It's an enterprise edition feature that is used when written to disk but any edition can read the data out. That could be good for your recovery plan. Row compression is good for OLTP. Page compression is good for Data Warehouse. There is a proc execute sp_estimate_data_compression_savings with params will show estimated space savings. This was the beginings of his query to show whether you had compression turned on. It's on the object level so you can't turn it on for some columns but not others. That can have an impact if you're using vardecimal as it has 3 byte cost associated with it so you could burn more than you save depending on your table

-- check compression status
select object_name(object_id), rows, data_compression_desc, index_id
FROM sys.partitions
where object_id = object_id('mytable')

Not a bad session and stuff I wouldn't necessarily go reading up on my own. Low turnout though, probably 10ish people in the room.

Next session was with Joe Celko on "Relational Division and Relatives" I took such good notes in my first session. This one I jotted down

Tables of pilots and planes they can fly (dividend)
planes in the hnager (divisor)
Something something else

and the rest went out via tweets. My feedback on the evaluation is that I wish he'd gone slower on his presentation b/c I didn't really catch much of what his scheduled topic was and it was over in under 15 minutes---for an hour long session. However, he filled the remainder in just fine by rehashing war stories of pretty much whatever crossed his mind. He had some great points in there, loved his bit on not reinventing the wheel. Just google the damn standard. Oh and I got to put a face to @jmarx with his very fine twitter shirt.

Sometimes, I think I'm so big and bad ass because I can write queries but while I think it's intuitive to me, people like Joe and Itzik who get set theory so well and can just spit these queries out that just mystify me. I really wish those 30some-odd hours of mathematics in college had clicked better. Combing through my tweets, the following is the wit and wisdom of Celko, quoted as warmly as possible in 140 characters or less.

• Diet (pop)? What's the point. It's like non-alcoholic beer
• Do not sign a contract while drinking with Brits
• The Minus operator in Oracle, Except in standard SQL, performs very well
• 7 ways to wite a query. 5 are worthless. 2 versions are worth keeping. Comment out the one that doesn't perform as well today as it's likely to do better next week
• aggregates in case statements (Romley's division). <-- this was a wicked trick that I wish I had copied down. Something about case statements and aggregates being efficient in them
• There's still more data in COBOL than all your fancy languages de jour
• If your storage is as fast as main memory, why bother with indexes
• Use the right tool for the right job and spatial data has no business in SQL Server

My takeaways were reading assignments. One is to get Celko's SQL for Smarties. The other is actually get Codd's book and sit down and try and understand what I'm really trying to do within a database. Also, dueling presenters with Celko and Buck Woody would be phenomenal as long as it wasn't after lunch. Would hate to see people blow chow from laughing so hard.

The next session I went to was with Kristin Ferrier on An Introduction to SSAS in 2008. I thought someone walked off with the room's laser pointer so I volunteered mine. While green is an awesome color for a pointer, that thing's too damn bright. I had never actually seen MDX and I liked Kristin's advice "See those SELECT, FROM and WHEREs? Pretend those are different words because they don't mean the same as they do in TSQL" She also said that it's rare that you will need to actually write MDX as the tools generally handle that now. She also advised people to change the project property from Default to Do not process during development as you don't want to wait every time you perform a build. Kristin's presentation was strongly demo driven and she was smart in that she had a checklist up there she was working off of to ensure she got her demo right. She ended up missing something so she was doubly wise by having already built out and was able to fail over to the prepared version when needed.

Lunch, remember how I said the caterer was good? They were better than good. The turkey and bacon sandwich was just alright (bacon was flavorless) but the salad had this roasted corn dressing that was so nomnomnom I'm drooling thinking about it 2 days later. Cookies were good too. For my lunchtime presentation, I sat in on the MidnightDBAs and won a printer but traded it for a MidnightDBA laptop sticker. It was a fair trade in my mind. During their talk, Sean mentioned how disconcerting it was to have strangers come up and talk to him about personal aspects of his life as if they were friends *cue the not-me whistling* Sat with Allen and we got to chat a bit more between bites and laughing at Sean and Jen's antics.

Post lunch, I went to listen to Geoff Hiten talk about "Bad SQL." I don't know any Geoff Hiten but I was following @SQLCraftsman and oh hey, they're the same person. I wouldn't have made the connection either except for @t_burger another person I followed on twitter and she of the most excellent Woot flying monkeys. Geoff had some really good bad sql samples in there. Some things I had seen before but I hadn't really come across an elegant solution. The one I liked best was where he described "Doing an OR when joining parent/child, the query algebrizer gives up and does scans. Better to rewrite as 2 separate queries and union the results" I need to get an example of the query to show better how it worked but it made sense when I was looking at it. He also had a great line in there that got me to thinking about a good blog post "Who took classes in Computer Science? (90% hands go up) How many took a class on 'How to inherit a system'?" It'd probably dovetail nicely with Brent Ozar's "BLITZ! 60 minute server takeover" from 24 Hours of SQL Pass #24hop Geoff showed some fine code formatting skills by writing his queries with leading commas in his select statements. For that alone, he gets 5s across the board. He talked about query optimzier's assumption of 100 rows of data and 40% cpu cap but the important take away is that scalar functions can bite you in the ass without you realizing it. Simon Sabin also touched on the subject of TVFs in his High Performance Functions talk at 24 Hours of SQL Pass. Geoff's final example had a nice insidious implicit conversion example. He had a blog post on it called Leverage

Next session was a toss up between Tim Mitchell and SSIS scripting or @WesBrownSQL and the basics of "Understanding storage systems and SQL Server". Even though I was far more interested in SSIS, I needed to better understand luns, raid settings, spindles, rpms, sectors, heads, blah blah blah. It's hard for hardware to really sink in for me, especially since I'm so far devoid from it. At work we have SAN admins for storage, LAN admins for VMs, my DBAs and some other teams in the mix. Developers don't ever get to touch anything physical. Wes did a good job just covering some of the basics with clear delineations between theoretical vs actual or expected. He had a good voice of experience in his presentation and I'd be interested in his RAID calculator were I to be involved with hardware. I've also been out of the World of Warcraft for 1.5 years now but raid calculator still means something entirely not-disk related.

My final session was by Jason Massie on "Query plan crash course" I knew the name sounded familiar and it was @statisticsio. I started trying to take more notes than tweets and got this far

Iterators
* access methods
scans = sequential read

* lookups  
Fine if it's only hitting a very few rows (random reads)

* spools
Can be trouble

* joins
Merge & hash joins (reporting environments)
Nested loops usually preferable in OLTP
Nested loop with Yield (warning)

* distributed queries

* sorts, aggregates, top

* parallelism

before I got a voicemail from SuperShuttle. Trying to save about half the cab fair to DFW airport, I contracted with these yahoos to pick me up at Region 10 between 4:50 and 5:05 They called me at 3:13 stating they would pick me up at 4:30 unless I called them back. What, they can break the contract unless I call back and say otherwise? Bounced out of the session to retrieve my phone which had gotten bumped on and ran out of battery. In retrospect, while I doubt I'll do business with SuperShuttle again, it was fortuitous that they did screw things up as I got to meet Stuart Ainsworth, @stuarta in the flesh. He was one of my missing SQL Bingo squares from SQL Pass 2009! He had to bounce early or something and I never got a chance to make his acquaintance but Saturday paid that debt in full. We had a chance to talk with him and Tim about life, SQL Server and his experience with 3 SQL Saturday's under his belt (rock on Atlanta).

Tracks

The day was organized into 7 tracks with 6 topics each ranging from 100 to 300 level courses

• SQL Beginner
• Developer
• DBA
• BI
• Advanced
• Futures
• Bonus

Bag of swag

• Plastic, non-dishwasher safe, stainless steel mug/thermos contraption. NTSSUG logo on one side, SQL Saturday Dallas logo on the reverse
• One red wristband, used for admittance to the after party
• Full color, 24 page booklet covering everything you need to know
• Flier for SQL Nitro, an offering from http://www.nitrosphere.net/
• 6 page pamphlet from Matrix Resources, http://www.matrixresources.com/
• Dual sided flier from Microsoft with an invitation to stop by their table for a chance to win an XBOX Elite or a SQL Server branded Eclipse audio speaker. Backside listed their speakers, topics and times. 8 speakers listed + CSS
• A not-quite 1/4 page flier encouraging membership in SQL Pass. Advertised #24hop, virtual chapters and a $200 discount for SQL Pass, code SQLSAT3D
• http://www.expressor-software.com/ had a 4 page greyscale flier discussing their SSIS objects.
• Attunity brought out Best Buy gift cards as well as an opportunity to learn more about their Change data capture CDC suite for SSIS http://www.atunity.com/SSIS
• Confio had a flier describing their ignite8 product and was giving away a FlipVideo camera http://confio.com
• Red-Gate had a postcard promoting SQL Search, http://www.red-gate.com/
• CozyRoc had a tri-fold brochure describing their SSIS+ library http://www.cozyroc.com
• Another tri-fold brochure from http://WestClinTech.com While I appreciate their sponsorship of the event, they should fire their marketing people. Their XLeratorDB product might be the bees kness but their bullet points read like something a tweenager would write
  • SMRTR
  • FSTR
  • BTTR
  • EZY
  • CHPR
  • SAFE
• A notepad from Novedea http://novedea.com
• SQL Server R2 puffy sticker
• One business card with the NTSSUG logo on it to signify I had paid for lunch
• A raffle ticket that contained my unique number to be written on each session evaluation as well as my overall survey (at least if I wanted to win a prize I should use it)
• Overall event survey

All of the swag came in an opaque white bag with sponsors and logos listed on one site and the NTSSUG/SQL Sat Dallas logo on the opposite.

Lessons learned

• Overbook the event like the airlines. 80% turnout was an unofficial number I heard
• Volunteers will do anything, just tell them what to do
• Matching shirts are great for identifying staff vs participants
• Nametags would also be helpful
• If using software to record sessions, be sure it works with everyone's setup and it's installed beforehand
• A good wireless connection is a must
• Afternoon gelato was masterful stroke
• Speakers should practice and use zoomit
• Good speakers have scripts/checklists for their demos
• Good speakers have backups in case scripts fail
• Good speakers repeat questions before answering them
• Good speakers use large fonts in demos
• Everyone loves an iPad
• Clear signage is a must
• Have extra program booklets ready
• 500 is the sweetspot for sponsors as it's large enough to draw people but small enough they can get face time
• Don't go overboard with "big name" speakers. Grow local folks too
• Make sure there are plenty of writing implements for folks
• Budget for carpet cleaning or find some other way to keep people from having accidents
• MCI > DFW airport. Free wifi vs non-free

Areas for improvement

With a sampling size of 1 event, I didn't have much that I'd have improved. The Demo Room was upstairs and better signage indicating where the stairs were would have helped. The bathrooms were a little pungent after lunch and I didn't even go in there but the aroma was ... rich. I'm sure the lunch sessions were the pound of flesh for the sponsors, but I have to say I really loved the Birds of a Feather concept from SQL PASS.

References

http://technet.microsoft.com/en-us/library/bb508963(SQL.90).aspx

S#arp Architecture missing SharpArchApplicationWizard.dll

Big preface, I don't know diddly about NHibernate, MVC and the SharpArchitecture project.  I was simply looking for a simple CRUD builder.

While it's entirely possible this is user error, I tried to get started with SharpArchitecture and snagged the VS2008 version of the template only (no source).  Step 5 indicates I need to copy SharpArchApplicationWizard.dll from the download into the IDE folder.  Only problem is, for the 2010-05-10 release, it's missing that file.  Simple resolution is to grab the FullSourceAndTemplates build and then you can find the dll in the VisualStudioTemplate subfolder

Next issue is the templates.  I installed them as directed (Windows server 2003) but I cannot make Visual Studio acknowledge them.  I'll have to try this at home as I have little faith in the veracity of the work installs.  I have verified (Tools, Options, Projects and Solutions) that the value for User project templates location is set correctly "C:\Documents and Settings\bfellows\My Documents\Visual Studio 2008\Templates\ProjectTemplates"

<pre>

C:\>dir /b "C:\Documents and Settings\bfellows\My Documents\Visual Studio 2008\Templates\ProjectTemplates\Visual C#\Web"

CrudScaffolding

CrudScaffoldingForEnterpriseApp

SharpArchApplication.vstemplate

SharpArchApplicationTemplate

SharpArchProject.ApplicationServices

SharpArchProject.Core

SharpArchProject.Data

SharpArchProject.Tests

SharpArchProject.Web

SharpArchProject.Web.Controllers

SolutionItemsContainer

ToolsSolutionItemsContainer

__TemplateIcon.ico

C:\>

</pre>  That one I can chalk up to user error (hooray).  Don't actually unzip the thing, drop the zip file into the C#\Web folder

And now I get this error

---------------------------

Microsoft Visual Studio

---------------------------

A problem was encountered creating the sub project 'FundEditor.Tests'.  Access to the path 'C:\sandbox\FundEditor\FundEditor\FundEditor.Tests' is denied.

---------------------------

OK   

---------------------------

Not sure if that was a deal breaker but I clicked through.

Clicked F5 to run and build it.  No compilation errors but it did pop up a dialog indicating it was looking for

e:\WorkSpaces\Git\SharpArchitecture\Trunk\src\SharpArch\SharpArch.Data\NHibernate\NHibernateSession.cs

Found it in the FullSourceAndTemplates build and once found, it finally threw an exception

FluentNHibernate.Cfg.FluentConfigurationException was unhandled by user code and it won't copy the bits out but it said "Database was not configured through Database method"  Found this on StackOverflow but my lunch hour is up so I'll have to play another day.

PPrint for DataSet

I've make no bones about the coolness that is python and one of the methods available in there is pretty print (pprint). In my current project, I'm working with datasets and as much fun as it is to click through the locals window to examine the object or to try and catch the magnifying glass handle, I wanted something that would enumerate all the elements in a dataset and dump it to text. Call it a poorman's persistence mechanism. Thus, PPrint for datasets was born

/// <summary>
/// Pretty print a dataset
/// </summary>
/// <param name="ds">dataset that should be rendered to console</param>
public static void PPrint(System.Data.DataSet ds)
{
    System.Data.DataTable dt = null;
    if (ds == null)
    {
        Console.WriteLine("Dataset is null, n00b");
        return;
    }

    for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
    {
        dt = ds.Tables[tableIndex];

        // Determine header
        // Console.WriteLine(dt.Namespace);
        Console.WriteLine("==========================================");
        Console.WriteLine(dt.TableName);
        Console.WriteLine("==========================================");

        foreach (System.Data.DataColumn dc in dt.Columns)
        {
            System.Console.Write(string.Format("{0}\t", dc.ColumnName));
        }

        Console.WriteLine();

        foreach (System.Data.DataRow row in dt.Rows)
        {
            for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
            {
                Console.Write(string.Format("{0}\t", row[columnIndex]));
            }

            Console.Write("\n");
        }

        Console.WriteLine("{0} rows in table", dt.Rows.Count);
        Console.WriteLine("\n\n");
    }
}

TODO: sample input and output

Visual Studio 2010 upgraded project "could not be resolved because it has an indirect dependency on the framework assembly "System.Web"

I had a perfectly working application at work---Visual Studio 2008, Windows Server 2003, SQL Server 2008. I bring it home to Visual Studio 2010, Windows Server 2008 R2, SQL Server 2008 R2 and SharePoint 2010 (because I'm a masochist). I upgrade the project in place, recompile and it throws this error.

The primary reference "C:\src\Foo\Src\Foo\Foo\bin\Debug\Foo.dll" could not be resolved because it has an indirect dependency on the framework assembly "System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" which could not be resolved in the currently targeted framework. ".NETFramework,Version=v3.5,Profile=Client". To resolve this problem, either remove the reference "C:\src\Foo\Src\Foo\Foo\bin\Debug\Foo.dll" or retarget your application to a framework version which contains "System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a". FooDriverApp

My solution is a simple one. There are two projects, one is a C# library (Foo) and the other is a tester/driver (FooDriverApp). Foo, which should have the indirect reference to System.Web doesn't. I deleted references, did all sorts of things to try and correct the issue but to no avail. My cursory searches lead to similar scenarios but all of these people were moving from the 3.5 framework to 4.0 I won't have the luxury when I commit my changes back to the repository. My issue ended up being the upgrade process flipped the target framework in my FooDriverApp.csproj from ".NET Framework 3.5" to ".NET Framework 3.5 Client Profile" In the broken version of the .csproj file, this value of <TargetFrameworkProfile>Client</TargetFrameworkProfile> is set to Client. Changing the selector in the project configuration menu to point to the 3.5 results in <TargetFrameworkProfile>
</TargetFrameworkProfile>

References
.NET Framework Client Profile
Troubleshooting .NET Framework Targeting Errors

MelissaData AddrObj how I hate thee

I've been bitten twice in my life for using MelissaData for address cleaning/standardization. The original bug was when I was impulsive and foolish but CASSing certain formats of County Road would cause the COM component to just belly up and die. No event raised, nothing catchable, just dead. We had DTS wired up to use the AddrObj.dll and we'd be in the middle of processing tens to hundreds of thousands of rows of data and without notice we'd see the process was dead. After a few rounds of these failures, different sets of data, different addresses, I went to their website to look for help. Instead of help, I found their free address cleaner. Interesting observation: the same address that would cause our process to fail would cause their entire website to crash. After a few months of this off-and-on behaviour, we moved on to different products for address standardization.

Fast-forward to a year ago. We had the need to detect fraudulent activity based and one of those criteria involved checks being delivered to registered addresses. You can't match non-standard data so we needed to clean address data and our in-house choices were MelissaData and Trillium. I wanted Trillium to be our solution, I really did. Based on the way it's configured here however, I just couldn't make it work so I went with what I knew. Developed, tested and implemented and everything had gone peachy for almost a year until we patched on 29 April. From that point forward, we couldn't clean a day's worth of data (approximately 1k rows) without it going belly up. If you have the version 4, 0, 1, 1567 of AddrObj.dll the following code is the minimum reproduction I sent MelissaData that will cause their code to die a silent death. The address that is set in the LastLine property is amusing as it's only a single character different from the sample provided.

using ADDRESSOBJECTLib;

public class Cleaner
{

    /// <summary>
    /// A minimum reproduction of the failing code
    /// </summary>
    /// <param name="licenseKey">A valid license key for the AddrObj</param>
    /// <param name="dataFileFolder">Fully qualified path to the Data Files folder</param>
    /// <param name="lastLine"></param>
    public static void Fail(string licenseKey, string dataFileFolder, string lastLine)
    {
        AddressCheck checker = null;
        checker = new AddressCheckClass();
        checker.SetLicenseString(licenseKey);
        checker.SetPathToUSFiles(dataFileFolder);
        AddressObjectErrorCodes addressObjectErrors;
        addressObjectErrors = checker.InitializeDataFiles();
        checker.ClearProperties();
        // This is where the failure will occur
        // Try/catch will make no difference as no exception is raised
        checker.LastLine = lastLine;
    }
    
    static int Main(string[] args)
    {
        string licenseKey = string.Empty;
        string libraryPath = string.Empty;
        licenseKey = "XX-XXX-XXX";
        libraryPath = @"D:\COM Objects\Data Files";
        string lastLine = "Rancho Sa Margarita, CA 92688-221A";
        Cleaner.Fail(licenseKey, libraryPath, lastLine);
     }

}

I grew up, I contacted MelissaData and worked with their tech support rather than piss and moan. To their credit, they did respond with a pair of fixes in 2 days time. The first was a beta version of the new address object but it was too wrapped in caveats---plus I wasn't the only user of this component. Fortunately-ish, the other team experienced a failure on the same day the patch email came in so what went from a low priority production fix to ZOMG-WE-ARE-DEAD-IN-THE-WATER-EVERYONE-LOOK-HERE was able to be handled with some grace. The resolution we took was to recover the AddrObj.dll from our previous disc, replace the existing dll and re-register it. Our automated processes ran fine this morning so I am a happy camper.

TSQL templates

A twitter conversation inspired this post. @sqlrunner and @MladenPrajdic were discussing the tweet "Mandate: Name of the developer who creates/updates a stored procedure. No anonymous procedures should be allowed."

While nothing fancy, I have had these two set up in my TextPad clip library for ages. If I need to create a new proc/fxn, this gets me started. What I think is important about this is these are the headers. Much like I've said before, the undocumented code is not worth reading. Beyond tombstone information (method name, author and date), the purpose of the method can help so much in understanding the intention of a new codebase. The Recordset portion can be more trouble than it is worth but queries for SSRS can be worth the trouble to call out the elements being returned in case they are aliased "funny." Side-effects are obviously handy in stored procs

TSQL Procedures template

IF EXISTS
(
    SELECT
        SO.*
    FROM
        dbo.sysobjects SO
    WHERE
        SO.id = OBJECT_ID('dbo.<TEMPLATE>')
        AND OBJECTPROPERTY(SO.id, 'IsProcedure') = 1
)
BEGIN
    PRINT 'Dropping stored procedure dbo.<TEMPLATE>'
    DROP PROCEDURE dbo.<TEMPLATE>
END
PRINT 'Creating stored procedure dbo.<TEMPLATE>'
GO
-----------------------------------------------------------------------------
-- Function: dbo.<TEMPLATE>
-- Author: Full name
-- Date: yyyy-mm-dd
--
-- This procedure ...
--
-- Recordsets:
-- None
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- yyyy-mm-dd username
--
-----------------------------------------------------------------------------
CREATE PROCEDURE dbo.<TEMPLATE>
(
)
AS
BEGIN
    SET NOCOUNT ON

END
GO
PRINT 'Granting rights for dbo.<TEMPLATE>'
GRANT EXECUTE ON dbo.<TEMPLATE> TO []
GO

TSQL Function template

This is the template I work from for developing functions.

IF EXISTS
(
    SELECT
        SO.*
    FROM
        dbp.sysobjects SO
    WHERE
        SO.id = OBJECT_ID('dbo.<TEMPLATE>')
        AND OBJECTPROPERTY(SO.id, 'IsDeterministic') IN (1, 0)
        AND OBJECTPROPERTY(SO.id, 'IsView') = 0
)
BEGIN
    PRINT 'Dropping function dbo.<TEMPLATE>'
    DROP FUNCTION dbo.<TEMPLATE>
END
PRINT 'Creating function dbo.<TEMPLATE>'
GO
-----------------------------------------------------------------------------
-- Function: dbo.<TEMPLATE>
-- Author: Full name
-- Date: yyyy-mm-dd
--
-- This function ...
--
-- Recordsets:
-- None
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- yyyy-mm-dd username
--
-----------------------------------------------------------------------------
CREATE FUNCTION dbo.<TEMPLATE>
(
)
RETURNS
AS
BEGIN
    

END
GO
PRINT 'Granting rights for dbo.<TEMPLATE>'
GRANT SELECT ON dbo.<TEMPLATE> TO []
GO

Some real-world examples of what this would look like are below

-----------------------------------------------------------------------------
-- Function: dbo.CareerDevelopmentConferenceAdvisorProgramLoad
-- Author: Bill Fellows
-- Date: 2008-05-07
--
-- This procedure will load the data into the advisor program table.
-- In essence, this records the current progress of an advisor in the CDC
-- program.  This does not touch the event history table as a seperate process
-- handles that.
--
-- Recordsets:
-- None
--
-- Side-effects:
-- Existing CDC program records will be purged from SR_M_ADV_PGM_S
-- New CDC program records will be added to SR_M_ADV_PGM_S
--
-- See also:
-- dbo.CareerDevelopmentConferenceAdvisorProgramCandidateGet
--
-- Modified:
--
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Function: dbo.AltPayeeReportingPeriodCandidateGet
-- Author: Bill Fellows
-- Date: 2009-07-16
--
-- This function will identify "new" reporting period dates in the altate payee
-- reporting table.  It will do this by finding periods that do not exist in
-- the advisor reporting table and returning the oldest.  The assumption being
-- if there are two rows present, the newest is currently in the process of
-- being populated.
--
-- Recordsets:
--  adv_id Advisor id
--  acct_idn Account number
--  rptg_prd_dt Reporting period
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- 2009-08-20 bfellows
-- Due to systematic redemption business rule, we should only encounter 0-2
-- reporting periods in the redemption table that do not exist in the advisor
-- reporting table.  The loading of the "other" reporting tables depends on the
-- FOO process populating shareholder account balance.  With all of that
-- in mind, this will only return the oldest reporting period date of the two
-- potential rows.
--
-- 2009-10-05 bfellows
-- Logic incorrect on determining candidates.
-----------------------------------------------------------------------------

Final cautionary note, don't trust comments in source code. They may be out of date, written by a habitual liar, sociopath, etc. Instead, treat it like Garmin/Google/MapQuest navigation directions---they are for planning purposes only. The only truth lies in the code, so get out your lamp and start looking.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

I have made no progress since 2008 on the SSIS Uploader. I still think it has merit and I hope to finish it some day. Today is not that day.

Background

At work, we are in the midst of rolling out SQL Server 2008. No R2, just vanilla SQL Server 2008 (named instance) in a side-by-side installation with the 2005 default instance. We are not in production yet but today marked the first issue we had encounted with it. We deploy SSIS packages via the dtsinstaller. It consumes a manifest file (XML) and deploys all of the specified packages into the destination location: file system or into SQL Server (msdb) the installer handles them both. We encountered the following error message today "Unable to find an entry point named 'DTSSKUIsWKGorUnder' in DLL 'dts'. (DTSInstall)." When Google turns up one hit and it's to a Connect article, that's never an encouraging sign. Labouring under the delusion that dtutil will work better than the fancy dtsinstaller, the thought struck me that I could get the benefits of a command line deploy and still incorporate the deployment manifest. For extra points, it seemed like a good reason to use PowerShell to glue it all together.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

The following script is my first pass at enumerating the elements of a SSISDeploymentManifest file and using dtutil to install those packages to a 2008 named SQL Server instance. Ain't that a mouthful?

# 2010-04-30
# Bill Fellows
# This PowerShell script is designed to shred an SSISDeploymentManifest
# and deploy file based SSIS packages into a SQL Server instance.
#
# Being the extreme novice with PowerShell, everything is hardcoded and 
# it assumes you have dtutil installed on the same drive as this script.
# Possible enhancements would be to look in the registry for location, 
# accept parameters, encapsulate code into a cmdlet, others as they come up

$format = """\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe"" /file ""{0}"" /DestServer {1} /COPY SQL;""{2}"""

# This deploy script assumes all packages are in the same folder as the 
# manifest
$fileName = "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\SSISHackAndSlash.SSISDeploymentManifest"

# The destination server, this should be a parameter
$destinationServer = "localhost\SQL2008"

$baseFolder = [System.IO.Path]::GetDirectoryName($fileName)

[xml] $list = Get-Content $fileName

foreach($package in $list.DTSDeploymentManifest.Package)
{
    $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)   
    
    # This might need to be a relative path
    $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)
    
    $cmd = [string]::Format($format, $fullyQualifiedPackage, $destinationServer, $basePackage)
 
    cmd /c $cmd
}

The script is simple enough. Given a manifest file in the form of

<?xml version="1.0"?> 
<DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true">
   <Package>Package.dtsx</Package>
   <Package>Package2.dtsx</Package>
   <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

it will simply run the following commands

"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package2.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package2"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package7.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package7"

What I like about this solution

• Scriptable---our deploys no longer require UI interaction so RDP rights no longer required
• Uses manifest file---Our devs are trained to look at the manifest as the definitive source for SSIS deployments
• It uses PowerShell

The big drawback in my mind, is that there is something buggered up with our servers. Ultimately, this is a workaround until we resolve that issue but if it becomes our defacto deploy method, I think we'll be better off.