Exporting to XML error in SQL server 2000

I have written these SP to export:

CREATE PROCEDURE CREATEXML
AS


select 1 as Tag, NULL as Parent, trans_date as [Item!1!trans_date], system_qty as [Item!1!system_qty],
part_name as [Item!1!part_name]
from fastpic.FP_INVTRANS as A
Where trans_date = (select max(trans_date) from fastpic.fp_invtrans i
where a.part_name = i.part_name)and trans_type <> 'ORDER COMPLETE'
order by trans_date desc
for xml explicit
GO


CREATE PROCEDURE RUNXML AS


DECLARE @cmd varchar(200)
set @cmd = 'bcp "EXEC fastpic2.dbo.CREATEXML" queryout c:\parts.xml -S. -Usa -P -c -r -t'
exec master ..xp_cmdshell @cmd  
GO


I get a fileexported but it is not a complete file. I get this error in the output window:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client][SQL Server]Warning: Server data (2033 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.



anybody has any ideas? does this error mean that my fields are too large? that's not possible.

thanks


lytung818Asked:
Who is Participating?
 
GranModConnect With a Mentor Commented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0
 
lytung818Author Commented:
I also read that using this method there is a limit in size...i was thinking about trying this method

sp_makewebtask @outputfile = 'c:\myxmlfile.xml',
@query = exec runxml,
@templatefile = 'c:\scripts\template1.tpl'


but it didn't work .How do i use a store procedure instead of a select statement in the @query?

thanks a lot.
0
 
Anthony PerkinsCommented:
>>anybody has any ideas? does this error mean that my fields are too large?<<
I suspect that BCP is seeing it as one field and so the answer is yes.  I suggest you use a front-end app to get the Xml document using something like VB or .NET
0
 
lytung818Author Commented:
thanks but i solved the problem ...


i did this

sp_makewebtask @outputfile = 'c:\allParts.xml',
@query ='select trans_date, system_qty, part_name
from fastpic.FP_INVTRANS as A Where trans_date = (select max(trans_date) from fastpic.fp_invtrans i
where a.part_name = i.part_name) and trans_type <> ''ORDER COMPLETE''
order by trans_date  desc for xml auto',
@templatefile = 'c:\scripts\template1.tpl'
0
 
Anthony PerkinsCommented:
>>thanks but i solved the problem ...<<
Great!  Please close the question.  See here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.