Hey Yo! SQLCMD may truncate your XML!

SQL Server 2005

While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated.

The script was similar to:

DECLARE @appconfig XML
SET @appconfig = '<configuration></configuration>'
... Insert configuration nodes
-- Output the config
SELECT @appconfig as ApplicationConfiguration

The default options for SQLCMD produced output like:


(the value was a little longer, but cut off at the end)

Clearly the xml was not valid. There's header text included, corrupted tags, and missing closing tags. So I hunted through books online and google for an answer.

Turns out I needed to use the -y 0 option, which will output xml columns up to 1MB in size.

Also the -h-1 (without any spaces) will remove the header from the output.

If you are planning to use SQLCMD to output XML, remember: Hey Yo! -h-1 -y 0!

sqlcmd -h-1 -y 0 ...

Read more on SQLCMD Utility Options

  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz

Hey Yo! SQLCMD may truncate your XML! | 2 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
Hey Yo! SQLCMD may truncate your XML!
Authored by: Anonymous on Tuesday, July 20 2010 @ 06:10 AM AEST


very helpful !!!! Save lots of work for me!!!

Thanks a LOT !!!!

[ # ]
Hey Yo! SQLCMD may truncate your XML!
Authored by: Anonymous on Thursday, May 12 2011 @ 09:53 AM AEST

I was fighting with this for almost an hour and saw you post using -y 0 instead of -Y 0 (which is what I was using) so I tried that and it worked.  Thank you very much.

[ # ]

Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 Copyright © 2018 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.