VMware Cloud Community
runclear
Expert
Expert
Jump to solution

vCenter upgrade issues to 4.0

hey guys, so im trying to upgrade our virtual center server 2.5 to 4.0 and im having some issues during the sql portion....

"exception thrown while executing sql script"

I figure this is due to permissions on the database, however ive checked those and the SQL account that we use to access the

database has the appropriate permissions per the KB article i found...

we have a 2005 SQL server, so

MSDB = "useraccount" = db_owner

VMWARE = "useraccount" = "db_owner

am i missing anything... ?

-


-------------------- What the f* is the cloud?!
Reply
0 Kudos
1 Solution

Accepted Solutions
admin
Immortal
Immortal
Jump to solution

vcdatabaseupgrade.log will be present under %temp% folder. Could you please check that once?

-Sandeep

View solution in original post

Reply
0 Kudos
14 Replies
Troy_Clavell
Immortal
Immortal
Jump to solution

I found this KB article, which may be useful

http://kb.vmware.com/kb/1009950

runclear
Expert
Expert
Jump to solution

thanks - i have a feeling its not getting that far though as the installer never creates a "vcdatabaseupgrade.log" file anywhere on the vc server.

-


http://www.rack42.com

-------------------- What the f* is the cloud?!
Reply
0 Kudos
admin
Immortal
Immortal
Jump to solution

Hi,

Could you check if you are able to connect to the database using that user manually and run some queries on vCenter database?

Also upload the installation logs if possible

-Sandeep

Reply
0 Kudos
vmthunder
Contributor
Contributor
Jump to solution

I get the same issue while authenticating to the SQL server 2005 server using the sa account. No db upgrade logs are being written and the one KB article posted doesn't apply since i'm going from 2.5 u4 to 4.0.

After the SQL Conversion Errors I can restart the Upgrade and it will complete, but I get the errors below.

My errors look like:

A database error occurred: "ODBC error: (42S22) - [SQL Native Client][SQL Server]Invalid column name 'increment_by'." is returned when executing SQL statement "select increment_by, id from vpx_sequence where name = ?".

and

The VMware VirtualCenter Server service terminated with service-specific error 2 (0x2).

I figure this is caused by the SQL Exception Error during the DB conversion process.

Has anyone found a fix for this?

Reply
0 Kudos
runclear
Expert
Expert
Jump to solution

7/8/2009 4:04:31 PM Error: Failed to execute SQL procedure. Got exception: ERROR 08004 Microsoft SQL Native Client SQL Server The server principal "SQLUSER" is not able to access the database "DDLEventsLog" under the current security context.

I dug through my emails and found this tid bit of info - I was looking in the wrong spot for the vc upgrade log, well once i located that I found this in the log... once the "SQLUSER" had rights to that db it was looking for, my upgrade was successfull.-----

-------------------- What the f* is the cloud?!
Reply
0 Kudos
vmthunder
Contributor
Contributor
Jump to solution

I'm Authing as the "SA" account though. It is not a SQL User permission failure. Searched HD for the vcdatabaseupgrade.log file and it does not exist.

Reply
0 Kudos
admin
Immortal
Immortal
Jump to solution

vcdatabaseupgrade.log will be present under %temp% folder. Could you please check that once?

-Sandeep

Reply
0 Kudos
cgshamilton
Contributor
Contributor
Jump to solution

I have exactly the same error (VCDatabaseUpgrade.log logfile attached below). The upgrade gives me an SQL error.

When I then try to launch VC service, it will not start.

When I then rerun the installer, it will install correctly (but does not ask to upgrade SQL anymore)

Trying to launch VC service, it fails again

Only option was to remove 4.0, restore 2.5 DB and reinstall VC 2.5

User (sa) has DBO rights to databases

Logfile shows this

Error while upgrading: ERROR [SQL Native Client][SQL Server]'FK_VPX_EVENT_ARG_REF_EVENT' is not a constraint.

ERROR [SQL Native Client][SQL Server]Could not drop constraint. See previous errors.

Any help would be appreciated

Reply
0 Kudos
vmthunder
Contributor
Contributor
Jump to solution

my issue is resolved. I was replicating the DB to a different server for DR purposes. I had to remove the replication, upgrade the VC install and then rebuild the replication.

Reply
0 Kudos
Rix
Contributor
Contributor
Jump to solution

Hi

We too had the same problem with our upgrade and when looking the the VCDatabaseUpgrade log below was the cause to the SQL script throwing an exception.

alter table vpx_event_arg drop constraint FK_VPX_EVENT_ARG_REF_EVENT

Error: Failed to execute SQL procedure. Got exception: ERROR [SQL Native Client][SQL Server]'FK_VPX_EVENT_ARG_REF_EVENT' is not a constraint.

ERROR [SQL Native Client][SQL Server]Could not drop constraint. See previous errors.

Error: Failed to execute command: alter table vpx_event_arg drop constraint FK_VPX_EVENT_ARG_REF_EVENT

Error while upgrading: ERROR [SQL Native Client][SQL Server]'FK_VPX_EVENT_ARG_REF_EVENT' is not a constraint.

ERROR [SQL Native Client][SQL Server]Could not drop constraint. See previous errors.

Info: Exiting Upgrade Wizard

We loged a call with VM support, below is the email reply with what they recommended and worked. After running the below sql script re-run the upgrade.

-


Hello,

Thank you for your Support Request.

As per the case description I understand we are not able to complete the database upgrade from VCDB 2.5 to VCDVB 4.0. And while in the process of upgrading the database we are receiving Database exceptions. To fix this issue lets restore a fresh copy of your production DB instance and run the below query on that instance and continue with Upgrade.

SQL Query:

