|
koolkeith19
Starting Member
14 Posts |
 Posted - 2008-11-12 : 09:53:52
|
| Hi Everyone,I would like to see the XML that is produced by this stored Proc but, when I run it it just give the ' Completed Suuccessfully' message and does not return anything else?ALTER PROCEDURE [dbo].[ET_Get_RouteToSync_SEL_kw] ---- This SP returns one route to process (pulling stored proc)-- AS set transaction isolation level read committed Declare @RouteId varchar (36) , @Status varchar (36), @CurrentSalesDate datetime, @Request_type varchar (36), @HistorySalesDate datetime, @RequiredMinSalesDate datetime, @IsUpload int, @UserId varchar(36), @timeoutMinute int set nocount on Set @timeoutMinute = -30 set @RouteId = null --set @isUpload = 1 --Select a record that is not processing by BT--select * from [cb_consol_p2]..[BT_Start_BTUpload] SELECT TOP 1 @RouteId = RouteId, @CurrentSalesDate = convert(datetime, convert(varchar, SalesDate,112), 112), @Request_type = Request_type FROM [cb_consol_p2].dbo.[BT_Start_BTUpload] with (rowlock, readpast) WHERE (ADB_SELECT_DATE IS NULL OR ADB_SELECT_DATE < DATEADD(minute, @timeoutMinute, getDate())) ORDER BY ADB_SEQUENCE --RouteId, SalesDate print ' route id is : ' + isnull(@Routeid, '')-- BEGIN TRAN -- PATCH Jumped days. Force process if @RouteId is not null begin--print ' got here 1' update [cb_consol_p2]..[BT_Start_BTUpload] set SalesDate = @CurrentSalesDate, ADB_SELECT_DATE = getdate() where RouteId = @RouteId and convert(datetime, convert(varchar, SalesDate,112), 112) = @CurrentSalesDate and (ADB_SELECT_DATE IS NULL OR ADB_SELECT_DATE < DATEADD(minute, @timeoutMinute, getDate())) --print 'updated record' end--print 'updated record 2' --print 'rouwcount is' + convert(varchar(36),@@rowcount) --if @@rowcount > 0 begin if @Request_Type != 'Missed Day' begin set @HistorySalesDate = null select @HistorySalesDate = max(CurrentSalesDate) from [cb_staging_p2]..[BT_HHSync_History] with (nolock) where RouteId = @RouteId and (Request_type = 'Missed Day' OR Request_type = 'Processing' OR Status = 'Upload') if @HistorySalesDate is not null and @HistorySalesDate < @CurrentSalesDate begin set @RequiredMinSalesDate = dateadd(day, 1, @HistorySalesDate) while @RequiredMinSalesDate < @CurrentSalesDate begin exec ET_BT_Start_BTUpload_INS @RouteId, @RequiredMinSalesDate, 'Missed Day' set @RequiredMinSalesDate = dateadd(day, 1, @RequiredMinSalesDate) end -- Modi 23 nov -- if datediff(day, @HistorySalesDate, @CurrentSalesDate) > 1 -- Set @Request_type = 'Missed Day' -- Modi 23 nov -- set @CurrentSalesDate = dateadd(day, 1, @HistorySalesDate) end end--print 'before execution' -- Upload/donwload Verification exec ET_Verification_UploadOrDownload @RouteId, @CurrentSalesDate, @IsUpload OUTPUT--print 'executed the second one upload or download' --Get userID ('1310' vs 'qc1030') set @UserId = null Select top 1 @UserId = UserId from [cb_consol_p2]..[v_InbtblUserRoute] with (nolock) where RouteId = @RouteId and ActiveFlg = 1 if @UserId is null set @UserId = @RouteId --SET statut if @IsUpload = 0 set @Status = 'Download' else begin set @Status = 'Upload' set @Request_type = 'Processing' end -- insert download or route incorrect into history insert into [cb_staging_p2]..[BT_HHSync_History] ( SystemDate, RouteId, UserId, HardwareId, PreviousLastSalesDate, LastSalesDate, CurrentSalesDate, Status, Request_type ) values ( getDate(), @RouteId, @UserId, null, getDate(), getDate(), @CurrentSalesDate, @Status, @Request_type ) --if download delete from initial table if @IsUpload = 0 delete from [cb_consol_p2]..[BT_Start_BTUpload] where RouteId = @RouteId and SalesDate = @CurrentSalesDate else --Stamp date begin --return one record SELECT IS_BT_SyncDone_SEL.RouteId, SalesDate, (select RegionId from [cb_consol_p2]..[v_InbtblRoute] with (nolock) where RouteId = @RouteId and ActiveFlg = 1)as RegionId, -- Get Region Request_type, ADB_SEQUENCE, ADB_TIMESTAMP, ADB_OPCODE FROM [cb_consol_p2]..[BT_Start_BTUpload] IS_BT_SyncDone_SEL where IS_BT_SyncDone_SEL.RouteId = @RouteId and IS_BT_SyncDone_SEL.SalesDate = @CurrentSalesDate and ADB_SEQUENCE = ( select max (ADB_SEQUENCE) from [cb_consol_p2]..[BT_Start_BTUpload] with (nolock) where RouteId = @RouteId and SalesDate = @CurrentSalesDate ) FOR XML AUTO, ELEMENTS --XMLDATA end-- COMMIT TRAN end |
|
Why Can I 'See' the results of this Store Proc?
