Version 4.3 Installation Failed Issue and Missing SQL Server Functions/Storage Procedures

[kyangcoder]

Version 4.3 installation (new system /.NET 4.7.2 Framework installation verified/ SQL super user created.) Failed. Tried on multiple OS and SQL server versions.

1. Failed at database initialization stage
   
   In looking at the source code, the failed point at the MOE.Common/Migrations/202103102112389_SplitFailAggregationUpdate.cs
   when it try to change the "dbo.ApproachPcDAggregation" table's "TotalDelay" column definition and it is hold by others.

Disabling this and publishing my own web site will pass the failing point and finish the installation.

2. The compiling web site failed to copy the "Entertiyframework.SqlServer.dll" lib to the \bin directory of the website, you can manually copy it from the executables package yourself
   

3. The installation manual 6.3 stated that several Functions and Storage Procedures and one agent job will showing up. Unfortunately, They were not showing up after my installation, where I can find them or even manually install them???

[jamesbley-rhythm]

The issue is the constraint on the TotalDelay column which sets the Default value to 0. I modified the code in the file referenced above to drop that constraint, modify the TotalDelay column, then add the constraint back. On the Down method, I did the same.

[kyangcoder]

@jamesbley-rhythm just curious, with your fixing, did you also get the database associated storage procedures/functions installed with the database (as the installation manual mentioned in section 6.3). Them were seems missing from my installation and unfortunately I could not figure out where they were. Do you have any tips on that?

[jamesbley-rhythm]

@kyangcoder I haven't proceeded any further. Let me get through the rest of the installation manual and I will provide a response back to you on that.

[mstanley-ave]

1. An additional executable, ConvertDBForHistoricalConfiguration, was supposed to be included in the release, but I guess we missed it. It's purpose is to help the update the database to the newest release. We're discussing how to fix this, but for now you can either do what @jamesbley-rhythm did and drop the constraint yourself or if you feel comfortable download the project zip yourself and run ConvertDBForHistoricalConfiguration yourself after configuring the connection strings.
2. Maybe I'm wrong or your setup is different, but I believe this file should already bundled within the SPM executable and you shouldn't have to extract anything from the folder before moving it to the server.
3. From what I understand the stored procedures used to be there to help update the database but were made obsolete a few years ago and were removed. I'll mention this to the team and we'll have the references to them in the installation manual updated to mention that.

[ian-cameron]

Can confirm. Building the and running ConvertDBForHistoricalConfigurations.exe from source fixes it... after editing the connection string in the .config file.

I concur, you should add ConvertDBForHistoricalConfigurations to the ATSPM Executables for the release.

[AndrewMC6]

Was hoping to see if there was any update in the last USDOT call but that was cancelled.... any update on this being pushed into the 'releases' tab on our github home page? I don't have the means to compile the source. Unless I'm missing something, it looks like you folks are making the change to source, and recompiling, there's nothing that can be done to the release files?

[avetraffic]

@AndrewMC6, we've been putting together a new release, 4.3.1, that should hopefully be up in the next week or so. It will include the ConvertDBForHistoricalConfiguration executable as well as some general bug fixes. It's currently the version we have on UDOT's production server so we're just confirming everything looks good first.

[avetraffic]

4.3.1 has been released and includes the ConvertDBForHistoricalConfigurations executable. The Installation Manual has also been updated.

[AndrewMC6]

The "TotalDelay" issue is still present in this release, on a new installation. I'm going to try the convertDB script now to see if that clears anything up

[avetraffic]

The TotalDelay error is due to differences between the database and the project, and not an issue with the release itself. The purpose of the convertDB executable is to get your database up to date and matching the latest build, so let me know if that works for you.

[AndrewMC6]

The TotalDelay error is due to differences between the database and the project, and not an issue with the release itself. The purpose of the convertDB executable is to get your database up to date and matching the latest build, so let me know if that works for you.

Yes this did work for me. Might be worth a note somewhere this is a necessary step for a new installation as well. Thanks for the reply! Install is now up and running, just upgrading SQL management studio to potentially resolve nothing showing up in the "Edit top 200 rows" for the regions.

[avetraffic]

That's good to hear. The new version of the installation manual that we just uploaded includes how to update your database using the DbConvert executable, but we may be more emphatic about it in the future as it seems to be an issue for quite a few people. It sounds like your regions table is missing data though?

[AndrewMC6]

Apparently according to stackoverflow I was on an older version of SSMS that didn't read sql server 2020 well. I've upgraded and now working on adding the regions I need now.