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.

Stored Proc fails when creating temp tables.

3 posts in General Discussion (old) Last posting was on 2000-03-23 00:32:55.0Z
Tim Nesham Posted on 2000-03-22 21:51:19.0Z
Newsgroups: sybase.public.easerver
From: "Tim Nesham" <tim.nesham@born.com>
Subject: Stored Proc fails when creating temp tables.
Date: Wed, 22 Mar 2000 15:51:19 -0600
Lines: 15
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
X-Original-NNTP-Posting-Host: 161.49.6.89
Organization: Quad/Graphics,Inc.
NNTP-Posting-Host: dns2.qgraph.com 206.158.124.2
Message-ID: <347_38d94059@news.qgraph.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com!news.qgraph.com!161.49.6.89
Xref: forums-1-dub sybase.public.easerver:25877
Article PK: 155911

Someone said to set "Transaction Not Supported" to fix this problem with the
HTMLDW component:

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

But what about a datastore inside a component? I get the same error even if
I say the component doen not support transactions.


TIA,

Tim


Jim O'Neil [Sybase] Posted on 2000-03-22 22:20:12.0Z
Newsgroups: sybase.public.easerver
Date: Wed, 22 Mar 2000 17:20:12 -0500
From: "Jim O'Neil [Sybase]" <joneil@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.7 [en]C-CCK-MCD (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Stored Proc fails when creating temp tables.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 35
NNTP-Posting-Host: joneil-nt.sybase.com 204.167.42.111
Message-ID: <347_38D9471C.585DA361@sybase.com>
References: <347_38d94059@news.qgraph.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.easerver:25873
Article PK: 155907


Tim Nesham wrote:

> Someone said to set "Transaction Not Supported" to fix this problem with the
> HTMLDW component:
>
> The 'CREATE TABLE' command is not allowed within a multi-statement
> transaction in the 'tempdb' database.
>
> But what about a datastore inside a component? I get the same error even if
> I say the component doen not support transactions.
>
> TIA,
>
> Tim

This is a PB component right? If so, when you say the component doesn't support
transactions, PB will actually inforce whatever setting of AutoCommit you have.
So, in the default scenario, AutoCommit would be FALSE which would cause the
same error message. Try setting AutoCommit to TRUE before/after connecting.

Note that I have been corrected since the last time I posted similar
information. Specifically, I was previously under the impression AutoCommit was
ALWAYS ignored in Jaguar, but it's only ignored if the component is
participating in a transaction. I suppose that could make for some interesting
behavior depending on how you've set up transactions, but the goal - at least
for PB components - was to make migration from technologies like Distributed
PowerBuilder require as little coding as possible to, and I think this is a
design decision made with that objective in mind.


--
Jim O'Neil
Senior Technical Support Engineer
Sybase, Inc


Dave Wolf [Sybase] Posted on 2000-03-23 00:32:55.0Z
Newsgroups: sybase.public.easerver
From: "Dave Wolf [Sybase]" <dwolf@sybase.com>
Subject: Re: Stored Proc fails when creating temp tables.
Date: Wed, 22 Mar 2000 19:32:55 -0500
Lines: 68
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
NNTP-Posting-Host: vpn-eme-038.sybase.com 130.214.8.38
Message-ID: <347_#b$e0$Fl$GA.285@forums.sybase.com>
References: <347_38d94059@news.qgraph.com> <347_38D9471C.585DA361@sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.easerver:25857
Article PK: 155892

You have only 2 choices

1) Mark the component as Transaction Not Supported
2) Set the DB to allow DDL in Tx.

use master
go
sp_dboption "mydatabase","ddl in tran","true"
go
use mydatabase
go
checkpoint
go

Dave Wolf
Internet Applications Division

"Jim O'Neil [Sybase]" <joneil@sybase.com> wrote in message
news:38D9471C.585DA361@sybase.com...
> Tim Nesham wrote:
>
> > Someone said to set "Transaction Not Supported" to fix this problem with
the
> > HTMLDW component:
> >
> > The 'CREATE TABLE' command is not allowed within a multi-statement
> > transaction in the 'tempdb' database.
> >
> > But what about a datastore inside a component? I get the same error even
if
> > I say the component doen not support transactions.
> >
> > TIA,
> >
> > Tim
>
> This is a PB component right? If so, when you say the component doesn't
support
> transactions, PB will actually inforce whatever setting of AutoCommit you
have.
> So, in the default scenario, AutoCommit would be FALSE which would cause
the
> same error message. Try setting AutoCommit to TRUE before/after
connecting.
>
> Note that I have been corrected since the last time I posted similar
> information. Specifically, I was previously under the impression
AutoCommit was
> ALWAYS ignored in Jaguar, but it's only ignored if the component is
> participating in a transaction. I suppose that could make for some
interesting
> behavior depending on how you've set up transactions, but the goal - at
least
> for PB components - was to make migration from technologies like
Distributed
> PowerBuilder require as little coding as possible to, and I think this is
a
> design decision made with that objective in mind.
>
>
> --
> Jim O'Neil
> Senior Technical Support Engineer
> Sybase, Inc
>
>