T-SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

T-SQL query

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-05-07 : 16:38:30

Hello All,

I have to find out the record count from certain tables on the 2 different servers. This count needs to be updated in one of the # table.

@tablename is coming from the # table which I am inserting the names of the tables I need the count on.

I am getting an error on the following line:


SET @OldDB = '[IND-FR-PR01].DB1.dbo.' + @tablename
SET @NewDB = '[IND-FR-PR02].DB2.dbo.' + @tablename

UPDATE #TempTable
    SET OldCount = COUNT(*) FROM @OldDB
    WHERE OldCount = @tablename

UPDATE #TempTable
    SET NewCount = COUNT(*) FROM @NewDB
    WHERE NewCount = @tablename

I get an error saying 'Must declare the table variable @OldDB' SQL is treating that as a table type variable. But it should be the variable value which is defined at the SET statements.

Thanks,
-S

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-07 : 16:55:54

You cannot replace a table name with a variable. In order to get past this you would need to use Dynamic SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-08 : 09:57:47

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources