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_DTSActiveScriptTas k_2 DTSRun OnFinish: DTSStep_DTSActiveScriptTas k_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_ 3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_ 3
DTSRun OnStart: DTSStep_DTSActiveScriptTas k_5 DTSRun OnFinish: DTSStep_DTSActiveScriptTas k_5
DTSRun OnStart: DTSStep_DTSActiveScriptTas k_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTas k_1
DTSRun OnStart: DTSStep_DTSActiveScriptTas k_3 DTSRun OnFinish: DTSStep_DTSActiveScriptTas k_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 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_
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
DTSRun OnStart: DTSStep_DTSActiveScriptTas
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
DTSRun OnStart: DTSStep_DTSActiveScriptTas
DTSRun OnStart: DTSStep_DTSActiveScriptTas
DTSRun OnStart: DTSStep_DTSActiveScriptTas
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
DTSStep_DTSExecuteSQLTask_
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'
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
')
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.