Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Server returned column limit: osql utility

Posted on 2003-02-25
Medium Priority
Last Modified: 2008-03-03
I am using a statement similar to this in an attempt to retrieve XML to a file:

EXEC master..xp_cmdshell 'osql -h-1 -w10000 -S ben -U todd -P todd -q "export_needs_xml" -o c:\test.xml'

In the stored procedure I am using XML EXPLICIT to create the XML. The problem is that the record returned is broken up into multiple rows, each roughly 2037 bytes.

When the data is written to an XML file, the XML is invalid because of the splits.

Is there a way that this can be modified???
Question by:tpatten
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

KatanaCS earned 400 total points
ID: 8021442
Instead of using OSQL from xp_cmdshell, you may want to have the query write the XML to a table (global temp [##] is ok) that has a field with a TEXT datatype.  From there, you can use xp_cmdshell to BCP OUT the XML stored in the table to your XML file.

Create Table ##XmlOut (XMLText TEXT)
Insert Into ##XmlOut EXEC export_needs_xml

Exec master.dbo.xp_cmdshell 'bcp "Select XMLText From ' + db_name() + '..##XMLOut" queryout c:\test.xml -c -S' + @@ServerName + ' -T'


Expert Comment

ID: 9276338
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question