query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-j
The error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***