Re: UPDATE and ALTER in transactions do not mix


Jonathan S via AccessMonster.com wrote:
> Can anyone verify that UPDATE followed by an ALTER statement, inside of a
> transaction, would cause the "table in use by another user" error

Here's my test (ADO Connection object):

m_Conn.Open
m_Conn.BeginTrans
m_Conn.Execute "ALTER TABLE Test ADD blah INTEGER"
m_Conn.Execute "UPDATE Test SET earnings_amt = 12"
m_Conn.RollbackTrans
-- no errors, schema and data remain unchanged

m_Conn.Open
m_Conn.BeginTrans
m_Conn.Execute "ALTER TABLE Test ADD blah INTEGER"
m_Conn.Execute "UPDATE Test SET earnings_amt = 12"
m_Conn.CommitTrans
-- no errors, schema and data have been changed

.

Relevant Pages

  • Re: Complete Neophyte Question(s)
    ... Correct, ALTER means ALTER anything in the schema (tables, ... GRANT ALTER ON SCHEMA::DBO TO MyUser ... All of our objects in this database are ...
    (microsoft.public.sqlserver.security)
  • Re: Alter table command vs table copy/rename
    ... I guess the bigger question is, why are they changing multiple columns ... What is wrong with the schema ... some debate over here regarding alter table vs. copy/rename. ... table statement rather than the copy/rename method. ...
    (microsoft.public.sqlserver.programming)
  • Re: Complete Neophyte Question(s)
    ... granting alter to the schema seems to allow a ... GRANT ALTER ON SCHEMA::DBO TO MyUser ... All of our objects in this database are ...
    (microsoft.public.sqlserver.security)
  • Re: Create Procedure Permission ONLY
    ... Schema dbo owns all objects. ... Developer's should not create or alter tables. ... CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//' ...
    (comp.databases.ms-sqlserver)
  • Re: Create Procedure Permission ONLY
    ... Schema dbo owns all objects. ... Developer's should not create or alter tables. ... CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//' ...
    (comp.databases.ms-sqlserver)
Loading