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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bill Lucas (Homebrew42_at_hotmail.ccom)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 10:53:55 -0500

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: Cant call method "Sql"... need help
    ... If this script runs fine from command prompt, ... connection to database. ... While executing script from web, it executes with lower permission, ... Try catching error at the time of opening connection to database... ...
    (comp.lang.perl.misc)
  • Problems running SQL scripts outside Management Studio
    ... I have a script that populates some tables in a database. ... When the scripts are executed via Management Studio the values are loaded ... reading each line of the text file and executing at each "GO" the ...
    (microsoft.public.sqlserver.tools)
  • Re: Cant call method "Sql"... need help
    ... I have a problem CGI script. ... connection to database. ... While executing script from web, it executes with lower permission, ...
    (comp.lang.perl.misc)
  • Re: dbms_sql.parse Question
    ... I would'nt want to add incorrect DDLS into the script. ... is then executed against a *different database*, ... because your DDL is not valid there. ... a user-invokable way of parsing DDL without executing it. ...
    (comp.databases.oracle.server)
  • [NEWS] IBM Informix Web DataBlade Local Root by Design
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... that ease development of "intelligent", interactive, Web-enabled database ... person who has access to change the Perl script. ...
    (Securiteam)