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 (butterfly (AT) nomail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote


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" <esquel (AT) sommarskog (DOT) se> wrote


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

Geoff (butterfly (AT) nomail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

It is the same name, I just jumbled it writing it here. I use the same name.


Obviously for some other reason.

There has to be more than two possibilities.

I have looked at this enough to know there
is NO SPELLING ERROR.

I know which servers I am on, There is plenty of way to make sure of this
not in the least is the big white
BGINFO letters in the upper right corner CLEARLY telling me what server I am
on.

????????

Yes. I get the local information back. I also told you I have compared the
tables side by side and they have identical information.

Geoff (butterfly (AT) nomail (DOT) com) writes:
As I said, I don't see your servers, so I cannot say what is going on
from a distance. But the error message is pretty clear. Then you can
question the answers I give you, but I'm afraid that it is getting
nowhere.

But run this in a query window with output to text:

SELECT * FROM master.dbo.sysservers
go
SELECT name FROM PUTYOURSERVERNAMEHERE.master.dbo.databases

And post the output.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Not sure what you are asking for at this point, but I did this. I did both
of these on the local 2000 machine.

SELECT * FROM master.dbo.sysservers
go
SELECT name FROM local2000ServerName.master.dbo.databases

Gave this.

(2 row(s) affected)

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

---------------------------------------------------------------------


SELECT * FROM master.dbo.sysservers
go
SELECT name FROM remote2005machinename.master.dbo.databases

Gave this.

(2 row(s) affected)

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

If I open the sysserver table in Enterprise Management, both local and
remote server names appear in the column SRVNAME.

"Geoff" <butterfly (AT) nomail (DOT) com> wrote


If I go to one of the servers that works and do this, however, it gives me
this.


select * from master.dbo.sysservers
go
select name from local2000machinename.master.dbo.databases

It gives me this.


(2 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.databases'.

Geoff (butterfly (AT) nomail (DOT) com) writes:
I wanted to see the actual data in the columns, and I wanted to see
the actual names you use in your query. Since you use different names
every time you post, and they appear to be mockup names, there is zero
possibility that I can assist you you any further.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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