lytung818
asked on
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
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
>>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
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
ASKER
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'
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'
>>thanks but i solved the problem ...<<
Great! Please close the question. See here:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
Great! Please close the question. See here:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.