sql remote: no data from remote to consolidated db

[Former Member]

Hello,

I have a problem with synchronizing a remote DB. All data was succesfully imported from the consolidated. I executed some statements on the remote db and could see these changes in the transactionlog (with dbtran). The I started the synchronization from remote to consolidated, but no data was synchronized. Just a small file was generated. No errors found in the sql remote log file. The publication is up-to-date. I synchronize everything, no specifications on tables or columns.

This publication have worked correctly before. I don't have a clue what to test or check as a next step, because everything seems to be defined correctly.

I use ASA 11.0.1.2376

[regdomaratzki]

If Volker's suggestions don't help then can you please run dbunload -n against the remote database and then paste in the section from the reload.sql file whose section is titled "SQL Remote Definitions". In addition to this information, please also post the SQL statements that you are executing that you believe are not being sent.

[regdomaratzki · Accepted solution]

Drat. I wasn't until you mentioned UCA that I was reminded of this bug fix I made almost two years ago. A quick glance at your build number indicates that you are using v11.0.1.2376, so this bug fix is definitely your issue. Sorry this took so long. When empty messages were being sent and your SQL Remote definitions looked OK, that should have triggered my memory for this bug fix.

QTS 619254 - Dbremote could fail to send operation on databases with accent sensitive UCA collations

Versions affected: v10 and up

Versions fixed: v10.0.1.4039, v11.0.1.2405, all v12 and up

Customer Description: If a database had been initialized with the UCA collation sequence, and had also been initialized to respect accent sensitivity on all UCA string comparisons, it was likely that operations on tables without a subscribe by clause in the publication definition would fail to replicate. No errors would have been reported, but operations that should have replicated would not have been sent. This has now been fixed.

[Former Member]

Hi, I have some breaking news. If remote DB has collation sequence 1252LATIN1 (default), the sync from remote to cons works. If remote DB has collation sequence UCA (we changed it because we wanted to store descriptions in other languages), it does not synchronize back to the cons. Do you know why?

[Former Member]

Checked the regional settings of both cons and remote db. Cons db runs on server on which all code page conversion tables are checked. This is not the case for the machines on which the remote databases run...

[VolkerBarth]

I assume you are running DBREMOTE with -v -o YourLog.txt.

Have you checked DBTRAN with -sr to see what operations will be sent to the cons?

Do the entries in SYSREMOTEUSER fit for the corresponding site in cons and remote?

BTW, when setting SQL Remote's "compression option" to 0, you will generate human-readable message files. Setting that at the remote will show the SQL statements that will be sent to the cons. - Of course, if you run DBREMOTE -v at the cons, these SQL statements are shown in its SQL Remote log, independent of the setting of the compression option.

[VolkerBarth]

IMHO, your remote simply seems to be out of sync:

The cons has

log_received,confirm_received
21090027,21090027

whereas the remote has

log_sent,confirm_sent
21090027,10286724

So the cons has confirmed to have received messages up to log offset 21090027 from the remote. In contrast, the remote does think it has sent that message but hasn't get any confirmation of messages in the offset range 10286724-21090027. So there's a contradiction here - which can usually only be repaired by re-extract (or proper and very careful resetting)...

I don't know how that has happened - may it be you have "reset" the SQL Remote information in the remote but only partially?

[Former Member]

I did following scenario: Drop user icat1036 on consolidated (this will remove all sql remote settings for this remote db, right?). Recreate the synchronisation for this user.

The remote db was completelty empty, I did use a stored procedure as mentioned before to reinstall the publication.

So in fact the problem is on the consolidated side, because wrong confirmation has been sent?

What exactly do you mean with re-extract? Do you mean recreate the publication and subscription on consolidated side?

[VolkerBarth]

Usually, one creates a remote database by extracting the relevant data from the cons - either by using DBXTRACT or by a custom extraction process, and then fills an "empty" database with the reload information. So one basically creates a "fresh" remote with freshly started subscriptions one both sides.

And when replication fails for some reason, one usually re-extracts that remote - that is, dropping the remote user (and thereby dropping his subscription as well, as you state), and re-creating both afterwards. Publications do not get changed (I assume they are shared between different remotes...).

I would check that your custom stored procedure just "does the right things" - possibly it would help if you use the DBXTRACT tool (or the Sybase Central Wizard) on the cons to see what exactly statements it will generate - both for the remote user in the cons and for the cons user in the remote. For freshly started remotes, both sites start with sending a "starting message" (with offset 0-...).

---

From your information above, I do not see anything obvious that might have caused a problem. But I'm not Reg:) - Only the non-fitting confirmation information seems to point to a problem.

[Former Member]

Did it all over again. Deleted publication on consolidated site and recreated the publication and subscription. Everything goes fine one way, but problem on remote still the same.

As you can see, a file was created from offset 0007342510.

Next run starts again from that offset...

SYSREMOTEUSER on remote: log_send 7342510 log_sent 7342510 confirm_sent 7342510

I did some new transactions but no new file was created. I should expect file icat1036.2 was created.

I. 2011-12-20 17:20:01. 14: D:DatabasesSybaseicaticatlocal I. 2011-12-20 17:20:01. SQL Remote Message Link Version 11.0.1.2376 I. 2011-12-20 17:20:01. Scanning logs starting at offset 0007342510 I. 2011-12-20 17:20:01. Processing transaction logs from directory "D:DatabasesSybaseicaticatlocal" I. 2011-12-20 17:20:01. Processing transactions from active transaction log I. 2011-12-20 17:20:06. Sending message to "icatcentral" (0-0007342510-0007342510-0) I. 2011-12-20 17:20:06. sopen "D:ApplicationDataiCatSyncicatreplicationfilesicatcentralicat1036.1" I. 2011-12-20 17:20:06. write "D:ApplicationDataiCatSyncicatreplicationfilesicatcentralicat1036.1" I. 2011-12-20 17:20:06. close "D:ApplicationDataiCatSyncicatreplicationfilesicatcentralicat1036.1" I. 2011-12-20 17:20:08. Execution completed I. 2011-12-20 17:20:28. SQL Remote Message Agent Version 11.0.1.2376 I. 2011-12-20 17:20:28. I. 2011-12-20 17:20:28. Copyright © 2001-2009, iAnywhere Solutions, Inc. I. 2011-12-20 17:20:28. Portions copyright © 1988-2009, Sybase, Inc. All rights reserved. I. 2011-12-20 17:20:28. Use of this software is governed by the Sybase License Agreement. Refer to http://www.sybase.com/softwarelicenses I. 2011-12-20 17:20:28. I. 2011-12-20 17:20:28. 1: -c I. 2011-12-20 17:20:28. 2: ******** I. 2011-12-20 17:20:28. 3: -b I. 2011-12-20 17:20:28. 4: -qc I. 2011-12-20 17:20:28. 5: -s I. 2011-12-20 17:20:28. 6: -os I. 2011-12-20 17:20:28. 7: 50M I. 2011-12-20 17:20:28. 8: -o I. 2011-12-20 17:20:28. 9: D:applicationdataICATSyncSqlRemoteLogsicatcentraldbremote_messages.log I. 2011-12-20 17:20:28. 10: -l I. 2011-12-20 17:20:28. 11: 100000 I. 2011-12-20 17:20:28. 12: -t I. 2011-12-20 17:20:28. 13: -v I. 2011-12-20 17:20:28. 14: D:DatabasesSybaseicaticatlocal I. 2011-12-20 17:20:29. SQL Remote Message Link Version 11.0.1.2376 I. 2011-12-20 17:20:29. Scanning logs starting at offset 0007342510 I. 2011-12-20 17:20:29. Processing transaction logs from directory "D:DatabasesSybaseicaticatlocal" I. 2011-12-20 17:20:29. Processing transactions from active transaction log I. 2011-12-20 17:20:33. Execution completed

[Former Member]

hello, I took a backup of previous database version on which sync does work. I will search for differences and upgrade steps that could be the cause of the problem.

[Former Member]

Hello, I added the content of the systables.

SYSREMOTEUSER (on cons)

user_id,consolidate,type_id,address,frequency,send_time,log_send,time_sent,log_sent,confirm_sent,send_count,resend_count,time_received,log_received,confirm_received,receive_count,rereceive_count 
105,'N',1,'icat1036','A',,138181048,'2011-12-20 11:43:12.000',138181048,137333423,119,0,'2011-12-19 17:39:48.000',21090027,21090027,5,0

SYSREMOTEUSER (on remote)

user_id,consolidate,type_id,address,frequency,send_time,log_send,time_sent,log_sent,confirm_sent,send_count,resend_count,time_received,log_received,confirm_received,receive_count,rereceive_count
102,'Y',1,'icatcentral','A',,21090027,'2011-12-20 12:43:47.000',21090027,10286724,7,0,'2011-12-19 16:48:29.000',137333423,137333423,86,0

SYSPUBLICATION (on remote)

publication_id,object_id,creator,publication_name,remarks,type,sync_type
2,4472,102,'icatHQPub',,'R',0

SYSSUBSCRIPTION (on remote)

publication_id,user_id,subscribe_by,created,started
2,102,'',10295305,10311787

Here the sql remote section of the reload file

