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.

ALTER TABLE error

2 posts in Windows NT Last posting was on 1999-12-07 06:14:26.0Z
Jim MacMillan Posted on 1999-12-06 21:58:38.0Z
Message-ID: <384C318E.ECB8437D@bah.com>
Date: Mon, 06 Dec 1999 16:58:38 -0500
From: Jim MacMillan <macmillan_james@bah.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en,pdf
MIME-Version: 1.0
Subject: ALTER TABLE error
Content-Type: multipart/mixed; boundary="------------EF36D04446A18E83C06014B0"
Newsgroups: sybase.public.sqlserver.nt
Lines: 42
NNTP-Posting-Host: 156.80.170.47
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2877
Article PK: 1090315

I hope this is an easy one -

I've created a fairly long script to update the database for a web
application I'm working on. The script is pretty straight-forward using
basic SQL and T-SQL. Using SQL Advantage, the script runs error free
until it reaches a section of consecutive ALTER TABLE statements. I am
basically just adding a column to several tables here. If I pull this
chunk of the script out and run it by itself it runs fine. Here is what
the SQL looks like:

ALTER TABLE dbo.xSelectionRank ADD xSelectionText text NULL
go

ALTER TABLE dbo.xControlRank ADD xControlRankText text NULL
go

ALTER TABLE dbo.xEvaluationRank ADD xEvaluationText text NULL
go

The error message tells me that "The ALTER TABLE command is not allowed
withina multi-statement transaction."

Any ideas?

Thanks


Download VCard macmillan_james.vcf


Mark A. Parsons Posted on 1999-12-07 06:14:26.0Z
Message-ID: <384CA5C2.EDD01C1F@compuserve.com>
Date: Tue, 07 Dec 1999 19:14:26 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: ALTER TABLE error
References: <384C318E.ECB8437D@bah.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 29
NNTP-Posting-Host: p439.ipa1-n8-16.iconz.net.nz 210.48.25.183
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2875
Article PK: 1090311


> The error message tells me that "The ALTER TABLE command is not allowed
> withina multi-statement transaction."

Check to see if you missed a 'go' command after one of your ALTER TABLE
commands ... or maybe you have another command right next (above/below)
to your ALTER TABLE command (between 'go' commands').

What the error message is telling you is that the ALTER TABLE command
must be by itself between 2 'go' commands. The 'go' command designates
the end, and subsequent execution, of a batch. If you only have one
command between 2 'go' commands this would be called a single-statement
transaction (or batch) ... 2 or more commands between 2 'go' commands is
referred to as a multi-statement transaction (or batch) ... the ALTER
TABLE command must be in a single-statement transaction (or batch).

Sybase's use of the term 'transaction' in this case is somewhat
misleading since it's not really talking about 'transaction' in terms of
a unit of work ... it's really talking about the batch (or group) of
commands that get executed when you issue the next 'go'.

--
Mark Parsons
Iron Horse Consulting, Ltd.