Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SP not returning the result set

Author  Topic 

ravinder1483
Starting Member

9 Posts
Posted - 2011-02-05 : 09:26:56
Hi,

When i run the following SP from front end, no result set is generated it says no result set found but data gets successfully inserted in the database. However, when i remove the INSERT INTO statements then front end shows the XML output.

I am sending the SP below:


USE [YOUNG]
GO
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spReadPO]')AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spReadPO]
GO
CREATE PROCEDURE spReadPO
@inputXMLStr XML
AS
BEGIN
BEGIN TRY
DECLARE @idoc INT
,@doc XML
,@HeaderId INT
,@errornum VARCHAR(100)
,@result VARCHAR(20)
,@errormsg VARCHAR(500)
,@FullString VARCHAR(MAX)

SELECT @doc = @inputXMLStr,@result='SUCCESSFUL',@errornum='',@errormsg=''

--Check for existence of temp tables and drop them if they exist
IF OBJECT_ID('tempdb..#XMLHeader') IS NOT NULL
DROP TABLE #XMLHeader
IF OBJECT_ID('tempdb..#XMLEDIHeader') IS NOT NULL
DROP TABLE #XMLEDIHeader
IF OBJECT_ID('tempdb..#XMLEDIData') IS NOT NULL
DROP TABLE #XMLEDIData
IF OBJECT_ID('tempdb..#XMLEDIFooter') IS NOT NULL
DROP TABLE #XMLEDIFooter

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--->Phase 1 ---> Read XML
--Read Header
SELECT * INTO #XMLHeader FROM OPENXML (@idoc, '/ROOT/HEADER',2)
WITH (
ACTION VARCHAR(50),
STORED_PROCEEDURE VARCHAR(50),
COMPANY_ID VARCHAR(50),
USER_ID VARCHAR(50),
DIVISION VARCHAR(50),
EDI_CLIENT VARCHAR(50),
ITEM_1 VARCHAR(50),
ITEM_2 VARCHAR(50)
)

