insert data from local database to production database

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

First table

SQL

CREATE TABLE [dbo].[emp](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

Second table

SQL

CREATE TABLE [dbo].[emp1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

Insert into emp

SQL

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

SQL

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 11:50am

k@ran

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-03-26T12:00:00

Solution 1

See this: Setting up a Linked Server for a Remote SQL Server-Instance[^]

Posted 26-Mar-13 12:00pm

Maciej Los

Comments

k@ran 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

k@ran 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.

Maciej Los 27-Mar-13 14:53pm

I don't know exact answer, but suggest you to read these:
http://msdn.microsoft.com/en-us/library/ms175537%28v=sql.105%29.aspx[^]
http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]
http://msdn.microsoft.com/en-us/library/aa560998.aspx[^]
http://msdn.microsoft.com/en-us/library/ms190479.aspx[^]
If above won't help, call again ;)

k@ran 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.