There is already an object named ##Temporal Table in the database

Navigation
0

I have the following stored procedure, which allows to generate a report in Excel. The fact is that when several users are generating the report, a series of errors appear indicating that there is already an object called ##rpttabla in the database.

I would like to know, how can I do to generate a random temporary table name every time the procedure is executed? 

IF @Opt = 0 BEGIN


    IF OBJECT_ID('tempdb..##RptMayorD')  IS NOT NULL
    BEGIN DROP TABLE ##RptMayorD END

    --** COMPROBANTE AUTOMATICOS **--
    SELECT 
        Cuenta = D.Cuenta, 
        Descripcion=CAST('' AS VARCHAR(250)),
        Folio=CAST(A.Folio AS VARCHAR(20)), 
        Lote = CAST(A.IdLote AS VARCHAR(20)), 
        Comprobante = C.Comprobante,
        FechaContable= CONVERT(CHAR(10),F.FechaContable,103), 
        A.CodigoTransaccion, 
        ReferenciaNegocio =  C.ReferenciaNegocio, 
        OficinaTransaccion = D.OficinaContable,
        Nombre_Oficina=CAST('' AS VARCHAR(50)),
        MonedaTransaccion = c.MonedaTransaccion, 
        Cotizacion  = D.Cotizacion , 
        ValorDebito = D.ValorDebito, 
        ValorDebitoEquivalencia= D.valordebitoequivalencia,
        ValorCredito  =  D.ValorCredito, 
        ValorCreditoEquivalencia= D.ValorCreditoEquivalencia,
        Concepto=C.Concepto, 
        Observacion = CAST('' AS VARCHAR(150)),
        NroDocumento = CAST('' AS VARCHAR(15)), 
        Cliente = CAST('' AS VARCHAR(250)) ,
        a.Origen,  
        FechaTransaccion=CONVERT(CHAR(10),A.FechaTransaccion,103),
        Nombre = CAST('' AS VARCHAR(150)), 
        NombreCliente = CAST('' AS VARCHAR(150)), 
        a.IDFechaContable, 
        A.IDLote, 
        IDAsiento = C.IDAsiento,
        IDComprobante 
        INTO  ##RptMayorD
    FROM Contabilidad..Comprobante A  WITH (NOLOCK)
    INNER JOIN Contabilidad..FechaContable F  WITH (NOLOCK) on F.IdFechaContable = A.IdFechaContable
    INNER JOIN GeneradorContableHistorico..Asientohistorico C  WITH (NOLOCK) on a.IdLote = c.LoteContable 
    INNER JOIN GeneradorContableHistorico..AsientoDetalleHistorico D WITH (NOLOCK) on c.IdAsiento = d.IdAsiento 
    WHERE A.EsMayorizado = 1 And A.EsCuadrado = 1 And A.EsVigente = 1 AND A.Origen <> 'MANUAL'  AND
          A.Folio LIKE @Fecha + '%'
          AND D.cuenta LIKE @Cuenta + '%' 
          AND D.Moneda LIKE  @Moneda + '%'



    --** COMPROBANTE MANUALES **--
    INSERT INTO ##RptMayorD
    SELECT 
        Cuenta = B.Cuenta, 
        Descripcion=CAST('' AS VARCHAR(250)),
        Folio=CAST(A.Folio AS VARCHAR(20)), 
        Lote = CAST(A.IdLote AS VARCHAR(20)), 
        Comprobante = CAST('' AS VARCHAR(200)), 
        FechaContable=CONVERT(CHAR(10),F.FechaContable,103), 
        A.CodigoTransaccion, 
        ReferenciaNegocio = CAST('' AS VARCHAR(250)), 
        OficinaTransaccion = B.IDOficinaContable, 
        Nombre_Oficina=CAST('' AS VARCHAR(50)),
        MonedaTransaccion = B.Moneda, 
        Cotizacion  = B.ValorCotizacion , 
        ValorDebito =B.MontoDebito, 
        ValorDebitoEquivalencia= B.MontoDebitoEquivalencia,
        ValorCredito  = B.MontoCredito, 
        ValorCreditoEquivalencia = B.MontoCreditoEquivalencia,
        Concepto= CAST(A.Comentario.query(N'/Glosa/Comentario/text()') AS VARCHAR(8000)), 
        Observacion = CAST('' AS VARCHAR(150)),
        NroDocumento = CAST('' AS VARCHAR(15)), 
        Cliente = CAST('' AS VARCHAR(250)) ,
        a.Origen,  FechaTransaccion=CONVERT(CHAR(10),
        A.FechaTransaccion,103),
        Nombre = CAST('' AS VARCHAR(150)), 
        NombreCliente = CAST('' AS VARCHAR(150)), 
        A.IDFechaContable, 
        IDLote, 
        IDAsiento = 0,A.IDComprobante
    FROM Contabilidad..Comprobante A  WITH (NOLOCK)
    INNER JOIN Contabilidad..FechaContable F  WITH (NOLOCK) on F.IdFechaContable = A.IdFechaContable
    INNER JOIN Contabilidad..detallecomprobante B WITH (NOLOCK) on a.idcomprobante = b.idcomprobante 
    WHERE A.EsMayorizado = 1 And A.EsCuadrado = 1 And A.EsVigente = 1 AND A.Origen = 'MANUAL'  
            AND B.cuenta LIKE  @Cuenta + '%'  
            AND A.Folio like @Fecha + '%'
            AND B.Moneda  LIKE  @Moneda + '%'


    --> Actualizar Nombre de Oficina
    UPDATE C SET Nombre_Oficina = trx.Nombre
    FROM ##RptMayorD C  WITH (NOLOCK)
    Join Configuracion..Oficina trx  WITH (NOLOCK) on trx.idoficina = c.OficinaTransaccion

    --> Actualizar Descripcion de la Cuenta
    UPDATE C SET Descripcion = trx.Descripcion
    FROM ##RptMayorD C  WITH (NOLOCK)
    Join contabilidad..catalogocuenta trx  WITH (NOLOCK) on trx.cuenta = c.cuenta

    --> Actualizar Nombre y Numero de Documento desde cuenta cliente
    UPDATE C SET NombreCliente=Nombres, NroDocumento = Documento, Cliente=Nombres
    FROM ##RptMayorD C  WITH (NOLOCK)
    Join Cuenta..CuentaCliente Cred  WITH (NOLOCK) On Cred.NumeroCuenta = c.ReferenciaNegocio 
    Join Cliente..VW_Clientes Cli  WITH (NOLOCK) on Cred.IdCliente = Cli.IdCliente
    WHERE Origen <> 'MANUAL' 

    -->Actualizar Nombre y Numero de Documento desde credito
    UPDATE C SET NombreCliente=Nombres, NroDocumento = Documento, Cliente=Nombres
    FROM ##RptMayorD C  WITH (NOLOCK)
    Join Credito..Credito Cred  WITH (NOLOCK) On Cred.NumeroCredito = c.ReferenciaNegocio 
    Join Cliente..VW_Clientes Cli  WITH (NOLOCK) on Cred.IdCliente = Cli.IdCliente
    WHERE Origen <> 'MANUAL'  AND Cliente = ''


    --> Actualizar Nombres desde Transaccion
    UPDATE C SET Nombre=trx.Nombre, Observacion=trx.Observacion, NroDocumento = NumeroIdentificacion, Cliente = trx.Nombre
    FROM ##RptMayorD C  WITH (NOLOCK)
    Left  Join Caja..transaccion trx  WITH (NOLOCK) on trx.secuencial = c.ReferenciaNegocio 
    WHERE Origen <> 'MANUAL'  AND Cliente = ''

    UPDATE ##RptMayorD SET Nombre='' WHERE Nombre IS NULL
    UPDATE ##RptMayorD SET Observacion='' WHERE Observacion IS NULL
    UPDATE ##RptMayorD SET NombreCliente='' WHERE NombreCliente IS NULL
    UPDATE ##RptMayorD SET NroDocumento='' WHERE NroDocumento IS NULL   
    UPDATE ##RptMayorD SET Cliente='' WHERE Cliente IS NULL 

    UPDATE A SET Concepto = Concepto_A
    FROM ##RptMayorD A 
    JOIN (SELECT NumeroCuenta, ComprobanteCanal, B.IDCuentaCliente, 
                 Concepto_A=Comentario + ' ' + ComentarioEstadoCuenta, FechaContable=CONVERT(CHAR(10),C.FEchaContable,103)
          FROM Cuenta..CuentaCliente B   WITH (NOLOCK)
          JOIN Cuenta..Transaccion C  WITH (NOLOCK) ON B.IDCuentaCliente = C.IDCuentaCliente  AND C.CodigoAtributo='ATRCRECASACOMER' 
          ) D
    ON A.ReferenciaNegocio = D.NumeroCuenta AND A.CodigoTransaccion='TRANSCTA' AND A.FEchaContable = D.FEchaContable AND ComprobanteCAnal = Comprobante 


    SELECT COUNT(*) N FROM ##RptMayorD

END

IF @Opt = 1 BEGIN
    SELECT Cuenta, Descripcion, Folio, Lote, Comprobante, FechaContable, CodigoTransaccion, ReferenciaNegocio,
           Cod_Oficina = OficinaTransaccion, Nombre_Oficina, Moneda = MonedaTransaccion, Cotizacion, ValorDebito,
           ValorDebitoEquivalencia, ValorCredito, ValorCreditoEquivalencia,
           Concepto, Observacion, NroDocumento, Cliente, Origen, FechaTransaccion
    FROM ##RptMayorD
    ORDER BY Folio
END

The version I use is 2012 and the table is only used in that stored procedure.

What happens is that I can not use local temporary tables because each part of the stored procedure uses a different connection string (as Davlio indicates in a comment). Temporary tables can only be used in the connection in which the user creates them. When the connection ends the temporary table disappears, when I use local temporary tables I get the error "the object was not found ****"

    
asked by CeciliaMa 22.09.2017 в 01:09
source

2 answers

0

I already solve it Thanks. 

declare @Cuenta Varchar(25),@Fecha Varchar(10),@Moneda Varchar(3),@User Varchar(20)
Declare @NomTabla Varchar(60)
    SET @User = 'CMARRUFO'
    SET @Cuenta ='190106010105'
    SET @Fecha ='20170812'
    SET @Moneda = 'SOL'
    SET @NomTabla = '##RptMayorDetalle' + @User



 EXEC ('IF OBJECT_ID(''tempdb..'+ @nomtabla + ''')  IS NOT NULL BEGIN DROP TABLE '+ @nomtabla + ' END')

    --** COMPROBANTE AUTOMATICOS **--
EXEC (' SELECT 
            Cuenta = D.Cuenta, 
            Descripcion=CAST('''' AS VARCHAR(250)),
            Folio=CAST(A.Folio AS VARCHAR(20)), 
            Lote = CAST(A.IdLote AS VARCHAR(20)), 
            Comprobante = C.Comprobante,
            FechaContable= CONVERT(CHAR(10),F.FechaContable,103), 
            A.CodigoTransaccion, 
            ReferenciaNegocio =  C.ReferenciaNegocio, 
            OficinaTransaccion = D.OficinaContable,
            Nombre_Oficina=CAST('''' AS VARCHAR(50)),
            MonedaTransaccion = c.MonedaTransaccion, 
            Cotizacion  = D.Cotizacion , 
            ValorDebito = D.ValorDebito, 
            ValorCredito  =  D.ValorCredito, 
            Concepto=C.Concepto, 
            Observacion = CAST('''' AS VARCHAR(150)),
            NroDocumento = CAST('''' AS VARCHAR(15)), 
            Cliente = CAST('''' AS VARCHAR(250)) ,
            a.Origen,  
            FechaTransaccion=CONVERT(CHAR(10),A.FechaTransaccion,103),
            Nombre = CAST('''' AS VARCHAR(150)), 
            NombreCliente = CAST('''' AS VARCHAR(150)), 
            a.IDFechaContable, 
            A.IDLote, 
            IDAsiento = C.IDAsiento,
            IDComprobante 
            INTO '+ @nomtabla +'
        FROM Contabilidad..Comprobante A  WITH (NOLOCK)
        INNER JOIN Contabilidad..FechaContable F  WITH (NOLOCK) on F.IdFechaContable = A.IdFechaContable
        INNER JOIN GeneradorContableHistorico..Asientohistorico C  WITH (NOLOCK) on a.IdLote = c.LoteContable 
        INNER JOIN GeneradorContableHistorico..AsientoDetalleHistorico D WITH (NOLOCK) on c.IdAsiento = d.IdAsiento 
        WHERE A.EsMayorizado = 1 And A.EsCuadrado = 1 And A.EsVigente = 1 AND A.Origen <> ''MANUAL''        
        AND A.Folio LIKE'+ @Fecha +' + ''%''
              AND D.cuenta LIKE '+ @Cuenta +' + ''%''  
              AND D.Moneda LIKE  '+ @Moneda +' + ''%''')
    
answered by 22.09.2017 / 20:12
source
2

Difference between a global and local temporary table

  

#local: Local temporary tables have a # as the first character in their name and can only be used in the connection in the   that the user creates them. When the connection ends the temporary table   disappears.

     

## global: Global temporary tables start with ## and are visible to any user connected to the SQL Server. And one thing   more, these tables disappear when no user is doing   references to them, not when the user who created it is disconnected.

The error you find:

  

there is already an object called ## rpttabla in the database

It is because all connected users find redundancy in the existence of the temporary table ##rpttabla , so you could use a temporary table of local type because you only use it in a single stored procedure.

You might also consider reviewing:

answered by 22.09.2017 в 02:44
Table View with height of different cells and adjustable to their contents CSS: Keep style by breaking the word