--Read EDI Header
SELECT * INTO #XMLEDIHeader FROM OPENXML (@idoc, '/ROOT/EDI_HEADER/N1',2)
WITH (
AUTHORIZATION_INFO_QL VARCHAR(50) '../AUTHORIZATION_INFO_QL',
AUTHORIZATION_INFO VARCHAR(50) '../AUTHORIZATION_INFO',
SECURITY_INFO_QL VARCHAR(50) '../SECURITY_INFO_QL',
SECURITY_INFO VARCHAR(50) '../SECURITY_INFO',
SENDER_ID_QL VARCHAR(50) '../SENDER_ID_QL',
SENDER_ID VARCHAR(50) '../SENDER_ID',
RECEIVER_ID_QL VARCHAR(50) '../RECEIVER_ID_QL',
RECEIVER_ID VARCHAR(50) '../RECEIVER_ID',
SUBMIT_DATE VARCHAR(50) '../SUBMIT_DATE',
SUBMIT_TIME VARCHAR(50) '../SUBMIT_TIME',
INTERCHANGE_STANDARD_ID VARCHAR(50) '../INTERCHANGE_STANDARD_ID',
INTERCHANGE_VERSION_ID VARCHAR(50) '../INTERCHANGE_VERSION_ID',
INTERCHANGE_CONTROL_NUM VARCHAR(50) '../INTERCHANGE_CONTROL_NUM',
ACKNOWLEDGEMENT VARCHAR(50) '../ACKNOWLEDGEMENT',
ENVIRONMENT_INDICATOR VARCHAR(50) '../ENVIRONMENT_INDICATOR',
SUBELEMENT_SEPARATOR VARCHAR(50) '../SUBELEMENT_SEPARATOR',
FUNCTIONAL_ID VARCHAR(50) '../FUNCTIONAL_ID',
APPLICATION_SENDER_ID VARCHAR(50)'../APPLICATION_SENDER_ID',
APPLICATION_RECEIVER_ID VARCHAR(50)'../APPLICATION_RECEIVER_ID',
INTERCHANGE_DATE VARCHAR(50) '../INTERCHANGE_DATE',
INTERCHANGE_TIME VARCHAR(50) '../INTERCHANGE_TIME',
CONTROL_NUMBER VARCHAR(50) '../CONTROL_NUMBER',
AGENCY_CODE VARCHAR(50) '../AGENCY_CODE',
VERSION_ID VARCHAR(50) '../VERSION_ID',
TRANSACTIONSET_ID VARCHAR(50) '../TRANSACTIONSET_ID',
TRANSACTIONSET_CONTROL_NUM VARCHAR(50) '../TRANSACTIONSET_CONTROL_NUM',
TRANSACTIONSET_PURPOSE VARCHAR(50) '../TRANSACTIONSET_PURPOSE',
PO_TYPE VARCHAR(50) '../PO_TYPE',
PO_NUMBER VARCHAR(50) '../PO_NUMBER',
PO_DATE VARCHAR(50) '../PO_DATE',
REFERENCE_NUMBER_QL1 VARCHAR(50) '../REFERENCE_NUMBER_QL1',
REFERENCE_NUMBER_1 VARCHAR(50) '../REFERENCE_NUMBER_1',
REFERENCE_NUMBER_QL2 VARCHAR(50) '../REFERENCE_NUMBER_QL2',
REFERENCE_NUMBER_2 VARCHAR(50) '../REFERENCE_NUMBER_2',
REFERENCE_NUMBER_QL3 VARCHAR(50) '../REFERENCE_NUMBER_QL3',
REFERENCE_NUMBER_3 VARCHAR(50) '../REFERENCE_NUMBER_3',
REFERENCE_NUMBER_QL4 VARCHAR(50) '../REFERENCE_NUMBER_QL4',
REFERENCE_NUMBER_4 VARCHAR(50) '../REFERENCE_NUMBER_4',
TERMS_TYPE_CODE VARCHAR(50) '../TERMS_TYPE_CODE',
TERMS_BASIS_DATE VARCHAR(50) '../TERMS_BASIS_DATE',
TERMS_DISCOUNT_PERCENT VARCHAR(50) '../TERMS_DISCOUNT_PERCENT',
TERMS_DISCOUNT_DAYS_DUE VARCHAR(50) '../TERMS_DISCOUNT_DAYS_DUE',
TERMS_NET_DAYS VARCHAR(50) '../TERMS_NET_DAYS',
DATE_TIME_QL1 VARCHAR(50) '../DATE_TIME_QL1',
DATE_1 VARCHAR(50) '../DATE_1',
DATE_TIME_QL2 VARCHAR(50) '../DATE_TIME_QL2',
DATE_2 VARCHAR(50) '../DATE_2',
ROUTING VARCHAR(50) '../ROUTING',
N9_REFERENCE_NUMBER_QL VARCHAR(50) '../N9_REFERENCE_NUMBER_QL',
N9_REFERENCE_NUMBER VARCHAR(50) '../N9_REFERENCE_NUMBER',
TEXT_MESSAGE VARCHAR(MAX) '../TEXT_MESSAGE',
PRINTER_CARRIAGE VARCHAR(50) '../PRINTER_CARRIAGE',
ORGANIZATION_IDENTIFIER VARCHAR(50) ,
NAME VARCHAR(100) ,
IDENTITY_CODE_QL VARCHAR(50) ,
IDENTITY_CODE VARCHAR(50) ,
FULL_STRING VARCHAR(MAX) '/ROOT/FULL_STRING'
)

--Read EDI Data
SELECT * INTO #XMLEDIData FROM OPENXML (@idoc, '/ROOT/EDI_DATA/ORDER_DETAILS/STORE_INFO',2)
WITH (
PO_LINE_NUMBER VARCHAR(50) '../PO_LINE_NUMBER',
QUANTITY_ORDERED VARCHAR(50)'../QUANTITY_ORDERED' ,
UNIT_CODE VARCHAR(50) '../UNIT_CODE',
UNIT_PRICE VARCHAR(50) '../UNIT_PRICE',
UNIT_PRICE_BASIS VARCHAR(50)'../UNIT_PRICE_BASIS',
SERVICE_ID_QL1 VARCHAR(50) '../SERVICE_ID_QL1',
SERVICE_ID_1 VARCHAR(50) '../SERVICE_ID_1',
SERVICE_ID_QL2 VARCHAR(50) '../SERVICE_ID_QL2',
SERVICE_ID_2 VARCHAR(50) '../SERVICE_ID_2',
SERVICE_ID_QL3 VARCHAR(50) '../SERVICE_ID_QL3',
SERVICE_ID_3 VARCHAR(50) '../SERVICE_ID_3',
SERVICE_ID_QL4 VARCHAR(50) '../SERVICE_ID_QL4',
SERVICE_ID_4 VARCHAR(50) '../SERVICE_ID_4',
SERVICE_ID_QL5 VARCHAR(50) '../SERVICE_ID_QL5',
SERVICE_ID_5 VARCHAR(50) '../SERVICE_ID_5',
SERVICE_ID_QL6 VARCHAR(50) '../SERVICE_ID_QL6',
SERVICE_ID_6 VARCHAR(50) '../SERVICE_ID_6',
SERVICE_ID_QL7 VARCHAR(50)'../PO_LINE_NUMBER',
SERVICE_ID_7 VARCHAR(50)'../SERVICE_ID_7',
SERVICE_ID_QL8 VARCHAR(50) '../SERVICE_ID_QL8',
SERVICE_ID_8 VARCHAR(50) '../SERVICE_ID_8',
ITEM_DESCRIPTION_TYPE VARCHAR(50) '../ITEM_DESCRIPTION_TYPE',
PRODUCT_CODE VARCHAR(50) '../PRODUCT_CODE',
ITEM_DESCRIPTION VARCHAR(50)'../ITEM_DESCRIPTION',
UNIT_MEASURE_CODE VARCHAR(50) ,
LOCATION_QL VARCHAR(50) ,
LOCATION_ID VARCHAR(50) ,
QUANTITY VARCHAR(50)
)

--Read EDI Footer
SELECT * INTO #XMLEDIFooter FROM OPENXML (@idoc, '/ROOT/FOOTER',2)
WITH (
TOTAL_LINE_ITEMS VARCHAR(50) ,
TOTAL_INCLUDED_SEGMENTS VARCHAR(50),
TRANSACTIONSET_CONTROL_NUMBER VARCHAR(50) ,
TOTAL_INCLUDED_TRANSACTIONSET VARCHAR(50),
DATA_CONTROL_NUMBER VARCHAR(50) ,
TOTAL_INCLUDED_GROUPS VARCHAR(50) ,
INTERCHANGE_CONTROL_NUM VARCHAR(50)
)

--Remove the XML document to save memory
EXEC sp_xml_removedocument @idoc
--Ends


--->Phase 2 ---> Insert data into respective tables
--Start Transaction so that if error is encountered while inserting data, then all data can be rolled back
BEGIN TRAN
--Insert Header data
INSERT INTO tblEDIHeader
SELECT NULL --EdiId
,CASE WHEN ORGANIZATION_IDENTIFIER='VN' THEN NAME END --VendorName
,CASE WHEN ORGANIZATION_IDENTIFIER='MA' THEN NAME END --CustomerName
,CASE WHEN ORGANIZATION_IDENTIFIER='AG' THEN NAME END --AgentName
,CASE WHEN ORGANIZATION_IDENTIFIER='SU' THEN NAME END --FactoryName
,INTERCHANGE_CONTROL_NUM --ISA_Ctrl_Id
,CONTROL_NUMBER --GS_Ctrl_Id
,TRANSACTIONSET_CONTROL_NUM --ST_Ctrl_Id
,RECEIVER_ID --OurQAID
,SENDER_ID --TheirQAID
,TRANSACTIONSET_PURPOSE --TransactionSetPurpose=
,INTERCHANGE_DATE --EDITransmissionDate
,INTERCHANGE_TIME --EDITransmissionTime
,SUBMIT_DATE --EdiCreatedDt
,SUBMIT_TIME --EdiCreatedTm
,TRANSACTIONSET_ID --EdiTypeCode
,PO_TYPE --EdiType
,NULL --EdiDocNumber
,NULL --EdiDocDetailCode
,NULL --DUNS
,ENVIRONMENT_INDICATOR --Env
,VERSION_ID --Version
,PO_NUMBER --PONumber
,PO_DATE --PODate
,CASE --InternalVendorId
WHEN REFERENCE_NUMBER_QL1='IA' THEN REFERENCE_NUMBER_1
WHEN REFERENCE_NUMBER_QL2='IA' THEN REFERENCE_NUMBER_2
WHEN REFERENCE_NUMBER_QL3='IA' THEN REFERENCE_NUMBER_3
WHEN REFERENCE_NUMBER_QL4='IA' THEN REFERENCE_NUMBER_4
END
,CASE --Dept
WHEN REFERENCE_NUMBER_QL1='DP' THEN REFERENCE_NUMBER_1
WHEN REFERENCE_NUMBER_QL2='DP' THEN REFERENCE_NUMBER_2
WHEN REFERENCE_NUMBER_QL3='DP' THEN REFERENCE_NUMBER_3
WHEN REFERENCE_NUMBER_QL4='DP' THEN REFERENCE_NUMBER_4
END
,NULL --ShipFrom
,CASE --ShipTo
WHEN ORGANIZATION_IDENTIFIER='ST' THEN IDENTITY_CODE
WHEN ORGANIZATION_IDENTIFIER='VN' THEN IDENTITY_CODE
WHEN ORGANIZATION_IDENTIFIER='SU' THEN IDENTITY_CODE
END
,NULL --BillTo
,CASE --Div
WHEN REFERENCE_NUMBER_QL1='19' THEN REFERENCE_NUMBER_1
WHEN REFERENCE_NUMBER_QL2='19' THEN REFERENCE_NUMBER_2
WHEN REFERENCE_NUMBER_QL3='19' THEN REFERENCE_NUMBER_3
WHEN REFERENCE_NUMBER_QL4='19' THEN REFERENCE_NUMBER_4
END
,CASE --StartDate
WHEN DATE_TIME_QL1='037' THEN DATE_1
WHEN DATE_TIME_QL2='037' THEN DATE_2
END
,CASE --EndDate
WHEN DATE_TIME_QL1='038' THEN DATE_1
WHEN DATE_TIME_QL2='038' THEN DATE_2
END
,TERMS_TYPE_CODE --TermCode
,TERMS_BASIS_DATE --TermBaseDtCode
,TERMS_NET_DAYS --TermNetDays
,TERMS_DISCOUNT_PERCENT --TermDiscountPercent
,TERMS_DISCOUNT_DAYS_DUE --TermDiscountDays
,TERMS_NET_DAYS --TermDaysOfMonth
,NULL --ShippedDate
,NULL --InvoiceDate
,NULL --DueDate
,NULL --BOL
,NULL --ProNumber
,NULL --RoutingNumber
,NULL --SCAC
,NULL --CarrierTypeCode
,NULL --CarrierName
,NULL --ShippedQty
,NULL --ShippedQtyUnit
,NULL --TotalShippedWt
,NULL --TotalShippedWtUnit
,TEXT_MESSAGE --Msg
,FULL_STRING --FullEDIFile
FROM #XMLEDIHeader

--Store the Identity value generated by the above insert
SELECT @HeaderId=SCOPE_IDENTITY()

--Insert EDI data
INSERT INTO tblEDIBody
SELECT @HeaderId --HeaderId
,PO_LINE_NUMBER --AssignID
,QUANTITY_ORDERED --Qty
,UNIT_CODE --QtyUnit
,UNIT_PRICE --SellingPrice
,UNIT_PRICE_BASIS --SellingPriceCode
,CASE WHEN SERVICE_ID_QL1='UP' THEN SERVICE_ID_1 END --UPC
,CASE WHEN SERVICE_ID_QL1='EN' THEN SERVICE_ID_1 END --EAN
,NULL --SKU
,NULL --CustomerItemID
,NULL --VendorItemID
,NULL --ItemDetailCode1
,NULL --ItemDetail1
,NULL --ItemDetailCode2
,NULL --ItemDetail2
,NULL --ItemDetailCode3
,NULL --ItemDetail3
,NULL --ItemDetailCode4
,NULL --ItemDetail4
,NULL --ItemDetailCode5
,NULL --ItemDetail5
,NULL --ItemQtyCodeQualifier
,LOCATION_ID --MarkForStore
,QUANTITY --StoreQty
,UNIT_MEASURE_CODE --StoreQtyUnit
,ITEM_DESCRIPTION --ProductDesc
,PRODUCT_CODE --ProductCode
,NULL --LaborCode
,NULL --PackQty
,NULL --InnerPackQty
,NULL --TotalLineItemCTT
FROM #XMLEDIData

--Insert Footer data
INSERT INTO tblEDIFooter
SELECT @HeaderId --HeaderId
,TOTAL_LINE_ITEMS --CTT01
,TOTAL_INCLUDED_SEGMENTS --SE01
,TRANSACTIONSET_CONTROL_NUMBER --SE02
,TOTAL_INCLUDED_TRANSACTIONSET --GE01
,DATA_CONTROL_NUMBER --GE02
,TOTAL_INCLUDED_GROUPS --IEA01
,INTERCHANGE_CONTROL_NUM --IEA02
FROM #XMLEDIFooter


--Now @FullString with Full String from tblEDIHeader

SELECT @FullString = FULL_STRING FROM #XMLEDIHeader

COMMIT TRAN

END TRY

BEGIN CATCH
SELECT @errornum=ERROR_NUMBER()
,@result='FAILED'
,@errormsg=ERROR_MESSAGE()
,@FullString='' --We don't return anything in Full String if error is encountered.
ROLLBACK TRAN
END CATCH

--->Phase 3 ---> Generate the XML ouput in the desired format

SELECT 1 AS Tag
,NULL AS Parent
,NULL AS 'ROOT!1'
,NULL AS 'HEADER!2'
,NULL AS 'HEADER!2!ACTION!CDATA'
,NULL AS 'HEADER!2!STORED_PROCEEDURE!CDATA'
,NULL AS 'HEADER!2!COMPANY_ID!CDATA'
,NULL AS 'HEADER!2!USER_ID!CDATA'
,NULL AS 'HEADER!2!DIVISION!CDATA'
,NULL AS 'HEADER!2!EDI_CLIENT!CDATA'
,NULL AS 'HEADER!2!ITEM_1!CDATA'
,NULL AS 'HEADER!2!ITEM_2!CDATA'
,NULL AS 'HEADER!2!RESULT!CDATA'
,NULL AS 'HEADER!2!ERROR_MESSAGE!CDATA'
,NULL AS 'HEADER!2!ERROR_NUMBER!CDATA'
,NULL AS 'HEADER!2!MESSAGE!CDATA'
UNION ALL
SELECT 2 AS Tag
,1 AS Parent
,NULL AS 'ROOT!1'
,NULL AS 'HEADER!2'
,ACTION AS 'HEADER!2!ACTION'
,STORED_PROCEEDURE AS 'HEADER!2!STORED_PROCEEDURE'
,COMPANY_ID AS 'HEADER!2!COMPANY_ID'
,USER_ID AS 'HEADER!2!USER_ID'
,DIVISION AS 'HEADER!2!DIVISION'
,EDI_CLIENT AS 'HEADER!2!EDI_CLIENT'
,ITEM_1 AS 'HEADER!2!ITEM_1'
,ITEM_2 AS 'HEADER!2!ITEM_2'
,@result AS 'HEADER!2!RESULT'
,@errormsg AS 'HEADER!2!ERROR_MESSAGE'
,@errornum AS 'HEADER!2!ERROR_NUMBER'
,@FullString AS 'HEADER!2!MESSAGE'
FROM #XMLHeader
FOR XML EXPLICIT
END





