Re: statement not allowed within multi-statement transaction (Long)

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 16:58:01 -0000

Do you have SET IMPLICIT_TRANSACTIONS ON? That would explain this behaviour. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Bill Lucas" <Homebrew42@hotmail.ccom> wrote in message
news:A-6dnbSzJICNwd7dRVn-hA@telcove.net...
> I know what the error is telling me, that is why I tried wrapping the call
> to sp_Detach DB with the following check.
>
> IF @@TRANCOUNT = 0
>
>     BEGIN
>
>         sp_AttachDB args...
>
>     END
>
> This syntax still executed sp_DetachDB but it still produced the error.
> When debugging the procedure through QA it also showed the @@Trancount
value
> to be 0 but when stepping inside sp_DetachDB it created a transaction that
> was causing the issue.  So if I do have an open transaction I can't figure
> out how to determine it and how to commit it.
>
> When I arbitrarily issue a COMMIT TRAN Right before making the call I ge
> tthe Error Message
> The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
>
> So that doesn't work either.  Any ideas of where this open tran may be
> hiding and how I can find it to commit or roll it back before I attempt to
> detach the database?
>
> Here is the syntax for bsp_DetachDB:
>
>
/***************************************************************************
> **********************************************
> Name: bsp_DetachDB     Author: Bill Lucas
> Date: 02/20/2004     Version: 1.00.001
>
> Description:  Detaches the DB Based on attribute settings
> Paramaters:
> Name       Direction Data Type   Description
> ----      --------- ----------   ------------
> NONE
>
> Custom Return Codes:
>
> Return Code  Meaning
> 0    Success
> 100    Database does not exist in sysdatabases can not dettach
> All Others  Error Retrived from sp_Detach_DB
>
> Revisions:
>
>
>
****************************************************************************
> *********************************************/
>
> CREATE PROCEDURE bsp_DetachDB
>
> --WITH ENCRYPTION
> AS SET NOCOUNT ON
> SET XACT_ABORT ON
> -- Declare Variables
>
> DECLARE @iError  int,
>   @vcDBName varchar(128),
>   @spid   smallint, -- The Process ID to be removed from the calman40 DB
> used in the while loop below.
>   @sqlStr  varchar(15),
>   @DropSQL nvarchar(255)
>
> -- Initalize Variables
>
> SET @iError  = 0
> SET @vcDBName = dbo.bfn_GetAttributeValue('DBName')
>
> --Verify database Exiists for detach
>
> IF NOT EXISTS(SELECT * FROM master..sysdatabases WHERE Name = @vcDBName)
>
>  BEGIN
>
>   RETURN 100
>
>  END
>
> -- Kill any active connections in the DB to be dropped
>
> IF EXISTS (SELECT * FROM master..sysProcesses WHERE DBID = (SELECT DBID
FROM
> master..sysdatabases WHERE Name = @vcDBName))
>
>  BEGIN
>
>   CREATE TABLE #tmpUsers( spid   smallint  NOT NULL PRIMARY KEY,
>         ecid  smallint  NOT NULL,
>         status  nchar(30)  NOT NULL,
>         LoginName nchar(128) NOT NULL,
>         HostName nchar(128) NOT NULL,
>         blk   char(5)  NOT NULL,
>         dbName  nchar(128) NULL,
>         cmd   nchar(16) NOT NULL)
>
>   INSERT INTO #tmpUsers
>   EXEC sp_Who
>
>   -- Remove All Proccesses that do not belong to Calibration Manager 4.0
> database
>
>   DELETE FROM #tmpUsers Where COALESCE(DBName, 'N/A') <> @vcDBName
>
>   -- Loop through remaining proccesses and kill them so we can detach the
> database
>
>    WHILE EXISTS(SELECT * FROM #tmpUsers)
>    BEGIN
>
>     SELECT TOP 1 @spid = spid FROM #tmpUsers ORDER BY spid
>
>     SELECT @sqlStr = 'KILL ' + CAST(@spID as varchar(5))
>
>     EXEC(@sqlStr)
>
>     DELETE FROM #tmpUsers WHERE spid = @spid
>
>    END
>
>   DROP TABLE #tmpUsers
>
>  END
>
> -- we now can detach the Database
> EXEC sp_Detach_DB @vcDBName
>
>
>
> SET @iError = @@Error
>
> RETURN @iError
>
> GO
>
> Thanks for the response though...  It may be that easy but not from what I
> have been finding.
>
> "Wayne Snyder" <wsnyder@computeredservices.com> wrote in message
> news:uK8EmM6$DHA.3352@TK2MSFTNGP09.phx.gbl...
> > The error is telling you that you are in transaction state, and are
trying
> > to do sp_detach_db....
> > perhaps you got an error and did not rollback the tran. or failed to
> commit
> > for some reason
> >
> > -- 
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Bill Lucas" <Homebrew42@hotmail.ccom> wrote in message
> > news:voidnQpGVoFYy97d4p2dnA@telcove.net...
> > > Server: Msg 226, Level 16, State 1, Procedure sp_detach_db, Line 32
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]SP_DETACH_DB statement
> not
> > > allowed within multi-statement transaction.
> > >
> > > I have a sample script that is available on request.  I am not posting
> it
> > > here because I do not want to attach the file and the script is
several
> k
> > in
> > > size.  If you would like to see the script to help figure out what is
> > going
> > > on please email me at WCLucas @at@ coolblue _dot_ com and I will send
> you
> > > the script that can be used to recreate the issue as well as what you
> will
> > > need on your machine to allow the script to work.  I will be leaving
the
> > > office today after 3:00 and be returning Wed. morning.  If anyone
emails
> > me
> > > for the files between those times I will reply when I return with the
> > > information
> > >
> > > Problem:
> > >
> > > I am getting the above error message when executing a stored
procedure.
> > > This stored procedure will be used to run update scripts against a
> release
> > > database and then create different versions of the database to meet
our
> > > product needs.  I have read into the reasons for this error and I
still
> am
> > a
> > > little confused.  Here's why.
> > >
> > > The script performs the following tasks.
> > >
> > > 1 Checks to see if the Target DB is attached tot he server
> > > 2. Drops the Database from Target Server (This is where the above
error
> > > message comes in)
> > > 3. Copies the Source Database mdf and ldf from a predefined storage
> > location
> > > 4. Attaches the Database to the server (This works using very similar
> > > technique)
> > > 5. Runs the update (SR) Script against the source database
> > > 6. Detaches the database and copies the upgraded mdf and ldf files to
> the
> > > Source Safe working folder
> > > 7. Reattaches the database and runs update statements against the DB
to
> > > create the different versions of the Database.  i.e Standard,
> Enterprise,
> > > Demo, msde, etc.
> > > 8. Detaches the DB and copies it to the source safe working directory
> > > 9. Repeats step 7 and 8 for all version of the database
> > > 10. Exists after all databases have been created leaving the last
> version
> > of
> > > the database detached from the server and ready for the next build
> > >
> > > In step 2 above I receive the error when calling a custom wrapper for
> > > sp_DetachDB.  In the custom wrapper I gather all info needed for the
> call
> > > and execute it.  I know the error relates to ddl statements being
> executed
> > > from within a transaction, but the issue is I can't figure out where
the
> > > open transaction is.  When I execute the wrapper stand alone the
> procedure
> > > works and detaches the database.  however whenever I call the
procedure
> > from
> > > the proc that controls the flow of the upgrade process I am receiving
> the
> > > error.
> > >
> > > Here is the block of code that I am working with.
> > >
> > > -- First check to make sure the calman 40 database doesn't exist and
if
> it
> > > does detach it.
> > >
> > > IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = 'Calman40')
> > >
> > >  BEGIN
> > >
> > >  -- EXEC @iError = bsp_EXECUTESQL '(Local)', ' BuildDB', 'EXEC
> > bsp_DetachDB
> > > '  -- This is my workaround and it works by calling out to osql from
> > > sp_CMDShell
> > >
> > > EXEC bsp_DetachDB -- This is the way I would like to do it and it is
> > > producing the error
> > >
> > >   IF @iError <> 0
> > >
> > >    BEGIN
> > >
> > >     RETURN @iError
> > >
> > >    END
> > >
> > >  END
> > >
> > > Inside bsp_DetachDB I have even wrapped the call to sp_DetachDB with a
> IF
> > > @@TRANCOUNT = 0
> > >
> > >     BEGIN
> > >
> > >         EXEC sp_DetachDB args...
> > >
> > >     END
> > >
> > > This still produces the error even though trancount is 0 and it looks
> like
> > > the transaction is started in sp_DetachDB itself.  I have tried
> executing
> > > the procedure using EXEC and sp_EXECUTESQL as well but still receive
the
> > > error.
> > >
> > > I do have a work around for this.  I created a procedure bsp_Execute
SQL
> > > which uses sp_CmdShell and formats an OSQL Command to execute
> bsp_DetachDB
> > > and this method works just fine.  However this is less than desirable,
> but
> > > if I have to I can live with it.
> > >
> > > Now on a final note here is the part that is completely confusing me.
> The
> > > embedded call to reattach the database works just fine using normal
> > syntax.
> > >
> > > When reattaching the database from the flow control procedure I use
this
> > > syntax
> > >
> > > -- Source Files are now in the correct location attach the source DB
> > >
> > > EXEC @iError = bsp_AttachDB
> > >
> > > IF @iError <> 0
> > >
> > >  BEGIN
> > >
> > >   RETURN @iError
> > >
> > >  END
> > >
> > > This works fine, produces no error and is basically the same as
> bsp_Detach
> > > db.  I do not need to use the work around for this that is required to
> > > detach the database.
> > >
> > > I know this is still sketchy on the details and DDL and DML will help
to
> > > solve this issue.  If anyone would like those scripts and details to
> > > reproduce this issue let me know.  I can't explain it and it is
driving
> me
> > > crazy.
> > >
> > > Regards,
> > > Bill
> > >
> > >
> >
> >
>
>

Relevant Pages

  • RE: Foreign Key Violations During Insert in Transaction
    ... parent row into the database, SQL Server generates a new identity value. ... that value does really exist even before you commit the transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... The log cannot be truncated beyond the first open transaction. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ... we can manually issue a checkpoint. ...
    (microsoft.public.sqlserver.server)
  • Re: SQLNCLI is not allowing CREATE DATABASE inside a TRansaction
    ... successfully call CREATE DATABASE. ... CREATE DATABASE statement not allowed within multi-statement transaction. ... open a second connection to SQL Server behind the back. ... I can post that some test program using JDBC ...
    (microsoft.public.data.oledb)
  • Re: Insert Into Without Log
    ... level of logging of logged in the database ... transaction log, however, you can minimize this via setting the database ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: using sql-dmo to detach and reattach a database
    ... you really should consider standard backups. ... They're faster and your users can access the database during the backup. ... Columnist, SQL Server Professional ... Is there a way, through sql-dmo, to detach and then reattach a database from ...
    (microsoft.public.sqlserver.server)