Using Powershell and TSQL to automate XML based integrations

As some of you know, I work for a software provider that provides Rental and Leasing software to a breadth of different industries (Oilfield Tools and Service Companies, Construction, General Rental, Truck/Trailer Rental and Leasing, and more).

The last year or so has seen a huge uptick in clients wanting to integrate with various systems (web-based telematics, EDI providers, and more) and more often than not the interface is XML documents. In an effort to provide faster and more end user editable solutions I've begun helping clients develop Powershell solutions rather than a more traditional service or application based approach.

I figured it'd be a useful Blog entry as i've learned quite a bit along the way and hopefully this blog post can help someone avoid the same issues.

Premise:

I've got clients wanting to send XML documents based on sales order data in an OLTP system and are asking me for options to streamline the process.  Traditionally we'd just write them a new windows service that handled it, however in recent times i've been tinkering more and more with using Powershell scripts to handle things like this.

1.  Original Attempt

As i'm still relatively new to Powershell my first inclination was to simply assign the value of the query i designed to a variable.  The query has been in use for months now manually so i was confident that the PS would be really straight forward since i already had the "hard" part done.

I utilized the invoke-sqlcmd -query cmdlets along with my query and connection information and went on my way developing the solution.  Everything was trucking along, i added additional functionality and smilingly delivered the solution to my client and let them embark on testing...

2.  Problems with truncating data

Well, not too long into testing the client started notifying me that the XML i was generating via the PS was being cut off.  The problem was the truncation just didn't make any sense.  I could take the query and run it in SSMS and the result was fine.  However, if i took the same query and executed it and assigned it's value to a PS variable i consistently saw the data being truncated.

It was giving me some cryptic errors along the lines of " Unexpected end of file while parsing Name has occurred. Line 3, position 3359"...as most of you reading this are thinking, OFF to google we go!

3.  Findings and musings on the subject

Once you start googling around, either my google foo is weak, or you'd swear this has never been run across from the angle i'm working.  You see all types of issues relating to Python, processing XML into and out of SQL Server, etc, etc.

Then I stumbled across this amazing post!  Not sure why i read it further as the original question didn't sound like it had anything to do with my problem...but alas, the code snippet in the answer gave me an idea...

4.  Solution

I re-wrote my PS solution to include a function that took a parameter of a sql query string, created a connection then ran a while reader.read loop....IT WORKED! IT WORKED!  I couldn't believe it, hours or googling spent trying to find the exact answer i needed, then just on a whim reading a seemingly similar issue the inspiration hit me that solved my solutions problem!

Here's the snippet i added to create my function...

Then when i need to assign the "large" variables, i use the function as so.  (actually have gone to creating this function and using it any time i have query based variable assignments!)

In closing, the point of this post is to let you know that there is some quantum allowance to variable assignment in Powershell that will essentially force truncate data assignment if not handled in this or a similar manner.  Hopefully this saves someone else hours of frustration, and at a minimum it'll get me some more blog posts and some feedback.

1st SQL Saturday Presentation

Well, as some of you know i recently (last few months) started speaking at our local user groups.  This weekend was my first time speaking at a SQL Saturday.  In addition to my planning duties this year i figured i wanted to give speaking on a larger stage a shot.

After running around like a chicken with my head cut off most of the morning, going to my room to speak was actually one of the more relaxing things i did!

I wasn't really expecting much attendence wise as being a beginner topic, i'm never sure what the demographics of the attendees is so it's hard to judge.  I was expecting to be happy with 5-10 people in the room...there was more than that sitting down when i got into the room 10 minutes early!

Anyone who's chatted with me knows I don't typically have a problem speaking in front of people, so i started some idle chatter with the attendees already present. This worked well because it really just flowed right into my session so there wasn't any awkward silence or dull crowd folks were easily engageable so that made me much more at ease.

The presentation actually felt like it was going really well until i got to my demos.  In an effort to make a "longer running demo" i modified one of my demos the morning of and apparently didn't test it well enough because it ended up pulling my machine to it's knees for about 6 minutes that i hadn't planned on.  I already had this topic pretty tightly scheduled, but i'm the kind of guy who likes to get alot of info in a session so i figured my style would lend towards the same.  The time spent trying to kill my first demo ended up making me cut my second demo, 2 discussion slides, and Q&A short.   I was expecting this to really turn off my attendees.

As i wrapped up, i realized my proctor hadn't handed out slips...this i thought would be the end of me.  How can i cut my presentation short, shove some slips in front of folks as the time had already expired, and expect any type of decent reviews.  Lets just say i wasn't immediately pumped about sitting down to read through them.

I met up with some colleagues and chatted awhile and it wasn't for about 30 minutes that i actually sat down to go through them.  I had received 18 feedback forms, that's AWESOME.  I know not everyone took the time to and of those 18 i got 7-10 actual comments all of which were encouraging.  When the "worst" feedback was "you need a longer session"...i felt like i'd just conquered another country single-handedly or something.  It was such a great rush.

