I'm working in a SP and I got two tables in different servers, one is the main and the other is a copy but whit less columns, all I want is to run the SP every 5 min over the main table in order to validate if new records has been created, if yes take those new records and insert them into the copy table, that resides in the other server.
Searching a solution I've found this script wich works ok in the same server, but when I try to run it with the linked server it fails....please somebody help me with this.
This is the error that a get....
OLE DB provider "SQLNCLI" for linked server "ESDBCGW001T" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Procedure sp_newrecords, Line 37 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" was unable to begin a distributed transaction.
All configurations are perfect, and when I run the insert without the variables runs fine,I mean, instead of @val1, @val2 and @val3 I use real values 1, 'A', 'B'.
USE [XXX]
GO
/****** Object: StoredProcedure [dbo].[sp_newrecords] Script Date: 03/02/2015 11:11:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <>
-- Create date: <>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_newrecords]
-- Add the parameters for the stored procedure here
AS
BEGIN
DECLARE @val1 int
DECLARE @val2 nvarchar(50)
DECLARE @val3 nvarchar(50)
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE mycur1 CURSOR for
select empid,fname,lname from employee
where empid = 508 --this is for test only
OPEN mycur1
FETCH NEXT FROM mycur1 INTO @val1, @val2 , @val3
WHILE @@Fetch_Status = 0
BEGIN
begin tran /* default read committed isolation level is fine */
if not exists (select * from openquery (linkedserver,'select * from DBNAME.dbo.Employee_backup'))
--insert employee_backup values (@val1, @val2 , @val3)
begin
INSERT into [linkedserver].[DBNAME].[dbo].[Employee_backup] values (@val1, @val2 , @val3)
end
/*if not exists (select * from employee_backup with (updlock, rowlock, holdlock)
where employee_backup.empid = @val1
and employee_backup.fname = @val2 )
--insert employee_backup values (@val1, @val2 , @val3)
INSERT [LINKEDSERVER].[DBNAME].[dbo].[Employee_backup] values (@val1, @val2 , @val3)*/
--else
-- /* update */
commit /* locks are released here */
FETCH NEXT FROM mycur1 INTO @val1, @val2 , @val3`enter code here`
END
CLOSE mycur1
DEALLOCATE mycur1
END
