asked on

DBArtisan 8.6.1 - Issue creating and accessing #temp tables

Hi
I have been using DBArtisan 8.1.1 for sometime now and I could run the statements that I copied in the Code section one after the other (meaning I did not need to execute the 2 statements together as a batch)

Now I am evaluating DBArtisan 8.6.1 with Sybase Open Client 15 (connecting to Sybase 12.5.3) and I can't run these 2 statements separately any longer. When I run the 2nd statement it tells me #T is not found. Both the statements would execute as a batch.

It should be something to do with the session settings. In the 8.1.1 version, if I tried to create another #T table, it would complain that there is another with the same name. But in 8.6.2, it just goes through.

Thanks.

CREATE TABLE #T ( ref_num INT )

INSERT INTO #T ( ref_num ) VALUES ( 1 )

Open in new window

alpmoon

You can change DBArtisan's behavior by using options. Options is under File on the top menu bar. Click it and then collapse ISQL and click Sybase on options window. You will see "Auto-commit changes". I guess it is 'on' now, which means every time you execute a statement it is commited automatically. Just make it 'off'

pganeshkumar

ASKER

Hi

I tried changing the auto commit to False but it throws an error with the first statement itself:

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

Thanks

Joe Woodhouse

This isn't about transaction management or autocommit - the previous DBArtisan behaviour was apparently to treat each statement as an implicit batch, i.e. add an implicit "go" after each statement.

The newer version seems to have stopped doing that. It might be another setting in the options to revert to the old behaviour. This is entirely a DBArtisan issue, though - ASE just does what it's asked to do, and can't control how client tools format up their command batches.

alpmoon

Joe,

You are right. I didn't realize that they started issuing a 'begin tran' in all sessions in the new release. I thought only default behaviour was changed.

ASKER CERTIFIED SOLUTION

Joe Woodhouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pganeshkumar

ASKER

Joe

You are right in that adding the GO statement (which is the batch delimiter as setup in the Options) resolved the issue.

Alpmoon

Thanks for your suggestions also.

Thanks a lot.