I'm not sure about this one so if someone could help I'd appreciate this.
As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.
No, you can't use a character variable to specify an object name in most Transact-SQL statements. Depending on a number of factors outside the immediate discussion, you may be able to use dynamic SQL to replace:
SELECT * FROM @name1
using syntax like:
EXECUTE ('SELECT * FROM [' + @name1 + ']')
There are a number of possible pitfalls to this approach, but give it a shot and see if it works for you.