March 15, 2005 at 4:08 am
Hi,
I want to compare data in tables in multiple databases (rows, nulls etc..). Rather than script the whole thing out (8000+ tables)I thought I would write a cursor selecting the table name from sysobjects however I can't run a select with the table name as the variable e.g.
DECLARE @name varchar(40)
select @name = 'MYTABLE'
Select * from @name
errors off with
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@name'.
I may well be doing something stupid....
Many thanks
March 15, 2005 at 5:44 am
March 15, 2005 at 5:45 am
Would this be an option
DECLARE @name varchar(40)
SELECT @name = 'MYTABLE'
EXECUTE ('SELECT * FROM ' + @name)
Hope this helps
March 15, 2005 at 6:48 am
That's great thanks. Problem no. 2!
when using this I don't seem to be able to attach to a linked server (if i just run the string then it is fine), any ideas?
declare @sql varchar(4000)
declare @Tablename varchar(30)
select @TableName = 'MYTABLE'
--Works Fine:
select @sql = 'select count(*) from [' + @TableName + ']'
exec (@sql)
-- works fine:
select count(*) from MYSERVER.MYDB.dbo.mytable
-- Fails:
select @sql = 'select count(*) from MYSERVER.MYDB.dbo.[' + @Tablename + ']'
exec @sql
March 16, 2005 at 4:23 am
If what you wrote is without typo, then there is a difference between the 2 exec statements: exec (@sql) vs. exec @sql. Missing parentheses would mean that @sql is name of a stored procedure - and that is why it fails.
If the parentheses are missing only in your post, then I don't know, because it works fine on our linked servers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply