Need help: Openxml failed for datasize greater than 120k

Hi,
Can anybody help me on what is going wrong, please?
I tried to pass an XML document as text parameter in a stored procedure
where openxml is being used to insert data into temp tables. If I pass any
XML document over 120k of size it goes in a loop and does not return any
error message. The SQL server is 2000 standard edition with service pack 3
running on windows 2003 server and it has all the latest versions of MSXML
(version 2, 3 and 4. All with latest service packs).
However if I try to execute it in my laptop (win XP professional, MSDE 2000
with service pack 3), it works fine. I tried with 500k XML data and it can
process.
Am I missing anything?
thanks
-Asir
Message posted via http://www.webservertalk.comHi
I assume you are calling SQLXML which uses MSXML? In which case make sure
that you have download the latest service pack (sp3). Without DDL, code or
example data it is not possible diagnose this problem. Check the differences
in the structure of the small files compared to the larger ones. You may wan
t
to try posting to microsoft.public.sqlserver.xml to see if anyone there has
more to add.
John
"Asir Sikdar via webservertalk.com" wrote:

> Hi,
> Can anybody help me on what is going wrong, please?
> I tried to pass an XML document as text parameter in a stored procedure
> where openxml is being used to insert data into temp tables. If I pass any
> XML document over 120k of size it goes in a loop and does not return any
> error message. The SQL server is 2000 standard edition with service pack 3
> running on windows 2003 server and it has all the latest versions of MSXML
> (version 2, 3 and 4. All with latest service packs).
> However if I try to execute it in my laptop (win XP professional, MSDE 200
0
> with service pack 3), it works fine. I tried with 500k XML data and it can
> process.
> Am I missing anything?
> thanks
> -Asir
> --
> Message posted via http://www.webservertalk.com
>|||My Code block is given below: From front end, ASP.Net(C#) I passed the XML
document as text to the stored procedure.
CREATE PROCEDURE p_xmlinsert
@.RequestId As Int,
@.Data1 As Int,
@.doc As text
As
Begin
Declare @.idoc Int
Declare @.TranCount Int,
@.Msg Varchar(256)
Set @.Msg = 'OK'
Set @.TranCount = @.@.TRANCOUNT
Exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
Into #Tmp1
FROM OPENXML (@.idoc, '/Data/Invoice',2)
WITH
( InvoiceNumber VarChar(64) '@.Invoice_Number',
Data1 Int '@.Data1',
Adderess1 VarChar(32) '@.Adder1',
Adderess2 VarChar(32) '@.Adder2',
City VarChar(32) '@.City',
State VarChar(32) '@.State',
Zip VarChar(32) '@.Zip',
Date VarChar(32) '@.Date',
Total VarChar(32) '@.Total'
)
SELECT *
Into #Tmp2
FROM OPENXML (@.idoc, '/Data/Invoice/Event',2)
WITH
( InvoiceNumber VarChar(64) '../@.Invoice_Number',
OrderNumber VarChar(32) '@.Order_Number',
OrderDate DateTime '@.Order_Date',
ContactName VarChar(64) '@.Contact_Name',
ContactEmail VarChar(64) '@.Contact_Email',
ContactPhone VarChar(24) '@.Contact_Phone',
ContactAddr1 VarChar(128) '@.Contact_Addr1',
ContactAddr2 VarChar(128) '@.Contact_Addr2',
ContactCity VarChar(32) '@.Contact_City',
ContactState VarChar(2) '@.Contact_State',
ContactZip VarChar(5) '@.Contact_Zip',
)
SELECT *
Into #Tmp3
FROM OPENXML (@.idoc, '/Data/Invoice/Event/Order_Detail',2)
WITH
(InvoiceNumber VarChar(64) '../../@.Invoice_Number',
OrderNumber VarChar(32) '../@.Order_Number',
ItemName VarChar(64) '@.Item_Name',
ItemDesc VarChar(256) '@.Item_Desc',
Quantity Decimal(18,2) '@.Quantity',
UnitPrice Decimal(18,2) '@.Unit_Price',
OrderTax Decimal(18,2) '@.Order_Tax',
OrderTotal Decimal(18,2) '@.Order_Total'
)
EXECUTE sp_xml_removedocument @.idoc
--Now Add data
If @.TranCount = 0
Begin Tran
Insert Into table1
(
ReqId,
Data1,
InvoiceNumber,
UnitAddress1,
UnitAddress2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
InvoiceNetAmount,
InvoiceTaxAmount,
InvoiceTotalAmount,
CreatedDate
)
Select
@.RequestId,
Data1,
InvoiceNumber,
UnitAdder1,
UnitAdder2,
UnitCity,
UnitState,
UnitZip,
InvoiceDate,
0.0,
0.0,
Total,
GetDate()
From #Tmp1
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert1 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into table2
(
Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddress1,
ContactAddress2,
ContactCity,
ContactState,
ContactZip,
)
Select A.Table1Id,
OrderNumber,
OrderDate,
ContactName,
ContactEmail,
ContactPhone,
ContactAddr1,
ContactAddr2,
ContactCity,
ContactState,
ContactZip,
From #Tmp2 A,
Table1 B
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert2 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Insert Into Table3
(
Table2Id,
ItemName,
ItemDescription,
Quantity,
UnitPrice,
SalesTax,
TotalPrice
)
Select B.Table2Id,
ItemName,
ItemDesc,
Quantity,
UnitPrice,
OrderTax,
OrderTotal
From #Tmp3 A,
Table1 B,
Table2 C
Where A.InvoiceNumber = B.InvoiceNumber
And B.Data1 = @.Data1
And B.ReqId = @.RequestId
And A.OrderNumber = C.OrderNumber
And B.Table1Id = C.Table1Id
If @.@.error<>0
Begin
Set @.Msg = 'Error: Insert3 failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
Update TableQueue Set Status = 'Ready' Where ReqId = @.RequestId and Status
= 'Pending'
If @.@.error<>0
Begin
Set @.Msg = 'Error: Update failed!'
RAISERROR (@.Msg, 16, 1)
If @.TranCount = 0
Rollback Transaction
Select 0
Return
End
If @.TranCount = 0
Commit Transaction
Select 3
Return
End
GO
Thanks
-Asir
Message posted via http://www.webservertalk.com