Page 1 of 5

JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 1:45 pm
by Luehman
Any one got an idea why we may be getting the following error whilst upgrading 6.5.13 to 7.2.1?

See attached spot where the upgrade script errors and the details of the error.

We don't use JobParts so not sure why this would be occurring. Previously we have successfully upgraded for testing so not sure why we are getting the error (trying to GoLive).

Side Note: we watched as the scripts are running and noticed that they seem to remove the dbo object SM_JobParts

Error:
sm_jobparts error.png
sm_jobparts error.png (3.97 KiB) Viewed 2202 times


Details of error:
sm_jobparts error details.png

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 1:53 pm
by Mike.Sheen
Interesting.

I suspect you have a trigger on the table SM_Parts which is trying to involve a table SM_JobParts which no longer exists (I think we effectively renamed it by creating a new one the dropping the old one).

Try disabling the trigger - see this post here on how to do that for ALL triggers - and then upgrading again - it'll ask you if you want to resume the upgrade if you try it again.

Edit: I should explain why I think it is a trigger causing the problem.

The script it is failing on is as follows:
Code: Select all
UPDATE SM_Parts SET SO_Main_RecID = (SELECT TOP 1 InvoiceID FROM SO_Main WHERE InvoiceNo = ProcessID)
ALTER TABLE SM_Parts DROP COLUMN ProcessID

That quite clearly does not involve a table named SM_JobParts - just SM_Parts and SO_Main. Experience tells me when an insert, update or delete on any table results in an error relating to some object not involved in the query, then a trigger must be involved.

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 1:58 pm
by Luehman
Thanks Mike

If the upgrade does go through after disabling "all triggers" would I then need to re-enabled them after the upgrade?

Thanks

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:01 pm
by Scott.Pearce
Follow the thread Mike gave you - It contains a script for disabling, and a script for re-enabling. Having said that there should be *NO* standard Jiwa triggers after upgrading anyway.

Run this script to list the triggers:

Code: Select all
SELECT object_name(parent_id) [TableName], name [TriggerName], CASE is_disabled WHEN 0 THEN 1 ELSE 0 END [Enabled] FROM sys.triggers


and post what it finds here.

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:03 pm
by Mike.Sheen
Luehman wrote:Thanks Mike

If the upgrade does go through after disabling "all triggers" would I then need to re-enabled them after the upgrade?

Thanks


Depends on what they do!

We don't have any triggers by default, so it won't affect the core product. These triggers might have been created as a customisation and in which case you really need to ask the person / organisation that created them what they do and what the impact is of not re-enabling them - AND - what the impact would be of them being disabled during upgrade.

Typically your Jiwa solution provider would know, or be able to find out, the answers to these questions - as they should have an understanding of all the customisations which have gone on.

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:04 pm
by Luehman
See below:

triggers.png
triggers.png (5.62 KiB) Viewed 2195 times


Do I go ahead and disable all triggers or just the SM_JobParts trigger? If just the SM_JobParts trigger how would I do this rather than all?

Re: JIWA 6.5.13 to 7.2.1  Topic is solved

PostPosted: Wed Mar 18, 2020 2:05 pm
by Scott.Pearce
Disable the lot and leave them disabled.

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:18 pm
by Luehman
Great, thanks....

Something odd is just happening....I have started a new blank dB, restored a backup of 6.5.13 in to it, ensured I have compatibility level is at 100 and now (all of a sudden) I am getting the following error:

On "Read upgrade scripts from the Access database":

System.InvalidOperationException: The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
at System.Data.OleDb.ColumnBinding.Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at JiwaFinancials.Jiwa.JiwaApplication.DatabaseManager.Main.ReadUpgradeScriptsAccessDatabase(Object sender, ExecuteActionEventArguments e)

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:21 pm
by Scott.Pearce
Try again. Do you get the same error?

Is the JiwaDatabaseScripts.mdb in the Jiwa installation folder locked by something else (i.e. do you have it open in MS Access)?

Re: JIWA 6.5.13 to 7.2.1

PostPosted: Wed Mar 18, 2020 2:23 pm
by Luehman
We don't have access open etc.....

We had SQL Studio open however no queries etc. open....will try again.