Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BCP warning messages when outputing XML

Posted on 2006-07-07
3
Medium Priority
?
1,215 Views
Last Modified: 2008-01-09
These warning messages are killing my processing time!

I am getting this warning message when running BCP within the SQL SERVER Query Analyzer FOR EVERY LINE CREATED!...

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (3046 bytes) exceeds host-file field length (0 bytes) for field (1).  Use prefix length, termination string, or a larger host-file field size.  Truncation cannot occur for BCP output files.

Now I realise this is just a warning message and comes about because of the -r option with the BCP command. I have seen this post http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20546470.html

bcp "SELECT * FROM [dbo].[_CommunityStaging] FOR XML AUTO, ELEMENTS" queryout c:\temp\_CommunityStaging.txt -Scas074\cas074 -c -r -T

...but it really hits the processing time!

If I run the same command without the -r (row terminator) option...
bcp "SELECT * FROM [dbo].[_CommunityStaging] FOR XML AUTO, ELEMENTS" queryout c:\temp\_CommunityStaging.txt -Scas074\cas074 -c -T

... it runs in 10 seconds but does not form proper XML due to the carriage return it places into the code. When run the correct way (first example) it takes 2 minutes 38 seconds.

This is not going to scale up well when I perform the same request on many tables!

My question is: Is there a way to stop the warning messages coming through and therefore speed up the processing?

     If not is there a way to use another method to output the XML? I know osql is an option but this gives me an identifier at the begining of the file, loads of hyphens, and loads of spaces between XML. Completely useless to me.

Many thanks in anticipation

Dale
0
Comment
Question by:daletrotman
[X]
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
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17062146
Consider writing a VB or .NET app to extract the Xml data.  It should not be more than 10 lines of code (depending on the number of parameters).
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17631636
PAQed with no points refunded (of 500)

CetusMOD
Community Support Moderator
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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