Selecting job history information from sysjobhistory in msdb

Hi all,

using SQL 2000

When selecting "message" column from "sysjobhistory" table, doesn't contain
the entire error message compared to the job history option, from right
clicking on a certain job under SQL Server Agent - Jobs.

For Example:
under Job history when right clicking on a job in Enterprise Manager I see
this:

Executed as user: ACCESS1\SQL2KAdmin. ... 42000] (Error 5173) Database 'AC'
cannot be opened. It is in the middle of a restore. [SQLSTATE 42000] (Error
927) ALTER DATABASE statement failed. [SQLSTATE 42000] (Error 5069) Log
file 'E:\D\CO\AC\D\AC_LOG.LDF' does not match the primary file. It may be
from a different database or the log may have been rebuilt previously.
[SQLSTATE 01000] (Error 5108) 10 percent restored. [SQLSTATE 01000]
(Message 3211) 20 percent restored. [SQLSTATE 01000] (Message 3211) 30
percent restored. [SQLSTATE 01000] (Message 3211) 40 percent restored.
[SQLSTATE 01000] (Message 3211) 50 percent restored. [SQLSTATE 01000]
(Message 3211) 60 percent restored. [SQLSTATE 01000] (Message 3211) 70
percent restored. [SQLSTATE 01000] (Message 3211) 80 percent restored.
[SQLSTATE 01000] (Message 3211) 90 percent restored. [SQLSTATE 01000]
(Message 3211) 100 percent restored. [SQLSTATE 01000] (Message 3211)
Processed 10... The step failed.

And when I query it in query analyzer I get partial message:

Executed as user: ACCESS1\SQL2KAdmin. ... 42000] (Error 5173) Database 'AC'
cannot be opened. It is in the middle of a restore. [SQLSTATE 42000] (Error
927) ALTER DATABASE statement failed. [SQLSTATE 42000] (Error 5069) Log
file 'E:\D\CO

How can I query the entire message like what is shown under job history in
Enterprise Manager?

thanks
gv

QA has an option to only show some 250 characters for a column. You can configure this under Tools,
Options.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"gv" wrote in message
> Hi all,
>
> using SQL 2000
>
> When selecting "message" column from "sysjobhistory" table, doesn't contain
> the entire error message compared to the job history option, from right
> clicking on a certain job under SQL Server Agent - Jobs.
>
> For Example:
> under Job history when right clicking on a job in Enterprise Manager I see this:
>
> Executed as user: ACCESS1\SQL2KAdmin. ... 42000] (Error 5173) Database 'AC' cannot be opened. It
> is in the middle of a restore. [SQLSTATE 42000] (Error 927) ALTER DATABASE statement failed.
> [SQLSTATE 42000] (Error 5069) Log file 'E:\D\CO\AC\D\AC_LOG.LDF' does not match the primary file.
> It may be from a different database or the log may have been rebuilt previously. [SQLSTATE 01000]
> (Error 5108) 10 percent restored. [SQLSTATE 01000] (Message 3211) 20 percent restored. [SQLSTATE
> 01000] (Message 3211) 30 percent restored. [SQLSTATE 01000] (Message 3211) 40 percent restored.
> [SQLSTATE 01000] (Message 3211) 50 percent restored. [SQLSTATE 01000] (Message 3211) 60 percent
> restored. [SQLSTATE 01000] (Message 3211) 70 percent restored. [SQLSTATE 01000] (Message 3211)
> 80 percent restored. [SQLSTATE 01000] (Message 3211) 90 percent restored. [SQLSTATE 01000]
> (Message 3211) 100 percent restored. [SQLSTATE 01000] (Message 3211) Processed 10... The step
> failed.
>
> And when I query it in query analyzer I get partial message:
>
> Executed as user: ACCESS1\SQL2KAdmin. ... 42000] (Error 5173) Database 'AC' cannot be opened. It
> is in the middle of a restore. [SQLSTATE 42000] (Error 927) ALTER DATABASE statement failed.
> [SQLSTATE 42000] (Error 5069) Log file 'E:\D\CO
>
> How can I query the entire message like what is shown under job history in Enterprise Manager?
>
> thanks
> gv
>