October 16, 2009 at 8:17 am
Background:
I installed an new instance on a clustered machine that already has multiple named instances installed and running fun for over a year.
I was unable to access the Maintenence Plans so I uninstalled the instance and reinstalled. The error still persisted.
I ended up having to install SSIS and install the client tools on the other node. This seemed to fix my issue although I wonder why the other instances were working fine without the latest installs that I had to do.
I went to create a maintenance plan and got error: Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
I found the following thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/3e53ba8b-ea23-4629-98db-43c80ba9888e
which said:
This problem occurs if some of the stored procedures while installing SP2 has not been applied successfully. So manually run the script below and it may resolve your issue. It resolved ours and we went throught exact same process of re-installing several times.
1) First go to the folder Install . The default location is this = “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install “ .
2) Then in this folder find this “sysdbupg.sql” .
3) Please open this script with a nodepad.
4) Then copy the whole script and then paste it in the Query window of the Management studio.
5) Then execute this script.
6) Once this script has been successfully executed, The issue should be resolved.
I looked in the install of this instance and that sql file did not exist. I found it in another instance, copied the code and ran it. It never did finish, I let it run for about 5 hours. I ended up cancelling it.
I tried to create a db backup maintenance plan and it worked! How it worked I have no idea. I saved it and scheduled it. I ran it through the Job Activity Monitor and it just hangs, it never finishes. I can manually create a backup (right-clicking db) and it finishes in seconds which I would expect because it's an empty db.
Now I'm stuck. My only thoughts are to try and piece-meal the sql file I had orginally cancelled out of.
Problem: I get to one of the first commands of the sysdbupg.sql file and run it:
-- temporary grant select to QE dmv
GRANT SELECT on sys.dm_exec_query_resource_semaphores TO PUBLIC
GRANT SELECT on sys.dm_exec_query_memory_grants TO PUBLIC
The result:
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 3
Cannot find the object 'dm_exec_query_memory_grants', because it does not exist or you do not have permission.
I checked and these do not exist. Of course I tried to copy the create script from another db, but you cannot do that. I do not know how to proceed. I hate to uninstall again bc. this is a production server and that would require another reboot!
Does anyone have any ideas??? Please help!! (Sorry for the length)
October 16, 2009 at 9:49 am
Those views are in SP1 but i would suggest to upgrade to SP3.
RELEASE NOTES:
Installing SQL Server 2005 SP3 on a Failover Cluster Instance
How to apply sp3 to a clustered MS SQL Server 2005 instance
October 16, 2009 at 9:55 am
AlexSQLForums (10/16/2009)
Those views are in SP1 but i would suggest to upgrade to SP3.
I currently have SP2 installed so I am wondering why those views are not there in the first place. Upgrading SP3 is something that I cannot
do easily/quickly and I need this working ASAP.
Do you think I can compare msdb.sys.objects from this instance to another working instance and try and create the missing ones?
Is that even allowed?
October 16, 2009 at 10:23 am
October 16, 2009 at 10:24 am
I just reviewed sysdbupg.sql that I attempted to run before and found drops/creates for these objects:
sp_validate_user
sp_help_jobhistory_full
sp_help_jobhistory_summary
sp_help_jobhistory_sem
I ran the creates for these.
I compared sys.objects to another instance and found a difference of 139 objects. Attempting to run this script wouldn't explain all the missing objects!!
October 16, 2009 at 10:32 am
Alex,
I appreciate your help with my issue. I reviewed the KB article and I don't believe it applies to my situation.
Initially there were named instances installed and SP2 installed afterwards and everything has been working fine now for over a year. Last week I installed another named instance without any install errors.
October 16, 2009 at 10:35 am
ang-473999 (10/16/2009)
sp_validate_user
sp_help_jobhistory_full
sp_help_jobhistory_summary
sp_help_jobhistory_sem
I ran the creates for these.
Update: Since I recreated the above SPs I tried to run a job through the Job Activity Monitor. Now it fails right away with:
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
I'm really unsure what to do at this point. Thoughts??
October 16, 2009 at 1:09 pm
I researched the syntax error and found the following thread:
Basically I updated the job by check marking the local connection under datasources tab and that did the trick. Now my jobs are running to completion.
However I am still concerned about all those missing objects. I fearful that this will keep rearing its ugly head in various ways down the road. Thoughts??
I want to make sure that when you apply SP's it's to the whole machine, it does not need to be installed per instance, correct??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply