1 of 1 people found this helpful
When you create a PWX SQL Server Capture Registration, the thing that links the PWX Capture Registration to the SQL Server Article is the object Id of the Article.
If anything changes that, then data in DistDB won't be related to the Article object ID that PWX is looking for.
That breaks CDC.
If something "turned off" replication, then that probably happened.
To check whether or not there are rows in DistDB for PWX related Articles, you can use these queries:
Run these queries against the Distribution Database in SQL Server.
DistDB name may be "distribution", but the DistDB name can vary.
-- Query to get Publication, Article IDs
SELECT a.article_id, b.id, a.publication_id, c.srvname
FROM dbo.MSarticles a
JOIN dbo.MSpublisher_databases b on
JOIN master.dbo.sysservers c on
ORDER BY a.article_id desc, b.id desc, a.publication_id
That query will give you a list of article IDs, from that you'll take the PWX article ID and use it in the following query:
-- Query to select rows for an Article ID
SELECT publisher_database_id, xact_seqno, type,
cast(command as nchar(100))
WHERE article_id = <article id>
1) The article should match the tag name in the PowerExchange capture registration.
2) If there are no rows in the DistDB, then there is no data for PWX to retrieve
You can use DTLUCBRG to rebuild the connection between the article(s) and the capture registration(s):
HOW TO: Run the PowerExchange DTLUCBRG utility using the MSSOPTS UPDATESTATUS parameter in PowerExchange
Thanks for the post. We have tried the steps you suggested and We could see 0 records in the MSrepl_commands so We have tried to create a DTLUCBRG txt file and tried to run from command prompt
Below is the file we created:
But it's throwing an error
PWX-09082 API function <DESCRIBE> returned non-zero return code <264>, DBCB RCs <264/9088/0>.
PWX-00264 DBAPI Error. DB_DESCRIBE failed for file V:\SOURCE\DTLCRTSTU\DEBUG\glledger.MSS.DWCR000.1.cpr.
PWX-01252 DBNTC Initial "Describe" CONVERSE failed to location "gblwbidbp02.*******.corp", rcs 260/9088/0.
PWX-09088 Cannot add record, record sequence <1>, does not match file <0>.
Could you please suggest ,how we can move forward?
Below is the entry taken from log file:
2019-03-05 04:35:26 DTLUCBRG REGISTRATION REPORT (TESTRUN=N)
CONDTYPE = <None>
CRGNAME = <glledger>
DBTYPE = <MSS>
INSTANCE = <>
LOCATION = <gblwbidbp02.******.corp>
LOCATION_CRG = <gblwbidbp02.******.corp>
LOCATION_DM = <gblwbidbp02.******.corp>
LOCATION_XDM = <gblwbidbp02.******.corp>
OUTPUT = <G:\Informatica\PWX91\MSSQL\logs\dtlucbrg.log>
REPLACE = <N>
REUSECRGNAME = <N>
RPTCOLS = <Y>
STATUS = <I>
TABLE = <dbo.JDEGAAccountLedger>
NOTIFYCHANGES = <N>
IMSOPTS: Not relevant for this run
ORAOPTS: Not relevant for this run
Dbserver = <gblwbidbp02.******.corp>
Dbname = <DWCR>
ADAOPTS: Not relevant for this run
Summary of registrations created in DWCR000 with status INACTIVE and
condense type NONE
No of registrations created = 0
No of registrations updated = 0
No of existing registrations not matching update parameters = 0
2019-03-05 04:35:33 END OF DTLUCBRG REGISTRATION REPORT
Please let us know if you need any information.
If you want to be able to use PWXCCL, then you need CONDTYPE=PART.
If you want DTLUCBRG to replace existing registrations, then you need REPLACE=Y and REPLACEACTIVE=Y.
If you want to maintain the same registration name, then you need REUSECRGNAME=Y.
Please note that for SQL Server source, the login that PWX uses requires dbo to create capture registrations, and sa to create capture registration groups.
Thanks for all your help,
We are able to resolve the issue by recreating the Capture registration using the old CAPTURE files.