Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daletrotman
Starting Member
3 Posts |
 Posted - 2006-07-07 : 09:56:48
|
| 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 = 0Error = [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.htmlbcp "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!Should have read BCP and not BCL as writtenMy 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 anticipationDale |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-10 : 08:07:19
|
| BCP is doing anything "bulk" here, its just a tool to output the contents of the query - which happens to output XML.What about a little VB-type program that connects to database, runs query, outputs results to file?Kristen |
 |
|
|
|
|
|
BCP to XML warning messages