Update table on a different server using linked server queries.

Question

Update table on a different server using linked server queries.

anand_vanam

SSCrazy

Points: 2376
More actions
February 22, 2012 at 4:38 pm

#391627

Hi All,
I am updating a table TabX on DBX of ServerX from ServerY with a stored procedure using linked server query.
Code ::
CREATE PROCEDURE [dbo].[TableUpdate]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Msg VARCHAR(8000)
,@Severity INT
,@State INT
BEGIN TRY
BEGIN TRAN
UPDATE TX
SET TX.ArchiveFlag = 'N'
,TX.SourceCode= 'S'
,TX.ProcessingDateTime = GETDATE()
FROM [SERVERX].[DBX].[dbo].[TabX] TX
JOIN dbo.TabA TA ON TA.ID = TX.ID
-- Have 5 more update table statements
--Exactly similar but on different tables.
-- Each statement updates 5K records.
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @Msg = 'Error: ' + CONVERT(VARCHAR(15), ERROR_NUMBER()) + ' - ' + ERROR_MESSAGE()
,@Severity = ERROR_SEVERITY()
,@State = ERROR_STATE()
IF @@TRANCOUNT <> 0
ROLLBACK TRAN
RAISERROR(@Msg, @Severity, @State)
END CATCH
END
also, added ServerX as linked servers on ServerY and ServerY as linked servers on ServerX.
Both connection are good. and I have sa permissions on both SQL Servers.
When I executed the SP on ServerY.
EXEC dbo.TableUpdate
Got the this Error.
OLE DB provider "SQLNCLI10" for linked server "SERVERX" returned message "Cannot start more transactions on this session.".
Executing the query "EXEC dbo.TableUpdate" failed with the following error: "Error: 7395 - Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SERVERX". A nested transaction was required because the XACT_ABORT option was set to OFF.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
the executed the SP like this
SET XACT_ABORT ON
GO
EXEC [dbo].[TableUpdate]
GO
SET XACT_ABORT OFF
Query ran for 15 minutes and none was updated on ServerX. So I killed the process.
Can anyone help me on this.
Thanks in advance.