About linked sever queries - SQL Server Forums

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)

About linked sever queries

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2005-08-05 : 02:28:28

I am using about 4 linked servers.
When I wanted to used two servers at the same time in query analyzer
it throws the error message stating that "sql server does not exist or access is deneied".
On the other hand if i connect to that particular server,the query works.
Effectively I am able to one server at a time.
But I have huge data which has to be pulled from one server and compared with another server data.
Would somebody help on this.

When I write the following query,It does not work.
I am connected to server1 and my linked servers are server2,server3.
select top 10 * from server1.databasename.dbo.tablename
union all
select top 10 * from server1.databasename.dbo.tablename.
The above query does not work and throws the error message.
I tried the Select * from openquery('servername','query')
The above one does not work either.
I checked the linked server properties and its set up for data access.
Any help would be greatly appreciated.
Plz some one help in this regard.

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 04:59:55

Your example shows you connected to SERVER1 and querying SERVER1 itself - did you mean that?

If {Big IF maybe!) you want to compare data on SERVER2 and SERVER3 I would suggest you connect to one of them, and then Link in the other. If you use a pair of remote server connections, linked from SERVER1, then it will be doubly slow.

An alternative would be to pull the data locally (probably not what you want if you are trying to do a Compare)

select top 10 *
INTO #MyTemp1
from server1.databasename.dbo.tablename

select top 10 *
INTO #MyTemp2
from server2.databasename.dbo.tablename

SELECT * FROM #MyTemp1
union all
SELECT * FROM #MyTemp2

I can't remember ever doing what you are suggesting, but I don't see why it wouldn't work. Are the userid/passwords for the linkages, and the actual logins on the three server, all the same?

Kristen

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2005-08-05 : 10:47:46

Thanks.
I mean connected to server1 and querying server1 only.
If I query any of the other server ,it throws the error message stating sql server does not exist or access deneied.

We use the windows authentication and I have perimissions to all the servers I am trying to Use.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 10:50:09

Make sure those servers are running at the time of executing the query

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 11:44:18

"We use the windows authentication and I have perimissions to all the servers I am trying to Use."

So is your SERVER LINK set up to propagate your permission, or impersonate someone?

(Mind you can't see how that would stop you connecting tow two, if you can connect to each of them individually)

Can you pull the data from each individually, and compare locally, [as per my earlier example] or is that not an option?

Kristen

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2005-08-05 : 18:51:45

yeah..I am able to connect to the servers individually.
But when I try to connect the linked server It throws up the error message.
What would u all suggest to get around this issue?
Thanks

Kristen
Test

22859 Posts

Posted - 2005-08-06 : 02:50:50

"But when I try to connect the linked server It throws up the error message"

You mean when you try to connect to TWO linked servers in one query, or even just ONE linked server?

i.e.

Connect to SERVER1 and then type

SELECT * FROM SERVER2.master.dbo.sysdatabases

and for the two server test something like:

SELECT * FROM SERVER2.master.dbo.sysdatabases
UNION ALL
SELECT * FROM SERVER3.master.dbo.sysdatabases

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources