Skip to Content
avatar image
Former Member

Problems with Publications when migrating Ultralite DB v.11 to v.16

Update: I have identified where is the problem located, is in this part of the Publication (where clause), but still don't have solution:

TABLE "TPDA_SERVICE_LINE_DETAIL" WHERE NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
                                  FROM TPDA_JOB JO, TPDA_SERVICE_LINE SL, TPDA_M_STATE_MACHINE SM, TPDA_M_STATE_MACHINE SM2
                                  WHERE SM.VSTM_ENTITY = 'J'
                                    AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
                                    AND JO.NSTM_STATUS_ID = SM.NSTM_STATUS_ID
                                    AND SL.NJOB_JOB_ID = JO.NJOB_JOB_ID
                                    AND SM2.VSTM_ENTITY = 'S'
                                    AND SM2.NSTM_NUMBER = 1 -- Processed. If the Service Line is Failed, its details won't be uploaded.
                                    AND SL.NSTM_STATUS_ID = SM2.NSTM_STATUS_ID ),

I have also changed the query to be more compliant, but no sucess:

NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
FROM
TPDA_SERVICE_LINE SL
inner join TPDA_JOB JO on JO.NJOB_JOB_ID = SL.NJOB_JOB_ID
inner join TPDA_M_STATE_MACHINE SM on SM.NSTM_STATUS_ID = JO.NSTM_STATUS_ID
inner join TPDA_M_STATE_MACHINE SM2 on SM2.NSTM_STATUS_ID = SL.NSTM_STATUS_ID
WHERE SM.VSTM_ENTITY = 'J'
AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
AND SM2.VSTM_ENTITY = 'S'
AND SM2.NSTM_NUMBER = 1) -- Processed. If the Service Line is Failed, its details won't be uploaded.

Hi All!

I'm having some problems on migrating an Ultralite database (v.11) to version 16 using sybase central on Windows 7 x64 (using both 32 and 64 bit versions). I had unload the database with the Sybase central wizard, and created an XML containing all data and schema, and also publications. When I try to load this XML into a new database version 16, the apps Crashes:

I've identified that if i don't include the Publications in the unload, then all works.

Also when I try to create a new publication the Sybase central crashes:

[ODBC] [UltraLite Database] Connection was terminated

SQLCODE: -308

SQLSTATE: ERROR

SQL Statement:

I have tried also to recreate the publications with this SQL Script:

CREATE PUBLICATION "fullSynchro_Pub" (
  TABLE "TPDA_ADJUSTMENT_LINE",
  TABLE "TPDA_JOB",
  TABLE "TPDA_M_ACTION_TYPE",
  TABLE "TPDA_M_ADJUST_TYPE",
  TABLE "TPDA_M_ASSIGNED_QUESTIONNAIRES",
  TABLE "TPDA_M_COUNTRY_PARAMETERS",
  TABLE "TPDA_M_DATA_USE_CONSENT",
  TABLE "TPDA_M_ELECTRIC_COMPANY",
  TABLE "TPDA_M_ELECTRIC_REFUND_TYPE",
  TABLE "TPDA_M_FAILURE_REASON",
  TABLE "TPDA_M_INDICATOR_TYPE",
  TABLE "TPDA_M_JOB_TYPE",
  TABLE "TPDA_M_LANGUAGE",
  TABLE "TPDA_M_MANUFACTURER",
  TABLE "TPDA_M_MODEL",
  TABLE "TPDA_M_PARAM_DOMINIUM",
  TABLE "TPDA_M_PARAMETER",
  TABLE "TPDA_M_PRODUCT",
  TABLE "TPDA_M_PRODUCT_ADJUST_NEED",
  TABLE "TPDA_M_PRODUCT_GROUP",
  TABLE "TPDA_M_PRODUCT_TYPE",
  TABLE "TPDA_M_QUESTIONNAIRE",
  TABLE "TPDA_M_RISK_ASSESSMENT_QUESTION",
  TABLE "TPDA_M_RISK_QUESTION_ANSWER",
  TABLE "TPDA_M_STATE_MACHINE",
  TABLE "TPDA_M_STREET_TYPE",
  TABLE "TPDA_M_THERAPY_SORT",
  TABLE "TPDA_M_TRIP_TYPE",
  TABLE "TPDA_M_UNSIGNED_REASON",
  TABLE "TPDA_M_VAN_OPERATIONS",
  TABLE "TPDA_M_VAN_STATUS",
  TABLE "TPDA_MATERIAL",
  TABLE "TPDA_NEXT_VISIT",
  TABLE "TPDA_PATIENT",
  TABLE "TPDA_PATIENT_ADDRESS",
  TABLE "TPDA_PATIENT_DOCUMENTS",
  TABLE "TPDA_PATIENT_INDICATOR",
  TABLE "TPDA_PATIENT_SIGNATORIES",
  TABLE "TPDA_PATIENT_SIGNATURE",
  TABLE "TPDA_READING_METER_LINES",
  TABLE "TPDA_RISK_ASSESSMENT_ANSWER",
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SERVICE_LINE" WHERE NSTM_STATUS_ID IN (11,12),
  TABLE "TPDA_SERVICE_LINE_DETAIL" WHERE NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
                                  FROM TPDA_JOB JO, TPDA_SERVICE_LINE SL, TPDA_M_STATE_MACHINE SM, TPDA_M_STATE_MACHINE SM2
                                  WHERE SM.VSTM_ENTITY = 'J'
                                    AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
                                    AND JO.NSTM_STATUS_ID = SM.NSTM_STATUS_ID
                                    AND SL.NJOB_JOB_ID = JO.NJOB_JOB_ID
                                    AND SM2.VSTM_ENTITY = 'S'
                                    AND SM2.NSTM_NUMBER = 1 -- Processed. If the Service Line is Failed, its details won't be uploaded.
                                    AND SL.NSTM_STATUS_ID = SM2.NSTM_STATUS_ID ),
  TABLE "TPDA_SUMMARY_MATERIAL",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_THERAPY",
  TABLE "TPDA_THERAPY_PARAMETER",
  TABLE "TPDA_TRIP",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1,
  TABLE "TPDA_W_IDENTIFIERS",
  TABLE "TPDA_W_SQL_SENTENCES"
)
GO
CREATE PUBLICATION "loadVanSynchro_Pub" (
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1
)
GO
CREATE PUBLICATION "loadVisionOx_Pub" (
  TABLE "TPDA_VISIONOX"
)
GO
CREATE PUBLICATION "openTrip_Pub" (
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_TRIP",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1
)
GO


But I only can create the first one ("fullSynchro_pub").

When I try to delete it on the new database, i get the same error again...

There is a bug related or something that i'm doing wrong? Somebody can help?

Thanks a lot. I've attached the full SQL Script and XML File to help understanding.

Thanks a lot in advance.

Message was edited by: Naim Raja

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 02, 2014 at 09:12 PM

    Hello Naim,

    See the documentation for the restrictions on creating publications. You cannot use a subquery here:

    The search condition in the WHERE clause can only reference columns that are included in the article. In addition, you cannot use any of the following in the WHERE clause:

    • subqueries
    • variables
    • non-deterministic functions

    These conditions are not enforced, but breaking them can lead to unexpected results.


    Your definition is using a subquery:

    WHERE NSLI_SERVICELINE_ID = ANY (SELECT ...

    You will have to re-formulate how you specify this definition for the publication.

    Regards,

    Jeff Albion

    SAP Active Global Support

    Add comment
    10|10000 characters needed characters exceeded