InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 11:12:52 GMT20http://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxHi Guys,Does anyone get a syntax issue on the below piece of code?[code="sql"]IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL DROP TABLE #tempSubscribedArticlesCREATE 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 #tempTransReplicationOPEN db_cursor_sFETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_dbWHILE @@FETCH_STATUS = 0 BEGINSET @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_idLEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db + '.dbo.sysindexes AS i on o.object_id = i.idWHERE ' + '''' + @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_sDEALLOCATE db_cursor_s[/code]I get this error when I am running it.[quote]Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.Msg 102, Level 15, State 1, Line 2Incorrect syntax near '-'.Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'AND'.[/quote]Any advice will be very helpful :-)Tue, 22 May 2012 07:26:20 GMTFox87http://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxGiven some of the limitations with Replication Monitoring, problems with stale subscriptions on large tables etc it's a solid approach.Kudos Thanh -- nice article!Fri, 06 Aug 2010 14:00:16 GMTRowland Goslinghttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxThe 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.)Fri, 06 Aug 2010 10:54:25 GMTmhotek-836094http://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxAnother 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.Thu, 05 Aug 2010 20:59:46 GMTtfriedman71http://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspx[quote][b]Thanh Ngay Nguyen (8/5/2010)[/b][hr]Good point, Jason. Thanks!Instead of using dbo.sysindexes, sys.partitions can be used to obtain the rowcount[i][code="sql"]SELECT ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName, p.rows AS RowCntFROM sys.partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_idWHERE o.type_desc = 'USER_TABLE' AND p.index_id IN ( 0, 1 )[/code][/i][/quote]You're welcome and thanks for updating your script.Thu, 05 Aug 2010 14:38:36 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxGood point, Jason. Thanks!Instead of using dbo.sysindexes, sys.partitions can be used to obtain the rowcount[i]SELECT ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName, p.rows AS RowCntFROM sys.partitions AS p INNER JOIN sys.objects AS o ON p.object_id = o.object_idWHERE o.type_desc = 'USER_TABLE' AND p.index_id IN ( 0, 1 )[/i]Thu, 05 Aug 2010 13:24:40 GMTThanh Ngay Nguyenhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxI like the concept. I have a concern about usability in later versions of SQL server. The scripts should be updated to not use the deprecated system objects and start using the dmv's that started shipping with SQL 2005.Thu, 05 Aug 2010 12:09:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxHi bill.galashanyou're abso correct. My script only works when all data from table is relicated. If data fiterring is used, it of course will cause diff in rowcounts between pub and sub. I forgot to mention it in the article. Thanks.Thu, 05 Aug 2010 10:43:59 GMTThanh Ngay Nguyenhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxHi tina.t.kurtluyan Counting rows in sysindexes is much faster than counting rows in the actual table. However, because sysindexes is lazily updated, the rowcount may not be accurate. If that's the case, you may need to use a full COUNT(*).Thu, 05 Aug 2010 10:31:31 GMTThanh Ngay Nguyenhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxWhat if sysindexes table does not have the most up to date counts? I have trouble with getting the accurate table row counts on sysindexes table. Would your script work on such cases? There are no built in tools in SQL 2000 I believe to check the accuracy of replication.Thu, 05 Aug 2010 10:02:03 GMTtina.t.kurtluyanhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxMy thoughts exactly... Uh...Why don't you use the article and publication validation that ships with the product and has been there for multiple versions?sp_article_validation allows you to do both a rowcount and a rowcount+checksumsp_publication_validation just runs sp_article_validation for all articles in the publicationBoth account for filtering. The reason that they both have a checksum option is because it is possible for the row counts to be the same, but the data is still out of synch.If you are running replication on SQL Server 2005 and higher, tracer tokens tell you precisely what you throughput is. How fast a single article is sending data is completely irrelevant, because the replication engine doesn't transmit on an article by article basis. It transmits in batches for the entire publication. So, the throughput of one article is affected by every other article in the publication. As long as you are posting tracer tokens are regular intervals, then when things fall behind, you can use the tracer tokens to tell that your subscriber is caught up to the publisher as of an exact time of the day.Additionally, the replication monitor will show you how many rows are still pending at any given time for each publication. It will also use the most recent throughput numbers (either from a tracer token or a batch that was just replicated) to compute "how long until I catch up".There is a special set of validation procedures for merge replication, but tracer tokens are only valid for transactional replication.While it's a nice academic exercise, I wouldn't use anything except the built in validation procedures + tracer tokens.Thu, 05 Aug 2010 07:03:22 GMTmhotek-836094http://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxWhy not use the built in validation? It has options to do a fast count as well as a checksum, and it understands whatever filters are in place.Thu, 05 Aug 2010 06:28:01 GMTAndy Warrenhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxI have put something similar in place a couple of years ago.. I do a simple count on Publisher tables, and a count at the destination, then query to find differences (plus some extended formulas for certain tables), this is then emailed to the team responsible for the clone database (reporting) as attached.This is all done as sequence of tasks within the replication jobs...Thu, 05 Aug 2010 03:01:04 GMToraculumhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxWhile this works it is on the basis that the row count at the subscriber is supposed to be the same as the publisher. I have seen many cases where vertical and horizontal filtering is used on publications and the table at the subscriber is therefore significantly different from the publisher both in columns and in row count.Have you looked at tracer tokens. These are simple to use and give very quick indication of any latency which can be investigated further and with the use of the underlying stored procedures very easy to set up to generate email alerts. There is also the dmv sys.dm_os_performance_counters which can be used to monitor latency and throughput.These two features available within SQL Server are in fact simpler to use than your TSQL script and less of an overhead for very busy replication systems.Thu, 05 Aug 2010 00:47:09 GMTbill.galashanhttp://www.sqlservercentral.com/Forums/Topic964002-2760-1.aspxComments posted to this topic are about the item [b][url=/articles/Replication/70545/]Using T-SQL to Verify Tables Row Counts in Transactional Replication[/url][/b]Hi Experts!I would like to thank you for your comments surrounding this topic. Of course there are many ways to achieve the rowcount verification and I totally agree with your methods described below. My script was originally written for purpose of documenting our current transactional replication setup. It later evolved into rowcount verification. So I thought since I've read many of your articles and made use many of your scripts, I here contribute my tiny part back to the community. Again I appreciate for your comments. It's just few hours since my article got posted, I've learnt a great deal from your idea and comments... :-)Thu, 05 Aug 2010 00:05:47 GMTThanh Ngay Nguyen