Comparing tables on two 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

General SQL Server Forums

New to SQL Server Programming

Comparing tables on two different servers

Author

Topic

NickOwer
Starting Member

7 Posts

Posted - 2011-08-22 : 22:27:23

Hey Guys,
I have been browsing around and have found a fair bit of information on this but cant seem to pull it together, Any help which can be provided is much appreciated!

So far i understand i need to use the sp_addlinkedserver command, Which i have as this:
sp_addlinkedserver ('bolsql62')
but i get a syntax error which says this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bolsql62'.

Do i need to run the sp_addlinkserver command as a once off or each time i execute the query?

What im trying to acheive is to have a powershell or SQL script which i can run which will output the difference between two tables on two different databases on different servers.

Thanks for your time!

Cheers

Nick

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 22:37:12

Remove those braces.

exec sp_addlinkedserver 'bolsql62'

Harsh Athalye
http://www.letsgeek.net/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 23:46:15

you need to create it only once and you can use it thereafter for all queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickOwer
Starting Member

7 Posts

Posted - 2011-08-23 : 01:25:05

Awesome thank you, Is working now and is returning two variables.

The code i am using is;
select COUNT (*) from [dbo].[tbl_orders] unionall select COUNT (*) from [BOLSQL62].[EAW2].[dbo].[tbl_orders]

Is there a way i can minus the two?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 01:29:44

[code]select SUM(cnt1) - SUM(cnt2) As yourvalue
from
(
select COUNT (*) AS cnt1,0 AS cnt2
from [dbo].[tbl_orders]
unionall
select 0,COUNT (*) from [BOLSQL62].[EAW2].[dbo].[tbl_orders]
)t

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-23 : 01:29:52

If both the table has same structure, you can use except operator to find the difference:

select * from [dbo].[tbl_orders]
except
select * from [BOLSQL62].[EAW2].[dbo].[tbl_orders]

Harsh Athalye
http://www.letsgeek.net/

NickOwer
Starting Member

7 Posts

Posted - 2011-08-23 : 18:47:35

Thank you everyone for your Help, It is much appreciated.

I have the following query which works
Select (select COUNT (*) from [dbo].[tbl_orders]) - (select COUNT (*) from [BOLSQL62].[EAW2].[dbo].[tbl_orders])

Is there a way i can put this into a powershell script which will output the result?

Im trying to get a script which i can run through our monitoring agent so it can alert us if the databases are more then 500+ records out of each other.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 01:43:43

why do you need a powershell script for that? isnt it enough to put this in a proc and check if difference is > 500 and if yes send a notification mail by using sp_send_dbmail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickOwer
Starting Member

7 Posts

Posted - 2011-08-24 : 02:17:07

Yes that would be awesome, How can i do that?

Can i get it too only alert if difference is greater then X?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 02:30:17

see

http://databases.about.com/od/sqlserver/ss/sql_server_agent_3.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickOwer
Starting Member

7 Posts

Posted - 2011-08-30 : 02:13:34

Hey Guys,
Thanks for all your help it is much appreciated.

So far i have this

EXEC
msdb.dbo.sp_send_dbmail

@profile_name = 'BRED',

@recipients

= 'me@me.com',

@query

= 'Select (select COUNT (*) from [BOLSQL60].[EAW1].[dbo].[tbl_orders]) - (select COUNT (*) from [BOLSQL62].[EAW2].[dbo].[tbl_orders])',

@subject

= 'Record Difference EAW1 - EAW2',

@query_result_header = 0, @exclude_query_output = 1, @append_query_error = 1, @attach_query_result_as_file = 1, @query_attachment_filename = 'qry.txt', @query_result_no_padding = 1

I have created the linked server for BOLSQL62 which is the second server this query connects to for the record count.

It is returning "Msg 18456, Level 14, State 1, Server BOLSQL62, Line 1
Login failed for user 'sa'."
even though the credentials are identical and specified in the linked server profle?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 08:11:35

have you passed correct credentials in linked server? is it enabled in second server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickOwer
Starting Member

7 Posts

Posted - 2011-08-31 : 03:16:21

Hi,
I added the linked server on the server i am running the query from, Do i need to add it on the server i am trying to connec to also

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 03:22:32

No - not unless the you want to be able to connect to the first server FROM the second one.

To access ServerB from ServerA you only need to create a linked server ON ServerA - with the link pointing to ServerB

NickOwer
Starting Member

7 Posts

Posted - 2011-08-31 : 03:28:59

I thought so, How come i am getting that error though? Driving me insane...

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 03:40:36

Password for SA on the remote server is not the same as the local one?

You can set up a login to use a specific login, and password, on the remote; or you can set it up to use the local login - in which case login at the remote must have same password.

Here's my script for creating a linked server: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources