We have a Stored procedure written few years back in SQL server 2000. It
accepts an xml document as a text parameter and then passes that xml to
sp_xml_PrepareDocument.
After that alot of temporary tables are created a cursor is opened on the
xml and with in cursor alot of insertions in different tables are done , a
few sps are also called with in the cursor where some conversions are done.

This stored procedure works fine without any error most of the time. The
problem is that some times it starts throwing the above error. And the most
wierd thing is that this error appears once on with a data and the next time
its gone.

The input xml is around 500KB to 1 MB. If the records are passed
individually the error disappears. but the whole xml gives error time to time.
All this situation is driving me nuts as i have been working on it for past
week and i have not even been able to pin point the statement which is
causing this issue.


Can anyone tell me, is this a bug with SQL server ? or some limitation? How
do i deal with this issue? I ll be Extremely thankful for any guidance. Below
is the SP , an di can provide the database script and sample input data as
well.


---------------------------------------------------------------------------------------------












ALTER PROCEDURE [dbo].[DWSP_Manually_Handle_POS_Data] @xmlPOSData
Text
AS
DECLARE @hDoc INTEGER

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlPOSData,'<ns0:Root
xmlns:ns0="http://Virgin.DW.POS.Schema.StoredProcedrue"/>'



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION

DECLARE #DW_POS_Header_Import_Cursor CURSOR FOR
SELECT
[Transaction_ID],[Store_ID],[Register_Number],[Transaction_Number],[Date_Transaction],[Time_Transaction],[Cashier_Number],[GSA_Type_Code],[Transaction_Type_Code],[Transaction_Tax_Dollars],[Transaction_Sales_Dollars],[Transaction_Return_Dollars],[Transaction_Giftcard_Dollars],[POS_Sales_Dollars],[Transaction_Gross_Dollars],[Transaction_Sales_Units],[Transaction_Return_Units],[Transaction_Giftcard_Units]
,[POS_Sales_Units],[Transaction_Gross_Units],[Transaction_Markdown_Dollars],[IsPV]FROM
OPENXML(@hDoc, 'ns0:Root/Record/DW_POS_Header_Import', 2)
WITH ([Transaction_ID] [int] ,
[Store_ID] [smallint],
[Register_Number] [smallint],
[Transaction_Number] [smallint] ,
[Date_Transaction] [varchar] (10) ,
[Time_Transaction] [varchar] (5),
[Cashier_Number] [smallint] ,
[GSA_Type_Code] [varchar] (3) ,
[Transaction_Type_Code] [varchar] (2) ,
[Transaction_Tax_Dollars] [decimal](10, 2) ,
[Transaction_Sales_Dollars] [decimal](10, 2) ,
[Transaction_Return_Dollars] [decimal](10, 2) ,
[Transaction_Giftcard_Dollars] [decimal](10, 2) ,
[POS_Sales_Dollars] [decimal](10, 2) ,
[Transaction_Gross_Dollars] [decimal](10, 2) ,
[Transaction_Sales_Units] [int] ,
[Transaction_Return_Units] [int] ,
[Transaction_Giftcard_Units] [int] ,
[POS_Sales_Units] [int] ,
[Transaction_Gross_Units] [int] ,
[Transaction_Markdown_Dollars] [decimal](10, 2),
[IsPV] [varchar] (5))


CREATE TABLE #DW_POS_Detail_Import_Tmp (
[Transaction_ID] [int] NULL ,
[GSA_Type_Code] [varchar] (3)NOT NULL ,
[Transaction_Line_Type_ID] [varchar] (3)NOT NULL ,
[Item_ID] [varchar] (22) NOT NULL ,
[Product_ID] [int] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Unit_Price_Current] [decimal](10, 2) NOT NULL ,
[Unit_Price_Override_Dollars] [decimal](10, 2) NOT NULL ,
[Unit_Dollar_Discount_Dollars] [decimal](10, 2) NOT NULL ,
[GSA_Dollar_Discount_Type] [varchar] (3)NULL ,
[Dollar_Discount_Type] [varchar] (3)NULL ,
[Unit_Percent_Discount_Dollars] [decimal](10, 2) NOT NULL ,
[GSA_Percent_Discount_Type] [varchar] (3)NULL ,
[Percent_Discount_Type] [varchar] (3) NULL ,
[Unit_Spread_TMD_Dollars] [decimal](10, 2) NOT NULL ,
[Unit_Lump_TMD_Dollars] [decimal](10, 2) NOT NULL ,
[GSA_TMD_Discount_Type] [varchar] (3) NULL ,
[TMD_Discount_Type] [varchar] (3) NULL ,
[Unit_Sell_Dollars_Actual] [decimal](10, 2) NOT NULL ,
[Extended_Sell_Dollars_Actual] [decimal](10, 2) NOT NULL ,
[Unit_Sell_Dollars_JDA] [decimal](10, 2) NOT NULL ,
[Extended_Sell_Dollars_JDA] [decimal](10, 2) NOT NULL ,
[Last_Item_Flag] [tinyint] NOT NULL ,
[Event_ID] [int] NULL ,
[PTSDI] [int] NOT NULL ,
[Sync_Flag] [bit] NULL ,
[Unit_Price_Regular] [decimal](10, 2) NOT NULL ,
[Unit_Cost_Current] [decimal](10, 2) NOT NULL ,
[Unit_Cost_Average] [decimal](10, 2) NOT NULL
)

CREATE TABLE #DW_POS_Tender_Import_Tmp (
[Transaction_ID] [int] NULL ,
[GSA_Type_Code] [varchar] (3) NOT NULL ,
[Tender_Type_ID] [varchar] (3) NOT NULL ,
[Tender_Net_Dollars] [decimal](10, 2) NOT NULL ,
[Tender_Tax_Dollars] [decimal](10, 2) NOT NULL ,
[Tender_Gross_Dollars] [decimal](10, 2) NOT NULL ,
[Tender_Customer_Number] [varchar] (24) NULL ,
[Tender_Customer_Name] [varchar] (55) NULL ,
[Tender_Customer_ZipCode] [varchar] (10) NULL ,
[Tender_Approval_Number] [varchar] (10) NULL,
[Tender_Customer_Expiration] [varchar] (4),
[Tender_Customer_Signature] [binary] (4000) NULL ,
)

CREATE TABLE #DW_POS_Orignal_Of_PV_Tmp (
[Transaction_ID] [int] NULL ,
[Register_Number_Org] [smallint],
[Transaction_Number_Org] [smallint]
)

DECLARE @Status int, @Message varchar(500), @recrdcount numeric
SET @Status = 0 --DEFAULT STAUS is PASS

--CREATE TEMP TABLE FOR STATUS OF TRANSACTIONS
CREATE TABLE #DW_POS_Header_Import_Status(
[Store_ID] [smallint],
[Register_Number] [smallint],
[Transaction_Number] [smallint] ,
[Date_Transaction] [varchar] (10) ,
[Time_Transaction] [varchar] (5),
[Cashier_Number] [smallint] ,
[Register_Number_PV] [smallint],
[Transaction_Number_PV] [smallint],
[Error_Message] varchar(500),
[Status] int
)

OPEN #DW_POS_Header_Import_Cursor


INSERT INTO
#DW_POS_Tender_Import_Tmp([Transaction_ID],[GSA_Type_Code],[Tender_Type_ID],[Tender_Net_Dollars],[Tender_Tax_Dollars],[Tender_Gross_Dollars],[Tender_Customer_Number],[Tender_Customer_Name],[Tender_Customer_ZipCode],[Tender_Customer_Signature],[Tender_Approval_Number],[Tender_Customer_Expiration])
Select
[Transaction_ID],[GSA_Type_Code],[Tender_Type_ID],[Tender_Net_Dollars],[Tender_Tax_Dollars],[Tender_Gross_Dollars],[Tender_Customer_Number],[Tender_Customer_Name],[Tender_Customer_ZipCode],(Select
case [Tender_Customer_Signature] when 'N' then NULL else Convert([binary]
(4000),[Tender_Customer_Signature])
end),[Tender_Approval_Number], [Tender_Customer_Expiration]
FROM OPENXML(@hDoc,
'ns0:Root/Record/DW_POS_Header_Import/DW_POS_Tender_Import', 2)
WITH( [Tender_Line_ID] [int],
[Transaction_ID] [int],
[GSA_Type_Code] [varchar] (3),
[Tender_Type_ID] [varchar] (3),
[Tender_Net_Dollars] [decimal](10, 2),
[Tender_Tax_Dollars] [decimal](10, 2),
[Tender_Gross_Dollars] [decimal](10, 2),
[Tender_Customer_Number] [varchar] (24),

Faiza Ashraf (Faiza Ashraf@discussions.microsoft.com) writes:
[quoted text, click to view]

My first reaction when looking at the procedure, is that I can see
nothing that justifies a cursor. It seems perfectly possible to write
this all with set-based statements. Do that, and your arithmetic overflow
may be gone too.

As for the error, the first thing to take notice of is on the exact line
the error occurs. This may not display if you are running the procedure
from a client that uses ADO. But if you run it from Query Analyzer, you
should be told. Without knowing exactly where the errors occurs you are
fumbling in the dark.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Erland thanks for such a quick reply ,but you see my problem is that Biztalk
is calling this stored procedure and passing it the XML , the XML size that
is paased to it is around certain MBs . Cross tier debugging between Biztalk
and SQL server is not possible , specially in clustered enviornment such as
our dev where SQL server is actually a linked server. and the other bottle
neck is that query analyser allows design time debugging , and i have no data
type which could read this amount of data from a table or a file at design
time and store it locally and then pass it on to sp_xml_preparedocument as
argument. text, ntext and image cant be defined locally. If you notice the
current SP, all the XML data is stored in memory using the handle returned by
so_xml_preparedoc.

Now as far as pin pointing the exact line is concerened, Do you know a way
using which i could debug my existing Stored procedure on my dev.I wrote a
console application which read the data from file and then passed that data
as argument to this SP, but then i faced the problem that while debugging the
control never hit the break point in the Stored procedure because it was on a
remote machine which had a link on our dev.

Can you exactly tell me, if it is supported in SQL server 2000 to do real
time multi tier debugging (b/w c# and SQL) where SQL server is on a remote
machine. Because unfortunatly i could not replicate this error on our local
dev where i had installed sql server 2005 and VS 2005 only for this cross
tier debugging.

Please guide me if it is a SQL server limitation, so that i can open a
ticket with MS.

Thanks alot again,
Faiza



[quoted text, click to view]