Exception repeately occurring

Hi

I have a strange problem with my SQL server 2k instllation - every 10
mintutes when I have the Profiler trace with the entire "Errors" Event
category selected, the following 5 exceptions show up in the trace and
that too for the same SPID.

Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1

I have no clue why this is occurring - I tried running a trace with the
SP:StmtCompleted event on, but no other stored procedures show up with
the same spid close to the time where this exception is logged.

Does anyone have a clue as to why this error is occurring ?

RahulPondy (fd96121@.yahoo.com) writes:
> I have a strange problem with my SQL server 2k instllation - every 10
> mintutes when I have the Profiler trace with the entire "Errors" Event
> category selected, the following 5 exceptions show up in the trace and
> that too for the same SPID.
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> I have no clue why this is occurring - I tried running a trace with the
> SP:StmtCompleted event on, but no other stored procedures show up with
> the same spid close to the time where this exception is logged.

So what you in a such situation like this is this:

select * from master..sysmessages where error in (16955, 16945)

You could also have looked up the errors in Books Online, by simply
searching for them. This could give you the bonus that there might be
entire topic to troubleshoot the problem. I would not expect that in
this case, though.

These are the messages:

16945 The cursor was not declared.
16955 Could not create an acceptable cursor.

I would guess that 16945 is a consequence of 16955.

Apparently there is some code out there where the cursor declaration
fails, and where there is no error handling, so that execution continues.
Note that this may not have to be a stored procedure. Hypothetically
it could be a server-side cursor initiated by some client API as well.

In any case, it's a problem specific to that process, and it is not that
your server is about to go belly-up.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Exception

I start a job (the job successfully executes) but I notice in a trace that an
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_id
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
--
Message posted via http://www.sqlmonster.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id => tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.sqlmonster.com

Exception

I start a job (the job successfully executes) but I notice in a trace that a
n
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_i
d
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
Message posted via http://www.droptable.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id =
> tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.droptable.com