Migrated SQL Database; Error Recreating Some Jobs

[esnmb]

I migrated my databases to a new SQL server successfully.  I am now recreating the SQL jobs per article 2006097.  All worked except for the following:

job_dbm_performance_data_mssql.sql

job_topn_past_day_mssql.sql

job_topn_past_month_mssql.sql

job_topn_past_week_mssql.sql

job_topn_past_year_mssql.sql

I am getting error:

Msg 208, Level 16, State 1, Line 36 (lines very depending on the job)

Invalid object name 'vpx_job_log'.

Thoughts?

[homerzzz]

Can you post the portion of the script that contains the SELECT statement?

[gerryvalen]

Hi Homerzzz,

Here's the part where it hangs:

IF (SELECT COUNT(*) FROM VPX_JOB_LOG WHERE JOB_ID = 2) < 1     
INSERT INTO VPX_JOB_LOG(JOB_ID) VALUES (2)
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Also, after hours struggling with this I needed to move the Production DB to the new server, so I setup a VM with Windows 2008 Server on it, then installed vCenter and pointed it to the new server, where it created (seems to me) all those jobs for me.
After I restored the production DB over this new server, both VCDB and RSA DB, and changed the ODBC on the (real) production vCenter to point to the new SQL Server, and it seems to be working fine.
Are there a few things I can check to make sure everything is running smoothly, so far I see no errors or issues with this migration done this way.

[homerzzz]

If your performance graphs are collecting data beyond realtime...day, week month etc. , then you know the jobs are good. You can check when they run by viewing the properties of each job under SQL Server Agent.

[gerryvalen]

Thanks homerzzz, checked the jobs and have been executed fine, so I guess it's all good, too bad about this VMware instructions for migrating, they only state to create 4 jobs yet the vcenter installer creates more than 4, and then who knows how the installer can run those scripts when I could not make that last one work. But well, gotta move on the next item I guess, thanks again for your help.

[dluu]

I know this is an old thread, but in case someone encounters this same error, I thought I'll update it with my findings.

I initially had a DBA to run the scripts and they were reporting the same errors as reported in the OP. So eventually, I just asked them to leave them and I'll deal with it later.

After carefully reading the KB article again, I realised that they were using they're DBO account rather than the account used by vCenter.

So I've just run the same scripts using the correct user and all jobs have been created.

[bbb123]

I've had a similar problem - the issue was that the VMW schema needs to be the default schema for the user.