10-10-05 11:00 PM
Here is my SP,data file and Response file i hv recieved.
//// Stored procedure to be called
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].usp_interface_erp_import_customer
@DestinationCode VARCHAR(10) -- @DestinationCode would be 'GSAP', 'P38'
, @CompanyCode VARCHAR(10) -- @CompanyCode would be 'MARINE', 'LUBES'
, @CustomerData VARCHAR(800)
AS
declare @idoc int
declare @doc varchar(1000)
BEGIN TRANSACTION
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @CustomerData
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/CustomerData/Customer',2)
WITH (CustomerID varchar(10) '@CustomerID')
--Return 0;
--EXEC sp_xml_removedocument @idoc
done:
COMMIT TRANSACTION
EXEC SP_XML_RemoveDocument @idoc
RETURN 0
ErrHandler:
ROLLBACK TRANSACTION
EXEC SP_XML_RemoveDocument @idoc
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[vbcol=seagreen]
///Sample data file for Stored Procedure
DECLARE @RC int
DECLARE @DestinationCode varchar(10)
DECLARE @CompanyCode varchar(10)
DECLARE @CustomerData varchar(800)
SELECT @DestinationCode = 'Marine'
SELECT @CompanyCode = 'Test'
SELECT @CustomerData = '<CustomerData><Customer CustomerID="VINET"
ContactName="Paul Henriot"> <Order OrderID="10248" CustomerID="VINET"
EmployeeID="5" OrderDate="1996-07-04T00:00:00"><OrderDetail ProductID="11"
Quantity="12"/><OrderDetail ProductID="42"
Quantity="10"/></Order></Customer></CustomerData>'
EXEC @RC = [Test].[dbo].[usp_interface_erp_import_customer]
@DestinationCode, @CompanyCode, @CustomerData
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Test.dbo.usp_interface_erp_import_customer'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine[vbcol=seagreen]
/// Response from the stored procedure as result of calling stored procedur
e.
<?xml version="1.0" encoding="utf-16" ?>
<ResRoot xmlns="Test" />
"Jayendra" wrote:
[vbcol=seagreen]
> HI,
> iam having SP which has to be called from the orchestration and I am using
> two way port to send the request and receive the response back.
>
> As per my SP it should return me a flag set to 0/1 depends on the exec sta
tus.
>
> As the result of request to SP ,i hv receive response back but it contains
> no information regarding status of execution of SP.
>
> In fact i dont know wheather SP executeing /not from the orch.
> But in the HAT it is shown as completed.
>
>
>
> Thanks & Regards,
>
> Jai
>
>
>
>
[ Post a follow-up to this message ]
| 
