The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

The version for Sybase is ASE 12.5.3, DBA told me this opcion is dangerous because if a transaction is dead lock is impossible kill it and the only solucion is re-start the server.

My code:

create proc dbo.sps_saldo_consulta_ad2
@as_usuario varchar(10),
@ai_tipo integer,
@ai_nombres integer = null,
@ai_consoli integer = null
 
AS
declare
@ls_usuario   varchar(20),
@ls_parametro varchar(255),
@ls_sucursal  varchar(3),
@ls_moneda    varchar(2),
@ls_mayor     varchar(3),
@ls_analitico varchar(15),
@ls_futuro    varchar(10),
@ls_pasado    varchar(10),
 
@li_sucursal  integer,
@li_mayor     integer,
@ldt_futuro   datetime,
@ldt_pasado   datetime,
@ls_oficina   varchar(255),
@ldt_switch   datetime,
@ld_minimo    numeric(20,4),
@ld_maximo    numeric(20,4),
@ld_switch    numeric(20,4),
@ls_minimo    varchar(22),
@ls_maximo    varchar(22),
@ls_cuenta    varchar(18)
 
DECLARE
@ldt_inicio                   datetime,
@ldt_fin                                   datetime,
@ln_registros    double precision
 
 
begin
 
            select @ldt_inicio = getdate()
            select @ls_usuario = @as_usuario,       --Obtiene el nombre del usuario
                                    @ls_oficina = space(255)
/*          execute ctrlcontable.dbo.sps_oficina_usuario 2, @ls_usuario, @ls_oficina output
            select @ls_oficina = rtrim(@ls_oficina)
 
print "permisos en oficina '%1!'", @ls_oficina
*/
                                            
/*if exists (select 1 from OFICINA WHERE (charindex(","+ofi_identificador+",",@ls_oficina) <>0 ) and ofi_privilegio = 'N')
begin*/
            select @ls_oficina = '*' --Oficina Privilegiada
--end
 
                        create table #SALDO_RESULTADO                                                    
                        (fecha    datetime,
                         sucursal int,
                         moneda   char(2),
                         mayor    int,
                         analitico varchar(15),
                         naturaleza int ,                                                                                                     
                         cargo    numeric(20,4),
                         abono    numeric(20,4),
                         saldo    numeric(20,4),
                         nombre   varchar(60) NUll
                        )
 
                        SELECT con_parametro
      INTO   #CONSULTA_USUARIO
                        FROM CONSULTA
                        WHERE usu_identificador = @ls_usuario
                        AND  con_tipo = @ai_tipo
                        ORDER BY con_subconsulta
 
           
            DECLARE SIG_CONSULTA CURSOR FOR                   --Cursor que extrae las consultas definidas para un tipo y un usuario
                        SELECT con_parametro
                        FROM #CONSULTA_USUARIO
 
            OPEN SIG_CONSULTA
 
            FETCH SIG_CONSULTA
            INTO     @ls_parametro
 
 
            WHILE @@sqlstatus = 0
                        BEGIN
 
                                     IF @ai_tipo = 1                                                                              
                                               begin
                                                           select @ls_sucursal  = substring(@ls_parametro,1,3),
                                                                                   @ls_moneda    = substring(@ls_parametro,4,2),
                                                                                   @ls_mayor     = substring(@ls_parametro,6,3),
                                                                                   @ls_analitico = substring(@ls_parametro,9,15),
                                                                                   @ls_futuro    = substring(@ls_parametro,24,10),
                                                                                   @ls_pasado    = substring(@ls_parametro,34,10)
 
 
 
                                                                                                                                    
                                                           if rtrim(@ls_sucursal) in ( null,'')
                                                                       select @li_sucursal = -1
                                                           else
                                                                       select @li_sucursal = convert(integer,@ls_sucursal)
 
                                                           select @ls_moneda = rtrim(@ls_moneda), @li_mayor = convert(integer, @ls_mayor)
 
                                                           if @ls_pasado = @ls_futuro select @ls_futuro = ''
 
                                                                                                                                      
                                                                       if rtrim(@ls_pasado) in ( null,'')  and rtrim(@ls_futuro) in ( null,'')
                                                                                  begin                                   
                       
                                                                                                          execute dbo.sps_saldo_rec_cue_hoy
                                                                                                                                                                     @li_sucursal , @ls_moneda    ,
                                                                                                                                                                     @li_mayor    , @ls_analitico ,
                                                                                                                                                                     @ls_oficina  
                                                                                  end
                                                    else  if rtrim(@ls_pasado) in ( null,'') and rtrim(@ls_futuro) not in ( null,'')
                                                                                  begin                                 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
--print "consulta de fecha '%1!' --> '%2!'  --> '%3!'  --> '%4!' --> '%5!' --> '%6!'", @ldt_futuro,
--                                                                                                                                                                   @li_sucursal  , @ls_moneda    ,
--                                                                                                                                                                   @li_mayor     , @ls_analitico ,
--                                                                                                                                                                   @ls_oficina  
                                                                                                          execute dbo.sps_saldo_rec_cue_fecha
                                                                                                                                                                     @ldt_futuro   ,
                                                                                                                                                                     @li_sucursal  , @ls_moneda    ,
                                                                                                                                                                     @li_mayor     , @ls_analitico ,
                                                                                                                                                                     @ls_oficina  
                                                                                  end
                                                    else  if rtrim(@ls_pasado) not in ( null,'') and rtrim(@ls_futuro) in ( null,'')
                                                                                  begin                                  
 
 
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
                                                                                                          execute dbo.sps_saldo_rec_cue_fecha
                                                                                                                                                                     @ldt_pasado   ,
                                                                                                                                                                     @li_sucursal  , @ls_moneda    ,
                                                                                                                                                                     @li_mayor     , @ls_analitico ,
                                                                                                                                                                     @ls_oficina  
                                                                                  end
                                                    else  if rtrim(@ls_pasado) not in ( null,'') and rtrim(@ls_futuro) not in ( null,'')
                                                                                  begin                                 
 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
                                                                                                          if @ldt_pasado > @ldt_futuro                                               
                                                                                                                      begin
                                                                                                                                 select @ldt_switch = @ldt_pasado
                                                                                                                                 select @ldt_pasado = @ldt_futuro
                                                                                                                                 select @ldt_futuro = @ldt_switch
                                                                                                                      end
                                                                                                          execute dbo.sps_saldo_rec_cue_perio
                                                                                                                                                                     @ldt_futuro   ,@ldt_pasado   ,
                                                                                                                                                                     @li_sucursal  ,@ls_moneda    ,
                                                                                                                                                                     @li_mayor     ,@ls_analitico ,
                                                                                                                                                                     @ls_oficina 
                                                                                  end
                                               end
                        ELSE IF @ai_tipo = 2                                                                                                
                                               begin
                                                           select @ls_sucursal  = substring(@ls_parametro,1,3),
                                                                                   @ls_futuro    = substring(@ls_parametro,4,10),
                                                                                   @ls_pasado    = substring(@ls_parametro,14,10)
 
                                                                                               
                                                           if rtrim(@ls_sucursal) in ( null,'') select @li_sucursal = -1
                                                           else select @li_sucursal = convert(integer,@ls_sucursal)
                                                           if rtrim(@ls_pasado) = rtrim(@ls_futuro) select @ls_futuro=''
 
                                                                                                                                  
                                                                       if rtrim(@ls_pasado) in ( null,'')  and rtrim(@ls_futuro) not in ( null,'')
                                                                                  begin                                   
                                                                                                          execute dbo.sps_saldo_rec_cue_hoy
                                                                                                                                                                     @li_sucursal , "",
                                                                                                                                                                     0, "%",@ls_oficina  
 
                                                                                  end
                                                    else  if rtrim(@ls_pasado) in ( null,'') and rtrim(@ls_futuro) not in ( null,'')
                                                                                  begin                                 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
                                                                                                          execute dbo.sps_saldo_rec_cue_fecha
                                                                                                                                                                     @ldt_futuro   ,
                                                                                                                                                                     @li_sucursal  , "",
                                                                                                                                                                     0, "%",
                                                                                                                                                                     @ls_oficina  
                                                                                  end
                                                    else  if rtrim(@ls_pasado) not in  ( null,'') and rtrim(@ls_futuro) in ( null,'')
                                                                                  begin                                 
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
                                                                                                          execute dbo.sps_saldo_rec_cue_fecha
                                                                                                                                                                     @ldt_pasado   ,
                                                                                                                                                                     @li_sucursal  , "",
                                                                                                                                                                     0, "%",
                                                                                                                                                                     @ls_oficina  
                                                                                  end
                                                    else  if rtrim(@ls_pasado) not in ( null,'') and rtrim(@ls_futuro) not in ( null,'')
                                                                                  begin                                 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
                                                                                                          if @ldt_pasado > @ldt_futuro                                               
                                                                                                                      begin
                                                                                                                                 select @ldt_switch = @ldt_pasado
                                                                                                                                 select @ldt_pasado = @ldt_futuro
                                                                                                                                 select @ldt_futuro = @ldt_switch
                                                                                                                      end
                                                                                                          execute dbo.sps_saldo_rec_cue_perio
                                                                                                                                                                     @ldt_futuro   ,@ldt_pasado   ,
                                                                                                                                                                     @li_sucursal  ,"",
                                                                                                                                                                     0,"",
                                                                                                                                                                     @ls_oficina 
                                                                                  end
 
 
                                               end
                        ELSE IF @ai_tipo = 3                                                                                    
                                               begin
                                                           select @ls_cuenta    = rtrim(substring(@ls_parametro,1,18)),
                                                                                   @ls_minimo    = rtrim(substring(@ls_parametro,19,22)),
                                                                                   @ls_maximo    = rtrim(substring(@ls_parametro,41,22)),
                                                                                   @ls_futuro    = rtrim(substring(@ls_parametro,63,10)),
                                                                                   @ls_pasado    = rtrim(substring(@ls_parametro,73,10))
 
