OMB
Yak Posting Veteran
88 Posts |
Posted - 2005-08-05 : 10:43:51
|
Hi All I have the following stored procedure that basicall creates ani nsert on the one SQL server and then creates the same insert on another. i have created linked servers between the two.here is the SP[code]SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER PROCEDURE spAdditionalSecurities_Update @AdditionalSecuritiesID NUMERIC(18,0) = NULL, @ID VARCHAR(6) = NULL, @SecurityID VARCHAR(6) = NULL, @ANNOUNCEMENT VARCHAR(8) = NULL, @ClassOfSecurity VARCHAR(25) = NULL, @Status VARCHAR(5) = NULL, @Symbol VARCHAR(15) = NULL, @PrimarkMnemonic VARCHAR(5) = NULL, @NMS VARCHAR(8) = NULL, @ISIN VARCHAR(12) = NULL, @SettlementTypesID NUMERIC(18,0) = NULL, @IssuePrice FLOAT = NULL, @SHaresInIssue FLOAT= NULL, @Terms VARCHAR(200) = NULL, @SendToFT BIT = NULL, @WithdrawalDate DATETIME = NULL,@WithdrawnByID NUMERIC(18,0) = NULL, @WithdrawalComment VARCHAR(2000) = NULLAS BEGINBEGIN TRANSACTION -- Insert data into Live db -- add new rows IF @AdditionalSecuritiesID IS NULL BEGIN INSERT INTO AdditionalSecurities( ID, SecurityID, Announcement, ClassOfSecurity, Status, Symbol, PrimarkMnemonic, NMS, ISIN, SettlementTypesID, IssuePrice, SharesInIssue, Details, SendToFT, WithdrawalDate, WithdrawnByID, WithdrawalComment ) VALUES( @ID, @SecurityID, @Announcement, @ClassOfSecurity, @Status, @Symbol, @PrimarkMnemonic, @NMS, @ISIN, @SettlementTypesiD, @IssuePrice, @SharesInIssue, @Terms, @SendToFT, @WithdrawalDate, @WithdrawnByID, @WithdrawalComment ) END -- Update existing rows IF @AdditionalSecuritiesID IS NOT NULL AND @ClassOfSecurity IS NOT NULL BEGIN PRINT 'UPDATE' UPDATE AdditionalSecurities SET ID = @ID, SecurityID = @SecurityID, Announcement = @Announcement, ClassOfSecurity = @ClassOfSecurity, Status = @Status, Symbol = @Symbol, PrimarkMnemonic = @PrimarkMnemonic, NMS = @NMS, ISIN = @ISIN, SettlementTypesID= @SettlementTypesID, IssuePrice = @IssuePrice, SharesInIssue = @SharesInIssue, Details = @Terms, SendToFT = @SendToFT, WithdrawalDate = @WithdrawalDate, WithdrawnByID = @WithdrawnByID, WithdrawalComment = @WithdrawalComment WHERE AdditionalSecuritiesID = @AdditionalSecuritiesID END -- delete rows IF @AdditionalSecuritiesID IS NOT NULL AND @ClassOfSecurity IS NULL BEGIN PRINT 'DELETE' DELETE AdditionalSecurities WHERE AdditionalSecuritiesID = @AdditionalSecuritiesID ENDIF @@ERROR <> 0 begin ROLLBACK TRANSACTION exec master..xp_sendmail @recipients = h@abc.com;b@abc.com', @message = 'Error occured in spAdditionalSecurities_Update. The table was not updated.', @subject = 'ERROR: Replication of database failed' endELSE begin COMMIT TRANSACTION -- commited changes to Live db end--COMMIT TRANSACTION -- commited changes to Live dbBEGIN TRANSACTION -- Insert data into Backup db -- add new rows IF @AdditionalSecuritiesID IS NULL BEGIN INSERT INTO a.b.dbo.AdditionalSecurities( ID, SecurityID, Announcement, ClassOfSecurity, Status, Symbol, PrimarkMnemonic, NMS, ISIN, SettlementTypesID, IssuePrice, SharesInIssue, Details, SendToFT, WithdrawalDate, WithdrawnByID, WithdrawalComment ) VALUES( @ID, @SecurityID, @Announcement, @ClassOfSecurity, @Status, @Symbol, @PrimarkMnemonic, @NMS, @ISIN, @SettlementTypesiD, @IssuePrice, @SharesInIssue, @Terms, @SendToFT, @WithdrawalDate, @WithdrawnByID, @WithdrawalComment ) END -- Update existing rows IF @AdditionalSecuritiesID IS NOT NULL AND @ClassOfSecurity IS NOT NULL BEGIN PRINT 'UPDATE' UPDATE a.b.dbo.AdditionalSecurities SET ID = @ID, SecurityID = @SecurityID, Announcement = @Announcement, ClassOfSecurity = @ClassOfSecurity, Status = @Status, Symbol = @Symbol, PrimarkMnemonic = @PrimarkMnemonic, NMS = @NMS, ISIN = @ISIN, SettlementTypesID= @SettlementTypesID, IssuePrice = @IssuePrice, SharesInIssue = @SharesInIssue, Details = @Terms, SendToFT = @SendToFT, WithdrawalDate = @WithdrawalDate, WithdrawnByID = @WithdrawnByID, WithdrawalComment = @WithdrawalComment WHERE AdditionalSecuritiesID = @AdditionalSecuritiesID END -- delete rows IF @AdditionalSecuritiesID IS NOT NULL AND @ClassOfSecurity IS NULL BEGIN PRINT 'DELETE' DELETE a.b.dbo.AdditionalSecurities WHERE AdditionalSecuritiesID = @AdditionalSecuritiesID END IF @@ERROR <> 0 begin ROLLBACK TRANSACTION exec master..xp_sendmail @recipients =h@abc.com;b@abc.com', @message = 'Error occured in, spAdditionalSecurities_Update. The table was not updated.', @subject = 'ERROR: Replication of database failed' end ELSE begin COMMIT TRANSACTION -- commited changes to backup db endENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO[code]When i try to execute the SP i keep on getting Server: Msg 7391, Level 16, State 1, Line 4The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].having looked on the web for the problem it seems that there are issues with DTC, and to make sure that both are running on each server ands they are configured the same.If anybody has any ideas i would really appreciate your help.OMB |
|