SQL Server 2005 Express y las transacciones anidadas

[4PS_MultiPass]

Hola a todos en el foro.

Si no es la primera vez que escribo, probablemente sea la segunda y es para pediros ayuda...

Tengo dos bases de datos creadas dentro de un servidor MS SQL server 2005 Express.

Tanto en la base de datos A como en la B existen las mismas tablas. La base de datos A es para los "dato de trabajo" y la base de datos B es para hacer las labores de "Histórico".

Lo que quiero implementar es el traspaso de información de la base de datos A a la base de datos B. Más específicamente, mover un registro de la base de datos A a la B.

Esto lo llevaría a cabo copiando el contenido de un registro de la tabla T1 de la base de datos A a la tabla T1 de la base de datos B y a continuación eliminando dicho registro de la tabla T1 de la base de datos A.

Además, para mí es muy importante hacerlo dentro de transacciones para asegurarme que si falla el copiado no ejecute el borrado, o que si falla el borrado que deshaga el copiado.

dbHistorico y dbDatos son dos TADOConnection

El trozo de código es el siguiente...

        cSentenciaHistorico := 'INSERT INTO T1 (campo)  values  ('valor');

        //Comienzo la primera transacción que controla la inserción del registro en el histórico.
        dbHistorico.BeginTrans;
        try
          dbHistorico.Execute(cSentenciaHistorico);
          // Comienza la segunda transaccion (anidada) que controla el borrado del registro en la base de datos "de diario".
          dbDatos.BeginTrans;
          try
            cSentenciaDatos := 'DELETE ' + cTabla + ' WHERE ' + cClave + ' = ' + TSQL.FieldByName( cClave ).AsString + ';';
            dbDatos.Execute(cSentenciaDatos);
            // si todo ha ido bien, hago un commit de las dos transacciones.
            dbDatos.CommitTrans;
            dbHistorico.CommitTrans;
          except
            // si falló el borrado, lo anulo.
            dbDatos.RollbackTrans;
            // reactivo la excepción para que también se deshaga la insercion.
            raise;
          end;
        except
          // si algo fué mal, que se deshaga el insert.. 
          dbHistorico.RollbackTrans;
        end;

Mi problema es que cuando el programa llega a la línea en donde se intenta abrir la segunda transacción, me salta una excepción con el siguiente mensaje "No se puede crear una nueva transacción, se excedió la capacidad".

No soy capaz de hayar una solución.

¿Vosotros veis si me estoy equivocando en algo?

¿Se os ocurre alguna otra forma de mover los registros de una db a otra de forma segura?

Perdonad por el tocho, pero es que no me sé explicar mejor.

Muchas gracias por adelantado.

[StartKill]

Hola

Sugerencia, ... si lo trabajas en un procedimiento almacenado???
(como un todo, ... debo suponer que todo esta en un mismo server)

Código SQL [-]
BEGIN TRAN
   BEGIN TRY
      ---todas tus instrucciones
      COMMIT TRANSACTION; 
   END TRY
   BEGIN CATCH
      ROLLBACK TRANSACTION
      PRINT 'Se ha producido un error!!!!'
      print ERROR_NUMBER() ;
      print ERROR_MESSAGE() ;
   END CATCH

Your friend

StartKill
Lima-Peru

[4PS_MultiPass]

Hola StartKill, gracias por tu respuesta.

Efectivamente tal y como supones, las dos bases de datos están en el mismo server.

Realizar toda la operación con un procedimiento almacenado es una opción que he barajado, pero debido a que aún ando un poco verde con SQL, no sé como referenciar la tabla de una y otra base de datos.

Siendo más específico, entre el begin try y el commit transaction, tendré que meter una sentencia INSERT que diga que el insert lo voy a hacer en la base de datos B y un delete que diga que lo voy a hacer en la tabla1 de la base de datos A, pero no conozco la sintaxis, un ejemplo sería de gran ayuda (mientras yo voy repasando documentación).