Then while moseying around during lunch one of my attendees stopped me to say how much he enjoyed my session and asked me a few questions about some recent issues he was having and steps he'd taken to get a second opinion on if he was going about his issues the right way.  After a good conversation, he told me that he enjoyed and got more out of my intro level sessions than he'd gotten out of some bigger conferences and trainings that he'd been to....lets just say i was riding cloud9 over here!

The day was an exciting, exhausting, fun-filled day of responsibilities, learning, and sharing knowledge unlike anything i'd experienced before.  Speaking at the event made a world of difference compared to all the other times i've "just attended".

I'd recommend that any of you that read this and have even an inkling of an idea to speak.  DO IT, hone your presentation at company lunch and learns, give them at local user groups, even ask to get some mentoring/review on a personal level from a respected peer.  It was so much more rewarding actually giving back to the community and getting the appreciative feedback than it ever has been simply consuming other peoples knowledge!

Wait Stats Presentation

Finally! another contribution to my blog...

As you may or may not know, my primary interests in SQL Server tends to revolve around performance tuning and troubleshooting.  Working for a software solution provider it's become painfully obvious over the years that our clients either

1. Don't have the correct IT resources to maintain and troubleshoot their environment
2. ...Or if they do, they aren't willing to troubleshoot / tune / identify problems that could possibly be written off as a "vendor database problem"

Below is the presentation I have begun working on for local user groups, internal meetings, and possibly even client consumption one day regarding utilizing wait stats in SQL Server to form a basis for performance troubleshooting, ENJOY!

Summit 2013 Recap

Summit 2013 marks my 3rd consecutive Summit, and i must say this one was GREAT!  Made some great connections, attended some amazing sessions, and even had some great discussions with vendors regarding some projects i've got cooking and how their offerings could help me going forward.

This next year could be a tremendous step forward for my SQL Server career as i'll finally have the opportunity to start putting my money where my mouth is in terms of capabilities and offerings i can bring to my company with my knowledge.

Now for a quick few top session reviews in no particular order:

• Paul Randall's - Waits, Latches, and Spinlocks: Internals and Analysis
• VERY well discussed topic, seeing a 500-level session offered by someone of his caliber i expected to be completely blown away.  Instead, the discussion deftly blended 100-500 level material in a way that i was able to follow very well (to my surprise and excitement).  While the session went nearly 30 minutes over, the room was still 98% full when we let out

• Jonathan Kehayias - SQL Server Archaeology: Dig into the Past with system_health
• Extended Events are one of thsoe areas that sound really cool, i just hadn't had an opportunity to dive into them.  This session was a great intro into them by showing the utility of the default system_health session.  I'm really excited to get back to the office to review the slide deck and improve some of my skills in more rapidly resolving deadlocks and other log related issues that this session shed some light on for me
• Paul Randall's - Index Fragmentation: Internals, Analysis, and Solutions
• This has always been one of those topics that i didn't really understand, i just did rebuilds as part of maintenance plans, in the rare event of a "small" table having query performance problems, i'd just recommend a rebuild...but sitting through this class i now have a much greater appreciation for just what the impact of index fragmentation is and how the different tools can apply to help fix the issues.  I also took a great appreciation away on just how bad page splits can be and how/why i should try to opitmize my systems to remove/mitigate them.
• Paul White's - Parallel Query Execution
• another one of those topics that i understood enough to probably make terrible decisions in the past on!  i'm hoping that a thorough review or 3 of this session will help me make better decisions both in tuning the performance on existing servers as well as helping me make better recommendations to my dev team to better leverage the capabilities available with the PROPER use of parallelism to the extent we can control

Obviously those aren't the only great sessions i attended, just the ones that are most notably in the forefront of my mind as i leave the conference.  I want to give a big warm shout out to all the Pass HQ folks as well as all the speakers and sponsors for making this amazing learning and community event happen.  Looking forward to Summit 2014 and the return to Seattle!

Getting Back into it

Well, i started this blog back in 2011 after speaking to a ton of people at the Pass Summit 2011, well, seeings how this is only my second blog entry, i guess that plan didn't work too well.  I'm going to try and blog at least weekly this time.

This blog will focus on my journey through SQL Server and it's various facets.  Likely focusing on my certification path and awesome things i experience along the way.  I might even throw in random musings, but don't want the blog to divert into randomness so i'll try to keep those to a minimum.

Finally starting the Journey

Well, alittle about myself.  I've been a Project Manager primarily focusing on Business Analysis, in my current position, i also find myself working alot in SQL Server to load, manipulate, and report on data.

About 2 years ago, i made a concerted effort to start applying myself to growing my knowledge of SQL Server workings and associated technology.  I've been tinkering around with various technologies with no real direction (SSIS, SSRS, Sharepoint with BI, etc), but this years SQL Summit has really motivated me to try and focus my learning path a bit.  I have a few new books and such that i will be reading shortly and hopefully it will help me focus my efforts some.

I don't plan on advertising this blog much until i get the hang of this...i mean starting Twitter and Blogging all in the same week...it's going to take some getting used to.