-
タイトル
Unable to upgrade Change Auditor with an account that is not directly a db owner in SQL Server -
説明
During an upgrade, it is possible to encounter an error if the account that you are using to connect to the CA database in SQL is not directly linked to the [dbo] account. i.e. If you have the current account as a db_owner, but not actually the dbo linked account.
---Begin Log text---
2023-11-14 13:18:41.073 [9][INFO][ChangeAuditor.Sys.DbInitializer.InitializeImpl(0)] Executing: script ChangeAuditor-Procedures.sql
2023-11-14 13:18:45.293 [9][ERROR][Quest.Data.SqlHelpers.ExecuteText(0)] Error There is already an object named 'usp_DataIntegrationGetFirstAuditEventRecord' in the database. on line 44785 executing SQL:
CREATE PROCEDURE usp_DataIntegrationGetFirstAuditEventRecord
(
@StartDate DateTimeOffset(7)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET DATEFORMAT ymd;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
DECLARE @MinRecord as bigint
DECLARE @MaxRecord as bigint
SELECT @MinRecord = MIN(RecordId), @MaxRecord = Max(RecordId)
FROM AuditEvent_IX_TimeBatched
WHERE TimeBatched >= @StartDate
IF (@MinRecord IS NULL AND EXISTS (SELECT 1 FROM AuditEvent_IX_TimeBatched WHERE TimeBatched < @StartDate))
BEGIN
SELECT @MinRecord = @MaxRecord + 1
END
SELECT ISNULL(@MinRecord, 0) as RecordId
END;
2023-11-14 13:18:45.293 [9][INFO][Quest.Data.ExceptionSql.OnExceptionSqlEvent(0)] SQL error(2714): There is already an object named 'usp_DataIntegrationGetFirstAuditEventRecord' in the database.;
command: ChangeAuditor-Procedures.sql
2023-11-14 13:18:45.293 [9][WARN][ChangeAuditor.Coordinator.SqlReconnectTask.ExceptionSql_ExceptionSqlEvent(0)] Attempting to reconnect to the database (1).
2023-11-14 13:18:45.310 [9][ERROR][ChangeAuditor.Coordinator.Service.Initialize(0)] Database initialization not successful on ChangeAuditor_BAD_74_UPGRADE.
The ConnectionString property has not been initialized.
2023-11-14 13:18:45.310 [9][INFO][ChangeAuditor.Service.Service.OnStop(0)] **** Service Stop
2023-11-14 13:18:45.310 [9][INFO][Quest.ChangeAuditor.WebServices.EventWebhookService.OnServiceStopping(0)] Service stopping
2023-11-14 13:18:45.325 [9][INFO][ChangeAuditor.Service.Service.CurrentDomainUnhandledException(0)] Unhandled exception caught in Service
2023-11-14 13:18:45.325 [9][ERROR][ChangeAuditor.Service.Service.CurrentDomainUnhandledException(0)] Object reference not set to an instance of an object.
---End Log Text--- -
原因
Product Defect 181000: Sections of ChangeAuditor database SQL scripts do not explicitly specify the DBO schema which can cause upgrades to fail. -
対策
WORKAROUND:
A) Stop the upgrade and change the account that you’re connecting to the database with, to the account that the [dbo] account object is linked to.
B) Stop the upgrade and change the account that the [dbo] account object is linked to, to match the account that you are currently using to connect to the CA Database.
Once the alterations have been completed, you should be able to start the coordinator again and let the upgrade finish. -
Defect ID
181000