Link to home
Start Free TrialLog in
Avatar of hispanicteleservices
hispanicteleservices

asked on

MS SQL 2000 (Jobs) recurrent error message (Error = -2147217900 (80040E14))

Hi!

We have 200 - 300 Jobs programmed in MS SQL Server 2000 that run different Data Transformation Services packages at different times of the day -mainly reports.

We've come up with a recurrent error, at least 10 times a day with different jobs.
When looking at the Job History for the error, it goes like this:



Executed as user: [can't tell]\Administrator. .....  
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_6   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_6  
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_4   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_4  
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_2   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_2  
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_3   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_3  
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_5   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_5  
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_1  
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_3   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_3  
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnError:  
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)      
Error string:  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.        
Error source:  Microsoft OLE DB Provider for SQL Server     . The step failed.


We do pass information from SQL Server to Excel files ('Microsoft.Jet.OLEDB.4.0') at some point in our queries.
But, how come it works most of the time, and all sudden reporte failures?

The part of the query we use follows.

Thank you for your help.



--we pass a parameter that tells where the Excel file is "@filepath"
 
declare @DataSource 	varchar(600)
set @DataSource = 'Excel 8.0;HDR=NO;Database='+ @FilePath + ';'
 
 
exec ('
              insert into
              OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''' + @DataSource + ''', ''SELECT * FROM [Sheet1$A4:L4]'')
              select *
              from   dbo.temp_Table
')

Open in new window

Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image

This error means... if you try to insert/update a column that does not allow NULL values, with NULL (e.g. by hard-coding NULL into the statement, or by leaving a column - that does not have a default value defined - out of the INSERT list).
ASKER CERTIFIED SOLUTION
Avatar of hispanicteleservices
hispanicteleservices

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial