1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| CREATE PROCEDURE p_SetTransactionLogMarkAll
@_name nvarchar(128)
AS
BEGIN
DECLARE @sql_pattern nvarchar(1024) = 'EXEC [SERVER_NAME].[DATABASE_NAME].dbo.sp_SetTransactionLogMark @name'
, @sql nvarchar(max);
BEGIN TRY
BEGIN TRANSACTION
SELECT @sql = CASE WHEN @sql IS NULL THEN '' ELSE @sql + CHAR(13) + CHAR(10) END
+ REPLACE(REPLACE(@sql_pattern, '[SERVER_NAME]', QUOTENAME(@@SERVERNAME)), '[DATABASE_NAME]', QUOTENAME(database_name))
FROM (
VALUES ('Base1'), ('Base2'), ('Base3')
) AS DB(database_name);
EXEC sys.sp_executesql
@sql
, N'@name nvarchar(128)'
, @name = @_name;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10))
+ ' - ' + CAST(ERROR_LINE() AS varchar(10)) + ' - '+ ERROR_MESSAGE()
, @err_svt int = ERROR_SEVERITY()
, @err_stt int = ERROR_STATE();
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
RAISERROR(@err_msg, @err_svt, @err_stt);
RETURN;
END CATCH
END |
Partager