From Red Leech, 1 Week ago, written in Text.
This paste is a reply to Untitled from Sweet Pig - view diff
Embed
  1. USE [BizTalkUMAXDb]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[umax_UcentraStatusTimeOut]    Script Date: 1/9/2018 11:24:02 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9.  
  10. -- ==============================================================
  11. -- Author:              Ram Mallampati
  12. -- Create date: 9/11/2017 10:30:26 PM
  13. -- Description: Stored Procedure for Ucentra UMAX update.
  14. -- ===============================================================
  15.  
  16. ALTER PROCEDURE [dbo].[umax_UcentraStatusTimeOut]
  17. AS
  18. BEGIN
  19.         DECLARE @status nvarchar(50), @errorInfo nvarchar(150), @createdDateTime nvarchar(20), @ucentraDateTime datetime,  @umaxUpdateTime datetime, @timeStamp nvarchar(20)
  20.  
  21.         SET @umaxUpdateTime = GETDATE();
  22.  
  23.         set @ErrorInfo = 'Response exceeded the 30 minute threshold';
  24.  
  25.         DECLARE @transTable TABLE(TransID varchar(50) NOT NULL)
  26.  
  27.         INSERT INTO @transTable
  28.         SELECT TransactionID FROM dbo.Ucentra_Status_Log WHERE UCentraResponseDateTime IS NULL AND DATEDIFF(minute, [RequestDateTime], GETDATE())>=30
  29.  
  30.         SET @timeStamp= CONVERT(VARCHAR(19),@umaxUpdateTime,126);
  31.  
  32.         BEGIN TRANSACTION;
  33.  
  34.     IF(LEN(@ErrorInfo) = 0)
  35.         BEGIN
  36.            SET @status = 'Success'
  37.         END
  38.         ELSE
  39.         BEGIN
  40.            SET @status = 'Failed'
  41.         END
  42.        
  43.         UPDATE [dbo].[Ucentra_Status_Log]
  44.         SET  [UCentraResponseDateTime]= GETDATE(),
  45.              [UmaxUpdateDateTime]= @umaxUpdateTime,
  46.          [UmaxUpdateStatus] = @ErrorInfo
  47.         WHERE [TransactionID] IN (SELECT TransactionID FROM @transTable)
  48.  
  49.         COMMIT TRANSACTION;
  50.  
  51.                 SELECT CAST(N'<ns0:GUS_SVM_UcentraRemoteServiceChangeMeterStatusRequest xmlns:ns0="http://schemas.itineris.net/dynamics/UMAX/2017/GUS/Ucentraservices">
  52.   <ns0:_serviceOrderId>'+transID+'</ns0:_serviceOrderId>
  53.   <ns0:_stateChange>'+@status+'</ns0:_stateChange>
  54.   <ns0:_errorString>'+@ErrorInfo+'</ns0:_errorString>
  55.   <ns0:_timeStamp>'+@timeStamp+'</ns0:_timeStamp>
  56.   </ns0:GUS_SVM_UcentraRemoteServiceChangeMeterStatusRequest>'AS XML)
  57.                                  FROM  @transTable
  58.                                  FOR XML RAW('Envelope')
  59. END
  60.  
  61.  
  62.