Error with Linked Servers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeD - 28 Mar 2007 02:49 GMT

From time to time the linked servers we have set up cannot be accessed. When
I click on the tables node under the linked server within enterprise manager
the session hangs and no error message is displayed. When querying the data
via an openquery no error is thrown, the query simply runs until manually
aborted.

I've created a linked server OK but immediately cannot open it. The only way
around this problem we have is a reboot of the server. Does anyone know how
to resolve this problem and also what would cause this.

Thanks

Mike

Reply to this Message

Uri Dimant - 28 Mar 2007 08:28 GMT

MikeD
Do you have ping to linked server?

> From time to time the linked servers we have set up cannot be accessed.
> When
[quoted text clipped - 14 lines]
>
> Mike

Reply to this Message

MikeD - 28 Mar 2007 08:52 GMT

I can ping the server that sql is on if that's what you mean. I can see and
create linked servers but cannot query them - see below.

> MikeD
> Do you have ping to linked server?
[quoted text clipped - 17 lines]
> >
> > Mike

Reply to this Message

Uri Dimant - 28 Mar 2007 09:42 GMT

MikeD
Do you run the query in this way?
SELECT <> FROM Server.dbname.dbo.table

>I can ping the server that sql is on if that's what you mean. I can see and
> create linked servers but cannot query them - see below.
[quoted text clipped - 22 lines]
>> >
>> > Mike

Reply to this Message

MikeD - 28 Mar 2007 09:54 GMT

No - it's an openquery so it's :
SELECT * FROM OPENQUERY[linked_server_name], 'Select * from table_name'

But the problem isn't so much in the query it's the fact that when you
select the linked server the system hangs with no error message and the only
way we have of resolving the issue is a server reboot

> MikeD
> Do you run the query in this way?
[quoted text clipped - 26 lines]
> >> >
> >> > Mike

Reply to this Message

Uri Dimant - 28 Mar 2007 10:14 GMT

Well, is it large set data you want to return?

exec sp_serveroption 'servername','data access','true'

> No - it's an openquery so it's :
> SELECT * FROM OPENQUERY[linked_server_name], 'Select * from table_name'
[quoted text clipped - 38 lines]
>> >> >
>> >> > Mike

Reply to this Message

MikeD - 28 Mar 2007 10:32 GMT

It's nothing to do with the query or the size of data. the problem is that
the linked server cannot be accessed until a reboot is done.

> Well, is it large set data you want to return?
>
[quoted text clipped - 42 lines]
> >> >> >
> >> >> > Mike

Reply to this Message