--print "pasado  '%1!'   futuro  '%2!' ",@ls_pasado, @ls_futuro
 
                                                                                    
                                                                                   if @ls_minimo = null select @ld_minimo = 0
                                                                                   else select                 @ld_minimo = convert(decimal(20,4),@ls_minimo)
 
                                                                                   if @ls_maximo = null select @ld_maximo = 0
                                                                                   else select                 @ld_maximo = convert(decimal(20,4),@ls_maximo)
 
                                                                                   if @ld_minimo > @ld_maximo                                                     
                                                                                   begin
                                                                                                          select @ld_switch = @ld_minimo
                                                                                                          select @ld_minimo = @ld_maximo
                                                                                                          select @ld_maximo = @ld_switch
                                                                                   end
                                                                                  if rtrim(@ls_pasado) = rtrim(@ls_futuro) select @ls_futuro=''
--print "pasado  '%1!'   futuro  '%2!' ",@ls_pasado, @ls_futuro
--print "maximo  '%1!'   '%2!'",@ld_maximo,@ld_minimo
                                                                                                                                      
                                                                       if (@ls_pasado in ( null,'') and @ls_futuro in ( null,''))
                                                                                  begin                                   
                                                                                                          execute dbo.sps_saldo_cya_imp_hoy
                                                                                                                                             @ls_cuenta    ,@ld_minimo    ,
                                                                                                                                             @ld_maximo    ,@ls_oficina  
                                                                                  end
                                                    else  if (@ls_pasado in ( null,'') and @ls_futuro not in ( null,''))
                                                                                  begin                                 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
                                                                                                          execute dbo.sps_saldo_rec_imp_fecha
                                                                                                                                             @ls_cuenta     ,
                                                                                                                                             @ld_minimo     ,@ld_maximo    ,
                                                                                                                                             @ldt_futuro    ,@ls_oficina  
                                                                                  end
                                                    else  if (@ls_pasado not in ( null,'') and @ls_futuro in ( null,''))
                                                                                  begin                                 
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
--print "-->pasado  '%1!'   futuro  '%2!' ",@ls_pasado, @ls_futuro
                                                                                                          execute dbo.sps_saldo_rec_imp_fecha
                                                                                                                                             @ls_cuenta     ,
                                                                                                                                             @ld_minimo     ,@ld_maximo    ,
                                                                                                                                             @ldt_pasado    ,@ls_oficina  
                                                                                  end
                                                    else  if (@ls_pasado not in ( null,'') and @ls_futuro not in ( null,''))
                                                                                  begin                                 
                                                                                                          select @ldt_futuro = convert(datetime,@ls_futuro)
                                                                                                          select @ldt_pasado = convert(datetime,@ls_pasado)
                                                                                                          if @ldt_pasado > @ldt_futuro                                               
                                                                                                                      begin
                                                                                                                                 select @ldt_switch = @ldt_pasado
                                                                                                                                 select @ldt_pasado = @ldt_futuro
                                                                                                                                 select @ldt_futuro = @ldt_switch
                                                                                                                      end
--print "periodo de fechas"
                                                                                                          execute dbo.sps_saldo_cya_imp_perio
                                                                                                                                                         @ls_cuenta    ,@ld_minimo    ,@ld_maximo    ,
                                                                                                                                                         @ldt_pasado   ,@ldt_futuro   ,@ls_oficina  
                                                                                  end
 
 
                                               end
 
            ELSE IF @ai_tipo = 17                                                                            
                                               begin
                                                           select @ls_sucursal  = substring(@ls_parametro,1,3),
                                                                                   @ls_moneda    = substring(@ls_parametro,4,2),
                                                                                   @ls_mayor     = substring(@ls_parametro,6,3),
                                                                                   @ls_analitico = substring(@ls_parametro,9,15),
                                                                                   @ls_futuro    = substring(@ls_parametro,24,10)
 
 
                                                                                                                                    
                                                           if rtrim(@ls_sucursal) in ( null,'')
                                                                       select @li_sucursal = -1
                                                           else
                                                                       select @li_sucursal = convert(integer,@ls_sucursal)
 
                                                           select @ls_moneda = rtrim(@ls_moneda),
                                                                                   @li_mayor  = convert(integer, @ls_mayor)
                                                           select @ldt_futuro = convert(datetime,@ls_futuro)
 
                                                           execute dbo.sps_afecta_rec_cue_fecha
                                                                                                                      @ldt_futuro   ,
                                                                                                                      @li_sucursal  , @ls_moneda    ,
                                                                                                                      @li_mayor     , @ls_analitico ,
                                                                                                                      @ls_oficina  
 
                                               end
 
 
                                                                    
                                   FETCH SIG_CONSULTA
                                   INTO     @ls_parametro
 
                        END
 
 
                                                                                                                     
            exito:
 
 
                        if @ai_nombres = 1 execute dbo.sps_saldo_nombres @ai_tipo
 
                        create table #NOREPETIDOS                                                    
                                               (fecha    datetime,
                                                sucursal int null,
                                                moneda   char(2),
                                                mayor    int,
                                                analitico varchar(15),
                                                naturaleza int,
                                                cargo    numeric(20,4),
                                                abono    numeric(20,4),
                                                saldo    numeric(20,4),
                                                nombre   varchar(60) NUll
                                               )
 
                                                                                                          
 
                        if @ai_consoli = 1                       
                                   begin
                                              
                                   IF @ai_tipo = 17
                                                           INSERT INTO #NOREPETIDOS                                                    
                                                                       (fecha, sucursal, moneda, mayor, analitico, naturaleza, cargo, abono, saldo, nombre)
                                                           select  distinct fecha,    NULL,    moneda   ,                   mayor    ,                    analitico ,
                                                                                               MAX(naturaleza) ,         SUM(cargo)    ,                        SUM(abono)    ,            SUM(saldo)    ,            MAX(nombre   )
                                                                       from #NOREPETIDOS
                                                                       GROUP BY fecha      ,  moneda   ,                   mayor    ,                    analitico
                                                                       order by mayor, fecha,  moneda,   analitico
                                               Else
                                                           INSERT INTO #NOREPETIDOS                                                    
                                                                       (fecha, sucursal, moneda, mayor, analitico, naturaleza, cargo, abono, saldo, nombre)
                                                           select  distinct fecha      ,          0     ,    moneda   ,                   mayor    ,                     analitico ,
                                                                                               MAX(naturaleza) ,         SUM(cargo)    ,                        SUM(abono)    ,            SUM(saldo)    ,            MAX(nombre   )
                                                           from #SALDO_RESULTADO
                                                           GROUP BY fecha      ,  moneda   ,                   mayor    ,                    analitico
                                                           order by mayor, fecha,  moneda,   analitico
 
                                                                                           
            IF @ai_tipo = 17
                                                           BEGIN
                                                                                                                                                                                                                                                                                                                                                                                                                 
                                                                       select 
                                                                                               fecha      ,        sucursal     ,     moneda   ,                   mayor    ,                       analitico ,
                                                                                               naturaleza ,      cargo    ,                     abono    ,                     saldo    ,                      nombre  
                                                                       from #NOREPETIDOS
                                                                       order by mayor, fecha,  moneda,   analitico
                                                           END
                                               ELSE
                                                           BEGIN
                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                                                       select 
                                                                                               fecha      ,        sucursal     ,     moneda   ,                   mayor    ,                       analitico ,
                                                                                               naturaleza ,      cargo    ,                     abono    ,                     saldo    ,                      nombre
                                                                       from #NOREPETIDOS
                                                                       order by fecha      ,         moneda   ,                   mayor    ,                    analitico
                                                           END
 
                                               DROP TABLE #NOREPETIDOS
                                   END
                        else                                                                                  
                                                                                           
          IF @ai_tipo = 17
                                                           BEGIN
                                                                       select  Distinct
                                                                                               fecha      ,        sucursal ,         moneda   ,                   mayor    ,                       analitico ,
                                                                                               naturaleza ,      cargo    ,                     abono    ,                     saldo    ,                      nombre  
                                                                       from #SALDO_RESULTADO
                                                                       order by  mayor   ,  fecha   ,       sucursal ,         moneda   ,         analitico
                                                           END
                                               ELSE
                                                           BEGIN
                                                                       create table #NOREPETIDOS2                                                    
                                               (fecha    datetime,
                                                sucursal int null,
                                                moneda   char(2),
                                                mayor    int,
                                                analitico varchar(15),
                                                naturaleza int,
                                                cargo    numeric(20,4),
                                                abono    numeric(20,4),
                                                saldo    numeric(20,4),
                                                nombre   varchar(60) NUll
                                               )
                                                                       INSERT INTO #NOREPETIDOS2                                                    
                                                                       (fecha, sucursal, moneda, mayor, analitico, naturaleza, cargo, abono, saldo, nombre)
                                                                       select  Distinct
                                                                                               fecha      ,        isnull(sucursal,0) ,         moneda   ,                   mayor    ,                    analitico ,
                                                                                               naturaleza ,      cargo    ,                     abono    ,                     saldo    ,                      nombre  
                                                                       from #SALDO_RESULTADO
                                                                       order by fecha      ,        sucursal ,         moneda   ,                   mayor    ,                        analitico
 
                                                          
 
                                                                       select  Distinct
                                                                                               fecha      ,        sucursal ,         moneda   ,                   mayor    ,                       analitico ,
                                                                                               naturaleza ,      cargo    ,                     abono    ,                     saldo    ,                      nombre  
                                                                       from #NOREPETIDOS2
                                                                       order by fecha      ,        sucursal ,         moneda   ,                   mayor    ,                        analitico
 
                                                           END
 
 
                        select @ln_registros = @@rowcount
                        select @ldt_fin = getdate()
                        execute dbo.spi_bitacora
                        @ldt_inicio                   ,
                        @ldt_fin                                   ,
                        @ls_usuario                 ,
                        @ai_tipo                                  ,
                        @ln_registros   
 
                       
            error:
 
 
 
            salir:
                        CLOSE SIG_CONSULTA
                        DEALLOCATE CURSOR SIG_CONSULTA
                        drop table #SALDO_RESULTADO
                        drop table #CONSULTA_USUARIO
end

> DBA told me this option is dangerous because if a transaction is dead lock is impossible kill it and the only solucion is re-start the server.

Uh, I have no idea why your DBA would have said that. As written it is 100% wrong.

If a transaction gets deadlocked, ASE automatically finds a deadlock victim based on whoever has done the least work so far. The "ddl in tran" option does not change this.

If a process is sleeping, any attempt to kill it (whether by the system in the case of a deadlock or manually by a DBA) will only work when it next wakes up. If it never wakes up (hung?) then indeed the only solution is to restart ASE... *but* again, the "ddl in tran" option does not change this.

A quick look in the manuals confirms this. "ddl in tran" does not make any deadlock situation more difficult to handle.

What it *can* do (and this is why it is disabled by default) is to cause more locks on system tables, which are held for the duration of your transaction. If your transaction is "long" (hard to define but 5 minutes is definitely long) then blocking system table access is a lot more intrusive. So it can need some attention to transaction design to make sure those locks are released as soon as possible. Also, don't enable this in tempdb as problems with locked system tables in tempdb can pretty much halt the server - maybe this was what your DBA was thinking of?

Still, in fast transactions outside tempdb, this option is not dangerous and is fine to use.

That being said there are an awful lot of "exciting opportunities for improvement" in your code, but that's beyond the scope of this question (and would need a new question raised)... but very very briefly: don't use cursors, and avoid local variables (and use procedure parameters plus calls to subprocedures) where possible.

SUMMARY: the database option "ddl in tran" is the answer to your problem.