none
Error 3930, The current transaction cannot be committed and cannot support

    Question

  •  

    I am trying to exectue this store procedure and I am getting this error

    Msg 3930, Level 16, State 1, Line 190

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

     

    I am using a linked server "SODIM" and I am trying to execute a distributed transaction between my SQL Server 2005 Express Edition and this linked server SQL Server 2005 standar Edition.

     

    I need to set the isolation level to serializable because I need to block tbl_GuiasDistribuidores table.

     

    I`m executing this SP under sa user and I dont know if a need certanly permissions in the database or in the MS DTC.

     

    I dont know what I`m missing, Could give me some help?

     

     

    This is the sp code:

     

    ALTER PROCEDURE [dbo].[PCN_ASIGNARGUIAS]

    @Planta varchar(15),

    @DB varchar(50)

    AS

    BEGIN

    DECLARE @lvaSentencia VARCHAR(8000)

    set @lvaSentencia = '

    SET XACT_ABORT on

    begin try

    begin transaction

    declare @DisCodigo as varchar(15)

    declare @idProceso as int

    declare @guia as numeric

    declare @idDato as int

    declare @Estado as bit

    declare @GuiaFinal as numeric

    declare @AsignarGuia as varchar(2)

    delete tbl_ErrorRangosDistribuidores

    IF OBJECT_ID('+'''ErrorGuias'''+') IS NOT NULL

    BEGIN

    drop table ErrorGuias

    END

    insert into sodim.' + @DB + '.dbo.Tbl_ArchivosSubidos

    (Ars_Fecha,

    Doc_Codigo,

    Ars_NombreArchivo,

    Ars_NombreArchivoSubido,

    Ars_OrdenProduccion,

    Ars_Requerimiento,

    Ars_OrdenServicio,

    Ars_Ciclo,

    Ars_UsuarioUltimoCambioEfectuado,

    Ars_FechaUltimoCambioEfectuado)

    select Ars_Fecha,

    Doc_Codigo,

    Ars_NombreArchivo,

    Ars_NombreArchivoSubido,

    Ars_OrdenProduccion,

    Ars_Requerimiento,

    Ars_OrdenServicio,

    Ars_Ciclo,

    Ars_UsuarioUltimoCambioEfectuado,

    Ars_FechaUltimoCambioEfectuado

    from tbl_archivossubidos

    SELECT @idProceso=a.Ars_Codigo FROM sodim.' + @DB + '.dbo.Tbl_ArchivosSubidos a, Tbl_ArchivosSubidos b

    where a.Ars_FechaUltimoCambioEfectuado = b.Ars_FechaUltimoCambioEfectuado

    and a.Ars_Fecha = b.Ars_Fecha

    and a.Ars_NombreArchivo = b.Ars_NombreArchivo

    and a.Ars_OrdenProduccion = b.Ars_OrdenProduccion

    and a.Doc_Codigo = b.Doc_Codigo

    declare CursorCodDistribuidor cursor for

    select Dis_Codigo,IndiceOLlaveunica from VisOrdenamientoDatosFinalCiudades

    order by Dis_Codigo

    open CursorCodDistribuidor

    fetch next from CursorCodDistribuidor

    into @DisCodigo,@idDato

    set @Estado = 1

    while @@fetch_status = 0

    begin

    SET TRANSACTION ISOLATION LEVEL Read COMMITTED

    --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    select @Estado = Gdis_Estado from sodim.' + @DB + '.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)

    where

    Dis_codigo = @DisCodigo and plan_Codigo = ' + @Planta + '

    while @Estado = 1

    begin

    select @Estado = Gdis_Estado from sodim.' + @DB + '.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)

    where

    Dis_codigo = @DisCodigo and plan_Codigo = ' + @Planta + '

    end

    update sodim.' + @DB + '.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

    set GDis_Estado = 1

    where

    Dis_codigo = @DisCodigo

    and plan_Codigo = ' + @Planta + '

    select @AsignarGuia = Dis_ImprimirGuia from sodim.' + @DB + '.dbo.tbl_Distribuidores --with(TABLOCKX SERIALIZABLE)

    where Dis_Codigo = @DisCodigo

    if @AsignarGuia = '+'''SI'''+'

    begin

    set @guia = -1

    --consulta si hay guias disponibles en la tabla Tbl_GuiasActivas

    select @guia = GAct_Guia from sodim.' + @DB + '.dbo.Tbl_GuiasActivas

    where dis_Codigo = @DisCodigo

    and plan_Codigo = ' + @Planta + '

    order by GAct_Guia desc

    --si no hay guias disponibles en la tabla Tbl_GuiasActivas, la variable @guia = -1

    if @guia = -1

    begin

    -- consulta el valor de GDis_RangoFinal y lo almacena en la variable @GuiaFinal

    select @GuiaFinal = GDis_RangoFinal from sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    where Dis_codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    -- consulta el valor de la siguiente guia disponible y lo almacena en la variable @guia

    select @guia = convert(numeric,GDis_ultimaguia) + 1 from sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    where dis_Codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    -- si @guia > @GuiaFinal no hay guias disponibles

    if @guia > @GuiaFinal

    BEGIN

    --PRINT '+'''@guia > @GuiaFinal'''+'

    --PRINT @guia

    --PRINT @GuiaFinal

    --consulta el valor de GDis_RangoFinalReserva y lo almacena en la variable @GuiaFinal

    select @GuiaFinal = GDis_RangoFinalReserva from sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    where Dis_codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    --si @GuiaFinal = 0 no existen guias en el rango de reserva (no hay guias para asignar)

    if @GuiaFinal = 0

    begin

    --PRINT '+'''entro error guias'''+'

    insert into Tbl_ErrorRangosDistribuidores

    (Dis_Codigo,error)

    values

    (@DisCodigo,'+'''Se agot el rango principal y de reserva para el Distribuidor'''+')

    --PRINT '+'''despues del insert'''+'

    RAISERROR (@DisCodigo, 16, 1)

    end

    --no hay guias para asignar pero si hay guias en reserva

    else

    BEGIN

    insert into sodim.' + @DB + '.dbo.tbl_HistoricoRangoGuias

    (Dis_Codigo,HR_RangoInicial,HR_RangoFinal)

    select Dis_Codigo,GDis_RangoInicial,GDis_RangoFinal from

    sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    where dis_Codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    --asigna las guias de reserva al rango principal

    update sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    set

    GDis_RangoInicial = GDis_RangoInicialReserva

    ,GDis_RangoFinal = GDis_RangoFinalReserva

    ,GDis_UltimaGuia = GDis_RangoInicialReserva

    ,GDis_RangoInicialReserva = 0

    ,GDis_RangoFinalReserva = 0

    where dis_Codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    --selecciona la ultima guia disponible y la almacena en la variable @guia

    select @guia = GDis_UltimaGuia from sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    where dis_codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    update sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    set

    GDis_ultimaguia = @guia

    where dis_Codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    insert into Tbl_ErrorRangosDistribuidores

    (Dis_Codigo,error)

    values

    (@DisCodigo,'+'''se agoto el rango principal para el Distribuidor'''+')

    end

    end

    --no hay guias disponibles en la tabla tbl_guiasActivas y si hay guias disponibles del distribuidor

    else

    BEGIN

    --PRINT '+'''graba ultima guia'''+'

    --PRINT @guia

    update sodim.' + @DB + '.dbo.Tbl_GuiasDistribuidores

    set

    GDis_ultimaguia = @guia

    where dis_Codigo = @DisCodigo

    and Plan_Codigo = ' + @Planta + '

    end

    end

    else

    BEGIN

    --PRINT '+'''elimina Guia activa'''+'

    --PRINT @guia

    delete sodim.' + @DB + '.dbo.Tbl_GuiasActivas

    where GAct_Guia = @guia

    and dis_Codigo = @DisCodigo

    END

    --PRINT '+'''guarda la Guia en Guia asignada'''+'

    insert into sodim.' + @DB + '.dbo.Tbl_GuiasAsignadas

    (Ars_Codigo,Dis_Codigo,GA_Guia,Plan_Codigo)

    values

    (@idProceso,@DisCodigo,@guia,' + @Planta + ')

    --PRINT '+'''guarda la Guia en datos subidos'''+'

    --PRINT @guia

    update dbo.Tbl_datosSubidos

    set Guia = @guia

    where IndiceOLlaveunica = @idDato

    END

    update sodim.' + @DB + '.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

    set GDis_Estado = 0

    where

    Dis_codigo = @DisCodigo and plan_Codigo = ' + @Planta + '

    --PRINT '+'''lee otro registro del cursor'''+'

    fetch next from CursorCodDistribuidor

    into @DisCodigo,@idDato

    --PRINT '+'''despues del commit'''+'

    end

    commit transaction

    end try

    --manejo de errores

    begin catch

    insert into sodim.' + @DB + '.dbo.Tbl_GuiasActivas

    (Dis_Codigo,GAct_Guia,Plan_Codigo)

    select Dis_Codigo,GA_Guia,plan_Codigo from sodim.' + @DB + '.dbo.Tbl_GuiasAsignadas

    where Ars_Codigo = @idProceso

    update Tbl_DatosSubidos

    set

    guia = '+'''SIN DATOS'''+'

    update sodim.' + @DB + '.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

    set GDis_Estado = 0

    --PRINT '+'''mostrar error'''+'

    select

    ERROR_NUMBER() AS Numero,

    ERROR_LINE() AS Linea,

    ERROR_MESSAGE() AS Mensaje

    into ErrorGuias

    rollback transaction

    end catch

    close CursorCodDistribuidor

    deallocate CursorCodDistribuidor

    delete sodim.' + @DB + '.dbo.Tbl_GuiasAsignadas

    where Ars_Codigo = @idProceso

    delete sodim.' + @DB + '.dbo.Tbl_ArchivosSubidos

    where Ars_Codigo = @idProceso

    SET TRANSACTION ISOLATION LEVEL Read COMMITTED'

    --PRINT @lvaSentencia

    EXEC (@lvaSentencia)

    end

    Thursday, June 14, 2007 4:57 PM

All replies

  •  

    try rolling back the failed transaction before you update data in your catch() block.

     

    e.g.

    begin catch

    if xact_state()=-1 rollback

    insert into sodim.' + @DB + '.dbo.Tbl_GuiasActivas

     

    Friday, June 15, 2007 7:27 AM
  • I am working on sql server reporting services (SQL RS). In my report, I am using one sp (sp1) which which do "insert into" from other sp (sp2). In sp2 I am doing "Select into" which creates one new table and at the end I droping the same table. When I execute this sp2 in SQL RS, it will exec properly. But problem is when I run the report in SQL RS it exec the sp1 , internally this sp try to exec drop table line in sp2 which trows execeptions.



    ===================================

    An error occurred while executing the query.
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (Microsoft Report Designer)

    ===================================

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3930&LinkId=20476

    ------------------------------
    Server Name: localhost
    Error Number: 3930
    Severity: 16
    State: 1
    Procedure: GetSortingData
    Line Number: 94


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
       at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.ExecuteQuery()

    Wednesday, June 17, 2009 9:50 AM