Linked server question.

[Geoff]

I am not sure where to put this, so I just chose two newsgroups with hopes
it fits. My problem is this:


I have about 10 SQL2000 servers. I have this working (properly working
linked servers) on about half of them now, but I ran into this.

I have two SQL2000 servers, ServerA and ServerB.

ServerA has under Enterprise management, both information from ServerA and
ServerB. On ServerB there is only information for
ServerB. (ServerB is just a test machine for the for the application, I
don't think any of this matters, I am only mentioning this portion
because this happens to not work on only these two machines)

I added SQL2005 server to the linked server in both ServerA and and ServerB.
SQL 2005 shows up in the linked servers area of them both. I can actually
see the tables and views in the linked servers area on both ServerA and
ServerB (I have data access temporarily enabled for testing purposes.) But
when I run query analyzer (for a simple test query) on both ServerA and
ServerB, it fails and returns this.

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server_2005' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.

But I did execute just like I did on the other servers and it shows up like
the others (that work properly). I am using the linked servers to utilize
a 2005 machines dbmail on Server2000.

I also opened up the tables on a failing 2000 machine and a 2000 machine
that works and checked the sysservers tables on both and they are identical.

I also have done this both ways, adding the linked servers from command line
and also through the GUI. I don't know what else to look at.
Does anyone have any ideas on this? It would be appreciated.

[Geoff]

[Erland Sommarskog]

Geoff (butt...@nomail.com) writes:
> I added SQL2005 server to the linked server in both ServerA and and
> ServerB. SQL 2005 shows up in the linked servers area of them both. I
> can actually see the tables and views in the linked servers area on both
> ServerA and ServerB (I have data access temporarily enabled for testing
> purposes.) But when I run query analyzer (for a simple test query) on
> both ServerA and ServerB, it fails and returns this.
>
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'Server_2005' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
>

So this error means that yot have not defined any server with the
name Server_2005 on these servers.

Since I don't see your actual server names, and your actual query, I
can't really say where you went wrong, but I would suspect it's a
trivial spelling error.

When you defined a linked server, it can be as simple as:

EXEC sp_addlinkserver 'THATSERVER'

And then you are able to connect to THATSERVER with queries like:

SELECT name FROM THATSERVER.master.dbo.sysdatabases

But a linked server is really only an alias, so you can say:

EXEC sp_addlinkedserver 'MYALIAS, '', 'SQLOLEDB', 'THATSERVER'

And now you can run queries on THATSERVER like this:

SELECT name FROM MYALIAS.master.dbo.sysdatabases



--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Geoff]

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9F9EEBFF3...@127.0.0.1...

> Geoff (butt...@nomail.com) writes:
>> I added SQL2005 server to the linked server in both ServerA and and
>> ServerB. SQL 2005 shows up in the linked servers area of them both. I
>> can actually see the tables and views in the linked servers area on both
>> ServerA and ServerB (I have data access temporarily enabled for testing
>> purposes.) But when I run query analyzer (for a simple test query) on
>> both ServerA and ServerB, it fails and returns this.
>>
>> Server: Msg 7202, Level 11, State 2, Line 1
>> Could not find server 'Server_2005' in sysservers. Execute
>> sp_addlinkedserver to add the server to sysservers.
>>
>
> So this error means that yot have not defined any server with the
> name Server_2005 on these servers.
>
> Since I don't see your actual server names, and your actual query, I
> can't really say where you went wrong, but I would suspect it's a
> trivial spelling error.
>
> When you defined a linked server, it can be as simple as:
>
> EXEC sp_addlinkserver 'THATSERVER'
>
> And then you are able to connect to THATSERVER with queries like:
>
> SELECT name FROM THATSERVER.master.dbo.sysdatabases
>
> But a linked server is really only an alias, so you can say:
>
> EXEC sp_addlinkedserver 'MYALIAS, '', 'SQLOLEDB', 'THATSERVER'
>
> And now you can run queries on THATSERVER like this:
>
> SELECT name FROM MYALIAS.master.dbo.sysdatabases

I DID execute it.

Did you see in the part that you snipped, I opened up a table on a server
that works and one that doesn't work and they are IDENTICAL

The TSQL that I used was pretty straightforward and I tried it just using
the GUI.


EXEC sp_addlinkedserver
@server='SQL_Server_2005 ',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='SQL Server 2005 instance name'

I enable RPC out and setup a login that I created on the 2005 machine. AGAIN
this all works on other machines that I have.

The simple query is select * from sql2005server.msdb.dbo.sysjobs

[Erland Sommarskog]

Geoff (butt...@nomail.com) writes:
> EXEC sp_addlinkedserver
> @server='SQL_Server_2005 ',
> @srvproduct='',
> @provider='SQLOLEDB',
> @datasrc='SQL Server 2005 instance name'
>
> I enable RPC out and setup a login that I created on the 2005 machine.
> AGAIN this all works on other machines that I have.
>
> The simple query is select * from sql2005server.msdb.dbo.sysjobs

I notice a difference here. In the call to sp_addlinkedserver you have
underscores, in the query you don't.

I am sorry, but it is impossible for me to say what you have done wrong.
But since the error message you got clearly says that the linked server is
not defined, there are really only two possibilties:

1) Spelling error.
2) You are running the query on a different server from where you defined
the server.

Since you seem to be referring to a UI, the latter is not an unlikely
explanation. Did you run "SELECT * FROM master.dbo.syservers" on the server
where you got this error?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx