System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'dbo.LoginSelectBySID'.
WARN ECS - Calculate email address failed. Ignore it and continue. Message is: Could not find stored procedure 'dbo.LoginSelect'.
System.Data.SqlClient.SqlException: Cannot drop the procedure 'dbo.LoginSelect', because it does not exist or you do not have permission.
The affected stored procedure (SP) no longer exists, but the database upgrade process expected it to exist, possibly due to records/entries in the master database.
With the following check:
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'LoginSelect' AND type = 'P')
DROP PROCEDURE dbo.LoginSelect
GO
**Note: Before proceeding with the below steps, be sure to stop the Archive Manager Services on the machine and set them to disabled.
**Note: Ensure you have a good SQL Backup of your Archive Manager database before proceeding.
1. Browse to ..\Archive Manager\Prerequisites\DatabaseInstaller and verify the version of Quest.AM.Setup.DatabaseInstaller.exe by looking into the properties.
2. In SQL Server Management Studio (SSMS), delete all entries of the failed upgrade version from dbo.Version (Example - For AM 5.5, delete entries with Version 5.5.0.0, 5.5.0.1, 5.5.0.2, 5.5.0.3).
3. In Configuration Console, look into the Connection String for the SQL Server name and Database name.
4. Run a CMD using the installation account and run the following:
Quest.AM.Setup.DatabaseInstaller.exe -u -server=AMSQL-10 -database=ArchiveManager
Note: Make sure to specify -u for database upgrade. For details, run Quest.AM.Setup.DatabaseInstaller.exe -?
5. Monitor the run to see if the database upgrade completes successfully. If so, all SP should be restored and dbo.Version updated accordingly.
A Full list of Stored Procedures the database upgrade is looking for is:
7. Refer to the unencrypted database upgrade script for the respective AM version.
8. Search for the SP, copy the CREATE PROCEDURE section of upgrade script and run it as a New Query against the AM database.
Note: StoredProceduresUpgrade.sql has been attached to this Knowledgebase article. Please ensure the Script is ran against your ArchiveManager database or create each stored procedure manually as seen below.
Example:
CREATE PROCEDURE LoginSelect
@LoginID int
...
SET NOCOUNT OFF
GO
9. Run the attached Script or Repeat Step 4 - 8 until all Stored Procedures referenced in the error during the DatabaseInstaller upgrade are created and the database gets upgraded successfully.
10. Start the Archive Manager Services and access your ArchiveManager website.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center