SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using T-SQL to Verify Tables Row Counts in Transactional Replication


Using T-SQL to Verify Tables Row Counts in Transactional Replication

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143433 Visits: 18649
Thanh Ngay Nguyen (8/5/2010)
Good point, Jason. Thanks!

Instead of using dbo.sysindexes, sys.partitions can be used to obtain the rowcount

SELECT  ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName,
p.rows AS RowCnt
FROM sys.partitions AS p
INNER JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND p.index_id IN ( 0, 1 )



You're welcome and thanks for updating your script.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

tfriedman71
tfriedman71
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 190
Another benefit of using the built in sp_publication_validation or sp_article_validation stored procs is that if the validation fails during execution, it will raise an error to the application log. There's a built in alert for replication validation failures which can automatically reinitialize the subscriptions, or just send email notifications via Database Mail to all concerned parties, or do both.
mhotek-836094
mhotek-836094
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 61
The built in validation procedures (article and publication) have existed since at least SQL Server 7.0. The parameters have not changed, so the same code that worked on SQL Server 7.0 would work on every version through SQL Server 2008 R2. (I can't remember if the validation procedures existed in SQL Server 6.5, but I do know they are there from 7.0 onwards.)

Michael Hotek
President - Champion Valley Software, Inc.
http://www.ChampionValleySoftware.com
Rowland Gosling
Rowland Gosling
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 324
Given some of the limitations with Replication Monitoring, problems with stale subscriptions on large tables etc it's a solid approach.

Kudos Thanh -- nice article!
Fox87
Fox87
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 621
Hi Guys,

Does anyone get a syntax issue on the below piece of code?


IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL
DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles
(
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_subscriber INT
)

DECLARE @sub_srv VARCHAR(255),
@sub_db VARCHAR(255),
@strSQL_S VARCHAR(4000)

DECLARE db_cursor_s CURSOR
FOR SELECT DISTINCT
subscriber_srv,
subscriber_db
FROM #tempTransReplication

OPEN db_cursor_s
FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + ''''
+ ' AS subscriber_srv, ' + '''' + @sub_db + ''''
+ ' AS subscriber_db, '
+ 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber
FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o
INNER JOIN ' + @sub_srv + '.' + @sub_db
+ '.sys.schemas AS s on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db
+ '.dbo.sysindexes AS i on o.object_id = i.id
WHERE ' + '''' + @sub_srv + '.' + @sub_db + ''''
+ ' + ' + '''' + '.' + ''''
+ ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT subscriber_srv + ' + '''' + '.' + ''''
+ ' + subscriber_db + ' + '''' + '.' + ''''
+ ' + destination_owner + ' + '''' + '.' + ''''
+ ' + destination_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempSubscribedArticles
EXEC ( @strSQL_S
)

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db
END
CLOSE db_cursor_s
DEALLOCATE db_cursor_s




I get this error when I am running it.


Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.


Any advice will be very helpful :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search