T-SQL syntax for servernames

[babreu]

I have a central server that has several servers on the network registered in the Enterprise Manager. I'd like to write some scripts or stored procedures that can access all of these servers. I tried fully qualifying the statement like this: "SELECT * FROM Servername.Database.dbo.tablename" but I get the message that the "object name contains more than the maximum number of prefixes."
Can someone tell me what I'm doing wrong or how I can accomplish this? Thanks in advance for any help.

[RamaKrishna Seelam]

Obviously you cannot use "Servername.Database.dbo.tablename" to access a remote table.
Instead use the following :
Create a Stored Procedure (SP) in the target server with the tablename as paramater. Code the SP such a way that it executes the required select statment on the required table. Call this SP from the first server using the syntax: exec Servername.Database.SPName @tablename
The prerequsite is that each server should be confiugred as remote servers within each other and should be trusted.

regards,
Rama



------------
babreu at 7/12/00 12:49:59 PM


I have a central server that has several servers on the network registered in the Enterprise Manager. I'd like to write some scripts or stored procedures that can access all of these servers. I tried fully qualifying the statement like this: "SELECT * FROM Servername.Database.dbo.tablename" but I get the message that the "object name contains more than the maximum number of prefixes."
Can someone tell me what I'm doing wrong or how I can accomplish this? Thanks in advance for any help.