Binaryworld - Easiest way to put multiple distributed statements in a transaction wh ... [ SQL Server -> Locks-Transactions ]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--select * from worksheet

--example : usp_UpdateWorksheet 'tst1.intownsuites.com' --//fully qualified name of Remotemachine
--example : usp_UpdateWorksheet 'tst1' --//only machine name
--example : usp_UpdateWorksheet '<server_ip>'

Alter  proc usp_UpdateWorksheet
@PropMCName as varchar(50)=NULL
as

Set XACT_ABORT ON

declare @propid varchar(100)
declare @sid varchar(100)

--//INSERT INTO OPENROWSET('...', 'SELECT field1, field2 FROM yourtable') VALUES (99, 'Test')

Exec('
  DELETE FROM
  OPENROWSET(''SQLOLEDB'',''' + @PropMCName + ''';''sa'';''mypwd'',''SELECT * FROM NV.dbo.Worksheet'')

     INSERT INTO
     OPENROWSET(''SQLOLEDB'',''' + @PropMCName + ''';''sa'';''mypwd'',''SELECT [wLockNumber], [wControlID], [wControlIndex], [wControlCaption], [wRequire], [wLeft], [wTop], [wHeight], [wWidth], [wTabStop], [wTabOrder] FROM NV.dbo.Worksheet'')
  SELECT   [wLockNumber_err], [wControlID], [wControlIndex], [wControlCaption], [wRequire], [wLeft], [wTop], [wHeight], [wWidth], [wTabStop], [wTabOrder] from test_worksheet

')

if @@error<>0
begin
  goto lblErrorEnd
end

Print 'Updated.....' + @PropMCName
goto lblSuccessEnd

lblErrorEnd:
Print 'Error While Updating.....' + @PropMCName

lblSuccessEnd:

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO