Adding Jobs

I am having an interesting problem. Under 6.5, we used a trigger to call a stored procedure that added a task to SQL Executive. We did this for over a year with no problems. Now, under 7.0, we're getting the error message:

"Server: Msg 287, Level 16, State 2, Procedure sp_verify_jobstep, Line 238
The CREATE TABLE statement is not allowed within a trigger."

Sure enough, inside of the sp_verify_jobstep, its creating a temp table. Now, I know that this is supposedly disallowed, however, it is discussed in the books on-line. Also, I find it hard to believe that MS would make it impossible to add jobs from a trigger, actually, a stored procedure called from a trigger.

I really need some guidance on this, as this is going to cause us to lose a large piece of functionality or at least cause us to have to completely re-write our code.



Thank you,
Kevin Whittenberger
I.B.I.S., Inc.
mailto://[email protected]

Try setting sp_dbcmptlevel to 65 on your database and see if the process will run. If it does, you are in good shape and can evaluate re-writing your process to run under 70.........


------------
Kevin Whittenberger at 3/30/99 8:35:08 AM

I am having an interesting problem. Under 6.5, we used a trigger to call a stored procedure that added a task to SQL Executive. We did this for over a year with no problems. Now, under 7.0, we're getting the error message:

"Server: Msg 287, Level 16, State 2, Procedure sp_verify_jobstep, Line 238
The CREATE TABLE statement is not allowed within a trigger."

Sure enough, inside of the sp_verify_jobstep, its creating a temp table. Now, I know that this is supposedly disallowed, however, it is discussed in the books on-line. Also, I find it hard to believe that MS would make it impossible to add jobs from a trigger, actually, a stored procedure called from a trigger.

I really need some guidance on this, as this is going to cause us to lose a large piece of functionality or at least cause us to have to completely re-write our code.



Thank you,
Kevin Whittenberger
I.B.I.S., Inc.
mailto://[email protected]

Thanks, but no good. This does seem to work if I connect through SQL, but the parent aplication (which I have no control over), still gets this as an error message. I really think this is an oversite by MS as this type of behavior doesn't make sense to me.


------------
Craig at 3/31/99 7:45:35 AM

Try setting sp_dbcmptlevel to 65 on your database and see if the process will run. If it does, you are in good shape and can evaluate re-writing your process to run under 70.........


------------
Kevin Whittenberger at 3/30/99 8:35:08 AM

I am having an interesting problem. Under 6.5, we used a trigger to call a stored procedure that added a task to SQL Executive. We did this for over a year with no problems. Now, under 7.0, we're getting the error message:

"Server: Msg 287, Level 16, State 2, Procedure sp_verify_jobstep, Line 238
The CREATE TABLE statement is not allowed within a trigger."

Sure enough, inside of the sp_verify_jobstep, its creating a temp table. Now, I know that this is supposedly disallowed, however, it is discussed in the books on-line. Also, I find it hard to believe that MS would make it impossible to add jobs from a trigger, actually, a stored procedure called from a trigger.

I really need some guidance on this, as this is going to cause us to lose a large piece of functionality or at least cause us to have to completely re-write our code.



Thank you,
Kevin Whittenberger
I.B.I.S., Inc.
mailto://[email protected]