Exception_access_violation wgen refreshing jobs...
-
Hi there,
When refreshing the jobwindow in EM I receive this error:
Error 5
SQL-DMO)Code execution exception: Exception_access_violationConsequently the window is not refreshed, which is a nuissance. Now, this server has been renemed in the past, which caused other problems, which have been solved, sofar. I searched the job-related tables in msdb and changed the old servername into the new servername. I guess I am missing something.
I have googled the internet, could not find an applicable solution. Anybode can help me out?
Greetz,
Hans Brouwer -
This was removed by the editor as SPAM
-
What is the platform information for the server and/or 'desktop' PC (OS Version, OS Service pack, SQL Version, SQL Service Pack) ?
Are you running EM on the server itself or on a 'desktop' PC ?
If this is occurring with EM running on the SQL server, does this happen on a 'destop' PC ?
If this is happening on your 'desktop' PC, does this happen on the SQL server ?
If this is happening on your 'desktop' PC, does this happen on another 'desktop' PC ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Extra info:
Platform of the server is W2000 with the latest servicepack. SQL Server 2000 with sp3a. EM is running on my desktop, W2000 Pro with latest servicepacks. I have no access to the server, but you have a point here, I will try and find out. Same for checking other desktops.
Tnx for the info.
Greetz,
Hans Brouwer -
Hi,
I got same error (Error 5: [SQL-DMO]Code Execution exception: EXCEPTION_ACCESS_VIOLATION) on SQL 2000 after I modified job created by maintenance plan.
I created "Optimization" job using Maintenance wizard. After that I modified actual job, I added 2 additional steps and moved original task in between 2 additional I added (actually sequence did not work, but that’s another issue).
Since I modified actual job, “Optimization” option was no longer enabled while viewing Maintenance plan even though “Optimization” job still existed under Jobs. I deleted job and after that whenever I refresh Jobs window I would get that error.
After looking through msdb tables, refreshing and resaving maintenance plan, I just delete maintenance plan (luckily it’s easy to recreate), restarted SQL agent and recreated Maintenance plan. And there error was gone. It’s sounds very simple and you might have tried it, but anyway…
Kindest Regards,
Margos -
Hi all,
I recieved the same error after creating scripts for rolling out various maintenance plans (with additional steps included) to about 20 production servers.
About a week after the successfull rollout we needed to make small changes to certain of the jobs schedules'. And that is when I noticed these errors.
But all the plans that had not been changed did not give any of these errors.
After a couple of hours of troubleshooting, I figured out what causes these:
It seems changing any details of a Job steps that's not originally part of a maintenance plan causes these errors when you try and access the same job via
the maintenance plan menu.
Unfortunately, I don't have a solution, but only 2 suggestions:
If you use a script to create your maintenance plans:
Make any changes you require to the additional steps inside the creation script.
Then delete the maintenance plans and rerun the script to create the maintenance plans which will inturn create the jobs and their steps containing the new changes.
If you use EM's wizards.
Where possible make the changes from the maintenance plan menu and from the job itself. If you can't implement your required changes without doing it from the job,
you would probably have to recreate the whole plan with the new changes
I couldn't find alot of articles on this problem, so if anyone has a more educated resolution, please let us know
What's this "backup strategy" everyone is on about? -
For Hans Brouwer: I'd try to reinstall EM and SP3a on your client computer. It's quick and easy. If it works, then problen solved assuming the access violation is on your PC and not the SQL Server. If its on the SQL Server then I'd open up a case woth MS PSS.
For Margos and Thinus: I've modified the sunsequent JOBs created from SQL Saintenance plans by addiing additional steps at the end and never had issues accessing the JOBs or Maintenance Plans via EM. I'm wondering just what kind of additional steps that you have added
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi Rudy,
The only changes I made was to the endtime of the jobschedule...
BUT...I did some more troubleshooting, and after making the required change to my script and recreating the plan and job, the error still exists!
I then tested by executing the same script on my desktop, but there it works fine...?
I then created the job as a "standalone" job by not linking it to a maintenance plan and using the DB name in stead of the pan_ID as an argument - and it works!!?
For now, I will stick to having it as a job only, , the only downside to this that I can think of would be that I will need to create either multiple jobs or multiple steps If I want to backup more than ons DB at at time. End result it works! But this is still a very strange and unexplained occurence??
Below if the very straight forward script I use to create the plan and jobs(Certain irrelevant parts have been removed)
(In my final attempt above, I just commented out the parts where I create the plan, link the plan to a job and to a database)
-- Create Maintenance Plan
EXECUTE msdb.dbo.sp_add_maintenance_plan @PlanName, @plan_id=@PlanID output
-- Add the Database to the Maint Plan
exec msdb.dbo.sp_add_maintenance_plan_db @PlanID,@DBName
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT,
@job_name = @JobName,
@owner_login_name = N'sa',
@description = @Jobname,
@category_name = N'Database Maintenance',
@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
-- Generate command
SET @Com = 'EXECUTE master.dbo.xp_sqlmaint ''-PlanID ' + cast(@PlanID as nvarchar(40)) + ' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -BkExt "TRN"'''
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N'Log Backup',
@command = @Com,
@database_name = @DBName,
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 3,
@on_success_action = 4,
@on_fail_step_id = 2,
@on_fail_action = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @Com = null
-- Generate command
SET @Com = @Message
-- Add the job step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 2,
@step_name = N'Generate Failure Notification Alert for Log Backup',
@command = @Com,
@database_name = N'',
@server = N'',
@database_user_name = N'',
@subsystem = N'CmdExec',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 2,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @Message = null
SET @Com = null
-- Generate command
SET @Com = @Message
-- Add the job step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 3,
@step_name = N'Generate Successfull Notification Alert for Log Backup',
@command = @Com,
@database_name = N'',
@server = N'',
@database_user_name = N'',
@subsystem = N'CmdExec',
@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
SET @Message = null
SET @Com = null
EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = @JobID,
@start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = @Jobname,
@enabled = 1,
@freq_type = 4,
@active_start_date = @SDate,
@active_start_time = @STime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE msdb.dbo.sp_add_maintenance_plan_job @PlanID,@JobID
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
What's this "backup strategy" everyone is on about? -
Thinus ... a few due dilligence questions ... SQL version on the client and server, and OS information please ... by the way ... too many typos on my last post in this thread ... it was atrocious !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thinus,
Did you ever find a resolution for this problem?
I am attempting something very similar and get the same results.
I can create the maintenance plan and I can create the job. Both appear fine until I add the job to the maintenance plan. Once I do that I get an error in EM when I try to open the maintenance plan that the job can not be parsed. After that error, if I try to view properties of the job I get an execution error and EM closes.
Theses are the two error messages I have seen when trying to view properties on the job:
Error 5: [SQL-DMO]Code execution exception: Exception_Access_Violation
AND
Error 150: [SQL-DMO]Code execution exception: EXCEPTION_PRIV_INSTRUCTION
>> Environment - Fresh install of SQL Server 2000 SP4 on Windows 2003 SP1.
Thanks!
Sincerely,
Dan B.
-
More info...
After I run my script to create the plan and the job, if I check properties of the job first, it works fine. However, if I open the maint plan first and get that 'parse' error message and THEN go to see the job properties it throws the SQL-DMO errors given above.
Here is the script I am using:
/*********************************/
use msdb
Declare @PlanID UniqueIdentifier
Declare @JobID UniqueIdentifier
Declare @JobCommand nvarchar(500)
exec sp_add_maintenance_plan @plan_name = '5User Databases Nightly Backup', @plan_id = @PlanID OUTPUT
exec sp_add_job @job_name = N'DB Backup Job for DB Maintenance Plan ''5User Databases Nightly Backup''',
@category_id = 3,
@category_name = 'Database Maintenance',
@owner_login_name = 'sa',
@job_id = @JobID OUTPUT
Set @JobCommand = N'Execute master.dbo.xp_sqlmaint N''-PlanID ' + cast(@PlanID as char(36)) + ' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "d:\MSSQL\Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'''
exec sp_add_jobstep @job_id = @JobID,
@step_name = 'Step 1',
@subsystem = 'TSQL',
@command = @JobCommand
exec sp_add_jobschedule @job_id = @JobID,
@name='Nightly Schedule',
@freq_type=4,
@freq_interval=1,
@active_start_time=20000
exec sp_add_jobserver @job_id = @JobID, @server_name=N'(local)'
-- Since there is no easy way to add the datbase groups available via EM,
-- add master, then change to all databases manually (seems to be the way the wiz does it...)
exec sp_add_maintenance_plan_db @plan_id = @PlanID, @db_name = 'master'
exec sp_add_maintenance_plan_job @plan_id = @PlanID, @job_id = @JobID
exec sp_configure 'allow updates',1
reconfigure with override
Update msdb.dbo.sysdbmaintplan_databases
Set database_name='All User Databases'
Where plan_id = @PlanID
exec sp_configure 'allow updates',0
reconfigure
/*********************************/
Thanks!
-Dan B
-
I just got the same error!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply