June 21, 2011 at 1:16 am
Morning All
I have not seen this before with all the instances of data collector I have set up but could anyone shed some light.
The 4th job in data collector (collection_set_4_noncached_collect_and_upload) is failing with the following error message
OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid object name 'tempdb.dbo.sysutility_batch_time_internal'.". . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: OLE DB Source [1], Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: Generate T-SQL Package Task, Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.
I understand the error, the object isn't there so the task fails.
The job step is a cmdexec step with the following line
dcexec -c -s 4 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" -m 1
Can anyone shed some light on this step so I can fix the issue?
This seems to have run three times as under SSIS -> Stored Packages -> MSDB -> Data Collector -> Generated there are 3 sets of collect upload packages, but this may be me reading into the error incorrectly.
Any assistance would be great.
Thanks
Ant
August 3, 2011 at 11:06 am
A little late, but it has to do with setting up a Utility Control Point and adding your box to be managed by it.
October 24, 2011 at 6:36 am
Create the following table on TempDB, It will resolve the issue
CREATE TABLE [tempdb].[dbo].[sysutility_batch_time_internal] (
latest_batch_time datetimeoffset(7) PRIMARY KEY NOT NULL
)
Ranjith Lekamalage
MCITP SQL 2012 (BI Development)
March 23, 2012 at 4:03 am
I was getting this same error on a SQL 2008 box after setting up data collection.
Fixed the problem.
Thanks 🙂
October 9, 2013 at 7:11 am
So what was the solution?
April 11, 2014 at 12:38 am
April 22, 2014 at 5:53 am
As far as I can remember I created the table suggested by sisaralek in this series of posts, see the code below:
Create the following table on TempDB, It will resolve the issue
CREATE TABLE [tempdb].[dbo].[sysutility_batch_time_internal] (
latest_batch_time datetimeoffset(7) PRIMARY KEY NOT NULL
)
🙂
April 23, 2014 at 7:10 am
July 2, 2015 at 3:25 am
sisiralek (10/24/2011)
Create the following table on TempDB, It will resolve the issueCREATE TABLE [tempdb].[dbo].[sysutility_batch_time_internal] (
latest_batch_time datetimeoffset(7) PRIMARY KEY NOT NULL
)
This is the table that the "Utility Information" uses.
It has this littered all over it
IF OBJECT_ID ('[tempdb].[dbo].[b]sysutility_batch_time_internal[/b]') IS NOT NULL
BEGIN
SELECT @batch_time = latest_batch_time FROM tempdb.dbo.sysutility_batch_time_internal
END
Surely it is not a good thing to make hte table manually but to run the job / task that is suposed to create it but what is responsible for making the table. As it is in tempdb it must be something that runs each time the server starts but I cannot find it.
Any ideas? Or is it in the binaries and therefore this is just a work around for when the creation fails?
I ask because I have the same issue and the creation of the table did not fix the problem so maybe someting else is also created when the server restarts. Obviously restarting a live server is not a trivial task.
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply