June 3, 2011 at 3:15 am
HI All,
I wanted to have a data comparison of two tables (Employee, lets say.) residing at different server(Both SQL Server only). Using the following query for the same:
SELECT * FROM dbo.Employee
EXCEPT
SELECT * FROM OPENROWSET('SQLNCLI','Server=ServerName1;Trusted_Connection=yes;';'MyUID';'MyPWD',
'SELECT * FROM DatabaseName.dbo.Employee')
but its throwing me error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 87, Level 16, State 1, Line 0
SQL Network Interfaces: Connection string is not valid [87].
Please suggest where I am making the mistake. Or is there any other way around.
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
June 9, 2011 at 1:27 am
There is a tool SQL Data Compare which will do that.
June 10, 2011 at 1:29 pm
Lets assume same table A is in 2 different instances. Both instances being 2008 SQL Server.
select a.col from table_name a
except
select * from openquery(servername,'select col from table_name')
if you have 2005 sql version, i dont think "except" works so try joining the data sets
select a.col from table_name a
join (select * from openquery(servername,'select col from table A') b on (a.somecolumn = b.col)
Change left or right joins as needed
June 20, 2011 at 6:01 am
except is valid for sql 2005
http://msdn.microsoft.com/en-us/library/ms188055%28v=SQL.90%29.aspx
The issue your facing is network , login realted.
Are u able to connect to the target using other methods like .udl file etc
Rigth click your linked server connection in ssms and check under server options for RPC
June 20, 2011 at 6:08 am
Also you could check if the server your trying to connect to allows remoted connection , i.e check is sqlbrowser service is running and
follow the steps in the below link
June 20, 2011 at 8:54 am
Create link server - it helps there.
Are both servers 2005 + ? Except didnt work in SQL Server 2000.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply