drop database MessageReceivergo/*==============================================================*//* Database: MessageReceiver                                    *//*==============================================================*/create database MessageReceivergouse MessageReceivergo/*==============================================================*//* Table: ReceiveMessage                                        *//*==============================================================*/create table ReceiveMessage (   ID                   int                  identity,   MessageType          nvarchar(200)        not null,   Operation            smallint             not null,   Content              nvarchar(max)        not null,   IsCompleted          bit                  not null,   TraceID              uniqueidentifier     not null default newid(),   constraint PK_RECEIVEMESSAGE primary key (ID))go/*==============================================================*//* Table: ReceiveMessageLog                                     *//*==============================================================*/create table ReceiveMessageLog (   ID                   int                  identity,   ReceiveMessageID     int                  not null,   LogTime              datetime             not null default getdate(),   Remark               nvarchar(100)        null,   constraint PK_RECEIVEMESSAGELOG primary key (ID))go/*==============================================================*//* Index: ix_ReceiveMessageLog_MsgID                            *//*==============================================================*/create index ix_ReceiveMessageLog_MsgID on ReceiveMessageLog (ReceiveMessageID ASC)go/*==============================================================*//* Table: SendMessage                                           *//*==============================================================*/create table SendMessage (   ID                   int                  identity,   MessageType          nvarchar(200)        not null,   Operation            smallint             not null,   Content              nvarchar(max)        not null,   IsArrived            bit                  not null,   TraceID              uniqueidentifier     not null default newid(),   constraint PK_SENDMESSAGE primary key (ID))go/*==============================================================*//* Table: SendMessageLog                                        *//*==============================================================*/create table SendMessageLog (   ID                   int                  identity,   SendMessageID        int                  not null,   LogTime              datetime             not null default getdate(),   Remark               nvarchar(100)        null,   constraint PK_SENDMESSAGELOG primary key (ID))go/*==============================================================*//* Index: ix_SendMessageLog_MsgID                               *//*==============================================================*/create index ix_SendMessageLog_MsgID on SendMessageLog (SendMessageID ASC)goalter table ReceiveMessageLog   add constraint fk_ReceiveMessage_ReceiveMessageID foreign key (ReceiveMessageID)      references ReceiveMessage (ID)goalter table SendMessageLog   add constraint fk_SendMessageLog_SendMessageID foreign key (SendMessageID)      references SendMessage (ID)gocreate procedure up_SendMessageToRemoteServerasdeclare @SendMessageID int,@MessageType nvarchar(200),@Operation smallint,@Content nvarchar(max),@TraceID uniqueidentifierwhile(1=1)begin    set @SendMessageID=null    select top(1)    @SendMessageID=ID,                    @MessageType=MessageType,                    @Operation=Operation,                    @Content=Content,                    @TraceID=TraceID        from SendMessage a        where a.IsArrived = 0        order by a.ID    if (@SendMessageID is null) break        exec Server001.MessageReceiver.dbo.up_cReceiveMessageForRemoteServer             @MessageType =@MessageType,             @Operation = @Operation,            @Content = @Content,            @TraceID=@TraceID     if (@@error <> 0) break    exec up_cSendMessageLog         @SendMessageID = @SendMessageID,        @Remark = N‘发送‘,        @IsArrived = 1endgocreate procedure up_cReceiveMessage(    @MessageType nvarchar(200),    @Operation smallint,    @Content nvarchar(max))asbegin try    begin transaction        declare @ReceiveMessageID int           insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted)            values(@MessageType,@Operation,@Content,0)                set @ReceiveMessageID=scope_identity()                insert into ReceiveMessageLog ( ReceiveMessageID, Remark )            values(@ReceiveMessageID,N‘接收.‘)     commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_cReceiveMessageForRemoteServer(    @MessageType nvarchar(200),    @Operation smallint,    @Content nvarchar(max),    @TraceID uniqueidentifier)asbegin try    begin transaction        declare @ReceiveMessageID int           insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted,TraceID)            values(@MessageType,@Operation,@Content,0,@TraceID)                set @ReceiveMessageID=scope_identity()                insert into ReceiveMessageLog ( ReceiveMessageID, Remark )            values(@ReceiveMessageID,N‘接收.‘)     commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_cReceiveMessageLog(    @ReceiveMessageID int,    @Remark nvarchar(100),    @IsCompleted bit)asbegin try    begin transaction                 insert into ReceiveMessageLog ( ReceiveMessageID, Remark )            values(@ReceiveMessageID,@Remark)                    update ReceiveMessage set IsCompleted=@IsCompleted where ID=@ReceiveMessageID            commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_cSendMessage(    @MessageType nvarchar(200),    @Operation smallint,    @Content nvarchar(max))asbegin try    begin transaction        declare @SendMessageID int           insert into SendMessage ( MessageType, Operation, Content,IsArrived)            values(@MessageType,@Operation,@Content,0)                set @SendMessageID=scope_identity()                insert into SendMessageLog ( SendMessageID, Remark )            values(@SendMessageID,N‘接收.‘)     commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_cSendMessageLog(    @SendMessageID int,    @Remark nvarchar(100),    @IsArrived bit)asbegin try    begin transaction                 insert into SendMessageLog ( SendMessageID, Remark )            values(@SendMessageID,@Remark)                    update SendMessage set IsArrived=@IsArrived where ID=@SendMessageID            commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_dReceiveMessageWithCompletedasset nocount onbegin try    begin transaction        declare @tb_del table(ID int)          insert into @tb_del(ID) select ID from ReceiveMessage where IsCompleted=1        delete a from ReceiveMessageLog a where exists(select 1 from @tb_del x where x.ID=a.ReceiveMessageID)                delete a from ReceiveMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)     commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgocreate procedure up_dSendMessageWithArrivedasbegin try    begin transaction            declare @tb_del table(ID int)          insert into @tb_del(ID) select ID from SendMessage where IsArrived=1         delete a from SendMessageLog a where exists(select 1 from @tb_del x where x.ID=a.SendMessageID)                delete a from SendMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)    commit transaction  end trybegin catch     declare @error nvarchar(2048)=error_message()    ;throw 50001 ,@error,1    if (@@trancount >0) rollback transactionend catchgo