I use the following query to execute the above SP

exec spReadPO '<ROOT>
<HEADER>
<ACTION><![CDATA[INSERT]]></ACTION>
<STORED_PROCEEDURE><![CDATA[spSavePOString]]></STORED_PROCEEDURE>
<COMPANY_ID><![CDATA[8732]]></COMPANY_ID>
<USER_ID><![CDATA[SAURABH]]></USER_ID>
<DIVISION><![CDATA[H1]]></DIVISION>
<EDI_CLIENT><![CDATA[Hibbet]]></EDI_CLIENT>
<ITEM_1><![CDATA[demo]]></ITEM_1>
<ITEM_2><![CDATA[demo]]></ITEM_2>
<RESULT><![CDATA[SUCCESSFUL]]></RESULT>
<ERROR_MESSAGE><![CDATA[]]></ERROR_MESSAGE>
<ERROR_NUMBER><![CDATA[]]></ERROR_NUMBER>
<MESSAGE><![CDATA[]]></MESSAGE>
</HEADER>
<EDI_HEADER>
<AUTHORIZATION_INFO_QL><![CDATA[00]]></AUTHORIZATION_INFO_QL>
<AUTHORIZATION_INFO><![CDATA[ ]]></AUTHORIZATION_INFO>
<SECURITY_INFO_QL><![CDATA[00]]></SECURITY_INFO_QL>
<SECURITY_INFO><![CDATA[ ]]></SECURITY_INFO>
<SENDER_ID_QL><![CDATA[08]]></SENDER_ID_QL>
<SENDER_ID><![CDATA[6112391050 ]]></SENDER_ID>
<RECEIVER_ID_QL><![CDATA[12]]></RECEIVER_ID_QL>
<RECEIVER_ID><![CDATA[7188863163 ]]></RECEIVER_ID>
<SUBMIT_DATE><![CDATA[100909]]></SUBMIT_DATE>
<SUBMIT_TIME><![CDATA[0029]]></SUBMIT_TIME>
<INTERCHANGE_STANDERD_ID><![CDATA[U]]></INTERCHANGE_STANDERD_ID>
<INTERCHANGE_VERSION_ID><![CDATA[00401]]></INTERCHANGE_VERSION_ID>
<INTERCHANGE_CONTROL_NUM><![CDATA[000000003]]></INTERCHANGE_CONTROL_NUM>
<ACKNOWLEDGEMENT><![CDATA[0]]></ACKNOWLEDGEMENT>
<ENVIRONMENT_INDICATOR><![CDATA[P]]></ENVIRONMENT_INDICATOR>
<SUBELEMENT_SEPARATOR><![CDATA[@]]></SUBELEMENT_SEPARATOR>
<FUNCTIONAL_ID><![CDATA[PO]]></FUNCTIONAL_ID>
<APPLICATION_SENDER_ID><![CDATA[6112391050]]></APPLICATION_SENDER_ID>
<APPLICATION_RECEIVER_ID><![CDATA[7188863163]]></APPLICATION_RECEIVER_ID>
<INTERCHANGE_DATE><![CDATA[20100909]]></INTERCHANGE_DATE>
<INTERCHANGE_TIME><![CDATA[0029]]></INTERCHANGE_TIME>
<CONTROL_NUMBER><![CDATA[3]]></CONTROL_NUMBER>
<AGENCY_CODE><![CDATA[X]]></AGENCY_CODE>
<VERSION_ID><![CDATA[004010VICS]]></VERSION_ID>
<TRANSACTIONSET_ID><![CDATA[850]]></TRANSACTIONSET_ID>
<TRANSACTIONSET_CONTROL_NUM><![CDATA[0001]]></TRANSACTIONSET_CONTROL_NUM>
<TRANSACTIONSET_PURPOSE><![CDATA[00]]></TRANSACTIONSET_PURPOSE>
<PO_TYPE><![CDATA[SA]]></PO_TYPE>
<PO_NUMBER><![CDATA[0082130601]]></PO_NUMBER>
<PO_DATE><![CDATA[20100908]]></PO_DATE>
<REFERENCE_NUMBER_QL1><![CDATA[DP]]></REFERENCE_NUMBER_QL1>
<REFERENCE_NUMBER_1><![CDATA[0583]]></REFERENCE_NUMBER_1>
<REFERENCE_NUMBER_QL2><![CDATA[IA]]></REFERENCE_NUMBER_QL2>
<REFERENCE_NUMBER_2><![CDATA[0124711]]></REFERENCE_NUMBER_2>
<REFERENCE_NUMBER_QL3><![CDATA[19]]></REFERENCE_NUMBER_QL3>
<REFERENCE_NUMBER_3><![CDATA[04]]></REFERENCE_NUMBER_3>
<TERMS_TYPE_CODE><![CDATA[05]]></TERMS_TYPE_CODE>
<TERMS_BASIS_DATE><![CDATA[2]]></TERMS_BASIS_DATE>
<TERMS_DISCOUNT_PERCENT><![CDATA[]]></TERMS_DISCOUNT_PERCENT>
<TERMS_DISCOUNT_DAYS_DUE><![CDATA[]]></TERMS_DISCOUNT_DAYS_DUE>
<TERMS_NET_DAYS><![CDATA[30]]></TERMS_NET_DAYS>
<DATE_TIME_QL1><![CDATA[037]]></DATE_TIME_QL1>
<DATE_1><![CDATA[20100929]]></DATE_1>
<DATE_TIME_QL2><![CDATA[038]]></DATE_TIME_QL2>
<DATE_2><![CDATA[20101015]]></DATE_2>
<ROUTING><![CDATA[SEE ROUTING GUIDE]]></ROUTING>
<N9_REFERENCE_NUMBER_QL><![CDATA[AH]]></N9_REFERENCE_NUMBER_QL>
<N9_REFERENCE_NUMBER><![CDATA[SEE MSG FOR LEGAL NOTICE]]></N9_REFERENCE_NUMBER>
<TEXT_MESSAGE><![CDATA[THIS PURCHASE ORDER IS SUBJECT TO:]]></TEXT_MESSAGE>
<PRINTER_CARRIAGE><![CDATA[]]></PRINTER_CARRIAGE>
<N1>
<ORGANIZATION_IDENTIFIER><![CDATA[ST]]></ORGANIZATION_IDENTIFIER>
<NAME><![CDATA[]]></NAME>
<IDENTITY_CODE_QL><![CDATA[92]]></IDENTITY_CODE_QL>
<IDENTITY_CODE><![CDATA[0021]]></IDENTITY_CODE>
</N1>
</EDI_HEADER>
<EDI_DATA>
<ORDER_DETAILS>
<PO_LINE_NUMBER><![CDATA[001001]]></PO_LINE_NUMBER>
<QUANTITY_ORDERED><![CDATA[4]]></QUANTITY_ORDERED>
<UNIT_CODE><![CDATA[EA]]></UNIT_CODE>
<UNIT_PRICE><![CDATA[50.5]]></UNIT_PRICE>
<UNIT_PRICE_BASIS><![CDATA[LE]]></UNIT_PRICE_BASIS>
<SERVICE_ID_QL1><![CDATA[UP]]></SERVICE_ID_QL1>
<SERVICE_ID_1><![CDATA[845061035543]]></SERVICE_ID_1>
<SERVICE_ID_QL2><![CDATA[IZ]]></SERVICE_ID_QL2>
<SERVICE_ID_2><![CDATA[3X]]></SERVICE_ID_2>
<SERVICE_ID_QL3><![CDATA[CB]]></SERVICE_ID_QL3>
<SERVICE_ID_3><![CDATA[]]></SERVICE_ID_3>
<SERVICE_ID_QL4><![CDATA[BO]]></SERVICE_ID_QL4>
<SERVICE_ID_4><![CDATA[DK LEAF]]></SERVICE_ID_4>
<SERVICE_ID_QL5><![CDATA[VC]]></SERVICE_ID_QL5>
<SERVICE_ID_5><![CDATA[E1010K01]]></SERVICE_ID_5>
<SERVICE_ID_QL6><![CDATA[]]></SERVICE_ID_QL6>
<SERVICE_ID_6><![CDATA[]]></SERVICE_ID_6>
<SERVICE_ID_QL7><![CDATA[]]></SERVICE_ID_QL7>
<SERVICE_ID_7><![CDATA[]]></SERVICE_ID_7>
<SERVICE_ID_QL8><![CDATA[]]></SERVICE_ID_QL8>
<SERVICE_ID_8><![CDATA[]]></SERVICE_ID_8>
<ITEM_DESCRIPTION_TYPE><![CDATA[F]]></ITEM_DESCRIPTION_TYPE>
<PRODUCT_CODE><![CDATA[08]]></PRODUCT_CODE>
<ITEM_DESCRIPTION><![CDATA[ARTILLERY]]></ITEM_DESCRIPTION>
<STORE_INFO>
<UNIT_MEASURE_CODE><![CDATA[EA]]></UNIT_MEASURE_CODE>
<LOCATION_QL><![CDATA[92]]></LOCATION_QL>
<LOCATION_ID><![CDATA[0021]]></LOCATION_ID>
<QUANTITY><![CDATA[4]]></QUANTITY>
</STORE_INFO>
</ORDER_DETAILS>
</EDI_DATA>
<FOOTER>
<TOTAL_LINE_ITEMS><![CDATA[1]]></TOTAL_LINE_ITEMS>
<TOTAL_INCLUDED_SEGMENTS><![CDATA[182]]></TOTAL_INCLUDED_SEGMENTS>
<TRANSACTIONSET_CONTROL_NUMBER><![CDATA[0001]]></TRANSACTIONSET_CONTROL_NUMBER>
<TOTAL_INCLUDED_TRANSACTIONSET><![CDATA[6]]></TOTAL_INCLUDED_TRANSACTIONSET>
<DATA_CONTROL_NUMBER><![CDATA[3]]></DATA_CONTROL_NUMBER>
<TOTAL_INCLUDED_GROUPS><![CDATA[1]]></TOTAL_INCLUDED_GROUPS>
<INTERCHANGE_CONTROL_NUM><![CDATA[000000003]]></INTERCHANGE_CONTROL_NUM>
</FOOTER>
<FULL_STRING><![CDATA[
ISA~00~ ~00~ ~08~6112391050 ~12~7188863163 ~100909~002
9~U~00401~000000003~0~P~@*
GS~PO~6112391050~7188863163~20100909~0029~3~X~004010VICS*
ST~850~0001*
BEG~00~SA~0082130601~~20100908*
REF~DP~0583*
REF~IA~0124711*
REF~19~04*
ITD~05~2~~~~~30*
DTM~037~20100929*
DTM~038~20101015*
TD5~~~~~SEE ROUTING GUIDE*
N9~AH~SEE MSG FOR LEGAL NOTICE*
MSG~THIS PURCHASE ORDER IS SUBJECT TO:*
N1~ST~~92~0021*
PO1~001001~4~EA~50.5~LE~UP~845061035543~IZ~3X~CB~7651980~BO~DK LEAF~VC~E1010K01*

PID~F~08~~~ARTILLERY*
SDQ~EA~92~0021~4*
CTT~1*
SE~182~0001*
GE~6~3*
IEA~1~000000003*
]]></FULL_STRING>
</ROOT>'

Ravinder Pal Singh
Email: ravinder1483@gmail.com

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-02-07 : 07:32:57
Try putting SET NOCOUNT ON right at the begging of the procedure.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nirene
Yak Posting Veteran

98 Posts
Posted - 2011-02-09 : 00:13:09
Hai,

Try inserting a Output Parameter in CREATE PROCEDURE spReadPO
@inputXMLStr XML,@OutputParam Varchar(50) Output and pass value with to
@OutputParam

Hope this is what you expected.

Nirene
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site