Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Triggers, Stored Procedures, and Temp Tables

7 posts in Windows NT Last posting was on 2000-07-28 18:38:07.0Z
Gary Huntress Posted on 2000-07-20 13:38:02.0Z
From: "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil>
Subject: Triggers, Stored Procedures, and Temp Tables
Date: Thu, 20 Jul 2000 09:38:02 -0400
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <pB1u8El8$GA.294@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: HUNTRESSG.NPT.NUWC.NAVY.MIL 129.190.71.92
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1845
Article PK: 1089331

We recently migrated from ASE 11 to 12 and have experienced a few problem
(not unexpected, we probably have 1000 stored procedures and a lot of legacy
code). A co-worker is attempting to debug a collection of code and has made
the following assertion based on experimentation with his code:

"In ASE 11.9.2 a trigger can call a stored procedure that creates a
temporary table, In ASE 12 a trigger cannot call a stored procedure that
creates a temp table"

Could this be true? He's been unable to back this up with any official
Sybase documentation.

Regards,

Gary H.


Rob Verschoor Posted on 2000-07-20 15:52:26.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <pB1u8El8$GA.294@forums.sybase.com>
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Thu, 20 Jul 2000 17:52:26 +0200
Lines: 31
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <6J2rURm8$GA.259@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0406.pvu.euronet.nl 194.134.165.151
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1844
Article PK: 1089328

This sounds odd. Could you post example code that show this behaviour
?

Rob V.

"Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote in
message news:pB1u8El8$GA.294@forums.sybase.com...
> We recently migrated from ASE 11 to 12 and have experienced a few
problem
> (not unexpected, we probably have 1000 stored procedures and a lot
of legacy
> code). A co-worker is attempting to debug a collection of code and
has made
> the following assertion based on experimentation with his code:
>
> "In ASE 11.9.2 a trigger can call a stored procedure that creates a
> temporary table, In ASE 12 a trigger cannot call a stored procedure
that
> creates a temp table"
>
> Could this be true? He's been unable to back this up with any
official
> Sybase documentation.
>
> Regards,
>
> Gary H.
>
>


Gary Huntress Posted on 2000-07-20 18:32:19.0Z
From: "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil>
References: <pB1u8El8$GA.294@forums.sybase.com> <6J2rURm8$GA.259@forums.sybase.com>
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Thu, 20 Jul 2000 14:32:19 -0400
Lines: 61
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <rMWO3pn8$GA.260@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: HUNTRESSG.NPT.NUWC.NAVY.MIL 129.190.71.92
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1843
Article PK: 1089329

Ok, I got a bit more info from the developer. The error message he is
getting is:

"The 'CREATE TABLE' command is not allowed within a multi-statement
transaction in the 'tempdb' database"

In his stored procedure he commented out the following code in order to get
it to work:

/*
insert #dd250(dd_250_date)
select s.dd250_hist from cmp.dbo.serial_history s, cmp.dbo.aur a
where a.tail_no = @tail_no
and s.tail_no = a.tail_no
and s.current_part_no = a.part_no
and convert(char(12),s.dd250_hist,112) >
convert(char(12),@induction_date,112) */

/* select @dd250_date = min(dd_250_date) from #dd250 */


Although we're working again, I'd like to understand the problem more
thoroughly. If you have any insight I'd like to hear it.


Gary H.

"Rob Verschoor" <rob@sypron.nl> wrote in message
news:6J2rURm8$GA.259@forums.sybase.com...
> This sounds odd. Could you post example code that show this behaviour
> ?
>
> Rob V.
>
> "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote in
> message news:pB1u8El8$GA.294@forums.sybase.com...
> > We recently migrated from ASE 11 to 12 and have experienced a few
> problem
> > (not unexpected, we probably have 1000 stored procedures and a lot
> of legacy
> > code). A co-worker is attempting to debug a collection of code and
> has made
> > the following assertion based on experimentation with his code:
> >
> > "In ASE 11.9.2 a trigger can call a stored procedure that creates a
> > temporary table, In ASE 12 a trigger cannot call a stored procedure
> that
> > creates a temp table"
> >
> > Could this be true? He's been unable to back this up with any
> official
> > Sybase documentation.
> >
> > Regards,
> >
> > Gary H.
> >
> >
>
>


