Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
First table
CREATE TABLE [dbo].[emp](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL
) ON [PRIMARY]
 
GO
Second table
CREATE TABLE [dbo].[emp1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL
) ON [PRIMARY]
 
GO

Insert into emp
insert into emp (name) values ('a')
insert into emp (name) values ('b')
insert into emp (name) values ('c')
 
Now tried to insert data into emp1 from emp..
 
BEGIN TRAN
select COUNT(*) from emp
declare @NumRow int= 0
select @NumRow =  count(id) from emp
insert into emp1 (name) select name from emp
IF (@@ROWCOUNT = @NumRow AND @@ROWCOUNT > 0 )
BEGIN
        COMMIT TRAN
        print 'Inserting Records..'
        delete from emp
END
ELSE
    BEGIN
        ROLLBACK TRAN
        print 'error occured while inserting record'
    END
 

It successfully insert data from emp to emp1 and delete data from emp. This query is fine if both emp and emp1 are on same server.
 
But What to do if emp table is on local server and emp1 is on production server.

 

Thanks in advance..!!
Posted 26-Mar-13 12:50pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
mrinal_kumar_jha at 26-Mar-13 18:43pm
   
http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm
mrinal_kumar_jha at 27-Mar-13 13:44pm
   
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.
mrinal_kumar_jha at 27-Mar-13 15:14pm
   
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
 

 
gives 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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 562
1 Sergey Alexandrovich Kryukov 484
2 Maciej Los 325
3 DamithSL 233
4 Mathew Soji 195
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 26 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100