Problem running dynamic sql in a While loop to get a list of Table_Names with Rowcounts per Table_Name (sql Server 2000)
-
Tuesday, February 05, 2013 4:59 PM
If I run the following code block (note the commented out statements inside the While Loop) -- it returns the following list of table_Names from Information_Schema.Tables (sql Server 2000):
set nocount on
If (object_id('tempdb..#tmp1') is not null) drop table #tmp1
create table #tmp1(table_Name varchar(100))
declare @rc int, @rc2 int, @inc int, @tN varchar(100), @sql nvarchar(4000)
select @rc = count(*) from information_schema.tables
set @inc = 1
while @inc <= @rc
Begin
select @tN = table_Name from
(select top 1 t1.table_name from information_schema.tables t1
where not exists (select table_name from #tmp1 t2 where t2.table_name = t1.table_name)
Order By t1.Table_Name) tA
insert into #tmp1 select @tn
--set @SQL = 'select @rc2 = count(*) from ' + quotename(@tN)
--execute sp_executeSQL @SQL, N'@rc2 int OUTPUT', @rc2 = @rc2 OUTPUT
print cast(@inc as varchar(2)) + ')' + @tN + --' rowcount = ' + cast(@rc2 as varchar(3))
Set @inc = @inc + 1
End
-----------
1)MSarticles
2)MSdistribution_agents
3)MSdistribution_history
4)MSdistribution_status
5)MSlogreader_agents
6)MSlogreader_history
7)MSmerge_agents
8)MSmerge_history
9)MSmerge_subscriptions
10)MSpublication_access
11)MSpublications
12)MSpublisher_databases
13)MSqreader_agents
14)MSqreader_history
15)MSrepl_backup_lsns
16)MSrepl_commands
17)MSrepl_errors
18)MSrepl_identity_range
19)MSrepl_originators
20)MSrepl_transactions
21)MSrepl_version
22)MSsnapshot_agents
23)MSsnapshot_history
24)MSsubscriber_info
25)MSsubscriber_schedule
26)MSsubscriptions
27)MSsync_states
28)sysconstraints
29)syssegmentsI want to see the same list but with a rowcount per Table_Name -- so I uncomment the commented out code in the While Loop -- and now I get the following result:
set nocount on
If (object_id('tempdb..#tmp1') is not null) drop table #tmp1
create table #tmp1(table_Name varchar(100))
declare @rc int, @rc2 int, @inc int, @tN varchar(100), @sql nvarchar(4000)
select @rc = count(*) from information_schema.tables
set @inc = 1
while @inc <= @rc
Begin
select @tN = table_Name from
(select top 1 t1.table_name from information_schema.tables t1
where not exists (select table_name from #tmp1 t2 where t2.table_name = t1.table_name)
Order By t1.Table_Name) tA
insert into #tmp1 select @tn
set @SQL = 'select @rc2 = count(*) from ' + quotename(@tN)
execute sp_executeSQL @SQL, N'@rc2 int OUTPUT', @rc2 = @rc2 OUTPUT
print cast(@inc as varchar(2)) + ')' + @tN + ' rowcount = ' + cast(@rc2 as varchar(3))
Set @inc = @inc + 1
End
-----------------------
1)MSarticles rowcount = 20
2)MSdistribution_agents rowcount = 0
3)MSdistribution_history rowcount = 0
4)MSdistribution_status rowcount = 0
5)MSlogreader_agents rowcount = 1
6)MSlogreader_history rowcount = 1
7)MSmerge_agents rowcount = 1
1)MSarticles rowcount = 20
2)MSdistribution_agents rowcount = 0
3)MSdistribution_history rowcount = 0
4)MSdistribution_status rowcount = 0
5)MSlogreader_agents rowcount = 1
6)MSlogreader_history rowcount = 1
7)MSmerge_agents rowcount = 1
Msg 8908, Level 22, State 6, Line 1
Table error: Database ID 19, object ID 117575457, index ID 2. Chain linkage mismatch. (1:717)->next = (1:810), but (1:810)->prev = (1:16601).
now it appears to only read 7 rows -- and reads them twice and then errors out. I would like to see all 29 rows from the original list with the rowcount for each Table_Name. Does anyone know what the problem is? How can I fix it?
Thanks
Rich P
All Replies
-
Tuesday, February 05, 2013 5:21 PMModerator
Chain linkage error fix:
Fix article: "Use 'dbcc checkdb' or 'dbcc check table', both have three repair options"
http://forums.databasejournal.com/showthread.php?34122-Chain-linkage-mismatch....
Kalman Toth SQL 2008 GRAND SLAM
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 05, 2013 5:25 PM
- Marked As Answer by Rich P123 Tuesday, February 05, 2013 5:58 PM
-
Tuesday, February 05, 2013 5:22 PM
Hi,
Please see below it's is clearly described there:
http://technet.microsoft.com/en-us/library/cc917590.aspx
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
-
Tuesday, February 05, 2013 5:25 PMModerator
I suspect the tables may be in different schemas or you have some internal problems with that database. Are you running the above against msdb database? What is the output of select @@version?
I ran the above in SQL Server 2008 R2 version and I got 221 entries (so I fixed your PRINT line to use something bigger than varchar(2)).
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Tuesday, February 05, 2013 5:58 PM
Thank you all for your replies. I am using sql server 2000 here. The problem was fixed with
DBCC CHECKDB (distribution, repair_allow_data_loss)
This did detect errors and did fix them. I ran it twice. The 2nd time there were no errors.
This problem has now been solved.
Thanks all.
Rich P
-
Tuesday, February 05, 2013 11:13 PM
Two caveats here:
- The option is not called REPAIR_ALLOW_DATA_LOSS for fun, but what DBCC does is simply to throw away pages that cannot be repaired. This may be OK in the distribution database, you should probably check that all your replication subscribers are in sync. Or restart application.
- Databases do not corrupt on a whim, but often the reason is bad hardware. If you don't know of any particular event like a power failure, you should consider the disk as tainted, and move the database elsewhere. Not that it has to be the disks that are bad - it could be a bad memory board as well, but the disk is the most common culprit.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
.png)
