skip to Main Content

SQL Server Implicit String Conversion/Concatenation (XML parsing: line <x>, character <y>, unexpected end of input)

If you are getting this error in your SQL Server T-SQL script:, you may be running into an issue with implicit string conversion in SQL Server:

declare @xml varchar(max), @doc XML, @stringVariable varchar(256)
set @stringVariable = 'a string value'

-- @doc is set by concatenating multiple string literals
-- and string variables, with all the variables having less than
-- or equal to 8000 characters
set @xml = '<root>' +
... + @stringVariable +
...
'</root>'

print len(@xml)

set @doc = @xml

Output

8000
Msg 9400, Level 16, State 1, Line 4
XML parsing: line 64, character 74, unexpected end of input

As you can see in the output, the @xml variable was truncated to 8000 characters, resulting in an invalid XML. This is due to the way SQL Server performs implicit string conversions when concatenating strings. When all the string literals/variables involved in the concatenation are 8000 characters or less, the resulting string will be exactly 8000 characters.

The same issue occurs with NVARCHAR data type. Instead of the 8000-character limit, it’s 4000 characters.

A simple fix is to make sure at least one of the string variables is of type VARCHAR(MAX):

declare @xml varchar(max), @doc XML, @stringVariable varchar(256)
declare @x varchar(max)

set @stringVariable = 'a string value'
set @x = ''

set @xml = @x + '<root>' +
... + @stringVariable +
...
'</root>'

print len(@xml)

set @doc = @xml

More Info

Using a Different Configured Binding in WCF Client

To programmatically switch bindings on the fly, you can do it via the constructor of the generated client:

var client = new WeatherClient(“MyEndpoint”);

“MyEndpoint” is the name of the endpoint defined in your config file:

<client>
    <endpoint address="" binding="basicHttpBinding" bindingConfiguration="http1" contract="MyContract" name="MyEndpoint" />
</client>

WCF Client Error “The connection was closed unexpectedly” Calling Java/WebSphere 7 Web Service

If you get the following exception calling a WebSphere web service from your .NET WCF Client (service reference):

System.ServiceModel.CommunicationException: The underlying connection was closed: The connection was closed unexpectedly. —>  System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly.

Try adding this code before the service call:

System.Net.ServicePointManager.Expect100Continue = false;

More info on the 100-Continue behavior from MSDN.

Give The Power of Speech and Sound to Your PowerShell Scripts

Do you ever have the problem where you start a long running script (such as running a code build), multi-task on something else on another monitor while waiting for the script to finish, and then totally forget about the script until half an hour later? Well, here’s a solution your problem: have your script give you holler at you when it’s done.

Hello sir! I am done running your command!

In my library script file, I have the following functions to play sound files and to speak any text:

function PlayMp3($path) {
    # Use the default player to play. Hide the window.
    $si = new-object System.Diagnostics.ProcessStartInfo
    $si.fileName = $path
    $si.windowStyle = [System.Diagnostics.ProcessWindowStyle]::Hidden
    $process = New-Object System.Diagnostics.Process
    $process.startInfo=$si
    $process.start() 
}

function PlayWav($path) {
    $sound = new-Object System.Media.SoundPlayer;
    $sound.SoundLocation="$path";
    $sound.Play();
}

function Say($msg) {
    $Voice = new-object -com SAPI.SpVoice
    $Voice.Speak($msg, 1 )
}

If you like the text-to-speech feature but find Windows’ speech engine lacking, check out Ivona. It’s a commercial text-to-speech engine but you are allow to generate and download short speech files for free personal use. Now, my script can nicely interrupt me to tell me when it’s done. Other online text-to-speech engines: vozMe, SpokenText.

If Making Noise Is Not Your Thing

If making noise is not your thing, consider displaying a message in the Notification Area. Here’s the code (courtesy Microsoft TechNet):

Build complete!
function Get-ScriptName {
    $MyInvocation.ScriptName
} 

function DisplayNotificationInfo($msg, $title, $type) {
    # $type - "info" or "error"
    if ($type -eq $null) {$type = "info"}
    if ($title -eq $null) {$title = Get-ScriptName}

    [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    $objNotifyIcon = New-Object System.Windows.Forms.NotifyIcon 
    # Specify your own icon below
    $objNotifyIcon.Icon = "C:CdoScriptsFolder.ico"
    $objNotifyIcon.BalloonTipIcon = "Info"  
    $objNotifyIcon.BalloonTipTitle = $title
    $objNotifyIcon.BalloonTipText = $msg

    $objNotifyIcon.Visible = $True 
    $objNotifyIcon.ShowBalloonTip(10000)
}

Automating Maven with PowerShell

I found out the hard way that mvn (Maven) on Windows always return a success code of true, which means you cannot use the return code ($?) to check whether the mvn command succeeded or failed. Why they decided to break this seemingly basic program contract is a mystery. A work-around is to scan the mvn output and look for specific strings such as “BUILD SUCCESSFUL”.

Here’s how:

function InvokeAndCheckStdOut($cmd, $successString, $failString) {
    Write-Host "====> InvokeAndCheckStdOut"
    $fullCmd = "$cmd|Tee-Object -variable result" 

    Invoke-Expression $fullCmd
    $found = $false
    $success = $false
    foreach ($line in $result) {
      if ($line -match $failString) {
       $found = $true
       $success = $false
       break
      }
      else {
       if ($line -match $successString) {
        $found = $true
        $success = $true
        #"[InvokeAndCheckStdOut] FOUND MATCH: $line"
        break
       }
       else {
        #"[InvokeAndCheckStdOut] $line"
       }
      }
    }

    if (! $success) {
      PlayWav "${env:windir}\Media\ding.wav"
      throw "Mvn command failed."
    }

    Write-Host "InvokeAndCheckStdOut <===="
}

function InvokeMvn($cmd) {
    InvokeAndCheckStdOut $cmd "BUILD SUCCESSFUL" "BUILD FAILED"
}


InvokeMvn "mvn clean install"

See Also

Back To Top