Re: Drop column in transaction

On Tue, 25 Apr 2006 03:32:18 +0200, "Daniel Mauric" <danny at neobee dot
net> wrote:

I perform a DROP COLUMN and it works fine. Yet if I do it while in
transaction the column doesn't get dropped and I get no exception. Any ideas
as to why that would happen ? I'm using ADO & MSSQL 2k

Hi Danny,

Are you sure that the transaction is being committed before you drop the
connection? If the connection is broken while a transaction is open, SQL
Server will automatically rollback a transaction.

Other than that, this should not happen. Run the below repro in QA to
see for yoursellf how a column *can* be dropped within a transaction,
but is directly restored (with no exception) if the transaction is
rolled back rather than committed.

CREATE TABLE test
(col1 int NOT NULL,
col2 int NOT NULL)
INSERT INTO test
VALUES (1, 2)
go
BEGIN TRANSACTION
ALTER TABLE test
DROP COLUMN col2
SELECT * FROM test
ROLLBACK TRANSACTION
SELECT * FROM test
go
BEGIN TRANSACTION
ALTER TABLE test
DROP COLUMN col2
SELECT * FROM test
COMMIT TRANSACTION
SELECT * FROM test
go
DROP TABLE test
go


--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • BEGIN TRANSACTION problem
    ... there was no BEGIN TRANSACTION. ... The test prior to the exception is the first to execute the parent ... The is the exception the unit test is expecting. ... Rollback statement faile with "The ROLLBACK TRANSACTION request has no ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: HELP on New request is not allowed to start because [1264822]
    ... allowed to start because it should come with valid transaction descriptor. ... System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean ... TRXOrders.CLogin.GetTradexUserIDFromQube(String QubeUserID, String GroupCode) ... This error always occurs when we issue the command to SQL Server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CMutex /CEvent (multiple threads)
    ... deals with exception detection. ...  If your function does not handle an exception in Java, ... designer did not understand what a Mutex was and that the notion that Lock could return ... ...roll back transaction ...
    (microsoft.public.vc.mfc)
  • RE: HELP on New request is not allowed to start because [1264822]
    ... "Jeff Glenn" wrote: ... We have posted a simple code reproducing the behavior to Microsoft. ... application doen't raise any exception during a distributed transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Behavior of Connection.commit()
    ... You have to remember that Sql Server does not understand the concept of a ... we have to fake this with "Begin transaction" ... exception you probably have data corruption. ... getting this error with our driver and filed it as a bug. ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading