Category: sql server manageability
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
Sql Severity 16
Sql Message ID 37007
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
Sql Severity 0
Sql Message ID 0
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.
Fanny Liu on Tue, 15 Oct 2013 08:45:21
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.
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
I am facing same issue in SQL Server 2008 SP3. What could be the solution?
No permission or config changes have been made recently.