JIWA 6.5.13 to 7.2.1

[Luehman]

Thanks....we must have had a SQL Query open in the background somewhere.....executing upgrade scripts now with all triggers off...wish me luck

[Scott.Pearce]

Good luck.

Coming from v6 I would expect some upgrade errors - all easily dealt with though.

[Mike.Sheen]

...wish me luck

Luck shouldn't be a factor... You can always engage us to take your V6 database, upgrade it and hand it back...

[Luehman]

Sorry.....got another error:

"Execute upgrade script [31524] 7.0.12.3080 - Arithmetic overflow error converting float to date type numeric"

Error
Jiwa Financials has uncounted the following error:

Arithmetic overflow error converting float to data type numeric. The statement has been terminated

Details
System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting float to data type numeric.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)
at JiwaFinancials.Jiwa.JiwaApplication.DatabaseManager.Main.ExecuteScript(String Script, String DatabaseNameToExecuteAgainst)
at JiwaFinancials.Jiwa.JiwaApplication.DatabaseManager.UpgradeScript.ExecuteMe(Object sender, ExecuteActionEventArguments e)
ClientConnectionId:e3c95c78-fa19-4e30-94a7-a775d9d7c0bc
Error Number:8115,State:6,Class:16

[Luehman]

Is it possible the above error is occurring due to our data having an email address (i.e. [email protected]) in Address 1 field of CR_Main?

[Mike.Sheen]

Ok, so that script I can see in the JiwaDatabaseScripts.mdb is trying to do the following:

Code: Select all

ALTER TABLE IN_AssemblyLineDetails ALTER COLUMN Cost DECIMAL(19,6) NOT NULL

So, I would say you have some data in the IN_AssemblyLineDetails.Cost field which can't be converted to Decimal(19,6) because it is too big or too small.

You'll need to identify and adjust those values - I suspect the values of the problem rows are in error anyway.

[Mike.Sheen]

Is it possible the above error is occurring due to our data having an email address (i.e. [email protected]) in Address 1 field of CR_Main?

Nope, wrong table, wrong field!

[Luehman]

Ok, all upgraded and looking good however when I go to login I get the following error:

LoginError.png (9.3 KiB) Viewed 2099 times

I have run the following script as I thought I would need this in order to get logging reset however it's still not working:

/******************************************************************************
This part of the script sets the SQL passwords for the three Jiwa SQL Logins
on the SQL Server Instance to:
JiwaLogin: JiwaApplicationLogin123
JiwaUser: JiwaFinancials123
JiwaReports: JiwaFinancials123
*******************************************************************************/
ALTER LOGIN JiwaLogin WITH Password = 'JiwaApplicationLogin123'
ALTER LOGIN JiwaUser WITH Password = 'JiwaFinancials123'
ALTER LOGIN JiwaReports WITH Password = 'JiwaFinancials123'
GO

/*******************************************************************************
This part of the script sets the SQL passwords in the HR_Staff table for ALL
users in Jiwa
********************************************************************************/
-- Run this part of the script over the Jiwa 7 database
UPDATE HR_Staff
SET SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
SQLLoginLastSavedDateTime = '2014-05-28 11:20:48.000',
ReportsLoginLastSavedDateTime = '2014-05-28 11:20:48.000'


/*******************************************************************************
This part of the script sets the password for the Jiwa Admin user to 'password'
********************************************************************************/
-- Run this part of the script over the Jiwa 7 database
UPDATE HR_Staff SET Password = '60A889064BAD474508723B9200184D09',
PasswordLastChangedDateTime = '2014-05-28 11:20:48.000',
LastSavedDateTime = '2014-05-28 11:20:48.000',
SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C'
WHERE UserName = 'Admin'
GO

GRANT_ALL_USER_TABLES


/*******************************************************************************
This part of the script sets the password for the Jiwa Admin user to 'password'
Manually Run Query on JiwaLogin, JiwaReports and JiwaUser.

E.g.:
alter user JiwaLogin WITH login=JiwaLogin
go

********************************************************************************/

[Mike.Sheen]

GRANT_ALL_USER_TABLES is now obsolete.

Run these two instead:

Code: Select all

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO

And at some point you should configure Jiwa to use your own secret SQL user name and password.

[Scott.Pearce]

Hang on. Is the upgraded database on the same server as the old v6 (live) database?