Set @Error = 1
IF (@@error <
>
0) --Check for error
IF @@error <
>
0 --Check for error
/* ENABLE Triggers as they were previously DISABLE */
EXEC ('ENABLE TRIGGER [dbo].[trig_sysmail_attachments] ON [dbo].[sysmail_attachments]')
EXEC ('ENABLE TRIGGER [dbo].[trig_sysmail_log] ON [dbo].[sysmail_log]')
EXEC ('ENABLE TRIGGER [dbo].[trig_sysmail_mailitems]ON [dbo].[sysmail_mailitems]')
IF @Error = 0
Set @Error = 0 --Assume no errors
PRINT 'NEED TO RE-ENABLE SSB'
RAISERROR (34054, 16, 1)
-- If an error occurred we want to ignore it
FETCH jobs_to_enable INTO @jobID
IF (@raiserror = 1)
RAISERROR (14365, -1, -1)
print 'Error Message: ' + ERROR_MESSAGE()
print 'Error Number: ' + convert(varchar(10),ERROR_NUMBER())
print 'Error Severity: ' + convert(varchar(10),ERROR_SEVERITY())
print 'Error State: ' + convert(varchar(10),ERROR_STATE() )
print 'Unable to add existing mail user to DatabaseMailUserRole'
print 'Unable to re-enable server broker. You might need to manually'
-- start our own transaction if there is no outer transaction
-- enable events infrastructure
DECLARE jobs_to_enable CURSOR LOCAL FOR
FETCH jobs_to_enable INTO @jobID
or (error = 17803 or error = 701 or error = 802 or error = 8645 or error = 8651 or error = 8657 or error = 8902)
print 'Error Message: ' + ERROR_MESSAGE()
print 'Error Number: ' + convert(varchar(10),ERROR_NUMBER())
print 'Error Severity: ' + convert(varchar(10),ERROR_SEVERITY())
print 'Error State: ' + convert(varchar(10),ERROR_STATE() )
print 'Unable to create a primary key constraint on sysmail_principalprofile table'
print 'Unable to map existing principal_id values to sid column'
print 'Unable to map existing profile id values to profile_sid column'
print 'Unable to map existing profile name values to profile_id column'
@schedule_id = @step_id, -- This is the start step
@monitor_autostart OUTPUT,
@sqlserver_restart OUTPUT,
-- S (Start - with service check),
@auto_start OUTPUT,
ELSE 3 -- Assume non auto-start if passed a junk value
@echo_error = @echo_error_int,
RAISERROR (14307, -1, -1, @name)
RAISERROR (14586, -1, -1, @name)
WHERE (error = @message_id)))
-- TODO: RAISEERROR that specified object_set isn't created from the facet that the policy condition has been created from
ELSE [status] -- Leave the error status unchanged
RAISERROR (34003, 1,1, @errorNumber, @errorMessage ) with log
-- start of the stored procedure.
@echo_error = @append_query_error,
RAISERROR (14307, -1, -1, @name)
-- Remove the preceding space if -Continuous does not start at the beginning of the commandline
-- start of the stored procedure.
RAISERROR (14307, -1, -1, @name)
-- Error case
-- Error in the conversation, hence ignore all the messages of this conversation.
-- TODO: RAISEERROR that specified object_set isn't created from the facet that the policy condition has been created from
-- TODO: RAISERROR that specified object set doesn't exist
-- this will throw an error of proxy_id does not exist
--Error msg with be in either the attachment table or the query table
--Log error and continue. Don't want to block the following messages on the queue
--Log error and continue. Don't want to block the following messages on the queue by rolling back the tran
@RetErrorMsg NVARCHAR(4000), --Impose a limit on the error message length to avoid memory abuse
ELSE [status] -- Leave the error status unchanged
RAISERROR (14365, -1, -1)
RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage)
RAISERROR (34001, 1,1, @bodyStr) with log
RAISERROR (34002, 1,1) with log
RAISERROR (34011, -1, -1, 'root_condition', @execution_mode)
RAISERROR (34014, -1, -1)
RAISERROR (34021, -1, -1, @name, @value_type)
--Note: we will get error no. 9617 if the service queue 'ExternalMailQueue' is currently disabled.
-- If an error occurred we want to ignore it
@raise_error = 0,
RAISERROR (14307, -1, -1, @name)
RAISERROR (15014, -1, -1, @readrole)
RAISERROR (15014, -1, -1, @writerole)
-- Always check the transfer tables for data. They may also contain error messages
-- Exit if there was an error and caller doesn't want the error appended to the mail
-- Procedure must start its own
IF (@rc <
>
0 AND @append_query_error = 0)
PRINT 'Error ' + @certError + ' backing up certificate.'
SET @currentExtensionStart = @currentExtensionEnd + 1
if (@@error <
>
0)
monitor_autostart = ISNULL(@monitor_autostart, 0),
select @certError = @@error
select @certError = ERROR_NUMBER()
sqlserver_restart = ISNULL(@sqlserver_restart, 1),
-- <
Failure Message=THe
-- Check if there was some error in reading from queue
-- Enable both jobs
-- If a valid request is read with or without an error break out of loop
-- Log the start of the collection set
-- Procedure must start its own
-- So, check if the job is currently running before asking agent to start it
-- Start the collection job if you are in ad hoc or continuous modes
-- enable policy automation
-- subplan_id supplied so simply start the subplan's job
-- this will throw an error of proxy_id does not exist
-- to start a collection set without a proxy, the caller has to be a sysadmin
--Log error and continue.
--Log error and continue. Don't want to block the following messages on the queue
@raise_error = 1,
ALTER TABLE [dbo].[syscollector_collection_sets_internal] ADD dump_on_any_error bit default 0
EXEC sp_syscollector_event_oncollectionstart @collection_set_id = @collection_set_id, @log_id = @log_id OUTPUT
RAISERROR (14351, -1, -1)
RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage)
RAISERROR (34011, -1, -1, 'is_enabled', @execution_mode)
RAISERROR (34011, -1, -1, 'schedule_uid', 4)
RAISERROR (34014, -1, -1)
RAISERROR (35001, -1, -1)
RAISERROR (35002, -1, -1)
RAISERROR (35003, -1, -1)
RAISERROR (35004, -1, -1)
RAISERROR (35005, -1, -1)
RAISERROR (35006, -1, -1)
RAISERROR (35007, -1, -1)
RAISERROR (35008, 1, 1)
RAISERROR (35009, -1, -1)
RAISERROR (35010, -1, -1, @server_type)
RAISERROR (35011, -1, -1)
RAISERROR (35012, -1, -1)
SET dump_on_any_error = @dump_on_any_error
dump_on_any_error bit default 0 NOT NULL, -- configure SQL dumper to dump on any SSIS errors
execution_row_count_errors int NULL, -- Number of rows that were re-directed to an error output due to processing errors
start_time datetime NOT NULL, -- Collection set or package execution start time
-- An error occurred, must determine
-- error and let the outer transaction do the rollback
RAISERROR (14591, -1, -1, @name)
RAISERROR (@ErrorMessage, -- Message text.
append_query_error BIT NULL,
-- An error occurred, must determine
-- Error 15240 happens when instmsdb.sql is run repeatedly and directly on a sql server, so the
-- Note, an error has already beed raised in this case
-- Only sysadmin can start multi-server job
-- Prepare tp echo error information to the caller.
-- Procedure must start its own
-- This error indicates that query results size was over the configured MaxFileSize.
-- nvarchar(max), then the engine will raise error 599.
-- start our own transaction if there is no outer transaction
@raise_error = 1,
DECLARE @currentExtensionStart int
IF (@raise_error <
>
0)
RAISERROR (14307, -1, -1, ''00000000-0000-0000-0000-000000000000'')
RAISERROR (14307, -1, -1, @name)
RAISERROR (14429,-1,-1)
RAISERROR (14442,-1,-1)
RAISERROR (14585, -1, -1, @name)
RAISERROR (14586, -1, -1, @name)
RAISERROR (14587, -1, -1, @name)
RAISERROR (14593, -1, -1, @name)
RAISERROR (@ErrorMessage, -- Message text.
RAISERROR('Unable to expand the msdb database. INSTMSDB.SQL terminating.', 20, 127) WITH LOG
SELECT @raise_error = 1
SET @currentExtensionStart = 0
if (@@error <
>
0)
select @certError = @@error
-- Procedure must start its own transaction if we don't have one already.
-- Restart the collection set if it has been already running
RAISERROR (@threshold_alert, 16, 1, @primary_server_name, @primary_database_name, @delta_string)
RAISERROR (@threshold_alert, 16, 1, @secondary_server_name, @secondary_database_name, @delta_string)
-- so if it's bad we'll get a runtime error when the job executes.
-- the start step was validated when the job was created at the MSX
-- Adding the -Continuous parameter (non auto-start to auto-start)
-- Check if there was some error in reading from queue
-- Error Handler
-- If @message is passed, we can allow to enter the error for a collection set
-- NOTE: For [new] MSX jobs we allow the start step to be other than 1 since
-- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
-- Post start instruction(s)
-- Raise an error if the @subplan_id doesn't exist
-- Raise an error if the @task_detail_id doesn't exist
-- Removing the -Continuous parameter (auto-start to non auto-start)
-- Start a transaction if not already in one.
-- TODO: Verify the error message is appropriate for object sets as well and not specific to policies
-- The 84CEC861... package is an id of our special Master package that is allowed to start
-- The job is local, so start (run) the job locally
-- The schedule_id (if specified) is the start step
-- error in execution that causes the log to stay open.
-- if you stopped the collection set, restart it
-- otherwise we will rely on the entries in sysssislog table to get the error message.
-- re-raise the error non-fatally
-- use a try catch login to prevent any error when trying
--Echo error must be specified
--Get the MSX originating_server_id. If @originating_server isn't the msx server error out
--Report error if the mail queue has been stopped.
--TODO: needs error better message ? >
>
'Specify the OLAP server name in the %s parameter'
--use a try catch login to prevent any error when trying to insert/update syscachedcredentials table
@dump_on_any_error bit = NULL, -- configure SQL dumper to dump on any SSIS errors
@row_count_error int = NULL,
AND ((@has_error IS NULL) OR (DATALENGTH(error_message) >
= 1 * @has_error))
BEGIN -- We created the jobs here in this transaction, post a request for agent to start as soon as we commit
BEGIN TRY --prevent sp_add_proxy raising severity 16 error that will terminate upgrade scritp when proxy account has bad info
DECLARE @raise_error bit
ENABLE TRIGGER [syspolicy_server_trigger] ON ALL SERVER
IF (@@ERROR <
>
0)
IF (@@error <
>
0 OR @rv <
>
0) GOTO rollback_quit -- error
IF (@@error <
>
0 OR @rv <
>
0) GOTO rollback_quit -- error
IF (@@error <
>
0)
IF (@certError <
>
0)
IF (@dump_on_any_error IS NOT NULL)
IF @@error <
>
0 or @rv <
>
0
IF @append_query_error IS NULL
RAISERROR ('Creating Collection Item %s...', 0, 1, @collection_item_name)
RAISERROR ('Creating system Collection Set %s...', 0, 1, @collection_set_name) WITH NOWAIT
RAISERROR ('Deleting cached auto-generated T-SQL Data Collection packages from msdb...', 0, 1) WITH NOWAIT
RAISERROR ('Stopping collection set with ID %d...', 0, 1, @collection_set_id) WITH NOWAIT
RAISERROR ('Updating Collection Item %s...', 0, 1, @collection_item_name)
RAISERROR ('Updating system Collection Set %s...', 0, 1, @collection_set_name) WITH NOWAIT
RAISERROR ('Uploading data collector package from disk: %s', 0, 1, @filename) WITH NOWAIT
RAISERROR (14262, 16, 1, N'primary_id', N'msdb.dbo.log_shipping_primaries')
RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
RAISERROR (14590, -1, -1, @name)
RAISERROR (14596, -1, -1, @name)
RAISERROR (14597, -1, -1, @stringfromclsid)
RETURN (1) -- error must be yyyymmddhhmm or yyyymmdd
SELECT @auto_start = 3 -- Manual start
SELECT @auto_start = CASE @auto_start
SELECT @sqlserver_restart = 1
SELECT description FROM master.dbo.sysmessages WHERE (error = @error) AND (msglangid = (SELECT msglangid FROM sys.syslanguages WHERE (langid = @@langid)))
SELECT description FROM master.dbo.sysmessages WHERE (error = @error) AND (msglangid = 1033)
if @retcode != 0 or @@error != 0
raiserror(N'Error executing sp_vupgrade_replication.', 10, 1)
raiserror(N'Error saving upgrade script status.', 10, 1)
select @certError = @@error
-- This will only be the case when @append_query_error is set to 0 (false)
-- insert new account record, rely on primary key constraint to error out
-- insert new profile record, rely on primary key constraint to error out
--Check the send attempts and log and error if send_attempts >
= retry count.
--Raise an error it the query execution fails
@append_query_error BIT = 0,
@append_query_error BIT = 0,
@echo_error BIT,
@raise_error INT = 1,
IF @raise_error = 1
IF (@@ERROR <
>
0)
IF (@@error <
>
0)
IF (@certError <
>
0)
RAISERROR (14261,16,1, N'primary_server_name.primary_database_name', @pair_name)
SELECT @flags = -1, @raise_error = 0
declare @certError int
print 'Error Message: ' + ERROR_MESSAGE()
print 'Error Number: ' + convert(varchar(20), ERROR_NUMBER())
print 'Error Severity: ' + convert(varchar(20), ERROR_SEVERITY())
print 'Error State: ' + convert(varchar(20), ERROR_STATE())
select @certError = @@error
-- Check start step
-- For first implementation we will have this parameters but using it will generate an error - not implemented yet.
-- If this is an autostart LogReader or Distribution job, add the [new] '-Continuous' paramter to the command
-- If this is an autostart job, start it now (for backwards compatibility with 6.x SQLExecutive behaviour)
-- In 6.x active start date was not restricted, but it is in 7.0
so to avoid a noisey
-- Is @enable the only parameter used beside jobname and jobid?
-- enable Shiloh-Yukon upgrade script
-- enable Yukon SP2-Katmai RTM upgrade script
-- enable intra-Yukon upgrade script
--// Don't error if no entries found, as the desired result will be met.
--// Starting parent category. If null, start at root.
--// This is used for realtime viewing of package logs, so don't error if no entries
--// definitions will act as an assert, but we check here (with a nice message) for user-error stuff
@auto_start INT = NULL, -- 1 or 0
@error INT
@error_flag INT = 1, -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
@error_flag INT = 1, -- Set to 0 to suppress the error from xp_sqlagent_notify if SQLServer agent is not running
@error_recipient NVARCHAR(30) = NULL, -- Network address of error popup recipient
@has_error TINYINT = NULL, -- NULL or 1
@monitor_autostart INT = NULL, -- 1 or 0
@server_name sysname = NULL, -- The specific target server to start the [multi-server] job on
@sqlserver_restart INT = NULL, -- 1 or 0
@step_name sysname = NULL, -- The name of the job step to start execution with [for use with a local job only]
DECLARE @auto_start INT
DECLARE @monitor_autostart INT
DECLARE @sqlserver_restart INT
IF ((@sqlserver_restart IS NOT NULL) AND (@sqlserver_restart <
>
0))
IF (@@ERROR <
>
0)
IF (@@error >
0)
IF (@@error <
>
0)
IF (@auto_start IS NOT NULL)
IF (@has_error IS NOT NULL) AND (@has_error <
>
1)
IF (@monitor_autostart IS NOT NULL)
IF (@monitor_autostart IS NOT NULL) AND ((@monitor_autostart <
0) OR (@monitor_autostart >
1))
IF (@retval = 1 AND @raise_error <
>
0)
IF (@sqlserver_restart IS NOT NULL)
IF @@error <
>
0 or @rv <
>
0
IF EXISTS (SELECT * FROM master.dbo.sysmessages WHERE (error = @error) AND (msglangid = (SELECT msglangid FROM sys.syslanguages WHERE (langid = @@langid))))
SELECT auto_start = CASE @auto_start
catalog_media_number SMALLINT NULL, -- containing the start of the 'directory' data stream
first_media_number SMALLINT NULL, -- containing the start of this backup (first SSET)
restart BIT NULL, -- Restart(1), Norestart(0)
- Katmai MSDB detected, so enable the last 1
- Shiloh MSDB detected, so enable all 3
- Yukon MSDB detected, so enable the last 2
-- 4- Restart the collection set if it was running (sp_syscollector_start_collection_set
-- Enable them here once and return them to their original state when done
-- Start a conversation with the remote service
-- Start and stop Data Collector
-- Temporarily enable the 'Agent XPs' config option so that sp_ssis_putpackage can
-- append_query_error : Option for appending query error messages to the mail item
-- enable certificate for OBD
-- enable policy checking
-- push and enable option state for xp_instance_regread in case categories are off
-- raise an error if the password already exists
-- sysmail_log : Contains error and event logging
-- use exec to drop, otherwise drop
/* Create auxilary procedure to enable OBD (Off By Default component */
/* Create auxilary procedure to enable OBD component */
/* Drop auxilary procedure to enable OBD component */
/* Enable Logshipping (Yukon) */
/* Record time of start of creates */
/* sp_maintplan_start */
@raiserror bit = 1
DECLARE @error int
Declare @start datetime
Declare @certError int
IF (@@ERROR <
>
0 OR @ReturnCode <
>
0) GOTO QuitWithRollback
IF (@@ERROR <
>
0 OR @ReturnCode <
>
0) GOTO QuitWithRollback
IF (@@error <
>
0)
IF (@certError <
>
0)
IF @error <
>
0
If @retcode != 0 or @@error != 0
It uses sys.sp_dbscriptlevel to enable the other upgrade scripts to run.
SELECT start = getdate() INTO #InstMsdb
SELECT start = getdate() INTO #sysdbupg
SET @error = @@ERROR
Select @certError = @@error
Select @start = start from #InstMsdb
Select @start = start from #sysdbupg