jim.sleeman Posted on 2000-07-28 18:36:35.0Z
From: jim.sleeman@iff.com
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Fri, 28 Jul 2000 18:36:35 GMT
Message-ID: <3981d24f.1385118254@forums.sybase.com>
References: <pB1u8El8$GA.294@forums.sybase.com> <6J2rURm8$GA.259@forums.sybase.com> <rMWO3pn8$GA.260@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
Newsgroups: sybase.public.sqlserver.nt
Lines: 53
NNTP-Posting-Host: isinfw01.iff.com 208.28.230.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1807
Article PK: 1089276

Sounds like you need to set 'ddl in tran' to true for your database
and maybe tempdb

Regards,
Jim Sleeman

>On Thu, 20 Jul 2000 14:32:19 -0400, "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote:

>Ok, I got a bit more info from the developer. The error message he is
>getting is:
>
>"The 'CREATE TABLE' command is not allowed within a multi-statement
>transaction in the 'tempdb' database"
>
>In his stored procedure he commented out the following code in order to get
>it to work:
>
>/*
> insert #dd250(dd_250_date)
> select s.dd250_hist from cmp.dbo.serial_history s, cmp.dbo.aur a
> where a.tail_no = @tail_no
> and s.tail_no = a.tail_no
> and s.current_part_no = a.part_no
> and convert(char(12),s.dd250_hist,112) >
> convert(char(12),@induction_date,112) */
>
> /* select @dd250_date = min(dd_250_date) from #dd250 */
>
>
>Although we're working again, I'd like to understand the problem more
>thoroughly. If you have any insight I'd like to hear it.
>
>
>Gary H.
>"Rob Verschoor" <rob@sypron.nl> wrote in message
>news:6J2rURm8$GA.259@forums.sybase.com...
>> This sounds odd. Could you post example code that show this behaviour
>> ?
>>
>> Rob V.
>>
>> "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote in
>> message news:pB1u8El8$GA.294@forums.sybase.com...
>> > We recently migrated from ASE 11 to 12 and have experienced a few
>> problem
>> > (not unexpected, we probably have 1000 stored procedures and a lot
>> of legacy
>> > code). A co-worker is attempting to debug a collection of code and
>> has made
>> > the following assertion based on experimentation with his code:
>> >
>> > "In ASE 11.9.2 a trigger can call a stored procedure that creates a
>> > temporary table, In ASE 12 a trigger cannot call a stored procedure
>> that
>> > creates a temp table"
>> >
>> > Could this be true? He's been unable to back this up with any
>> official
>> > Sybase documentation.
>> >
>> > Regards,
>> >
>> > Gary H.
>> >
>> >
>>
>>
>
>


jim.sleeman Posted on 2000-07-28 18:11:10.0Z
From: jim.sleeman@iff.com
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Fri, 28 Jul 2000 18:11:10 GMT
Message-ID: <3981cc3b.1383562177@forums.sybase.com>
References: <pB1u8El8$GA.294@forums.sybase.com> <6J2rURm8$GA.259@forums.sybase.com> <rMWO3pn8$GA.260@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
Newsgroups: sybase.public.sqlserver.nt
Lines: 3
NNTP-Posting-Host: isinfw01.iff.com 208.28.230.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1809
Article PK: 1089277

Sound like you need to set 'ddl in tran' to true in your database and
maybe tempdb

Jim Sleeman


jim.sleeman Posted on 2000-07-28 18:38:07.0Z
From: jim.sleeman@iff.com
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Fri, 28 Jul 2000 18:38:07 GMT
Message-ID: <3981d30c.1385306755@forums.sybase.com>
References: <pB1u8El8$GA.294@forums.sybase.com> <6J2rURm8$GA.259@forums.sybase.com> <rMWO3pn8$GA.260@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
Newsgroups: sybase.public.sqlserver.nt
Lines: 50
NNTP-Posting-Host: isinfw01.iff.com 208.28.230.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1806
Article PK: 1089278

On Thu, 20 Jul 2000 14:32:19 -0400, "Gary Huntress"

<huntressgb@code831.npt.nuwc.navy.mil> wrote:

