i am trying to execute following command from local to truncate table on production
truncate table server1.udupikrishna.dbo.emp1
but it throws :Msg 4701, Level 16, State 1, Line 1 Cannot find the object "emp1" because it does not exist or you do not have permissions. server1 is my linked server.
server1 is my linked server i have setup in local environment.
cant we perform delete operation on linked server.
if it is permisiion issue then from where i can set modification permission to link server ?
ISSUE 2
declare @NumRow int= 0
select @NumRow = count(id) from emp
BEGIN TRAN t1
insert into server1.udupikrishna.dbo.emp1 (name) select name from emp
IF (@@ROWCOUNT = @NumRow AND @@ROWCOUNT > 0 )
BEGIN
COMMIT TRAN t1
print 'Inserting Records..'
delete from emp
END
ELSE
BEGIN
print 'There are not any record to insert..'
ROLLBACK TRAN t1
END
Above query return error:
OLE DB provider "SQLNCLI11" for linked server "server1" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 4
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "server1" was unable to begin a distributed transaction.