Invalid Object Name - Grr...

This is what I have. It works fine until I get to the select statement, then it tells me that I have an invalid object name. What am I missing? Thanks!

DECLARE @.SvrName varchar(100)

if @.@.SERVERNAME='pubs' begin
set @.SvrName=pubs.books.isbn
print @.SvrName
end
if @.@.SERVERNAME='MGMFILENET' begin
set @.SvrName=store.books.isbn
print@.SvrName
end

print @.@.SERVERNAME
PRINT @.SvrName
SELECT * FROM "@.SvrName"Doesn't work that way

DECALRE @.SQL
SET @.SQL = 'SELECT * FROM ' + @.SvrName
EXEC(@.SQL)

But why do this...uhh dynamic sql...|||Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.|||Originally posted by acral
Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.

Huh?|||This will be running as a stored proc... the proc takes many steps in moving data around, but first I need to determine what environment the user is in (i.e. what server)...

The there will be a series of statements such as Update this table, email a percentage to that group, make a temp table over there, and so on. In one environment, all of the databases are on one server, in another environment, the database are on different servers. In both cases, they have to interact.

So if YOU are logged into the system, when the stored proc executes, it will see which server you are logged to then point you from there by way of the rest of the statements.

Make sense?|||Not to me, but that's not saying much..

What's the application layer?|||Wouldn't it just be simpler to write a stored procedure that does what you need on each server, then call the stored procedure on the appropriate server? This gets about a gazillion RPC calls and cross-server queries (with potential cross-server joins) out of the way.

That way each box can call one stored procedure (you could even make it an sp_ if you wanted to make thing simple), and there are so many fewer moving parts.

-PatP|||Okay, here's what I ended up doing...

using a string like

Exec(@.SQL) was not going to cut it, so I have an if/then scenario that checks @.@.SERVERNAME then sets a variable. The contents of that variable in turn point to the right server for the right instance.

The reasoning is this.. in one evironment the databases referenced are on the same server, in another environment they are on different servers.

Thanks for the help... I would have kept pounding on that stupid "string" half the night had you guys not set me straight. :)