Error while installing Interaction packages - Database update

[#1]

I was trying to install the interaction packages in our on-premise Adobe Campaign instance. I selected interaction, interactionControl and interactionExec together (should have done one by one with interaction first I think as that is how the dependency is shown in XML packages dependency). Now I faced some SQL error during the installation as below but the other parts of the installation were done. Is there a way I can get the list of SQLs that were executed and I can manually check them and run if it has not done what it was supposed to do. If not, can we rollback the installation? If that is also no, what are the other options to fix the environment? Please don't tell me to restore from a previous backup

Package 'Offer engine for execution instances': Saving entities of type 'xtk:option'...

Package 'Control of offer engine with execution instance': Saving entities of type 'xtk:workflow'...

Package 'Control of offer engine with execution instance': Saving entities of type 'xtk:option'...

Package 'Control of offer engine with execution instance': Saving entities of type 'nms:extAccount'...

Package 'Control of offer engine with execution instance': Saving entities of type 'xtk:folder'...

Package 'Offer engine (Interaction)': Saving entities of type 'nms:offer'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:report'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:workflow'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:olapAggregate'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:olapDimension'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:olapDimension'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:olapMeasure'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:olapCube'...

tPath="/" upload="true"><dataSourceConfig Param(19)=108511909

76929 Param(5)=07/12/2018 2:44:11 PM Param(6)=1 Param(7)=New offer simulation Param(8)=108219090 Param(9)=108219091 Param(10)=0 Param(11)=0 Param(12)=simulation Param(13)=108276939 Param(14)=07/12/2018 2:44:11 PM Param(15)=0 Param(16)=1 Param(17)=0 Param(18)=<?xml version='1.0'?> <simulation doNotPersist="false" outOfProcess="false"><properties warning="false"/><offerFilter schema="nms:offer"/><source batchSize="200" format="text" rejectsFromTextConnector="false" star

WDB-200001 SQL statement 'INSERT INTO NmsSimulation (sInternalName, sXtkschema, iBuiltIn, iIsModel, iModifiedById, tsLastModified, iType, sLabel, iFolderId, iFolderProcessId, iKeepDetails, iStatus, sJobType, iCreatedById, tsCreated, iPriority, iMaxPropositionCount, iGenerateOverlappingStats, mData, iSimulationId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' could not be executed. Param(0)=simuOfferEmpty Param(1)=nms:simulation Param(2)=1 Param(3)=1 Param(4)=1082

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. SQLState: 37000

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'iMaxPropositionCount'. SQLState: S0022

Package 'Offer engine (Interaction)': Saving entities of type 'nms:simulation'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:rights'...

Package 'Offer engine (Interaction)': Saving entities of type 'xtk:folder'...

Package 'Offer engine (Interaction)': Saving entities of type 'nms:typologyRule'...

Generating the navigation tree...

Saving data related to packages...

Updating documents of type 'xtk:navtree'

Updating documents of type 'xtk:form'

Updating documents of type 'xtk:srcSchema'

Writing entities in the database...

Writing schemas in the database...

WDB-200001 SQL statement 'ALTER TABLE NmsPropositionRcp ALTER COLUMN iInteractionId bigint' could not be executed.

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]ALTER TABLE ALTER COLUMN iInteractionId failed because one or more objects access this column. SQLState: 37000

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]The object 'DF__NmsPropos__iInte__32767D0B' is dependent on column 'iInteractionId'. SQLState: 37000

Updating table 'NmsPropositionRcp'

Updating table 'NmsOfferSpace'

Creating table 'NmsOfferSimulationRel'

Creating table 'NmsOfferContext'

Generating SQL for update of the database structure...

Generating schemas...

Enumerating the file entities...

Now the packages are coming in the list under Administration>Configuration>Package Management>Installed Packages

But if I go to the offer Catalog section and click on the folder I am getting error

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'iDeliveryMappingId'. SQLState: S0022

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'iAnonymous'. SQLState: S0022

ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. SQLState: 37000

WDB-200001 SQL statement 'SELECT   O0.sLabel, O0.sName, O0.sTargetSchema, O0.iContentStatus, O0.iEligibilityStatus, O0.iOfferId, IsNull(O0.sLabel, '') + N' (' + IsNull(O0.sName, '') + N')', O0.sCode, O0.iIsModel, case when X2.iLive = 1 then case when O0.iEligibilityStatus = 3 and O0.iContentStatus = 3 then 6 else 5 end else case when O0.iEligibilityStatus IN (1 , 2 , 4) or O0.iContentStatus IN (1 , 2 , 4) then 2 else case when O0.iEligibilityStatus = 3 and O0.iContentStatus = 3 then 7 else case when O0.iEligibilityStatus = 5 and O0.iContentStatus = 5 then 8 else 0 end end end end, O0.iOwnerId, O0.iCategoryId, O0.sDesc, O0.tsStart, O0.tsEnd, O0.tsExpectedEligibility, O0.tsReminderEligibility, O0.tsExpectedContent, O0.tsReminderContent, X1.iEnvId, X1.sLabel, X1.iFolderId, X1.sFullName, X2.sInputSchema, X2.iDeliveryMappingId, X2.iLive, X2.iAnonymous, X2.iFolderId, IsNull(O3.sLabel, '') + N' (' + IsNull(O3.sName, '') + N')', O3.iOperatorId, O3.iType, O0.mData, X2.mData FROM NmsOffer O0 JOIN XtkFolder X1 ON (X1.iFolderId = O0.iCategoryId) JOIN XtkFolder X2 ON (X2.iFolderId = X1.iEnvId) JOIN XtkOperator O3 ON (O3.iOperatorId = O0.iOwnerId) WHERE (O0.iOfferId = ?)' could not be executed.

  Param(0)=108392988

------

Error while loading the detail.

Either a data does not exist, or you do not have access to the data.

[#2 · Accepted solution]

Hi Aalokitoaami ,

Please go into UpdateDB structure module under Tools> Advanced.

Keep clicking Next on the wizard to identify what changes are pending. Review the pending changes and persist them to database.

I'm sure you will find those missing columns there.

If this doesn't work, please restore the database prior to what you had when installing these packages. Post that, start with careful installation of packages.

Regards,
Vipul

[#3]

Thanks Vipul this gave me the direction to resolve the issue.

Using Update DB structure, I was able to find the SQLs that it is running. Then from the error log I took the name of the constraint that is causing the issue. For me it is local installation on SQL Server 2008. I ran this SQL to get the details of the constraint.

select top 5 * from sys.objects where NAME = 'DF__NmsPropos__iInte__32767D0B'

select top 5 * from sys.columns  where object_id = 782625831

I found that it is a default constraint and ran the sql to drop it.

ALTER TABLE NmsPropositionRcp drop constraint DF__NmsPropos__iInte__32767D0B

Then after running the actual alter statement, I added the default

ALTER TABLE [neolane].[NmsPropositionRcp] ADD  DEFAULT ((0)) FOR [iInteractionId]

Repeat this for all the statements which gives error. here is the sequence for recipient tables

ALTER TABLE NmsPropositionRcp drop constraint DF__NmsPropos__iProp__3552E9B6

ALTER TABLE NmsPropositionRcp ALTER COLUMN iPropositionId bigint;

ALTER TABLE [neolane].[NmsPropositionRcp] ADD  DEFAULT ((0)) FOR [iPropositionId]

Similarly for visitor also had to run something similar to this. And

ALTER TABLE NmsPropositionVisitor drop constraint DF__NmsPropos__iInte__3CF40B7E

ALTER TABLE NmsPropositionVisitor ALTER COLUMN iInteractionId bigint;

ALTER TABLE [neolane].[NmsPropositionVisitor] ADD  DEFAULT ((0)) FOR [iInteractionId]