[olbeup]

Hay una utilidad de MS que te muestra toda las sentencias de SQL SERVER 2005 y te la puedes descargar Libro en pantalla de SQL Server es la que yo uso cuando tengo alguna duda.

Un saludo.

[4PS_MultiPass]

...descargando...

[[mightydragonlor]]

Empezado por 4PS_MultiPass

Hola StartKill, gracias por tu respuesta.

Efectivamente tal y como supones, las dos bases de datos están en el mismo server.

Realizar toda la operación con un procedimiento almacenado es una opción que he barajado, pero debido a que aún ando un poco verde con SQL, no sé como referenciar la tabla de una y otra base de datos.

Siendo más específico, entre el begin try y el commit transaction, tendré que meter una sentencia INSERT que diga que el insert lo voy a hacer en la base de datos B y un delete que diga que lo voy a hacer en la tabla1 de la base de datos A, pero no conozco la sintaxis, un ejemplo sería de gran ayuda (mientras yo voy repasando documentación).

Código SQL [-]
BEGIN TRY
BEGIN TRANSACTION
  INSERT INTO [base de datos 1].dbo.tabla1(
  SELECT campos FROM [base de datos 2].dbo.tabla2)

  DELETE FROM [base de datos 1].dbo.tabla1
COMMIT TRANSACTION
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH

Algo por este estilo es como deberías usar, espero te sirva.

Saludos.

[4PS_MultiPass]

Muchas gracias compañeros, sin vuestra ayuda me hubiese quedado atascado demasiado tiempo intentando resolver este tema.

Bueno, pues finalmente me he decidido por el siguiente grupo de instrucciones. Espero que no parezca muy chapucero, pero después de algunas pruebas, esto me encaja para lo que quiero llevar a cabo, es decir, funciona, no sé si será la mejor opción, pero al menos me funciona.

Finalmente no he recurrido ni a un procedimiento almacenado ni a transacciones anidadas pero encajará (creo ).

Tomando el ejemplo de mightydragonlor, he visto la sintaxis para referenciar tablas de distintas bases de datos, es decir [base de datos 1].dbo.tabla1

Con ese dato lo que voy a hacer es componer una transacción que implique cambios en las dos bases de datos y... o todo va bien, o no se hace nada.

Nota: cSentencia es una variable de tipo WideString, dbDatos es un TADOConnection y zComillaSimple es una función que agrega una comilla simple ' al principio y al final de la cadena de caracteres que se le pasa como parámetro.

...
// preparo la transacción que implica dos bases de datos.
cSentencia := 
' INSERT INTO [HISTORIC].[dbo].[ARTICULO] (cCodigoArt,cDescriArt) ' + 
'     SELECT cCodigoArt,cDescriArt ' + 
'     FROM [DATA].dbo.ARTICULO ' + 
'     WHERE cCodigoArt = ' + zComillaSimple('009999') + ';';

cSentencia := cSentencia + 
' DELETE FROM [DATA].dbo.ARTICULO'+
'     WHERE cCodigoArt = ' + zComillaSimple('009999') + ';'

// Inicio la transacción.
dbDatos.BeginTrans;
try
    frmPrincipal.dbDatos.Execute(cSentencia);
    dbDatos.CommitTrans;
    // si se hace el cut & paste de manera correcta, saldrá por aquí.
    showMessage('todo correcto');
except
    dbDatos.RollbackTrans;
    // si algo va mal, se deshacen los cambios y saldrá por aquí.
    showMessage('Oh, oh. Algo ha ido mal.');
end;

Este trozo de código traspasa el artículo con código 009999 de la tabla ARTICULO de la base de datos DATA a la tabla ARTICULO de la base de datos HISTORIC, es decir una especie de Cut and paste.

Vuelvo a insistir en agradecer vuestra ayuda, sois fabulosos.