Weired Error message when trying to update linked

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Weired Error message when trying to update linked

Author

Topic

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
GO
SET ANSI_NULLS ON
GO

ALTER 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) = NULL
AS

BEGIN

BEGIN 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
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 Live db
end

--COMMIT TRANSACTION -- commited changes to Live db

BEGIN 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
end

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[code]

When i try to execute the SP i keep on getting

Server: Msg 7391, Level 16, State 1, Line 4
The 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources