Curious about these statements:

"Normally these are the correct steps but there is a flaw in the "prepare imis database" that should drops and recreates the IMISUSER_DBName and MANAGER_DBName when run in SQL 2005.

The DBUpgrader did not create those if they didn't exist and seems like it should."

I've just tested these functions on SQL2005 (and in fact have been using the scripts for years on 2005) and haven't seen or found any issues.  Can you point out the "flaw" in the Prepare iMIS Database that is failing to work in some situations, and be clear about what those situations are?

Any time a database is restored under a new name, "Prepare iMIS Database" should be run to create the correct logins so that iMIS can function.  Are you saying there is some situation where this doesn't work?

Any time a database is restored under a new name, "Prepare iMIS Database" should be run to create the correct logins so that iMIS can function.  

Are you saying there is some situation where this doesn't work? Yes SQL 2000 to SQL 2005 restore and DB name change.

To re-create the exact error reported in this thread we ran the 15.0.3. to 15.1.2 DB Upgrade tools on the SQL 2000 iMIS DB.

When the iMIS DB is already 15.1.2 on SQL2000 and restored to a SQL 2005 server we replicate the error reported.

The "Prepare iMIS Database" was run and did not create the correct logins so that iMIS can function.  

Bruce M Walker

BSCI

Chicago IL

The prefered method would be to move the database first, then upgrade.

If you upgrade first and then moved the database, did you set the proper database compatibility level?  Run this, and try Prepare iMIS Database again:

EXEC sp_dbcmptlevel dbname, 90

...where 'dbname' is the name of your database

Is there any error reported when "Prepare iMIS Databse" is run and doesn't create the correct users?  I've not personally been able to duplicate this or find a case where the proper users aren't created.

Other things to try: 

In the imis.net\IMIS15DBUpgrade\ScriptFiles\IMIS156DBUpgrade folder, open the "CreateSqlLogins.sql" file in Microsoft SQL Server Manager, and run it.  Does it run correctly?  Any errors?  Do you see the user name now?  

If you're still having issues, try running "FixSqlLogins.sql" in that same folder.  You will see some errors.  Then run CreateSqlLogins.sql again.  Are the correct user names there now?

Off the top of my head, I cannot explain what you're seeing unless it has to do wtih the database compatibility level (and even that doesn't make sense to me immediately).  I'll see if I can duplicate this issue following the steps you outlined... but that will take some time.  In the meantime, let me know if anything I've posted above works.

Yes I upgrade first and then moved the database, and we did set the proper database compatibility level.

Still the error when the SQL users and schemas are not created.

Just refreshed with DB that had indexes sucessfully rebuilt in SQL 2000 andRunning the DB Upgrade tool on SQL 2005 with (90) compatibility.

Just exited the DB Upgrade tool and the users were not created.

Just ran the script and attempting to logon to iMIS desktop.

That worked. Ready to go live, thanks

Bruce M Walker

BSCI

Chicago IL

I'm still curious about what is going on, because all "Prepare Database" does is run that "CreateSqlLogins.sql" script... are you saying that after upgrade, and moving the database, and then running DBRepair and "Prepare Database", you couldn't log in, but then after manually running "CreateSqlLogins.sql", you could?

I'd like to get a clearer idea of exactly what you did and didn't do, when things didnt' work, and then did, so I can investigate the issue further.

Thanks!

 To re-create the exact error reported in this thread we ran the 15.0.3. to 15.1.2 DB Upgrade tools on the SQL 2000 iMIS DB.

When the iMIS DB is already 15.1.2 on SQL2000 and restored to a SQL 2005 server we replicate the error reported.

To fix the error reported.

When the iMIS DB is already 15.1.2 on SQL2000 and restored to a SQL 2005 server we then ran the DB upgrade tool against the iMIS 15.1.2.**** SQL 2005 DB.

The DB must be restored in the SQL (90) compatibility mode.

After the DB upgrade tool is run against the iMIS 15.1.2.**** SQL 2005 DB we then run the CreateSqlLogins.sql.

iMIS functions as expected.

Bruce M Walker

BSCI

Chicago IL

Okay.  Sorry if I'm being pedantic but I'm still not clear about what went wrong.

If you upgrade to 15.1.2 on SQL 2000 and then backup/restore the database to a SQL 2005 server and try to log in, it will fail.  This is *expected* behavoir, and as such, doesn't concern me.  It is why we suggest moving the database first, and THEN upgrading.  ANY time a database is "moved" or "renamed", you need to run "Prepare Database" against the database before iMIS will function.

The part I'm having difficulty with is that at that point, you should be able to just set the database compatibility level via SQL Server Management Studio,  run DBRepair and connect to the new database location on SQL 2005, and then click "Prepare Database", and you should then be able to log in.  All this process does is run the "CreateSqlLogins.sql" script against the database.  There should be no need to re-run the upgrade tool against the database (it's already upgraded).

From what you've said above, this did not work, and this is the part that concerns me.  That running Prepare Database against the database did not create the SQL logins... I have never seen this, and looking at the code, cannot see how that can happen.  Are you certain DBRepair was pointed at the correct database, in its new location?

Am I being clear?  Am I missing something?

Paul thanks for your "ATTENTION TO DETAIL" please see my responses in line.

Hopefully enhancements will prevent others from my experience.

We upgraded to 15.1.2 on SQL 2000 and then backup/restore the database to a SQL 2005 server and try to log in, it will fail.  Yes

This is *expected* behavoir, and as such, doesn't concern me.   Me either.

It is why we suggest moving the database first, and THEN upgrading. That was not an option. 

 ANY time a database is "moved" or "renamed", you need to run "Prepare Database" against the database before iMIS will function. That was expected except the logon failed because the Login IMISUSER_DBName and MANAGER_DBName were not created.

The part I'm having difficulty with is that at that point, you should be able to just set the database compatibility level via SQL Server Management Studio,  run DBRepair and connect to the new database location on SQL 2005, and then click "Prepare Database", and you should then be able to log in.  I did that and was not able to logon because the Login IMISUSER_DBName and MANAGER_DBName were not created.

All this process does is run the "CreateSqlLogins.sql" script against the database.  There should be no need to re-run the upgrade tool against the database (it's already upgraded).

That was my thought as well but not the way it played out for my customer.

From what you've said above, this did not work, and this is the part that concerns me. 

That running Prepare Database against the database did not create the SQL logins... This is correct

I have never seen this, and looking at the code, cannot see how that can happen.  Just the facts as I saw them and the Logins were not created.

Are you certain DBRepair was pointed at the correct database, in its new location? Yes

Am I being clear? Yes  Am I missing something? No

The solution

Bruce M Walker

BSCI

Chicago IL

To fix the error reported.

When the iMIS DB is already 15.1.2 on SQL2000 and restored to a SQL 2005 server we then ran the DB upgrade tool against the iMIS 15.1.2.**** SQL 2005 DB.

The DB must be restored in the SQL (90) compatibility mode.

After the DB upgrade tool is run against the iMIS 15.1.2.**** SQL 2005 DB we then run the CreateSqlLogins.sql.

iMIS functions as expected.

Any deviation did not produce the desired results.

And there were NO error messages or anything in the logs?

Can you look at the DBRepair logs and see if anything was printed when "Prepare Database" was clicked, but the correct SQL Users were NOT created?  I'm really scratching my head over this one.  I cannot duplicate it, and cannot imagine how it can be that clicking "Prepare Database" does not create the SQL Users.  This is very puzzling.

I am also very puzzled by teh "run the DBupgarde tool" again step, as it effectively does nothing.  Are you saying that's a crucial step in getting things to work?  Did you try those steps minus the re-run of the DBUpgrade?  Because upgrading a 15.1.2 database to 15.1.2 doesn't do anything... all the scripts are just skipped as they notice the database is already upgraded.

Thanks for your time and patience.  I'm glad you're functioning again right now.  I'll look into this further as I get the chance.

Puzzling was not a word in my vocabulary by Monday morning, however your response early Monday made me very greatful for your support.

I am also very puzzled by teh "run the DBupgarde tool" again step, as it effectively does nothing.  

Well it did something and was definately needed as the "Prepare Database" did not produce any errors or IMISUSER_DBName and MANAGER_DBName

Are you saying that's a crucial step in getting things to work?  Yes!

Did you try those steps minus the re-run of the DBUpgrade?  Yes and all failed too create the IMISUSER_DBName and MANAGER_DBName.

Because upgrading a 15.1.2 database to 15.1.2 doesn't do anything... all the scripts are just skipped as they notice the database is already upgraded.

Yeah puzzling!

Bruce M Walker

BSCI

Chicago IL