Up Prev Next
From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: dbd::sybase - autocommit and procs creating temp tables
Date: Nov 7 2000 6:09PM
Matthew E Studley writes:
> Hi all
> a quick question.
> I have written a stored proc that creates a temporary table, loads this
> with a very complex query, and then performs a number of analyses
> against this data.
> I'd much prefer to do it this way, rather than have to do the analytical
> queries against the real tables every time... performance overhead.
> This is to plug into an application in which we have dbd::sybase
> connections with autocommit = 0.
> result : problem.
> text=The 'CREATE TABLE' command is not allowed within a multi-statement
> transaction in the 'tempdb' database.
> Now, I realise that we could temporarily set autocommit = 1 while we
> execute this proc. Is there any reason why this should have a knock-on
> effct to the rest of our application? I suspect not, but thought I'd
> because I'm lazy and don't want to rewrite my proc.
You have a couple of solutions.
Assuming you are using Sybase ASE 11.x or later, you can set the
syb_chained_txn attribute to TRUE. This will allow you to create
temporary tables while autocommit is off (however, if your app or
stored procs use transactions internally then this won't work).
You can set the 'ddl in tran' option to TRUE (see sp_dboption)
although that *is* a performance issue.
You can turn ON AutoCommit while you run your proc - that should have
no negative effect, unless there are pending transactions/operations
that depend on the results of your stored proc.
In any case, if your proc is only an analysis proc it makes NO sense
to run it with AutoCommit off (in my most humble opinion :-)
Michael Peppler - Data Migrations Inc. - email@example.com
http://www.mbay.net/~mpeppler - firstname.lastname@example.org - AIM MPpplr
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: email@example.com