Cannot execute linked server commands on named instance

Question

Cannot execute linked server commands on named instance

SueVecchi

SSC Veteran

Points: 235
More actions
July 29, 2004 at 8:50 am

#162327

Hi everyone,
I have two SQL instances on one server (2K, SP3a). On the second, named instance, I have added linked servers usingthe login/password and selecting SQL Server as the server type.
If I execute the distributed query in QA on the named instance, I get an error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'mylinkedserver' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
However, if I just add the server 'mylinkedserver' to the main instance on the box, then execute the query in the named instance again, it works.
As soon as I delete 'mylinkedserver' from the main instance, the query will not work on the named instance.
I am stumped as to why this would be the case. Why would the named instance be looking on the main instance's sysservers table for the linked server's info?
Any feedback is definitely appreciated.
Sue V.
s.vecchi@neu.edu

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Are you connected via QA to the main instance or the named instance?? I have several linked servers setup against named instances and use the following context SELECT * FROM [Server\Instance].database.owner.object....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

SueVecchi SSC Veteran Points: 235 More actions · Answer 2

Hi,

Thanks for your reply.

My problem is executing the linked server commands while on the named instance.

For example, my main instance is called spec8, the named instance

is spec8\sqlinst2.

On the spec8\sqlinst2 "linked servers" I have registered spec5.

When in QA on spec8\sqlinst2 and issuing a distributed query that runs on spec5 - which I have linked on spec8\sqlinst2 - it gives me the error message saying that it can't find spec5 in my sysservers table. Of course, it is there in the sysservers table and even if I enter sp_linkedservers to list them - spec5 is there.

If I add spec5 to the "spec8" main instance linked servers, the distributed query will work when run from spec8\sqlinst2.

Hope that makes sense. Do you have this situation as well ? If so,

how did you add your linked servers ? With EM or by the query analyzer?

Thanks,

Sue

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Sue,

I have gotten lazy and have done it through EM. Through EM can you see the tables/views?

Try the following: CREATE a table that ONLY exists in your spec5 system. Verify that you can see it through EM then run the following statement:

SELECT * FROM spec5.INSERTDBNAMEHERE..TABLENAMEHERE

I have learned that doing certain things the lazy (I mean efficient way) sometimes works ok.....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 4

On our clustered instances, every once in a while the linked servers just give up

In our case that is not deadly because the linked servers are only used by the servers application-administrators for administrative purposes.

After reboot (stop/start of the instance is not enough) it works again.

This reboot is then planned to be done between 12 and 12.15 and didn't fail yet.

Which security context did you enable for the linked server definition.

Try using a fixed (mapped for everyone) one, so you know it is not a user or authority error that tricks you.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SueVecchi SSC Veteran Points: 235 More actions · Answer 5

I am starting to believe that the culprit is bcp.

Since I am using the standard bcp format I think that it is

taking the bcp.exe and running under the main instance by default.

Is that normal behavior? The bcp.exe is shared by both instances.

declare @querycmd2 varchar(256)

select @querycmd2 = 'bcp "exec [spec5].master..xp_cmdshell ''dir d:\dirname\filename.txt''" queryout d:\dirname\filename.txt /c'

exec master..xp_cmdshell @querycmd2

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 6

Unfortunately, that does sound reasonable Sue. I haven't reviewed BCP in awhile. Is there a way to specify server like -S[Spec8\Inst2]???

Good Hunting!

AJ Ahrens

webmaster@kritter.net