Creating a job in a trigger causes intermittent Can not CREATE TABLE error

Creating a job in a trigger causes intermittent Can not CREATE TABLE error

Post by Martin Robert » Thu, 13 Jan 2000 04:00:00



Hi all,

I have a stored procedure which schedules a job in SQL Server 7.0.  This
stored procedure is called from an insert trigger which works most of the
time.  Occasionally I get the error 'Can not CREATE TABLE in a trigger'
which then occurs every time I attempt an insert.  The only way to clear the
error is to run the stored procedure in Query Analyser which stops the
problem for a period of time.

Anyone any idea how to stop this problem.  It's proving rather embarrassing.

Cheers,

Martin Roberts

 
 
 

Creating a job in a trigger causes intermittent Can not CREATE TABLE error

Post by Tibor Karasz » Fri, 14 Jan 2000 04:00:00


Martin,

It seems like SQL Server, for one reason or the other, quite suddenly
believes that the proc creates a table (which isn't allowed). (Are you sure
that it doesn't?).
I'd start by EXEC'ing part of the proc's statement. Don't know if it helps,
but I have a feeling that this has to do with the proc-plan.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


Quote:> Hi all,

> I have a stored procedure which schedules a job in SQL Server 7.0.  This
> stored procedure is called from an insert trigger which works most of the
> time.  Occasionally I get the error 'Can not CREATE TABLE in a trigger'
> which then occurs every time I attempt an insert.  The only way to clear
the
> error is to run the stored procedure in Query Analyser which stops the
> problem for a period of time.

> Anyone any idea how to stop this problem.  It's proving rather
embarrassing.

> Cheers,

> Martin Roberts


 
 
 

Creating a job in a trigger causes intermittent Can not CREATE TABLE error

Post by Martin Robert » Fri, 14 Jan 2000 04:00:00


Here is some more information.

The code is:













set nocount on





























If I comment out the code to add the job step the error does not occur.

Any ideas?

TIA,

Martin


Quote:>Hi all,

>I have a stored procedure which schedules a job in SQL Server 7.0.  This
>stored procedure is called from an insert trigger which works most of the
>time.  Occasionally I get the error 'Can not CREATE TABLE in a trigger'
>which then occurs every time I attempt an insert.  The only way to clear
the
>error is to run the stored procedure in Query Analyser which stops the
>problem for a period of time.

>Anyone any idea how to stop this problem.  It's proving rather
embarrassing.

>Cheers,

>Martin Roberts

 
 
 

Creating a job in a trigger causes intermittent Can not CREATE TABLE error

Post by Tibor Karasz » Fri, 14 Jan 2000 04:00:00


Martin,

My guess then is that sp_add_jobstep under certain circumstances executes a
code path which creates a table. I guess that you could wade through its
source code (and the sub-proc's that it calls) in msdb to see if that is the
case.

Also, you could see BOL if MS specifies whether or not you should be able to
add a jobstep within a transaction/trigger. That could help you decide
whether you want to call MS PSS for this...

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


Quote:> Here is some more information.

> The code is:

<snip>
 
 
 

1. create table or create trigger error

When I want to create table or create trigger, the following error occurs:

 Error 2610:[SQL SERVER] Could not find leaf row in nonclustered index
'ncsysobjects' that corresponds to data row from logical data page 5, row
offset 6 during update index attempt after data page split.

  The error usually occurs after I create some talbes and triggers, then I
can't create tables any more.
  The Sqlserver version is v6.5 .

   Would you please tell me how to fix this problem?

Sincerely,
   Fang Jing

2. REINDEXing user table in non stand-alone?

3. Error : create table statement is not allowed within trigger

4. Newbie 2.6 to 5.0 question

5. v7 ERROR: The CREATE TABLE statement is not allowed within a trigger

6. SQL GURUS: Blank Fields NOT EQUAL???

7. create temp table: -261 Cannot create file for table - Not owner

8. Time display in MS AS2000sp3

9. Can't create a trigger on table i created

10. NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN

11. NOTICE: CREATE TABLE will create implicit trigger(s)

12. Create Table Query does not Create Desired Datafields

13. Error when creating table 2760 do not have rights to this user