October 29, 2009 at 8:14 am
What I'm trying to do is create a table that has the tables with in a certain Database on my server. This table will contain the table names, active rows, and expired rows. The active and expired rows will be done by performing a select count(*) where xxxx = ' '. I have a cursor started to do this but an running into problems. The code I have looks like this:
truncate table damon_work.dbo.DW_Table_Rowcount_stats
declare @table_name varchar(100)
declare @exp_rows numeric(18,0)
declare @active_rows numeric(18,0)
declare objects CURSOR
for
select name
from Dw_data_warehouse.dbo.sysobjects
where Dw_data_warehouse.dbo.sysobjects.xtype = 'U'
order by dw_data_warehouse.dbo.sysobjects.name
--declare @table_name varchar(100)
--declare @exp_rows numeric(18,0)
--declare @active_rows numeric(18,0)
open objects
FETCH NEXT From objects into @table_name
WHILE (@@FETCH_STATUS=0)
BEGIN
select count(*) as @exp_rows
from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt = '12/31/3030'
select count(*) as @active_rows
from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt <> '12/31/3030'
begin
update damon_work.dbo.DW_Table_Rowcount_stats
set table_name = @table_name,
expired_rows = @exp_rows,
Active_rows = @active_rows
end
FETCH NEXT From objects into @table_name
end
close objects
deallocate objects
The problem I'm having is with the select statements within the OPEN/ Fetch area. I get the following error msg's:
Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near '@exp_rows'.
Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '@active_rows'.
Any help will be greatly appreciated on this. Also posting this in teh SQL2005 forum to see if any advice from them.
October 29, 2009 at 8:22 am
Any help will be greatly appreciated on this. Also posting this in teh SQL2005 forum to see if any advice from them.
A couple things.
First, don't cross post.
Second, you can't use a variable for a table name like you're doing, you need to use Dynamic SQL. I'd create a temp table and insert rows into it with dynamic SQL statements, then update your table at the end by joining to your temp table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply