The following SQL script can be run on any 10.6 database to validate that some specific, common data problems that would interfere with a smooth upgrade to 15.x are not present. If any issues are present, they are displayed. If you run this from SQL Query Analyzer or SQL Server Management Studio, set the "results to text" option before running this script, for the best display of the results.
-------------------------------------------------------------------
-- Preparing to upgrade iMIS 10.6 to iMIS 15.x, Part I
--
-- Check for non-unique user logins
--
-- This script will check for any duplicate user ids or web logins
-- that would cause the database upgrade process to fail. This is
-- a simple 'sanity' check. Run this script and if any user ids or
-- web logins are identified as having duplicate entries, you will
-- need to correct those duplicates (provide unique logins for each
-- user) prior to upgrading to 15.x
--
-- Hint: If using Query Analyser or SQL Server Management Studio,
-- use the "Results to Text" option to view the results.
-------------------------------------------------------------------
IF EXISTS ( SELECT [UserId], COUNT([UserId]) AS [Count]
FROM [dbo].[Users]
WHERE [UserId] <> ''
GROUP BY [UserId]
HAVING COUNT([UserId]) > 1 )
BEGIN
PRINT 'Error: duplicate User IDs exist in the Users table:'
SELECT [UserId], COUNT([UserId]) AS [Count]
FROM [dbo].[Users]
WHERE [UserId] <> ''
GROUP BY [UserId]
HAVING COUNT([UserId]) > 1
END
ELSE
PRINT 'The User IDs in the Users table are all unique.'
IF EXISTS ( SELECT [WEB_LOGIN], COUNT([WEB_LOGIN]) AS [Count]
FROM [dbo].[Name_Security]
WHERE [WEB_LOGIN] <> ''
GROUP BY [WEB_LOGIN]
HAVING COUNT([WEB_LOGIN]) > 1 )
BEGIN
PRINT 'Error: duplicate Web Logins exist in the Name_Security table:'
SELECT [WEB_LOGIN], COUNT([WEB_LOGIN]) AS [Count]
FROM [dbo].[Name_Security]
WHERE [WEB_LOGIN] <> ''
GROUP BY [WEB_LOGIN]
HAVING COUNT([WEB_LOGIN]) > 1
END
ELSE
PRINT 'The Web Logins in the Name_Security table are all unique.'
IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserMain' )
BEGIN
IF EXISTS ( SELECT [UserId], COUNT([UserId]) AS [Count]
FROM [dbo].[UserMain]
WHERE [UserId] <> ''
GROUP BY [UserId]
HAVING COUNT([UserId]) > 1 )
BEGIN
PRINT 'Error: duplicate User IDs exist in the UserMain table:'
SELECT [UserId], COUNT([UserId]) AS [Count]
FROM [dbo].[UserMain]
WHERE [UserId] <> ''
GROUP BY [UserId]
HAVING COUNT([UserId]) > 1
END
ELSE
PRINT 'The User IDs in the UserMain table are all unique.'
END
If everything is fine, the output should look like this:
The User IDs in the Users table are all unique. The Web Logins in the Name_Security table are all unique. The User IDs in the UserMain table are all unique.
If there are any problems, the specific issues will be displayed.
Good stuff, need more like this
Thanks for putting this out here, Paul. I know there have to be more issues like this. Hopefully this can become a "sticky" topic where everyone can contribute their own discoveries.
I know of one that can affect the upgrade: the existence of a table named "Demo". If you have a table by this name, the upgrade will crash, as it first thinks the table already exists (so it won't try to create it), then references the fields within it (which likely don't match what iMIS expects). Tech Support has a semi-supported script which will remove references to Demo in all the releases and patches from 15.0 until the version where it was no longer included.
We discovered this for one client who used "Demo" as their primary custom demographics table. The number of references to the table under its old name (views, procedures, reports) made it prohibitive to simply rename the table.
The "Demo" table was mistakenly included in the iMIS release, but isn't needed by iMIS. I suspect it was created for some kind of testing, and nobody noticed it to remove it before the final build. There's one other table in this situation, but I don't recall the name of it, and I don't know whether it's included in the patch.
Suggested addition to the script:
IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Demo' ) PRINT 'Error: Database includes a table named Demo. Obtain patch from Tech Support before attempting upgrade.' ELSE PRINT 'No "Demo" table found, OK' END-- Bruce