J'ai un script SQL qui est réglé pour rouler à la production. J'ai enveloppé les différents projets dans des transactions distinctes. Dans chacune des transactions, nous avons créé des procédures stockées. Je reçois des messages d'erreur
msg 156, niveau 15, état 1, ligne 4 Syntaxe incorrecte près du mot clé 'Procédure'. P>
J'ai créé cet exemple script pour illustrer P>
Begin Try
Begin Transaction
-- do a bunch of add/alter tables here
-- do a bunch of data manipulation/population here
-- create a stored proc
create procedure dbo.test
as
begin
select * from some_table
end
Commit
End Try
Begin Catch
Rollback
Declare @Msg nvarchar(max)
Select @Msg=Error_Message();
RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch
4 Réponses :
Essayez de faire le Créer la procédure code> dans EXEC ("... ') code>, comme ceci: Begin Try
Begin Transaction
-- do a bunch of add/alter tables here
-- do a bunch of data manipulation/population here
-- create a stored proc
EXEC ('create procedure dbo.test
as
begin
select * from some_table
end')
Commit
End Try
Begin Catch
Rollback
Declare @Msg nvarchar(max)
Select @Msg=Error_Message();
RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch
GO
+1. Cela doit être fait, en tant que Créer une procédure ... Appel doit être la première instruction dans le script. Mettez-le dans son propre lot avec EXEC résout cela. Cela peut également être utilisé pour créer des fichiers de script créant ou met à jour une procédure stockée. Très utile pour l'archivage de script de procédure.
Dans l'échantillon, si vous l'exécutez directement (placez un nom de table réel pour certains_Table) avec le document DDL / DML commenté que l'erreur se produit toujours et pourtant la procédure de création est la première déclaration ou suis-je mal compris.
Le code se déroule bien pour moi, je ne suis pas sûr de ce que vous changez
KM, vous connaissez les problèmes que User1035920 vient de faire remarquer ci-dessous?
@knocte, vous pouvez toujours suivre votre exécutant () avec un chèque des tables système pour voir si la procédure existe et sinon, émettez un RaisError () pour tomber dans la prise et la restauration.
Je semble vous rappeler que vous ne pouvez pas faire des choses comme créer, modifier ou supprimer des objets de schéma de base de données, y compris les procédures stockées - dans une transaction (car ces modifications structurelles ne sont pas transactionnelles: vous ne pouvez pas les rouler avec des modifications de données).
J'ai testé le code de ma réponse, cela crée non seulement une procédure stockée, mais si vous forcez une restauration dans le code, elle le retournera également.
@Km: Cela aurait pu modifier les versions plus récentes de SQL Server bien sûr.
Vous ne pouvez pas écrire votre script de cette manière car il existe de nombreuses déclarations qui doivent être exécutées dans leur propre lot. Au lieu de cela, je recommanderais de faire quelque chose de ressemblant à la comparaison SQL de Red-Gate's Construit ses scripts:
Set Xact_Abort On
GO
Begin Transaction
GO
If object_id('tempdb..#tmpErrors') is not null
Drop Table #tmpErrors
GO
Create Table #tmpErrors( [Error] int not null )
GO
Create Procedure dbo.Test
As
Begin
--....
End
GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO
-- more statements
GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO
--.....
IF NOT EXISTS(SELECT * FROM #tmpErrors)
BEGIN
PRINT 'The database update succeeded'
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'The database update failed'
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
END
GO
DROP TABLE #tmpErrors
GO
SSMS se plaint que erreur n'est pas une colonne dans #tmperrors (erreur) Sélectionnez 1 doit-il être ID (ou Créer une table #TMPorts (ID INT NON NULL) doit être Créer une table #TMPortsRors (Erreur Int Not NULL) ?
@Samholder - Oui. C'est une faute de frappe de ma part que j'ai corrigé. Si vous allez avec ID , l'instruction Créer une table doit évidemment utiliser ID . Si vous allez avec erreur , l'instruction Créer une table doit être mise à jour pour ce fait.
Merci de clarifier. +1 de moi. Très bonne réponse.
C'est une excellente réponse merci beaucoup merci beaucoup. Où avez-vous appris cela?
@ PLASSESTOPUPUPVOTINGME - Comme mentionné dans mon message, voici la manière dont SQL de Red-Gate se compare à ses modifications de données.
Il existe divers problèmes avec la solution proposée par km:
Si le contenu de ce que vous mettez dans votre appel Exec () est incorrect sémantiquement (par exemple, vous mettez une table non existante dans la procédure stockée de l'intérieur) que l'erreur ne bulle pas et la transaction n'est pas retouché.
Si le contenu de ce que vous mettez dans votre appel EXED () est incorrect syntaxiquement (par exemple, vous mettez SelectT au lieu de sélectionner à l'intérieur de la procédure stockée), la transaction semble être roulée en arrière mais une édition complètement cryptique des bulles d'erreur. Up:
Je suis donc toujours en perte de créer une procédure à l'intérieur d'une transaction mais que vous avez toujours la transaction et que la capture d'essai soit utile.
Utilisez le bloc de capture qui est dans la réponse de Mile ici: Stackoverflow.com/Questtions/180075/...
Oui, désolé, j'aurais dû mentionner ça. SQL 2008.
J'ai décidé de tirer le Create ProC hors de la transaction et d'essayer / attraper dans sa propre section. Je mets dans un "si existez alors lâchez" la déclaration et ajoute un "si il existe alors un message d'impression", puis de l'imprimerie "autour de la création de processus stockée. Merci pour tous les commentaires, j'ai appris quelque chose.