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?

[JvodQl0D]

same here.  anyone?

[homerzzz]

Does the VPX_JOB_LOG table exist on the new SQL server?

[JagadeeshDev]

refer this . vSphere Documentation Center

[JvodQl0D]

I see: Databases>VCDB>Tables>VMW.VPX_JOB_LOG

I'm logged in as domain administrator(full rights)

Error is only occurring from job_dbm_performance_data_mssql.sql, not "job_schedule1,2,&3"

[homerzzz]

What do the rows in that table look like? I am surprised you would only see the error stated by esnmb only with job_dbm_performance_data_mssql.sql and not the others since they all use the same SELECT statement, just looking for a different job ID.

[JvodQl0D]

JOB_ID    STATUS    LAST_RUN
1    WAITING    2013-08-14 19:40:00.000
2    WAITING    2013-08-14 17:30:00.000
3    WAITING    2013-08-14 08:00:00.000
4    WAITING    2013-08-09 00:00:00.000

Also, "VPX_JOB_LOG" does not appear in "job_schedule1_mssql.sql", 2 & 3.

[homerzzz]

OK, try refreshing the local cache if you are using SQL Server Management Studio. Then execute the sql again. Edit>IntelliSense>Refresh Local Cache

[JvodQl0D]

I don't have "IntelliSense"  Edit>Bookmarks is my last menu-item.

[homerzzz]

What version of SQL are you using? The only other suggestion I have is you can always manually create the job. Just make sure to VCDB is selected and the 'owner' account is correct.

[JvodQl0D]

SQL Server Standard 64bit (R2) c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

Microsoft SQL Server Management Studio					10.50.4000.0
Microsoft Analysis Services Client Tools					10.50.4000.0
Microsoft Data Access Components (MDAC)					6.1.7601.17514
Microsoft MSXML					3.0 6.0
Microsoft Internet Explorer					9.0.8112.16421
Microsoft .NET Framework					2.0.50727.5472
Operating System					6.1.7601

This is all to fix the performace overview, where realtime works, but day,week, etc do not.

I'm almost to the point of wanting to install the latest vsphere iso.  (this is the second to last iso version.)  but it's production, and i'm iffy about it.

[homerzzz]

I recreated your issue in my environment and this is the solution that worked for me. The VPX_JOB_LOG table on my VCDB database is dbo.VPX_JOB_LOG. So I changed the sql for job_dbm_performance_data_mssql.sql from :

IF (SELECT COUNT(*) FROM VPX_JOB_LOG WHERE ....

to this:

IF (SELECT COUNT(*) FROM VCDB.dbo.VPX_JOB_LOG WHERE ...

and it worked just fine.

[JvodQl0D]

Well it seems it would have worked, but the same error occurred.  I tried changing it to VMX.VPX_JOB_LOG which destroyed the jobs list/service.  Thank goodness for snapshots.

Thank you very much @homerzzz for your time and effort!  Becoming a DBAdmin overnight was not in my plans(or pay-scale).  Not certain i want to continue hacking at this.

Apologies to @esnmb for hijacking his thread.

[homerzzz]

I am out of ideas, except maybe try the Refresh Cache again. To get IntelliSense to show up, open the job_dbm_performance_data_mssql.sql in Management Studio, select VCDB in the drop down just to the left of the Execute button, Edit>IntelliSense>Refresh Local Cache should show up now.

Unfortunately, I am not a dba either...

[krismcewan]

at the top of each SQL query type

USE [VCDBNAME]

before every single job and you will find that it runs fine.

its because its not pointing to the right DB when running the query

[homerzzz]

True what krismcewan says. Also make sure the database is Case Insensitive. The collation should be similar to SQL_Latin_General_CP1_CI_AS   (where CI refers to case insensitive)

[etsahi]

Make sure you are working on the right DB (sql studio left corner)

or new query

use vcdb

go

read this http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=200609...

[gerryvalen]

Happening the same to me. Trying to relocate my SQl DB, so as per Moving the VMware vCenter Server 4.x/5.x SQL database (7960893),  I'm recreating the jobs at the SQL server as per step 5 (1004382). The location of the files is: Program Files\VMware\Infrastructure\VirtualCenter Server\sql And all goes well until the one script failing so far is: job_dbm_performance_data_DB.sql When I ran that script, making sure I have VCDB selected on SQL manager, and also trying the USE VCDB, still getting the error: Invalid object name 'vpx_job_log' Where you able to solve this issue? Thanks.

[homerzzz]

Is your database Case Insensitive? That is one of the requirements when going to 5.x. The case in the scripts in many places is upper case...script is looking for VPX_JOB_LOG, so vpx_job_log is invalid if you have your database case sensitive.

[gerryvalen]

checked and this is what I have, which is default install for English lang. servers I think:

Latin1_General_CI_AS