[dba-SQLServer] Accessing Tables in another server

Try establishing Server2 as a linked server.
Try referencing S2's table as \\S2\database\table.

Let me know if these go wrong. I can do this. I may need to know your specific names, etc. 

That said, there are certain things you cannot do, such as use RI across databases. Bummer, but that is the case (AFAIK).

----- Original Message ----
From: David Emerson <newsgrps at dalyn.co.nz>
To: dba-SQLServer at databaseadvisors.com
Sent: Tuesday, January 16, 2007 10:31:47 PM
Subject: [dba-SQLServer] Accessing Tables in another server

SQL2000.

I have two servers registered in enterprise manager.  I would like to 
run a stored procedure from a database on one server which gets data 
from a table on the second server.  For example, assuming we have:

Stored Procedure1 in database1 on server 1
Table2 in Database2 on Server 2

I would like to do something like this in Database1

Select *
 From Server2.Database2.dbo.Table2

The error message I get when trying to save the procedure is "The 
number name contains more than the maximum number of prefixes.  The 
maximum is three."

How can I reference the data on the other server?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com