FatalExceptionError
Technical User
I have a job that is broken down in multiple steps. It works but randomly one of the steps will fail. This is a step where I query the database itself. I applied the hotfix that was supposed to fix the job scheduler not releasing processes. that helped my cpu resources but I notice memory starts going up.
This is the error I get
This is the source code for the procedure called at the failing step. I believe it is the top portion where it locks up as I can't see the bottom portion causing this kind of error. Is something wrong with my cursor or inserts?
FYI Weights is a linked server from a paradox DB. The procedure is below.
=======================================
The statement below is true
The statement above is false
There are 10 kinds of people, those who know binary and those who don't
This is the error I get
Code:
Executed as user: NT AUTHORITY\SYSTEM. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) OLE DB provider 'MSDASQL' reported an error. [SQLSTATE 42000] (Error 7399) Driver's SQLSetConnectAttr failed] [SQLSTATE 01000] (Error 7312) Driver's SQLSetConnectAttr failed] [SQLSTATE 01000] (Error 7312) System resource exceeded.] [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.This is the source code for the procedure called at the failing step. I believe it is the top portion where it locks up as I can't see the bottom portion causing this kind of error. Is something wrong with my cursor or inserts?
FYI Weights is a linked server from a paradox DB. The procedure is below.
Code:
CREATE PROCEDURE ExtractSPCData
AS
dbcc traceon (7300, 3604)
-- declare variables
declare @file_name varchar(255)
declare @current_date_time varchar(255)
declare @select_statement varchar(400)
declare @bcp_argument varchar(400)
declare @day varchar(5)
declare @month varchar(5)
declare @year varchar(5)
declare @hour varchar(5)
declare @minute varchar(5)
declare @ticket float
declare @truck_id varchar(15)
declare @time_in varchar(7)
declare @time_out varchar(7)
declare @date varchar(50)
declare @gross_unit varchar(2)
declare @container varchar(15)
declare @s_id varchar(15)
declare @day_cast tinyint
declare @hour_cast tinyint
declare @month_cast tinyint
declare @minute_cast tinyint
declare @net float
declare @gross float
declare @truck_wt float
declare @date_time smalldatetime
declare @last_date_read smalldatetime
--==============================================================================================================
SELECT @last_date_read = MAX(date_time) FROM s_info
DECLARE s_data_cursor CURSOR FOR
SELECT ticket, [truck id], [time in], [time out], date, net, gross, [gross unit], container, [s id], tare
FROM
OPENQUERY(Weights,'Select * from Weights2 UNION ALL Select * from Weights3 UNION ALL Select * from Weights4')
WHERE CONVERT(SMALLDATETIME, (date+' '+[time in])) > @last_date_read
and (c = 'sl' or c = 'hl' or c = 'tb' )
if @@ERROR <> 0 GOTO ExitProc
OPEN s_data_cursor
FETCH NEXT FROM s_data_cursor
INTO @ticket, @truck_id, @time_in, @time_out, @date, @net, @gross, @gross_unit, @container, @s_id, @truck_wt
SET @date_time = CONVERT(SMALLDATETIME, (@date+' '+@time_in))
if @@ERROR <> 0
begin
CLOSE s_data_cursor
DEALLOCATE s_data_cursor
GOTO ExitProc
end
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT info(ticket, truck_id, time_in, time_out, date, date_time, net_wt, gross_wt, unit, container, s_id, truck_wt)
VALUES (@ticket, @truck_id, @time_in, @time_out, @date, @date_time, @net, @gross, @gross_unit, @container, @s_id, @truck_wt)
if @@ERROR <> 0
begin
CLOSE s_data_cursor
DEALLOCATE s_data_cursor
GOTO ExitProc
end
FETCH NEXT FROM s_data_cursor
INTO @ticket, @truck_id, @time_in, @time_out, @date, @net, @gross, @gross_unit, @container, @s_id, @truck_wt
SET @date_time = CONVERT(SMALLDATETIME, (@date+' '+@time_in))
if @@ERROR <> 0
begin
CLOSE s_data_cursor
DEALLOCATE s_data_cursor
GOTO ExitProc
end
END
CLOSE s_data_cursor
DEALLOCATE s_data_cursor
--==============================================================================================================
-- get different parts of current date/time
-- this will be used to set the filename
select @hour = DATEPART(hour, GETDATE())
select @minute = DATEPART(minute, GETDATE())
select @month = DATEPART(month, GETDATE())
select @day = DATEPART(day, GETDATE())
select @year = DATEPART(year, GETDATE())
-- casting is done so that comparisons can be
-- done on the different date parts.
set @day_cast = CAST(@day as tinyint)
set @hour_cast = CAST(@hour as tinyint)
set @month_cast = CAST(@month as tinyint)
set @minute_cast = cast(@minute as tinyint)
-- These if statements add '0' to different parts
-- of the current date. This is done so that the
-- filename can properly reflect the mmddyyyyhhmm
-- naming convention for numbers truncating the '0'
-- when they are less than 10.
IF @minute_cast < 10
BEGIN
SET @minute = '0' + @minute
END
IF @month_cast < 10
BEGIN
SET @month = '0' + @month
END
IF @day_cast < 10
BEGIN
SET @day = '0' + @day
END
IF @hour_cast < 10
BEGIN
SET @hour ='0' + @hour
END
-- sets the current_date_time to mmddyyyyhhmm
SET @current_date_time = @month + @day + @year + @hour + @minute
-- removes the : from current_date_time
SET @current_date_time = REPLACE(@current_date_time, ':','')
-- sets the full filename path and extension included.
SET @file_name = 'C:\DBquery\sData\'+ @current_date_time + '.txt'
-- sets the sql query statement
SET @select_statement = 'select * from info where date_time > CONVERT(SMALLDATETIME,'''+CONVERT(varchar(50),@last_date_read,100 )+''') order by date_time" queryout '
-- sets the full BCP command to
SET @bcp_argument = 'bcp "' + @select_statement + ' ' + @file_name + ' -Sapp-bidfax -T -c'
ExitProc:
SET QUOTED_IDENTIFIER OFF
GO=======================================
The statement below is true
The statement above is false
There are 10 kinds of people, those who know binary and those who don't
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")