Error 547 - collection_set_4_noncached_collect_and_upload?

Question

Post reply

Error 547 - collection_set_4_noncached_collect_and_upload?

SQL Galaxy

One Orange Chip

Points: 29420
More actions
April 21, 2012 at 5:01 am

#267748

Hi,

Jobs Name - collection_set_4_noncached_collect_and_upload and created by

UTC utility control point SQL 2K8R2. after that UTC configuration was removed.

I could not remove this Jobs at SQL Agent.. due to too many FK relationship to many table in MSDB database. could anyone suggestion me how to remove this Jobs from server which were already connected SQL Instance into UTC.

Thanks

ananda

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLArcher SSC Eights! Points: 950 More actions · Answer 1

Hi,

You need to execute the following on the server:

EXEC msdb.sys.sysutility_mi_remove;

If you do plan to use the same server again as a UCP, then you need to follow the steps described here.

The error you are receiving is due the instance not being properly removed from the UCP, ideally it should be done through the UI unless your server crashed or uninstalled, then using the T-SQL commands is the only way (that I know of) to remove the jobs as well as the other data created when the server is added to a UCP.

SQL Galaxy One Orange Chip Points: 29420 More actions · Answer 2

Hi , Thanks for reply...

currently UCP utility server not avilable due to some reason SQL 2K8R2 uninstalled in testing setup server.

I want remove jobs manually for Remote server SQL Instrances which were already connected to UCP server.

Tried to connected remote server & execute as below script

use msdb

go

EXEC msdb.sys.sysutility_mi_remove

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'msdb.sys.sysutility_mi_remove'.

thanks

ananda

SQLArcher SSC Eights! Points: 950 More actions · Answer 3

SQLArcher

SSC Eights!

Points: 950

April 22, 2012 at 10:49 pm

#1477255

Apologies, got a typo in 🙂

EXEC [dbo].[sp_sysutility_mi_remove]

SQL Galaxy One Orange Chip Points: 29420 More actions · Answer 4

I am running this SP at remote server, not in UCP server

use msdb

go

EXEC [dbo].[sp_sysutility_mi_remove]

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

Msg 22001, Level 1, State 1

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

Msg 22001, Level 1, State 1

RegDeleteKey() returned error 2, 'The system cannot find the file specified.'

Msg 22001, Level 1, State 1

SQL Galaxy One Orange Chip Points: 29420 More actions · Answer 5

successfully removed Jobs at SQL Agent in remote server by disabled FK constraint in table msdb database

use msdb

go

alter table [dbo].syscollector_collection_sets_internal

nocheck constraint FK_syscollector_collection_sets_collection_sysjobs

use msdb

go

alter table [dbo].syscollector_collection_sets_internal

nocheck constraint FK_syscollector_collection_sets_upload_sysjobs

rgds

ananda