sp_helpserver not showing all 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

SQL Server 2000 Forums

SQL Server Development (2000)

sp_helpserver not showing all servers

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 15:37:11

I'm attempting to setup a linked server, but am hitting this error:

sp_addlinkedsrvlogin
@rmtsrvname = SERVERNAME
, @useself = false
, @locallogin = NULL
, @rmtuser = SERVERNAME_PUBS
, @rmtpassword = SERVERNAME_PUBS

error:


The server 'SERVERNAME' does not exist. Use sp_helpserver to show available servers.

When I run sp_helpserver I don't see the server that I want to link to. The linking server is on sql server 2005 and the one I want to link to is 2000. Could this be part of it?

I tried sp_helpserver from within the server I want to link to and don't see the server I wasnt to link from either.

Please advise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-14 : 15:41:27

sp_addlinkedserver must be run first. Check it out in BOL.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-15 : 11:52:17

Thanks tkizer, I now see my linked server.
I also successfully executed the addlinkedsrvlogin stored procedure.

Now all I have to do is get a query to run successfully against the other server's tables.

I'm hitting an error though:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

Here's my query
SELECT TOP 10
*
FROM
LINKEDSERVERNAME.EXTERNALSERVERNAME..TABLENAME

It seems to be finding the table correctly (if I use a tablename that I know doesnt exist I get a 'does not contain table...' error).
Both servers are SQL Server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 12:39:02

You aren't using the 4 part naming convention correctly. Check the other thread for the format.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-15 : 16:00:06

Rock! Thanks tkizer, it works now:

Here's my query (following the LinkedServerName.DbName.OwnerName.ObjectName convention you provided)

SELECT TOP 10
*
FROM

LINKEDSERVERNAME.EXTERNALSERVERNAME.OwnerName.TABLENAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources