[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

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


0
lytung818
Asked:
lytung818
  • 2
  • 2
1 Solution
 
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
 
GranModCommented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now