>Ok, I got a bit more info from the developer. The error message he is
>getting is:
>
>"The 'CREATE TABLE' command is not allowed within a multi-statement
>transaction in the 'tempdb' database"
>
>In his stored procedure he commented out the following code in order to get
>it to work:
>
>/*
> insert #dd250(dd_250_date)
> select s.dd250_hist from cmp.dbo.serial_history s, cmp.dbo.aur a
> where a.tail_no = @tail_no
> and s.tail_no = a.tail_no
> and s.current_part_no = a.part_no
> and convert(char(12),s.dd250_hist,112) >
> convert(char(12),@induction_date,112) */
>
> /* select @dd250_date = min(dd_250_date) from #dd250 */
>
>
>Although we're working again, I'd like to understand the problem more
>thoroughly. If you have any insight I'd like to hear it.
>
>
>Gary H.
>"Rob Verschoor" <rob@sypron.nl> wrote in message
>news:6J2rURm8$GA.259@forums.sybase.com...
>> This sounds odd. Could you post example code that show this behaviour
>> ?
>>
>> Rob V.
>>
>> "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote in
>> message news:pB1u8El8$GA.294@forums.sybase.com...
>> > We recently migrated from ASE 11 to 12 and have experienced a few
>> problem
>> > (not unexpected, we probably have 1000 stored procedures and a lot
>> of legacy
>> > code). A co-worker is attempting to debug a collection of code and
>> has made
>> > the following assertion based on experimentation with his code:
>> >
>> > "In ASE 11.9.2 a trigger can call a stored procedure that creates a
>> > temporary table, In ASE 12 a trigger cannot call a stored procedure
>> that
>> > creates a temp table"
>> >
>> > Could this be true? He's been unable to back this up with any
>> official
>> > Sybase documentation.
>> >
>> > Regards,
>> >
>> > Gary H.
>> >
>> >
>>
>>
>
>


jim.sleeman Posted on 2000-07-28 18:21:48.0Z
From: jim.sleeman@iff.com
Subject: Re: Triggers, Stored Procedures, and Temp Tables
Date: Fri, 28 Jul 2000 18:21:48 GMT
Message-ID: <3981cecf.1384222486@forums.sybase.com>
References: <pB1u8El8$GA.294@forums.sybase.com> <6J2rURm8$GA.259@forums.sybase.com> <rMWO3pn8$GA.260@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
Newsgroups: sybase.public.sqlserver.nt
Lines: 53
NNTP-Posting-Host: isinfw01.iff.com 208.28.230.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1808
Article PK: 1089279

Sounds like you need to set 'ddl in tran' to true in your database and
maybe tempdb.

Regards,
Jim Sleeman

>On Thu, 20 Jul 2000 14:32:19 -0400, "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote:

>Ok, I got a bit more info from the developer. The error message he is
>getting is:
>
>"The 'CREATE TABLE' command is not allowed within a multi-statement
>transaction in the 'tempdb' database"
>
>In his stored procedure he commented out the following code in order to get
>it to work:
>
>/*
> insert #dd250(dd_250_date)
> select s.dd250_hist from cmp.dbo.serial_history s, cmp.dbo.aur a
> where a.tail_no = @tail_no
> and s.tail_no = a.tail_no
> and s.current_part_no = a.part_no
> and convert(char(12),s.dd250_hist,112) >
> convert(char(12),@induction_date,112) */
>
> /* select @dd250_date = min(dd_250_date) from #dd250 */
>
>
>Although we're working again, I'd like to understand the problem more
>thoroughly. If you have any insight I'd like to hear it.
>
>
>Gary H.
>"Rob Verschoor" <rob@sypron.nl> wrote in message
>news:6J2rURm8$GA.259@forums.sybase.com...
>> This sounds odd. Could you post example code that show this behaviour
>> ?
>>
>> Rob V.
>>
>> "Gary Huntress" <huntressgb@code831.npt.nuwc.navy.mil> wrote in
>> message news:pB1u8El8$GA.294@forums.sybase.com...
>> > We recently migrated from ASE 11 to 12 and have experienced a few
>> problem
>> > (not unexpected, we probably have 1000 stored procedures and a lot
>> of legacy
>> > code). A co-worker is attempting to debug a collection of code and
>> has made
>> > the following assertion based on experimentation with his code:
>> >
>> > "In ASE 11.9.2 a trigger can call a stored procedure that creates a
>> > temporary table, In ASE 12 a trigger cannot call a stored procedure
>> that
>> > creates a temp table"
>> >
>> > Could this be true? He's been unable to back this up with any
>> official
>> > Sybase documentation.
>> >
>> > Regards,
>> >
>> > Gary H.
>> >
>> >
>>
>>
>
>