statement too long for bcp? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

statement too long for bcp?

Author

Topic

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2002-04-08 : 05:26:32

Folks,

another problem. Well not really a "problem" as the code does what it is supposed to but I have error messages that I do not understand.

I want to dump some custom log tables (own sp´s activities log, not server log) to XML files. I use BCP to do that and it runs fine. The dumps are there and look good but nevertheless bcp gives me errors like:


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.

I guess that my long "...for xml explicit" statement that I pass to bcp is somewhat too long as I do not get this error for short statements.

Is there anything like a max legth for statements in bcp?

Daniel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 09:51:22

Can you rewrite your SQL statement (including the FOR XML EXPLICIT part) as a stored procedure? Then you can have bcp use the "exec sp_getXML" call instead of a long statement.

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2002-04-09 : 03:25:46

Did that. And did some more tests.

I get the errors when I use bcp´s option -r, that I currently use to concatenate the xml rows the procedure returns. If I don´t set the option I have space characters in the output file that break up the xml (so IE cannot render it).

Daniel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-09 : 08:56:56

Yeah, I forgot about that. Take a look at this post:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9336

The bcp settings that are in there should fix it.

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2002-04-10 : 03:49:02

Tried some combinations of flags. Here is the result:


-c      : XML broken, no BCP errors
-c -t   : XML broken, no BCP errors
-c -r   : XML OK, BCP gives errors
-c -r -t: XML OK, BCP gives errors

Looks like I have a XOR situation here. I could live with BCP giving errors as long as the XML is OK but I really don´t like the errors. I know that -r does the right thing but there seem to be side effects...

Daniel

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2003-03-07 : 10:26:33

Remember this one. Some time ago...

Well now a new problem arises. We recently applied SP3 to one of our machines and on exactly this machine bcp produces line breaks in the output file, no matter what parameter switches we use.

Has anybody experienced problems with bcp regarding SP3?

Daniel

axyl99
Starting Member

1 Post

Posted - 2004-05-25 : 15:37:34

I am getting the same result. Can you let me know if you were able to figure out the solution.

Thanks

James Birch
Starting Member

2 Posts

Posted - 2005-04-08 : 09:24:44

Hi guys,

I've figured this out. This happens when we encounter the 2034 line length break in the SQL Server ODBC driver (don't know why it says 3046 in the message). It's just a warning to let us know. Because we have already set the line break character to be nothing this has no adverse effect on our output.

To put it another way, you will get the same number of these error messages as lines of output when running the query/proc in query analyser (which breaks at 2035 characters).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources