Compare DB tables from different servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare DB tables from different servers

Author

Topic

dayo73
Starting Member

6 Posts

Posted - 2005-07-13 : 04:17:41

Hi there

I'm looking to compare two database tables from different servers grabbing the rows from the table with the difference and adding to the other table. I have the code to do this if the two databases are on the same server, but not if on different servers.

SELECT db1.* FROM Test..Table1 AS db1 LEFT JOIN Test2..Table1 AS db2 ON db1.ID = db2.ID WHERE db2.ID IS NULL

Obviously the above sql doesn't do the insert part yet.

I appreciate any help.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 04:50:43

You have to add otherserver as Linked server
sp_addLinkedServer 'OtherServer'
Then you can use OtherServer.dbname.dbo.tablename in your query

Madhivanan

Failing to plan is Planning to fail

dayo73
Starting Member

6 Posts

Posted - 2005-07-13 : 10:33:18

Hi

Thanks for that :) So i would do the following for example

CREATE PROCEDURE sp_AddLinkedSever 'OtherServer'

AS

SELECT db1.* FROM OtherServer.Test..Table1 AS db1 LEFT JOIN Test2..Table1 AS db2 ON db1.ID = db2.ID WHERE db2.ID IS NULL

GO

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 10:38:47

No
In Query Analyser Run this query

sp_addLinkedServer 'OtherServer'

and then execute your other query as I specified

Madhivanan

Failing to plan is Planning to fail

dayo73
Starting Member

6 Posts

Posted - 2005-07-13 : 11:09:23

Hi

I get the following error when i try to check the syntax of my procedure:

"Error 7202: Could not find server 'ServerName' in sysservers. Execute p_addLinkedServer to add the server to sysservers"

Do ihave to set up some sort of reference to the other server or something?

My procedure is thus:
CREATE PROCEDURE p_AddLinkedServer

AS

SELECT db1.* FROM SERVERNAME.Test..Table1 AS db1 LEFT JOIN Test2..Table1 AS db2 ON db1.ID = db2.ID WHERE db2.ID IS NULL

GO

Cheers

jhermiz

3564 Posts

Posted - 2005-07-13 : 11:11:31

As mad stated you need to add the linked server before you can specify the .. notation
Add the other server as a linked server.
Go to query analyzer
Type this:
sp_addLinkedServer 'YourServerGoesHere'

Only until you add this as a linked server can you do:

SELECT db1.* FROM SERVERNAME.Test..Table1 AS db1 LEFT JOIN Test2..Table1 AS db2 ON db1.ID = db2.ID WHERE db2.ID IS NULL

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 11:19:31

What are the names of the servers?
You have to use the real name of that server

Madhivanan

Failing to plan is Planning to fail

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-07-06 : 04:37:05

I get the same error when I use update statement for rowset from xl . Any ideas . Is it that i can not update with rowset from exceljoined with table in my user databse .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 06:03:31

quote:
Originally posted by Hitesh Shah

I get the same error when I use update statement for rowset from xl . Any ideas . Is it that i can not update with rowset from exceljoined with table in my user databse .

Refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-07-06 : 07:38:50

Thanks for the link . I have already used the same to successfully insert the records from Excel . But the problem is updating the records using update ... set .... SQL statement .

urichi
Starting Member

2 Posts

Posted - 2007-08-01 : 16:57:01

For the same issue I got the following error message.
Can you help me

--Error
"Msg 15247, Level 16, State 1, Procedure sp_addlinkedserver, Line 26
User does not have permission to perform this action."

so what type of permission I need it in that server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources