Job History Logs

Hi,
Maximum Job History Log size is set to 1000 and Maximum Job History Rows
Per Job is set to 100. There are some some jobs that are running every
minute. In less then five minute the Log Size grows to 45 from 0. So I guess
the jobs that are running every minute are filling up the History Log. When
other jobs that run once every night are not able to write in the History
Log. What should be the preferred setting in such scenario that will allow
the nightly jobs to write history in the History Log.
I can think of two options. I can either uncheck the Limit Size of Job
History Log option or change the value of the Maximum Job History Log Size
option. But then I don't know how large the History Log will grow. Can you
give me an idea on that? Also is there a way to automatically clear the
History Log?
Thanks in advance...If you have n number of jobs then set history log size to (n * 100) +
100.
Set maximum number of rows per job to 100.
Regards
Amish Shah.sql

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
Will
Never Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:
> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job Aborting ??

I have a job created on my server which runs a stored procedure. The
SP uses a cursor on a recordset to insert rows into a table (via a
stored procedure - hence the cursor).
It is expected that some inserts will fail due to missing information
or that a record already exists for it. One of the main reasons for me
using the cursor is so that ALL the data is not rolled back when this
occurrs.
But it seems that when I run the job, the job is aborting after a few
error messages. When I execute the SP throught query analyzer, it
completes. I can tell this is happening because of the difference in
row numbers afterwards. Another indication that the job is aborting is
that a summary message does not show up in the job history.
What could be the cause of the job aborting? What other info do I need
to provide? (Me posting table schemas and sample data may not help
since I could not give you a large enough dataset to simulate.)
Job Output (from history)
--
Executed as user: LSP\sa. Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000) Associated statement is not prepared
[SQLSTATE HY007] (Error 0) Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000). The step failed.
Count after Job
--
1737
Query Analyzer Output (notice last line summary output)
--
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERH4 9999/ERH4 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERZ3 9999/ERZ3 XNAS
...
... and so on
...
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: NQH4 9999/NQH4 XNAS
SecurityMaster load completed : 6345 success, 0 skipped, 0
unqualified, 97 failed
Count after Job
--
6342Jason,
That is just how Agent work for TSQL jobsteps. It will abort processing if any error. I suggest you
put your TSQL in a script file and use a CmdExec jobstep instead, and use OSQL.EXE with the script
as an input file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> I have a job created on my server which runs a stored procedure. The
> SP uses a cursor on a recordset to insert rows into a table (via a
> stored procedure - hence the cursor).
> It is expected that some inserts will fail due to missing information
> or that a record already exists for it. One of the main reasons for me
> using the cursor is so that ALL the data is not rolled back when this
> occurrs.
> But it seems that when I run the job, the job is aborting after a few
> error messages. When I execute the SP throught query analyzer, it
> completes. I can tell this is happening because of the difference in
> row numbers afterwards. Another indication that the job is aborting is
> that a summary message does not show up in the job history.
> What could be the cause of the job aborting? What other info do I need
> to provide? (Me posting table schemas and sample data may not help
> since I could not give you a large enough dataset to simulate.)
> Job Output (from history)
> --
> Executed as user: LSP\sa. Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000). The step failed.
> Count after Job
> --
> 1737
> Query Analyzer Output (notice last line summary output)
> --
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERH4 9999/ERH4 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERZ3 9999/ERZ3 XNAS
> ...
> ... and so on
> ...
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: NQH4 9999/NQH4 XNAS
> SecurityMaster load completed : 6345 success, 0 skipped, 0
> unqualified, 97 failed
> Count after Job
> --
> 6342
>|||What you said makes sense but I am unable to prove it.
Given the following code, the print statement 'Got to here' should NOT print
when run as a job but it does.
USE TempDb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE [id] =object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spTest]
GO
CREATE PROCEDURE spTest
AS
SET NOCOUNT ON
RAISERROR('Test error', 15, 1)
PRINT 'Got to end'
GO
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Jason,
> That is just how Agent work for TSQL jobsteps. It will abort processing if
any error. I suggest you
> put your TSQL in a script file and use a CmdExec jobstep instead, and use
OSQL.EXE with the script
> as an input file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > I have a job created on my server which runs a stored procedure. The
> > SP uses a cursor on a recordset to insert rows into a table (via a
> > stored procedure - hence the cursor).
> >
> > It is expected that some inserts will fail due to missing information
> > or that a record already exists for it. One of the main reasons for me
> > using the cursor is so that ALL the data is not rolled back when this
> > occurrs.
> >
> > But it seems that when I run the job, the job is aborting after a few
> > error messages. When I execute the SP throught query analyzer, it
> > completes. I can tell this is happening because of the difference in
> > row numbers afterwards. Another indication that the job is aborting is
> > that a summary message does not show up in the job history.
> >
> > What could be the cause of the job aborting? What other info do I need
> > to provide? (Me posting table schemas and sample data may not help
> > since I could not give you a large enough dataset to simulate.)
> >
> > Job Output (from history)
> > --
> > Executed as user: LSP\sa. Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000). The step failed.
> >
> > Count after Job
> > --
> > 1737
> >
> > Query Analyzer Output (notice last line summary output)
> > --
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERH4 9999/ERH4 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERZ3 9999/ERZ3 XNAS
> > ...
> > ... and so on
> > ...
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: NQH4 9999/NQH4 XNAS
> > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > unqualified, 97 failed
> >
> > Count after Job
> > --
> > 6342
> >
> >
>|||I created a TSQL jobstep where I call the proc and an output file. I did not get the print in the
output file. Do you see anything else?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> What you said makes sense but I am unable to prove it.
> Given the following code, the print statement 'Got to here' should NOT print
> when run as a job but it does.
> USE TempDb
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE [id] => object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[spTest]
> GO
> CREATE PROCEDURE spTest
> AS
> SET NOCOUNT ON
> RAISERROR('Test error', 15, 1)
> PRINT 'Got to end'
> GO
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Jason,
> >
> > That is just how Agent work for TSQL jobsteps. It will abort processing if
> any error. I suggest you
> > put your TSQL in a script file and use a CmdExec jobstep instead, and use
> OSQL.EXE with the script
> > as an input file.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > I have a job created on my server which runs a stored procedure. The
> > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > stored procedure - hence the cursor).
> > >
> > > It is expected that some inserts will fail due to missing information
> > > or that a record already exists for it. One of the main reasons for me
> > > using the cursor is so that ALL the data is not rolled back when this
> > > occurrs.
> > >
> > > But it seems that when I run the job, the job is aborting after a few
> > > error messages. When I execute the SP throught query analyzer, it
> > > completes. I can tell this is happening because of the difference in
> > > row numbers afterwards. Another indication that the job is aborting is
> > > that a summary message does not show up in the job history.
> > >
> > > What could be the cause of the job aborting? What other info do I need
> > > to provide? (Me posting table schemas and sample data may not help
> > > since I could not give you a large enough dataset to simulate.)
> > >
> > > Job Output (from history)
> > > --
> > > Executed as user: LSP\sa. Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000). The step failed.
> > >
> > > Count after Job
> > > --
> > > 1737
> > >
> > > Query Analyzer Output (notice last line summary output)
> > > --
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERH4 9999/ERH4 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERZ3 9999/ERZ3 XNAS
> > > ...
> > > ... and so on
> > > ...
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: NQH4 9999/NQH4 XNAS
> > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > unqualified, 97 failed
> > >
> > > Count after Job
> > > --
> > > 6342
> > >
> > >
> >
> >
>|||This is the output from job history.
Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
end [SQLSTATE 01000] (Error 0). The step failed.
Could you post your code you I can try it on my system?
Thanks,
Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> I created a TSQL jobstep where I call the proc and an output file. I did
not get the print in the
> output file. Do you see anything else?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > What you said makes sense but I am unable to prove it.
> >
> > Given the following code, the print statement 'Got to here' should NOT
print
> > when run as a job but it does.
> >
> > USE TempDb
> > GO
> >
> > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') =1)
> > DROP PROCEDURE [dbo].[spTest]
> > GO
> >
> > CREATE PROCEDURE spTest
> > AS
> > SET NOCOUNT ON
> >
> > RAISERROR('Test error', 15, 1)
> >
> > PRINT 'Got to end'
> > GO
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Jason,
> > >
> > > That is just how Agent work for TSQL jobsteps. It will abort
processing if
> > any error. I suggest you
> > > put your TSQL in a script file and use a CmdExec jobstep instead, and
use
> > OSQL.EXE with the script
> > > as an input file.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > I have a job created on my server which runs a stored procedure. The
> > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > stored procedure - hence the cursor).
> > > >
> > > > It is expected that some inserts will fail due to missing
information
> > > > or that a record already exists for it. One of the main reasons for
me
> > > > using the cursor is so that ALL the data is not rolled back when
this
> > > > occurrs.
> > > >
> > > > But it seems that when I run the job, the job is aborting after a
few
> > > > error messages. When I execute the SP throught query analyzer, it
> > > > completes. I can tell this is happening because of the difference in
> > > > row numbers afterwards. Another indication that the job is aborting
is
> > > > that a summary message does not show up in the job history.
> > > >
> > > > What could be the cause of the job aborting? What other info do I
need
> > > > to provide? (Me posting table schemas and sample data may not help
> > > > since I could not give you a large enough dataset to simulate.)
> > > >
> > > > Job Output (from history)
> > > > --
> > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > >
> > > > Count after Job
> > > > --
> > > > 1737
> > > >
> > > > Query Analyzer Output (notice last line summary output)
> > > > --
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERH4 9999/ERH4 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > ...
> > > > ... and so on
> > > > ...
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: NQH4 9999/NQH4 XNAS
> > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > unqualified, 97 failed
> > > >
> > > > Count after Job
> > > > --
> > > > 6342
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, indeed, you are right. I see that now when looking a bit closer (my class was about to start,
so I was a bit hasty when looking as the output earlier). So, it seems like Agent will stop
processing under *some* circumstances (evidently) and I guess one could do some research to find out
under what circumstances. Fact is, however, that Agent will stop processing under some circumstances
and a way around that is to execute the SQL code from OSQL instead of Agent. :-)
...
I tried one more thing. Adding a second PRINT after the proc call didn't make a difference. However,
when adding another batch (GO) that batch was not executed if the first had an error, while it was
executed if the first one was execute OK. I guess it all makes sense if you think about how batches
are handled in SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> This is the output from job history.
> Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
> end [SQLSTATE 01000] (Error 0). The step failed.
> Could you post your code you I can try it on my system?
> Thanks,
> Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > I created a TSQL jobstep where I call the proc and an output file. I did
> not get the print in the
> > output file. Do you see anything else?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > What you said makes sense but I am unable to prove it.
> > >
> > > Given the following code, the print statement 'Got to here' should NOT
> print
> > > when run as a job but it does.
> > >
> > > USE TempDb
> > > GO
> > >
> > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') => 1)
> > > DROP PROCEDURE [dbo].[spTest]
> > > GO
> > >
> > > CREATE PROCEDURE spTest
> > > AS
> > > SET NOCOUNT ON
> > >
> > > RAISERROR('Test error', 15, 1)
> > >
> > > PRINT 'Got to end'
> > > GO
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Jason,
> > > >
> > > > That is just how Agent work for TSQL jobsteps. It will abort
> processing if
> > > any error. I suggest you
> > > > put your TSQL in a script file and use a CmdExec jobstep instead, and
> use
> > > OSQL.EXE with the script
> > > > as an input file.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > I have a job created on my server which runs a stored procedure. The
> > > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > > stored procedure - hence the cursor).
> > > > >
> > > > > It is expected that some inserts will fail due to missing
> information
> > > > > or that a record already exists for it. One of the main reasons for
> me
> > > > > using the cursor is so that ALL the data is not rolled back when
> this
> > > > > occurrs.
> > > > >
> > > > > But it seems that when I run the job, the job is aborting after a
> few
> > > > > error messages. When I execute the SP throught query analyzer, it
> > > > > completes. I can tell this is happening because of the difference in
> > > > > row numbers afterwards. Another indication that the job is aborting
> is
> > > > > that a summary message does not show up in the job history.
> > > > >
> > > > > What could be the cause of the job aborting? What other info do I
> need
> > > > > to provide? (Me posting table schemas and sample data may not help
> > > > > since I could not give you a large enough dataset to simulate.)
> > > > >
> > > > > Job Output (from history)
> > > > > --
> > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 1737
> > > > >
> > > > > Query Analyzer Output (notice last line summary output)
> > > > > --
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > ...
> > > > > ... and so on
> > > > > ...
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > unqualified, 97 failed
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 6342
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I modified my problem job to use osql and seems to be running as expected.
What is the difference in how Agent submits jobs and how OSQL does?
Thanks for all your help.
- Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> Yes, indeed, you are right. I see that now when looking a bit closer (my
class was about to start,
> so I was a bit hasty when looking as the output earlier). So, it seems
like Agent will stop
> processing under *some* circumstances (evidently) and I guess one could do
some research to find out
> under what circumstances. Fact is, however, that Agent will stop
processing under some circumstances
> and a way around that is to execute the SQL code from OSQL instead of
Agent. :-)
> ...
> I tried one more thing. Adding a second PRINT after the proc call didn't
make a difference. However,
> when adding another batch (GO) that batch was not executed if the first
had an error, while it was
> executed if the first one was execute OK. I guess it all makes sense if
you think about how batches
> are handled in SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > This is the output from job history.
> >
> > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
to
> > end [SQLSTATE 01000] (Error 0). The step failed.
> >
> > Could you post your code you I can try it on my system?
> >
> > Thanks,
> > Jason
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > I created a TSQL jobstep where I call the proc and an output file. I
did
> > not get the print in the
> > > output file. Do you see anything else?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > What you said makes sense but I am unable to prove it.
> > > >
> > > > Given the following code, the print statement 'Got to here' should
NOT
> > print
> > > > when run as a job but it does.
> > > >
> > > > USE TempDb
> > > > GO
> > > >
> > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
N'IsProcedure') => > 1)
> > > > DROP PROCEDURE [dbo].[spTest]
> > > > GO
> > > >
> > > > CREATE PROCEDURE spTest
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > RAISERROR('Test error', 15, 1)
> > > >
> > > > PRINT 'Got to end'
> > > > GO
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > Jason,
> > > > >
> > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > processing if
> > > > any error. I suggest you
> > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
and
> > use
> > > > OSQL.EXE with the script
> > > > > as an input file.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at:
> > > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > I have a job created on my server which runs a stored procedure.
The
> > > > > > SP uses a cursor on a recordset to insert rows into a table (via
a
> > > > > > stored procedure - hence the cursor).
> > > > > >
> > > > > > It is expected that some inserts will fail due to missing
> > information
> > > > > > or that a record already exists for it. One of the main reasons
for
> > me
> > > > > > using the cursor is so that ALL the data is not rolled back when
> > this
> > > > > > occurrs.
> > > > > >
> > > > > > But it seems that when I run the job, the job is aborting after
a
> > few
> > > > > > error messages. When I execute the SP throught query analyzer,
it
> > > > > > completes. I can tell this is happening because of the
difference in
> > > > > > row numbers afterwards. Another indication that the job is
aborting
> > is
> > > > > > that a summary message does not show up in the job history.
> > > > > >
> > > > > > What could be the cause of the job aborting? What other info do
I
> > need
> > > > > > to provide? (Me posting table schemas and sample data may not
help
> > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > >
> > > > > > Job Output (from history)
> > > > > > --
> > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
prepared
> > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 1737
> > > > > >
> > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > --
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC420P
GGZ33DEC420P
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC460C
GGZ33DEC460C
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > ...
> > > > > > ... and so on
> > > > > > ...
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > unqualified, 97 failed
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 6342
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> What is the difference in how Agent submits jobs and how OSQL does?
AFAIK, both use ODBC. Apart from that, I don't know of any technical details released on the
subject.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:eBTMeDYxDHA.2712@.tk2msftngp13.phx.gbl...
> I modified my problem job to use osql and seems to be running as expected.
> What is the difference in how Agent submits jobs and how OSQL does?
> Thanks for all your help.
> - Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> > Yes, indeed, you are right. I see that now when looking a bit closer (my
> class was about to start,
> > so I was a bit hasty when looking as the output earlier). So, it seems
> like Agent will stop
> > processing under *some* circumstances (evidently) and I guess one could do
> some research to find out
> > under what circumstances. Fact is, however, that Agent will stop
> processing under some circumstances
> > and a way around that is to execute the SQL code from OSQL instead of
> Agent. :-)
> > ...
> >
> > I tried one more thing. Adding a second PRINT after the proc call didn't
> make a difference. However,
> > when adding another batch (GO) that batch was not executed if the first
> had an error, while it was
> > executed if the first one was execute OK. I guess it all makes sense if
> you think about how batches
> > are handled in SQL Server.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > > This is the output from job history.
> > >
> > > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
> to
> > > end [SQLSTATE 01000] (Error 0). The step failed.
> > >
> > > Could you post your code you I can try it on my system?
> > >
> > > Thanks,
> > > Jason
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > > I created a TSQL jobstep where I call the proc and an output file. I
> did
> > > not get the print in the
> > > > output file. Do you see anything else?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > > What you said makes sense but I am unable to prove it.
> > > > >
> > > > > Given the following code, the print statement 'Got to here' should
> NOT
> > > print
> > > > > when run as a job but it does.
> > > > >
> > > > > USE TempDb
> > > > > GO
> > > > >
> > > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
> N'IsProcedure') => > > 1)
> > > > > DROP PROCEDURE [dbo].[spTest]
> > > > > GO
> > > > >
> > > > > CREATE PROCEDURE spTest
> > > > > AS
> > > > > SET NOCOUNT ON
> > > > >
> > > > > RAISERROR('Test error', 15, 1)
> > > > >
> > > > > PRINT 'Got to end'
> > > > > GO
> > > > >
> > > > > "Tibor Karaszi"
> > > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > > Jason,
> > > > > >
> > > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > > processing if
> > > > > any error. I suggest you
> > > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
> and
> > > use
> > > > > OSQL.EXE with the script
> > > > > > as an input file.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > Archive at:
> > > > >
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > > >
> > > > > >
> > > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > > I have a job created on my server which runs a stored procedure.
> The
> > > > > > > SP uses a cursor on a recordset to insert rows into a table (via
> a
> > > > > > > stored procedure - hence the cursor).
> > > > > > >
> > > > > > > It is expected that some inserts will fail due to missing
> > > information
> > > > > > > or that a record already exists for it. One of the main reasons
> for
> > > me
> > > > > > > using the cursor is so that ALL the data is not rolled back when
> > > this
> > > > > > > occurrs.
> > > > > > >
> > > > > > > But it seems that when I run the job, the job is aborting after
> a
> > > few
> > > > > > > error messages. When I execute the SP throught query analyzer,
> it
> > > > > > > completes. I can tell this is happening because of the
> difference in
> > > > > > > row numbers afterwards. Another indication that the job is
> aborting
> > > is
> > > > > > > that a summary message does not show up in the job history.
> > > > > > >
> > > > > > > What could be the cause of the job aborting? What other info do
> I
> > > need
> > > > > > > to provide? (Me posting table schemas and sample data may not
> help
> > > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > > >
> > > > > > > Job Output (from history)
> > > > > > > --
> > > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
> prepared
> > > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 1737
> > > > > > >
> > > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > > --
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC420P
> GGZ33DEC420P
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC460C
> GGZ33DEC460C
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > > ...
> > > > > > > ... and so on
> > > > > > > ...
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > > unqualified, 97 failed
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 6342
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

JDBC ResultSet writes to tmp file

The problem I got is when I am using the ResultSet to loop through the
result rows, the JDBC driver keeps writing each row's data to a tmp
file at /Tomcat/temp/ After the loop is finished, I do a
"ResultSet.close();". After that, the content in that tmp file is
gone.
Because the content written to the tmp file is really big during the
looping, it significantly slows done the performance. Following is my
code.
[code]
int size = 0;
int i = 0;
ObjectOutputStream outputToApplet = new
ObjectOutputStream(response.
getOutputStream());
ResultSet resultSet = statement.executeQuery(q);
if (page == 0) { //send back the number of rows.
resultSet.last();
size = resultSet.getRow();
resultSet.beforeFirst();
outputToApplet.writeObject(new Integer(size));
outputToApplet.flush();
}
else if (page > 0) { //send back 1000 as the number of rows,
and start from page*1000
outputToApplet.writeObject(new Integer(1000));
outputToApplet.flush();
resultSet.absolute(page * 1000);
}
while (resultSet.next()) {
String MailID = resultSet.getString("mail_Id");
String Name = resultSet.getString("mailFrom");
String To = resultSet.getString("mailToSorted");
String Subject = resultSet.getString("Subject");
String InsertDate = resultSet.getString("insertDate");
CustomerServiceReturn C = new CustomerServiceReturn(Name,
To, MailID, Subject, InsertDate);
outputToApplet.writeObject(C);
outputToApplet.flush();
i++;
if (i == 1000) {
outputToApplet.close();
resultSet.close();
return;
}
}
resultSet.close();
outputToApplet.close();
[/code]
The name of the file is "ddtbxxxxx.tmp". xxxxx is a number.
Thank you so much for any help.
Hong
Hong wrote:
> The problem I got is when I am using the ResultSet to loop through the
> result rows, the JDBC driver keeps writing each row's data to a tmp
> file at /Tomcat/temp/ After the loop is finished, I do a
> "ResultSet.close();". After that, the content in that tmp file is
> gone.
> Because the content written to the tmp file is really big during the
> looping, it significantly slows done the performance.
Did you actually verify this with some measurements that the tmp file is
the reason for slow performance?

> Following is my
> code.
> [code]
> int size = 0;
> int i = 0;
> ObjectOutputStream outputToApplet = new
> ObjectOutputStream(response.
> getOutputStream());
> ResultSet resultSet = statement.executeQuery(q);
> if (page == 0) { //send back the number of rows.
> resultSet.last();
> size = resultSet.getRow();
> resultSet.beforeFirst();
> outputToApplet.writeObject(new Integer(size));
> outputToApplet.flush();
> }
> else if (page > 0) { //send back 1000 as the number of
> rows, and start from page*1000
> outputToApplet.writeObject(new Integer(1000));
> outputToApplet.flush();
> resultSet.absolute(page * 1000);
> }
> while (resultSet.next()) {
> String MailID = resultSet.getString("mail_Id");
> String Name = resultSet.getString("mailFrom");
> String To = resultSet.getString("mailToSorted");
> String Subject = resultSet.getString("Subject");
> String InsertDate = resultSet.getString("insertDate");
> CustomerServiceReturn C = new
> CustomerServiceReturn(Name, To, MailID, Subject, InsertDate);
> outputToApplet.writeObject(C);
> outputToApplet.flush();
> i++;
> if (i == 1000) {
> outputToApplet.close();
> resultSet.close();
> return;
> }
> }
> resultSet.close();
> outputToApplet.close();
> [/code]
> The name of the file is "ddtbxxxxx.tmp". xxxxx is a number.
> Thank you so much for any help.
I'd remove the invocations of "outputToApplet.flush();". Closing does a
flush automatically and with the flushes in between you underutilize the
streams buffers which might be *one* reasons for bad performance.
It's also a good idea to put close() calls into finally blocks. That way
you make sure that even in case of an exception resources are properly
deallocated.
Kind regards
robert