SQL Agent and SSIS packages have multiple failure codes but DTS_E_OLEDBERROR or 0x80040E55 don’t tell you much about the cause.
One of my jobs failed sporadically with the following error.
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80040E55.
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Description: "Column does not exist.".
Error: 2016-11-30 11:20:03.11
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on returned error code 0xC0202009.
The component returned a failure code when
the pipeline engine called PrimeOutput().
The meaning of the failure code is defined
by the component, but the error is fatal and
the pipeline stopped executing.
There may be error messages posted before this
with more information about the failure.
The error message “Column does not exist.” led me to think that the stored procedure or one of the tables have been altered.
A quick look at the created date and modified date of the stored procedure and the tables confirmed that there were no schema changes.
So why the missing column error?
I executed the stored procedure manually and that revealed the issue.
There was TRY CATCH block in the procedure. It didn’t re-raise the error but returned it with a SELECT statement.
For example –
-- some sample sql
dbo.Order as o
ERROR_NUMBER() AS ErrorNumber
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
So when there was any error, the error message was returned as a SELECT output.
The column names of this output were different from the original columns. This caused the SSIS package to fail.
Take away –
Use RAISERROR or THROW in TSQL to return the errors to the calling application. Using a SELECT for this purpose can confuse the calling application to consider that as a data set.
One of our database restore attempt failed with an error message that mentioned cluster resources. At least the error message indicated that the issue was not related to backward compatibility but rather a physical resource or cluster settings.
The Error Message Window –
The Error Message –
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'MYDEVSQLSERVER'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&
System.Data.SqlClient.SqlError: Cannot use file 'J:MSSQL10_50MSSQLDATAMyDatabaseName.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&LinkId=20476
Both the links in the error message above pointed to a missing information message on Microsoft website –
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.
Thank you for searching on this message; your search helps us identify those areas for which we need to provide more information.
Cause and Resolution
We determined the cause rather quickly. The source system of the backup file had a drive letter layout that was different from the destination server. The restore process was trying to create the data files on a drive that didn’t exist on the destination! So the location of the files was changed in the restore dialog to a correct drive letter of the destination server. After that the restore progressed normally.