У меня есть sql сценарий, который установлен прокрутиться к производству. Я перенес различные проекты в отдельные транзакции. В каждой из транзакций мы создали хранимые процедуры. Я получаю сообщения об ошибках
Сообщение 156, Уровень 15, состояние 1, Строка 4 Неправильных синтаксиса около ключевого слова 'процедура'.
Я создал этот сценарий в качестве примера для иллюстрирования
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
Ошибка, кажется, подразумевает, что я не могу создать сохраненный procs в транзакции, но я не нахожу документов, в которых говорится иначе (возможно, Google не является дружественным сегодня).
Вы не можете написать свой сценарий таким образом, поскольку есть много операторов, которые должны выполняться в отдельном пакете. Вместо этого я бы порекомендовал сделать что-нибудь похожее на то, как Red-Gate SQL Compare создает свои скрипты:
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
попробуйте выполнить процедуру создания
в EXEC ('...')
, например:
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
Кажется, я припоминаю, что вы не можете делать такие вещи, как создание, изменение или удаление объектов схемы базы данных, включая хранимые процедуры, внутри транзакции (поскольку такие структурные изменения не являются транзакционными: вы не можете откатить их назад с изменениями данных).