SQL Server 2008
J'ai une procédure stockée qui sélectionne les données d'une source, les insère dans une table intermédiaire, supprime certaines lignes de la table de faits, puis insère d'autres lignes de la table intermédiaire table dans la table de faits mentionnée.
Je voudrais ne pas exécuter la procédure si N'IMPORTE QUELLE partie du code échoue. Ainsi, par exemple, si la suppression des lignes échoue pour une raison quelconque, je voudrais que la table de scène soit également inchangée.
Est-ce possible? Pour 'vérifier' le code et insérer des tableaux avant d'exécuter le code?
Edit: merci! J'ai mis en œuvre l'une des solutions que vous avez suggérées. Le fait est que j'ai une procédure imbriquée dans une autre procédure (try catch imbriqué dans une autre try catch) - Je veux attraper les erreurs et les insérer dans la table du journal des erreurs que j'ai créée. La capture d'erreur a fonctionné comme prévu auparavant, mais après les modifications, les erreurs ne sont plus insérées dans la table et j'obtiens l'erreur: La transaction en cours ne peut pas être validée et ne peut pas prendre en charge les opérations qui écrivent dans le fichier journal. Annulez la transaction.
La requête que j'exécute est la procédure MAIN (ci-dessous)
la structure de ma première requête est:
CREATE PROCEDURE [dbo].[myload] AS BEGIN set nocount on; declare @trancount int; set @trancount = @@trancount; begin try if @trancount = 0 begin transaction else save transaction [myload]; EXEC MAIN_procedure lbexit: if @trancount = 0 commit; END TRY BEGIN CATCH declare @error int, @severity varchar(4000), @estate varchar(55), @procedure varchar (55), @eline varchar (255), @emessage varchar (4000), @edate date; SELECT @error=ERROR_NUMBER(), @severity=ERROR_SEVERITY(), @estate=ERROR_STATE() , @procedure=ERROR_PROCEDURE(), @eline=ERROR_LINE(), @emessage= ERROR_MESSAGE() , @edate=GETDATE(); if XACT_STATE() = -1 rollback; if XACT_STATE() = 1 and @trancount = 0 rollback if XACT_STATE() = 1 and @trancount > 0 rollback transaction [mytransaction]; INSERT INTO myErrorTable SELECT ProcedureName= 'myload', @error,@severity, @estate, @procedure, @eline, @emessage, @edate; END CATCH END
et le code de la procédure principale est:
[procedure main] BEGIN TRY EXEC [procedure instertdata] END TRY BEGIN CATCH INSERT INTO MyErrorLogTable SELECT ProcedureName= 'mytable', ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS TimeAndDate; END CATCH
Qu'est-ce que je fais mal? Comment le gérer?
MODIFIER: J'ai déjà mentionné que j'avais une procédure imbriquée dans une autre. Ce que j'ai fait, j'ai déplacé l'annulation de la transaction vers la transaction externe. La procédure MAIN n'a pas de restauration de transaction .. la procédure est imbriquée Donc, cela ressemble à ceci:
[procedure instertdata] BEGIN TRANSACTION BEGIN TRY INSERT INTO Your_Table (Col1, Col2) VALUES .... UPDATE Your_Table SET [Col1] = ....... WHERE ........ COMMIT TRANSACTION END TRY BEGIN CATCH INSERT INTO MyErrorLogTable SELECT AffectedTable = 'mytable', ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS TimeAndDate; IF @@trancount > 0 ROLLBACK TRANSACTION END CATCH
Semble fonctionner jusqu'à présent, merci à tous pour votre aide! p>
3 Réponses :
Le bloc de transaction ROLLBACK
toutes les transactions, si l'une des transactions échoue à l'intérieur du bloc de transaction.
N'utilisez pas TRUNCATE
dans le bloc de transaction, car les instructions tronquées n'ont jamais été annulées.
BEGIN TRANSACTION [Tran1] BEGIN TRY INSERT INTO Your_Table (Col1, Col2) VALUES .... UPDATE Your_Table SET [Col1] = ....... WHERE ........ COMMIT TRANSACTION [Tran1] END TRY BEGIN CATCH ROLLBACK TRANSACTION [Tran1] END CATCH
Vous pouvez utiliser une instruction try / catch
et raiserror
pour renvoyer le contrôle à la procédure principale lors de la reprise de la transaction.
Mais je peux suggérer au lieu de jouer avec la table de faits, de créer une deuxième table de transfert comme 'staging_table_2' et de manipuler les données dans cette mise en scène, vous pouvez faire référence aux tables de faits dans les jointures, puis utiliser une simple fusion pour remplir le fait table.
De cette façon, vous serez toujours sûr que les données de votre fait sont propres et que les échecs n'ont aucun impact sur l'incohérence des données.
d'accord «lancer» est un recommandé plutôt que «riseerror» maintenant.
Vous devez utiliser la fonction Savepoint pour enregistrer une partie de la transaction, c'est-à-dire enregistrer les résultats dans la table de préparation. J'ai utilisé l'approche de transaction imbriquée Remus Rusunu avec des points de sauvegarde. Vous pouvez lire le même ici
declare @trancount int; set @trancount = @@trancount; BeginTry if @trancount = 0 BEGIN TRANSACTION INSERT INTO StagingTable... SAVE TRANSACTION StagingTableInsertion DELETE FROM FactTable WHERE <CONDITION>... INSERT INTO FactTable... SELECT * FROM StagingTable IF @tranCount = 0 COMMIT TRANSACTION EndTry BeginCatch declare @error int, @message varchar(4000), @xstate int; select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE(); if @xstate = -1 rollback; if @xstate = 1 and @trancount = 0 rollback if @xstate = 1 and @trancount > 0 rollback transaction StagingTableInsertion; raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ; EndCatch
Enveloppez le code dans une transaction et restaurez-le en cas d'échec
Si une transaction a échoué, vous devez l'annuler avant , vous pouvez continuer les opérations SQL telles que les erreurs de journalisation. En règle générale, dans un bloc try / catch, vous testez s'il existe une transaction ouverte et s'il y a une annulation. Ensuite, effectuez une gestion des erreurs.
Merci Dale. J'ai mis IF @@ trancount> 0 ROLLBACK TRANSACTION avant INSERT mais j'obtiens toujours l'erreur
Quelle erreur réelle utilisez-vous pour tester cela? Certaines erreurs sont irrécupérables et ne vous permettront donc pas de continuer quoi qu'il arrive.
simples: mauvais type de données de colonne ou nom de table
Je pense qu'ils pourraient bien compter comme non récupérables. Essayez un défaut de convertir une chaîne en une date d'heure.
J'ai déplacé la «restauration de transaction» vers la procédure externe et cela fonctionne. Au moins semble fonctionner, je le teste toujours!