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.