ALTER TABLE dbo.[VPX_EVENT_ARG|http://communities.vmware.com/community-document-picker.jspa?communityID=&subject=VPX_EVENT_ARG] WITH CHECK ADD CONSTRAINT FK_VPX_EVENT_ARG_REF_EVENT FOREIGN KEY(EVENT_ID)

REFERENCES dbo.[VPX_EVENT|http://communities.vmware.com/community-document-picker.jspa?communityID=&subject=VPX_EVENT] (EVENT_ID)

ON DELETE CASCADE

Technical Support Engineer

VMware Global Support Services

-


Hope this helps

Reply
0 Kudos
TonyJK
Enthusiast
Enthusiast
Jump to solution

After fixing the constraint problem, we encounter another error during the database upgrade:

Upgrade-v5-to-v6\T-SQL\job_schedule1_mssql.sql

BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT DECLARE @VARDBNAME VARCHAR(128) DECLARE @JobNAME VARCHAR(128) SELECT @ReturnCode = 0 IF ((select serverproperty('edition')) = 'Express Edition') BEGIN COMMIT TRANSACTION GOTO EndSave END select @VARDBNAME = db_name(dbid) from master..sysprocesses where spid=@@SPID set @JobNAME = 'Past Day stats rollup'+@VARDBNAME IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup' SELECT distinct @JobID = j.job_id FROM MSDB.dbo.sysjobs j , MSDB.dbo.sysjobsteps t where j.name = N'Past Day stats rollup' and j.job_id=t.job_id and t.database_name=db_name() IF (@JobID IS NOT NULL) BEGIN EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup' SELECT @JobID = NULL END SELECT distinct @JobID = j.job_id FROM MSDB.dbo.sysjobs j , MSDB.dbo.sysjobsteps t where j.name = @JobNAME and j.job_id=t.job_id and t.database_name=db_name() IF (@JobID IS NOT NULL) BEGIN EXECUTE msdb.dbo.sp_delete_job @job_name = @JobNAME SELECT @JobID = NULL END BEGIN EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = @JobNAME, @description = N'This job is to roll up 5 min stats and should run every 30 mins', @category_name = N'Stats Rollup', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Past day stats rollup', @command = N'EXECUTE stats_rollup1_proc', @database_name = @VARDBNAME, @server = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Delete past day rolled up records', @command = N'exec purge_stat1_proc', @database_name = @VARDBNAME, @server = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'30 min schedule', @enabled = 1, @freq_type = 4, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:

Error: Failed to execute SQL procedure. Got exception: ERROR [SQL Native Client][SQL Server]The specified @job_name ('Past Day stats rollupVPXProd') does not exist.

Error: Failed to execute command: BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT DECLARE @VARDBNAME VARCHAR(128) DECLARE @JobNAME VARCHAR(128) SELECT @ReturnCode = 0 IF ((select serverproperty('edition')) = 'Express Edition') BEGIN COMMIT TRANSACTION GOTO EndSave END select @VARDBNAME = db_name(dbid) from master..sysprocesses where spid=@@SPID set @JobNAME = 'Past Day stats rollup'+@VARDBNAME IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup' SELECT distinct @JobID = j.job_id FROM MSDB.dbo.sysjobs j , MSDB.dbo.sysjobsteps t where j.name = N'Past Day stats rollup' and j.job_id=t.job_id and t.database_name=db_name() IF (@JobID IS NOT NULL) BEGIN EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup' SELECT @JobID = NULL END SELECT distinct @JobID = j.job_id FROM MSDB.dbo.sysjobs j , MSDB.dbo.sysjobsteps t where j.name = @JobNAME and j.job_id=t.job_id and t.database_name=db_name() IF (@JobID IS NOT NULL) BEGIN EXECUTE msdb.dbo.sp_delete_job @job_name = @JobNAME SELECT @JobID = NULL END BEGIN EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = @JobNAME, @description = N'This job is to roll up 5 min stats and should run every 30 mins', @category_name = N'Stats Rollup', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Past day stats rollup', @command = N'EXECUTE stats_rollup1_proc', @database_name = @VARDBNAME, @server = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Delete past day rolled up records', @command = N'exec purge_stat1_proc', @database_name = @VARDBNAME, @server = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'30 min schedule', @enabled = 1, @freq_type = 4, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:

Error while upgrading: ERROR [SQL Native Client][SQL Server]The specified @job_name ('Past Day stats rollupVPXProd') does not exist.

Info: Exiting Upgrade Wizard

Is there any suggestion ?

Reply
0 Kudos
TonyJK
Enthusiast
Enthusiast
Jump to solution

It is a VMWare update script bug as it hasn't considered SQL Server Express with Advanced Services installed. For my vCenter Server, the database used is "Express Edition with Advanced Services" (i.e. SQL Server 2005 Express with Management Stuido Express installed).

We have to wait for VMWare to rectify the update script before we can proceed.

Thanks

Reply
0 Kudos
Arrow1
Enthusiast
Enthusiast
Jump to solution

Hi,

I experienced this as well yesterday while preparing a What's new course.

I'm using SQL2005 for the VC database and SQL express for the Update manager.

My VirtualCenter is 2.5 U3

I fixed this by manually deleting VMware "stored procedure" using Management studio connected to my SQL server.

I do that just before upgrading to vCenter 4.0 (VMware-VIMSetup-all-4.0.0-162902-vCenter)

Hope this could help.

Regards

Bernard

Regards Bernard
Reply
0 Kudos
rocker77
Enthusiast
Enthusiast
Jump to solution

Hi all,

I have the same issue and I think that this happen if you are using vSphere client on VC Server 2.5.

By using vSphere client in VCDB 2.5 it has been created new views which want create SQL upgrade sript too and this is reason, why instalation (DB upgrade) failure.

Look to VCDatabaseUpgrade.log.

Reply
0 Kudos