Question

SQL4Life on Thu, 10 Oct 2013 17:15:56


I have setup an MDW UCP with 5 enrolled instances. Three of two instances are working as they should and are reporting to the UCP MDW dashboard. On the other two instances, the job sysutility_mi_collect_and_upload fails on step 3 with the following error:

Job Name  sysutility_mi_collect_and_upload
Step Name  Upload to Utility Control Point
Duration  00:00:30
Sql Severity  16
Sql Message ID  37007

Message
Executed as user: Domain\SQLSvc. Starting collection set. [SQLSTATE 01000] (Message 50000)  Job 'collection_set_5_noncached_collect_and_upload' started successfully. [SQLSTATE 01000] (Message 14243)  Waiting for the collection set to kick off jobs. [SQLSTATE 01000] (Message 50000)  Waiting for collection set to finish its previous run.  Total seconds spent waiting : 10 [SQLSTATE 01000] (Message 50000)  Waiting for collection set to finish its previous run.  Total seconds spent waiting : 20 [SQLSTATE 01000] (Message 50000)  An error occurred during upload to the SQL Server utility control point. [SQLSTATE 42000] (Error 37007).  The step failed.

Now when I go to look at the Job History for the job collection_set_5_noncached_collect_and_upload, it fails on step 1 with the following error:

Job Name  collection_set_5_noncached_collect_and_upload
Step Name  collection_set_5_noncached_collect_and_upload_collect
Duration  00:00:20
Sql Severity  0
Sql Message ID  0

Message
Executed as user: Domain\SQLSvc. SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540, Subcomponent: Generate T-SQL Package Task, Description: An error occurred with the following error message: "Get DataType failed for Column 'physical_server_name'. : Index was outside the bounds of the array.".   .  The master package exited with error, previous error messages should explain the cause.  Process Exit Code 5.  The step failed.

Now unlike other posts where users are having connections (not enabling TCP/IP etc) or permissions issues (Service Account does not have permissions to upload data to the UCP MDW), this is different. I saw a KB article http://technet.microsoft.com/en-us/library/ee210592.aspx  under the "Failed Data Collection section Microsoft recommends:

"If data collection or data upload fail due to timeout issues, update the function dbo.fn_sysutility_mi_get_collect_script() in the MSDB database. Specifically, in the function "Invoke-BulkCopyCommand()" add line: $bulkCopy.BulkCopyTimeout=180"

You cannot modify system function. Is there anyone that knows what Microsoft is talking about when it comes to updating system functions? Besides this timeout doesn't seem to my problem, my issue lies in the failure of the collection_set_5_noncached_collect_and_upload with the error message shown. Any assistance is truly appreciated.

Thanks,

SQL4Life

Replies

Fanny Liu on Tue, 15 Oct 2013 08:45:21


Hello,

Sorry for delay.

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.

Regards,
Fanny Liu

Karthick P.K - karthick krishnamurthy on Wed, 30 Oct 2013 08:03:16


I think this is because of  SMO compatibility issues with SQL 2012/2008R2 SMO. Please upgrade the SQL2008R2 to SP1 or later. It should solve your issue.

SQL4Life on Wed, 30 Oct 2013 13:59:57


Karthick P.K - You were right on the money. Installing Microsoft SQL Server 2008 R2 SP2 resolved this issue. 

VVinayPrasad on Fri, 07 Mar 2014 07:08:32


Hi  Karthick,

I am facing same issue in SQL Server 2008 SP3. What could be the solution?

No permission or config changes have been made recently.

THanks

VInay