CREATE REMOTE TYPE "FILE" ADDRESS 'icat1036' 
go
GRANT PUBLISH TO "icat1036"
go
GRANT CONSOLIDATE TO "icatcentral" TYPE "FILE" ADDRESS 'icatcentral'
go
call SYS.sa_setremoteuser( 102,21090027,10286724,7,0,137333423,137333423,86,0)
go
CREATE PUBLICATION dummy_pub_1( TABLE dbo.RowGenerator )
go
DROP PUBLICATION dummy_pub_1
go
CREATE PUBLICATION "icatcentral"."icatHQPub" 
(
    TABLE "icat"."SystemSetting" ("SystemSettingID","Attribute","CurrentValue","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Rep_Key") SUBSCRIBE BY Rep_Key,
    TABLE "icat"."Brand" ("BrandID","EvalType","Name","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Currency" ("CurrencyID","Code","Name","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."ExchangeRate" ("ExchangeRateID","CurrencyID","FromDate","Rate","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Language" ("LanguageID","Code","Name","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Translation" ("TranslationID","Type","FK","LanguageID","Translation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."TypeOfMeal" ("TypeOfMealID","Name","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Country" ("CountryID","Code","ISO_Code","Name","LanguageID","CurrencyID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Statistics_Defaults" ("Statistics_DefaultsID","StatisticsTypeID","CountryID","VWID","CurrentValue","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Product" ("ProductID","EvalType","Number","PrefixNumber","Reference","Name","ProductCategoryID","IsSeasonal","IsChargeable","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Origin","UnitOfMeasureID","StatisticsTypeCode","AvailableFor","Weighing"),
    TABLE "icat"."Product_Brand" ("Product_BrandID","EvalType","ProductID","BrandID","BrandReference","IsPreferred_Brand","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."DeliveryPlace" ("DeliveryPlaceID","Name","CountryID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Vendor" ("VendorID","Name","Address1","Address2","ZipCode","City","CountryID","Contact","Phone","EMail","WebSite","LanguageID","CurrencyID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","LoginID","Password"),
    TABLE "icat"."Vessel_WorkShop" ("Vessel_WorkShopID","Code","Name","Type","CurrencyID","Occupation","ListTheExtras","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","EmailCook","EmailMaster"),
    TABLE "icat"."RequestForProposal" ("RequestForProposalID","Name","CountryID","Date_Closure","CurrencyID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Quotation" ("QuotationID","RequestForProposalID","VendorID","CurrencyID","Date_Received","Remark_Supplier","Remark_Evaluation","Evaluation","Score","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","UrlID"),
    TABLE "icat"."Country_Vendor" ("Country_VendorID","VendorID","CountryID","IsPreferred","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."VW_Country_Vendor" ("VW_Country_VendorID","VWID","Country_VendorID","IsFixed","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."VW_Country" ("VW_CountryID","VWID","CountryID","FromDate","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."iCatUser" ("iCatUserID","LoginID","Name","Password","EMail","IsCentral","TypeOfUser","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","SID"),
    TABLE "icat"."iCatUser_VW" ("iCatUser_VWID","iCatUserID","FromDate","CountryID","VWID","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Vendor_DeliveryPlace" ("Vendor_DeliveryPlaceID","Country_VendorID","DeliveryPlaceID","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."FoodOrder" ("FoodOrderID","VWID","SeqNumber","VendorID","NumberOfDays","Estimated_NOP_Per_Day","Prepared_ByID","MasterID","Date_Approved_By_Master","SuperIntendentID","Date_Approved_By_SI","Date_Ordered","LocalPurchaseOrder","DeliveryPlaceID","Date_Delivered","DeliverySlipNumber","Date_Invoiced","InvoiceNumber","Date_Delivery_Wanted","Contact","Bunker","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","URLID","ProjectManagerID","Date_Approved_By_PM","ProjectID","UnloadingPlace","InvoiceDetails"),
    TABLE "icat"."FoodOrder_ProductList" ("FoodOrder_ProductListID","FoodOrderID","Vendor_ProductListID","Qty_Requested","Qty_Approved","Qty_Ordered","Qty_Delivered","Qty_Accepted","Remark_Delivery","Status_Delivery","Qty_Invoiced","Evaluation","TypeOfUOM","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Qty_Weight","Weighted","Weighted_UnitOfMeasurementID","Remark_Website"),
    TABLE "icat"."NumberOfMeals" ("NumberOfMealsID","VWID","TypeOfMealID","Date_Meal","NumberOfMeals","IsBunker","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."ExtraMeals" ("ExtraMealsID","NumberOfMealsID","Name","Evaluation","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."RFP_ProductList" ("RFP_ProductListID","RequestForProposalID","ProductListID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."ProductList" ("ProductListID","EvalType","Product_BrandID","UnitOfMeasureID_Unit","Content","UnitOfMeasureID_StandardPack","Factor","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","StatisticalWeight","Reference"),
    TABLE "icat"."StatisticsType" ("StatisticsTypeID","Code","Name","UnitOfMeasureID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","SortingKey"),
    TABLE "icat"."Quotation_ProductList" ("Quotation_ProductListID","QuotationID","ProductListID","RFP_ProductListID","VendorReference_Unit","Price_Quoted_Unit","DeliveryTime_Unit","Remark_Evaluation_Unit","Evaluation_Unit","Origin_Unit","Remark_Supplier_Unit","VendorReference_StandardPack","Price_Quoted_StandardPack","Remark_Evaluation_StandardPack","Evaluation_StandardPack","TypeOf","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Score","SuggestedProductName"),
    TABLE "icat"."Vendor_ProductList" ("Vendor_ProductListID","VendorID","Quotation_ProductListID","ProductListID","VendorReference_Unit","DeliveryTime_Unit","Origin_Unit","Remark_Supplier_Unit","VendorReference_StandardPack","IsMandatoryToOrder","IsQuoted","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."VPL_Price" ("VPL_PriceID","Vendor_ProductListID","FromDate","Price_Unit","Price_StandardPack","CurrencyID","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."TableCodes" ("TableCodeID","TableName","Code","Created_By","Creation_Date","Last_Updated_By","Last_Updated_Date","Status","Log_Annul"),
    TABLE "icat"."Project" ("ProjectID","Name","CountryID","AXI_Code","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Invoice_Name1","Invoice_Name2","Invoice_Address1","Invoice_Address2","Invoice_Zipcode","Invoice_City","Invoice_Country","Invoice_Free1","Invoice_Free2"),
    TABLE "icat"."iCatUser_Project" ("iCatUser_ProjectID","iCatUserID","ProjectID","Created_By","Last_Updated_Date","Last_Updated_By","Log_Annul","Status","Creation_Date","FromDate"),
    TABLE "icat"."Discrepancies" ("DiscrepancyID","Original_FoodOrderProductID","Replacement_FoodOrderProductID","Deliverability","Reason","Solution","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."ProductCategory" ("ProductCategoryID","Name","P_ProductCategoryID","AvailableFor","StatisticsTypeCode","IsSeasonal","IsChargeable","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","Code","SortingKey"),
    TABLE "icat"."Assignments" ("AssignmentID","iCatUserID","VWID","CountryID","ProjectID","FromDate","TillDate","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."UnitOfMeasure" ("UnitOfMeasureID","Code","Name","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status","TypeOf"),
    TABLE "icat"."UOMConversion" ("UOMConversionID","FromUOM","ToUOM","Factor","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."Replication_Audit" ("Replication_AuditID","Pub","RemoteUser","ErrorMsg","CreationDate","LogAnnul","Status","Rep_Key"),
    TABLE "icat"."FoodOrder_ProductList_InvoiceInfo" ("FoodOrder_ProductList_InvoiceInfoID","FoodOrder_ProductListID","Amount","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status"),
    TABLE "icat"."FoodOrder_InvoiceInfo" ("FoodOrder_InvoiceInfoID","FoodOrderID","InfoIsOfType","Amount","Created_By","Last_Updated_Date","Last_Updated_By","Creation_Date","Log_Annul","Status")
)
go
CREATE SUBSCRIPTION TO "icatcentral"."icatHQPub" FOR "icatcentral"
go
call SYS.sa_setsubscription(2,102,'',10295305,10311787)
go
SET REMOTE "FILE" OPTION "icat1036"."directory" = 'D:\\ApplicationData\\iCat\\Sync\\icatreplicationfiles'
go
SET REMOTE "FILE" OPTION "icat1036"."debug" = 'YES'
go

[VolkerBarth]

Please try to format the above to be more readable - cf. the "preformatted text" and "quote" buttons in the edit view...

[VolkerBarth]

@Saegerman: If the ill-formatted answer above doesn't contain any contents that you have not added otherwise, I sould strongly suggest to delete that answer, as its long lines make this page very hard to read. If it contains relevant information not given elsewhere, please please format it accordingly.

Note: There's an edit button just on the left bottom of each question/answer.

[Former Member]

I'm sorry for the bad format. I didn't notice the pre-button. Do you have enough information about the publication now?

[VolkerBarth]

See my new answer (and Reg will tell more, methinks).

BTW: For questions like this, I would suggest to add a comment rather than a "real answer" - you can comment on each question/answer with the help of the "add new comment" button on the right bottom - and you can comment on comments with the "reply" button.

I don't want to sound like a know-it-all, it just helps to organize all these postings:)

[Former Member]

okay, no problem 😉

[Former Member]

Did some more testing. Content of sql remote file is always the same: 0A0100000000082F8AAF000000000141CEEB000000000141CEEB000000000000J g 2áx\x01\x01 õÿUicat1036?\x12}

The last part "icat1036" is the remote user and publisher_address in SYSREMOTETYPE. In SYSREMOTEOPTIONS "icat1036" is the user_name.

This is the stored procedure which makes the publication (replicationkey = "icat1036")

EXECUTE IMMEDIATE ('GRANT CONNECT TO "icatcentral" IDENTIFIED BY "icatcentral"'); EXECUTE IMMEDIATE ('GRANT CONNECT TO "' + cast (@ReplicationKey as varchar) + '" IDENTIFIED BY "' + cast (@ReplicationKey as varchar) + '"');

/ Create SQL Remote definitions / EXECUTE IMMEDIATE ('CREATE REMOTE MESSAGE TYPE FILE ADDRESS ''' + cast (@ReplicationKey as varchar) + ''''); EXECUTE IMMEDIATE ('GRANT PUBLISH TO "' + cast (@ReplicationKey as varchar) + '"'); EXECUTE IMMEDIATE ('GRANT CONSOLIDATE TO "icatcentral" TYPE "FILE" ADDRESS ''icatcentral''');

/ Create the publication icatHQPub to define what data gets published. / CREATE PUBLICATION "icatcentral".icatHQPub ( TABLE icat.Assignments (AssignmentID, iCatUserID, VWID, CountryID, ProjectID, FromDate, TillDate, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Brand (BrandID, EvalType, Name, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Country (CountryID, Code, ISO_Code, Name, LanguageID, CurrencyID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Country_Vendor (Country_VendorID, VendorID, CountryID, IsPreferred, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Currency (CurrencyID, Code, Name, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.DeliveryPlace (DeliveryPlaceID, Name, CountryID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Discrepancies (DiscrepancyID, Original_FoodOrderProductID, Replacement_FoodOrderProductID, Deliverability, Reason, Solution, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.ExchangeRate (ExchangeRateID, CurrencyID, FromDate, Rate, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.ExtraMeals (ExtraMealsID, NumberOfMealsID, Name, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.FoodOrder (FoodOrderID, VWID, SeqNumber, VendorID, NumberOfDays, Estimated_NOP_Per_Day, Prepared_ByID, MasterID, Date_Approved_By_Master, SuperIntendentID, Date_Approved_By_SI, Date_Ordered, LocalPurchaseOrder, DeliveryPlaceID, Date_Delivered, DeliverySlipNumber, Date_Invoiced, InvoiceNumber, Date_Delivery_Wanted, Contact, Bunker, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, URLID, ProjectManagerID, Date_Approved_By_PM, ProjectID, UnloadingPlace, InvoiceDetails), TABLE icat.foodorder_invoiceinfo(foodorder_invoiceinfoid, foodorderid, infoisoftype, amount, created_by, last_updated_date, last_updated_by,creation_date, log_annul, status), TABLE icat.FoodOrder_ProductList (FoodOrder_ProductListID, FoodOrderID, Vendor_ProductListID, Qty_Requested, Qty_Approved, Qty_Ordered, Qty_Delivered, Qty_Accepted, Remark_Delivery, Status_Delivery, Qty_Invoiced, Evaluation, TypeOfUOM, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Qty_Weight, Weighted, Weighted_UnitOfMeasurementID, Remark_Website), TABLE icat.foodorder_productlist_invoiceinfo(foodorder_productlist_invoiceinfoid, foodorder_productlistid, amount, created_by, last_updated_date, last_updated_by,creation_date, log_annul, status), TABLE icat.iCatUser (iCatUserID, LoginID, Name, Password, EMail, IsCentral, TypeOfUser, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, SID), TABLE icat.iCatUser_Project (iCatUser_ProjectID, iCatUserID, ProjectID, Created_By, Last_Updated_Date, Last_Updated_By, Log_Annul, Status, Creation_Date, FromDate), TABLE icat.iCatUser_VW (iCatUser_VWID, iCatUserID, FromDate, CountryID, VWID, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Language (LanguageID, Code, Name, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.NumberOfMeals (NumberOfMealsID, VWID, TypeOfMealID, Date_Meal, NumberOfMeals, IsBunker, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Product (ProductID, EvalType, Number, PrefixNumber, "Reference", Name, ProductCategoryID, IsSeasonal, IsChargeable, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Origin, UnitOfMeasureID, StatisticsTypeCode, AvailableFor, Weighing), TABLE icat.Product_Brand (Product_BrandID, EvalType, ProductID, BrandID, BrandReference, IsPreferred_Brand, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.ProductCategory (ProductCategoryID, Name, P_ProductCategoryID, AvailableFor, StatisticsTypeCode, IsSeasonal, IsChargeable, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Code, SortingKey), TABLE icat.ProductList (ProductListID, EvalType, Product_BrandID, UnitOfMeasureID_Unit, Content, UnitOfMeasureID_StandardPack, Factor, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, StatisticalWeight, "Reference"), TABLE icat.Project (ProjectID, Name, CountryID, AXI_Code, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Invoice_Name1, Invoice_Name2, Invoice_Address1, Invoice_Address2, Invoice_Zipcode, Invoice_City, Invoice_Country, Invoice_Free1, Invoice_Free2), TABLE icat.Quotation (QuotationID, RequestForProposalID, VendorID, CurrencyID, Date_Received, Remark_Supplier, Remark_Evaluation, Evaluation, Score, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, UrlID), TABLE icat.Quotation_ProductList (Quotation_ProductListID, QuotationID, ProductListID, RFP_ProductListID, VendorReference_Unit, Price_Quoted_Unit, DeliveryTime_Unit, Remark_Evaluation_Unit, Evaluation_Unit, Origin_Unit, Remark_Supplier_Unit, VendorReference_StandardPack, Price_Quoted_StandardPack, Remark_Evaluation_StandardPack, Evaluation_StandardPack, TypeOf, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Score, SuggestedProductName), TABLE icat.Replication_Audit (Replication_AuditID, Pub, RemoteUser, ErrorMsg, CreationDate, LogAnnul, "Status", Rep_Key), TABLE icat.RequestForProposal (RequestForProposalID, Name, CountryID, Date_Closure, CurrencyID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.RFP_ProductList (RFP_ProductListID, RequestForProposalID, ProductListID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Statistics_Defaults (Statistics_DefaultsID, StatisticsTypeID, CountryID, VWID, CurrentValue, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.StatisticsType (StatisticsTypeID, Code, Name, UnitOfMeasureID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, SortingKey), TABLE icat.SystemSetting (SystemSettingID, Attribute, CurrentValue, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, Rep_Key) SUBSCRIBE BY Rep_Key, TABLE icat.TableCodes (TableCodeID, TableName, Code, Created_By, Creation_Date, Last_Updated_By, Last_Updated_Date, Status, Log_Annul), TABLE icat.Translation (TranslationID, Type, FK, LanguageID, Translation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.TypeOfMeal (TypeOfMealID, Name, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.UnitOfMeasure (UnitOfMeasureID, Code, Name, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, TypeOf), TABLE icat.UOMConversion (UOMConversionID, FromUOM, ToUOM, Factor, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Vendor (VendorID, Name, Address1, Address2, ZipCode, City, CountryID, Contact, Phone, EMail, WebSite, LanguageID, CurrencyID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, LoginID, Password), TABLE icat.Vendor_DeliveryPlace (Vendor_DeliveryPlaceID, Country_VendorID, DeliveryPlaceID, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Vendor_ProductList (Vendor_ProductListID, VendorID, Quotation_ProductListID, ProductListID, VendorReference_Unit, DeliveryTime_Unit, Origin_Unit, Remark_Supplier_Unit, VendorReference_StandardPack, IsMandatoryToOrder, IsQuoted, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.Vessel_WorkShop (Vessel_WorkShopID, Code, Name, Type, CurrencyID, Occupation, ListTheExtras, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status, EmailCook, EmailMaster), TABLE icat.VPL_Price (VPL_PriceID, Vendor_ProductListID, FromDate, Price_Unit, Price_StandardPack, CurrencyID, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.VW_Country (VW_CountryID, VWID, CountryID, FromDate, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status), TABLE icat.VW_Country_Vendor (VW_Country_VendorID, VWID, Country_VendorID, IsFixed, Evaluation, Created_By, Last_Updated_Date, Last_Updated_By, Creation_Date, Log_Annul, Status) );

        EXECUTE IMMEDIATE ('CREATE SUBSCRIPTION TO "icatcentral"."icatHQPub" FOR "icatcentral"');
        EXECUTE IMMEDIATE ('START SUBSCRIPTION TO "icatcentral"."icatHQPub" FOR "icatcentral"');

        EXECUTE IMMEDIATE ('SET REMOTE FILE OPTION "directory" = ''' + @DriveForSync + ':\\ApplicationData\\iCat\\Sync\\icatreplicationfiles''');
        EXECUTE IMMEDIATE ('SET REMOTE FILE OPTION "debug" = ''YES''');

[Former Member]

Okay, I used the -sr parameter. I did an update on the brand table.

--CONNECT-1030-0021090027-DBA-2011-12-19 16:50
--BEGIN TRANSACTION-1030-0021090038 BEGIN TRANSACTION go
--REMOTE-1030-0021090041-0102-SENT_CONFIRM-0021090027-0137333423
--COMMIT-1030-0021090065 COMMIT WORK go
--CHECKPOINT-0000-0021090089-2011-12-19 17:00
--CONNECT-1001-0021090115-DBA-2011-12-19 17:03
--BEGIN TRANSACTION-1001-0021090126 BEGIN TRANSACTION go
--REMOTE-1001-0021090129-0102-SENT_CONFIRM-0021090027-0137333423
--COMMIT-1001-0021090153 COMMIT WORK go
--CONNECT-1006-0021090163-DBA-2011-12-19 17:03
--BEGIN TRANSACTION-1006-0021090174 BEGIN TRANSACTION go
--REMOTE-1006-0021090177-0102-SENT_CONFIRM-0021090027-0137333423
--COMMIT-1006-0021090201 COMMIT WORK go
--CONNECT-1019-0021090211-DBA-2011-12-19 17:11
--BEGIN TRANSACTION-1019-0021090222 BEGIN TRANSACTION go
--SQL-1019-0021090225 set option PUBLIC.compression = '0' go
--COMMIT-1019-0021090272 COMMIT WORK go
--CONNECT-1004-0021090275-DBA-2011-12-19 17:11
--BEGIN TRANSACTION-1004-0021090286 BEGIN TRANSACTION go
--REMOTE-1004-0021090289-0102-SENT_CONFIRM-0021090027-0137333423
--COMMIT-1004-0021090313 COMMIT WORK go
--CONNECT-1022-0021090323-DBA-2011-12-19 17:15
--BEGIN TRANSACTION-1022-0021090334 BEGIN TRANSACTION go
--UPDATE-1022-0021090599 UPDATE icat.Brand    SET Last_Updated_Date='2011-12-19 17:15:47.923',
       Last_Modified='2011-12-19 17:15:47.923' VERIFY (Last_Updated_Date,
      Last_Modified) VALUES ('2010-06-01 08:52:01.077','2011-12-19 16:48:23.330179')  WHERE BrandID=6001438 go
--PUBLICATION-1022-0021090599-0002-SUBSCRIBE UPDATE icat.Brand
--   SET Last_Updated_Date='2011-12-19 17:15:47.923' VERIFY (Last_Updated_Date)
--VALUES ('2010-06-01 08:52:01.077')
-- WHERE BrandID=6001438
--UPDATE-1022-0021090683 UPDATE icat.Brand    SET Last_Updated_Date='2011-12-19 17:15:47.923',
       Last_Modified='2011-12-19 17:15:47.923001' VERIFY (Last_Updated_Date,
      Last_Modified) VALUES ('2010-12-30 14:56:55.056','2011-12-19 16:48:23.33018')  WHERE BrandID=6001439 go
--PUBLICATION-1022-0021090683-0002-SUBSCRIBE UPDATE icat.Brand
--   SET Last_Updated_Date='2011-12-19 17:15:47.923' VERIFY (Last_Updated_Date)
--VALUES ('2010-12-30 14:56:55.056')
-- WHERE BrandID=6001439
--UPDATE-1022-0021090766 UPDATE icat.Brand    SET Last_Updated_Date='2011-12-19 17:15:47.923',
       Last_Modified='2011-12-19 17:15:47.923002' VERIFY (Last_Updated_Date,
      Last_Modified) VALUES ('2010-06-01 09:09:29.287','2011-12-19 16:48:23.346')  WHERE BrandID=6001440 go

[Former Member]

Hi

I collected some examples from the log-files

**********DBREMOTE LOG
I. 2011-12-19 17:17:48. Copyright © 2001-2009, iAnywhere Solutions, Inc.
I. 2011-12-19 17:17:48. Portions copyright © 1988-2009, Sybase, Inc. All rights reserved.
I. 2011-12-19 17:17:48. Use of this software is governed by the Sybase License Agreement. Refer to http://www.sybase.com/softwarelicenses
I. 2011-12-19 17:17:48. 
I. 2011-12-19 17:17:48. 1: -c
I. 2011-12-19 17:17:48. 2: ******************************************************
I. 2011-12-19 17:17:48. 3: -b
I. 2011-12-19 17:17:48. 4: -qc
I. 2011-12-19 17:17:48. 5: -s
I. 2011-12-19 17:17:48. 6: -os
I. 2011-12-19 17:17:48. 7: 50M
I. 2011-12-19 17:17:48. 8: -o
I. 2011-12-19 17:17:48. 9: D:\\applicationdata\\ICAT\\Sync\\SqlRemoteLogs\\icatcentral\\dbremote_messages.log
I. 2011-12-19 17:17:48. 10: -l
I. 2011-12-19 17:17:48. 11: 100000
I. 2011-12-19 17:17:48. 12: -t
I. 2011-12-19 17:17:48. 13: -v
I. 2011-12-19 17:17:48. 14: D:\\Databases\\Sybase\\icat\\icatlocal
I. 2011-12-19 17:17:48. SQL Remote Message Link Version 11.0.1.2376
I. 2011-12-19 17:17:48. Scanning logs starting at offset 0021090027
I. 2011-12-19 17:17:48. Processing transaction logs from directory "D:\\Databases\\Sybase\\icat\\icatlocal"
I. 2011-12-19 17:17:48. Processing transactions from active transaction log
I. 2011-12-19 17:17:53. Sending message to "icatcentral" (0-0021090027-0021090027-0)
I. 2011-12-19 17:17:53. sopen "D:\\ApplicationData\\iCat\\Sync\\icatreplicationfiles\\icatcentral\\icat1036.4"
I. 2011-12-19 17:17:53. write "D:\\ApplicationData\\iCat\\Sync\\icatreplicationfiles\\icatcentral\\icat1036.4"
I. 2011-12-19 17:17:53. close "D:\\ApplicationData\\iCat\\Sync\\icatreplicationfiles\\icatcentral\\icat1036.4"
I. 2011-12-19 17:17:54. Execution completed

**TRANSACTIONLOG (from specified offset)

--CONNECT-1030-0021090027-DBA-2011-12-19 16:50
--BEGIN TRANSACTION-1030-0021090038
BEGIN TRANSACTION
go
--COMMIT-1030-0021090065
COMMIT WORK
go
--CHECKPOINT-0000-0021090089-2011-12-19 17:00
--CONNECT-1001-0021090115-DBA-2011-12-19 17:03
--BEGIN TRANSACTION-1001-0021090126
BEGIN TRANSACTION
go
--COMMIT-1001-0021090153
COMMIT WORK
go
--CONNECT-1006-0021090163-DBA-2011-12-19 17:03
--BEGIN TRANSACTION-1006-0021090174
BEGIN TRANSACTION
go
--COMMIT-1006-0021090201
COMMIT WORK
go
--CONNECT-1019-0021090211-DBA-2011-12-19 17:11
--BEGIN TRANSACTION-1019-0021090222
BEGIN TRANSACTION
go
--SQL-1019-0021090225
set option PUBLIC.compression = '0'
go
--COMMIT-1019-0021090272
COMMIT WORK
go
--CONNECT-1004-0021090275-DBA-2011-12-19 17:11
--BEGIN TRANSACTION-1004-0021090286
BEGIN TRANSACTION
go
--COMMIT-1004-0021090313
COMMIT WORK
go
--CONNECT-1022-0021090323-DBA-2011-12-19 17:15
--BEGIN TRANSACTION-1022-0021090334
BEGIN TRANSACTION
go
--UPDATE-1022-0021090599
UPDATE icat.Brand
   SET Last_Updated_Date='2011-12-19 17:15:47.923',
       Last_Modified='2011-12-19 17:15:47.923'
 WHERE BrandID=6001438
go
......

*CONTENT OF THE SQL REMOTE FILE (no matter the amount of transactions, always 1 line) 0A0100000000082F8AAF000000000141CEEB000000000141CEEB000000000000J g 2áx\x01\x01 õÿUicat1036?\x12}

At cons DBREMOTE log I don's see statements. File was processed without error. Just the usual unlink, sopen, fstat, read and close commands.

Next files are always starting from the same offset

I have checked the systables with a working remote db and found no differences.

Only change in db options: cons db CHAR Collation sequence = 1252LATIN1 CHAR characterset encoding = windows-1252 remote CHAR Collation sequence = UCA CHAR characterset encoding = UTF-8 But that won't do any harm I suppose...

[regdomaratzki]

If the translated log file did in fact have the -sr switch on it as Volker suggested, the lack of any "--PUBLICATION" statements in the translated output shows that the operations in question (for example, the update to the icat.Brand table at offset 21090599) are not operations that are marked for replication. Can you please post the information that I asked for in the comment on your first post please?

[VolkerBarth]

From the translog, I don't notice any change that should be replicated. What operations (inserts, updates ...) do you expect in the log?

[VolkerBarth]

Nevertheless I'm puzzled that the TL goes until offset 0021090599 (or later), whereas the message just comprises the starting offset ("0-0021090027-0021090027-0")...

[Former Member]

hello, You need some more information then? I'm as puzzled as you I'm afraid...

[VolkerBarth]

As Reg has asked, the contents of the SYSREMOTEUSER table in the remote and in the cons (here only for the according remote) would be helpful, as the contents of the SYSPUBLICATION and SYSSUBSCRIPTION table in the remeote.

As you don't rely on DBXTRACT to generate the SQL Remtote definitions but with your own stored procedure - as shown in your new answer - it's somewhat difficult to read and "link" these pieces of information... Note, I don't claim at all that not using DBXRACT is bad - it's just harder to read EXECUTE IMMEDIATE statements...