Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_repl_job_unsafe

  No additional text.


Syntax
create procedure sys.sp_MSdrop_repl_job_unsafe
(
	@job_name		sysname = NULL,
	@job_id 		binary(16) = NULL,
	@job_step_uid	uniqueidentifier = NULL
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
	DECLARE @retcode			int,
			@command			nvarchar(4000),
			@credential 		sysname,
			@proxy_id			int,
			@subsystem_id		int,
			@flags				int,
			@sid				varbinary(85),
			@login				sysname
	
	SELECT @command				= NULL,
			@credential			= NULL,
			@proxy_id 			= NULL,
			@subsystem_id 		= NULL,
			@flags 				= NULL,
			@sid 				= NULL,
			@login				= NULL

    -- Return immediately if this is running on SQLExpress or SQLWeb
    IF (sys.fn_MSrepl_editionid () in (22, 40))
        RETURN 0

	BEGIN TRANSACTION tr_drop_repl_job_internal
	SAVE TRANSACTION tr_drop_repl_job_internal

	SELECT @credential = sc.name,
			@proxy_id = sp.proxy_id,
			@subsystem_id = ss.subsystem_id,
			@flags = spl.flags,
			@sid = spl.sid
		FROM msdb.dbo.sysjobs_view as sjv
			JOIN msdb.dbo.sysjobsteps as sjs
				ON sjv.job_id = sjs.job_id
			JOIN msdb.dbo.sysproxies sp
				ON sjs.proxy_id = sp.proxy_id
			JOIN msdb.dbo.syssubsystems ss
				ON sjs.subsystem = ss.subsystem
			JOIN sys.credentials sc
				ON sp.credential_id = sc.credential_id
			-- left join since in sysadmin user case no proxylogin will be found
			LEFT JOIN msdb.dbo.sysproxylogin spl
				ON sp.proxy_id = spl.proxy_id
		WHERE sjv.master_server = 0
			AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
			AND (sjv.job_id = @job_id
					OR sjv.name = @job_name)
			AND sjs.step_uid = @job_step_uid
	IF @@ERROR <> 0 or @retcode <> 0
		GOTO UNDO

	EXEC @retcode = msdb.dbo.sp_delete_job @job_name = @job_name,
											@job_id = @job_id
	IF @@ERROR <> 0 or @retcode <> 0
		GOTO UNDO

	-- we will not drop the proxy, proxy mappings etc if there's
	-- still at least 1 job that's still using the given proxy id
	IF NOT EXISTS (SELECT *
					FROM msdb.dbo.sysjobsteps
					WHERE proxy_id = @proxy_id)
	BEGIN
		IF @proxy_id IS NOT NULL
		BEGIN
			CREATE TABLE #enm_login_for_proxy (proxy_id int, proxy_name sysname, flags int, name sysname NULL, sid varbinary(85), principal_id int NULL)
			
			INSERT INTO #enm_login_for_proxy
				EXEC msdb.dbo.sp_enum_login_for_proxy @proxy_id = @proxy_id

			SELECT @login = name
				FROM #enm_login_for_proxy
				WHERE proxy_id = @proxy_id
					AND @sid IS NOT NULL
					AND sid = @sid

			DROP TABLE #enm_login_for_proxy
				
			IF @login IS NOT NULL
			BEGIN
				EXEC @retcode = msdb.dbo.sp_revoke_login_from_proxy @name = @login,
																	@proxy_id = @proxy_id
				IF @@ERROR <> 0 OR @retcode <> 0
					GOTO UNDO
			END

			IF @subsystem_id IS NOT NULL
				AND EXISTS (SELECT *
								FROM msdb.dbo.sysproxysubsystem
								WHERE proxy_id = @proxy_id
									AND subsystem_id = @subsystem_id)
			BEGIN
				EXEC @retcode = msdb.dbo.sp_revoke_proxy_from_subsystem @subsystem_id = @subsystem_id,
																		@proxy_id = @proxy_id
				IF @@ERROR <> 0 OR @retcode <> 0
					GOTO UNDO
			END

			IF EXISTS (SELECT *
						FROM msdb.dbo.sysproxies
						WHERE proxy_id = @proxy_id)
			BEGIN
				EXEC @retcode = msdb.dbo.sp_delete_proxy @proxy_id = @proxy_id
				IF @@ERROR <> 0 OR @retcode <> 0
					GOTO UNDO
			END
		END
		
		IF @credential IS NOT NULL
			AND EXISTS (SELECT *
							FROM sys.credentials
							WHERE name = @credential)
		BEGIN
			SELECT @command = N'DROP CREDENTIAL ' + QUOTENAME(@credential)

			EXEC(@command)
			IF @@ERROR <> 0 OR @retcode <> 0
				GOTO UNDO
		END
	END
	
	COMMIT TRANSACTION tr_drop_repl_job_internal

	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_drop_repl_job_internal
	COMMIT TRANSACTION
	
	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repl_job (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash