Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_drop_job_internal

  No additional text.


Syntax
create procedure sys.sp_cdc_drop_job_internal
(
	@job_type nvarchar(20)
)
with execute as 'dbo'
as
begin
    set nocount on

    declare @database_id    int
           ,@job_id         uniqueidentifier
           ,@retcode        int
           ,@action nvarchar(1000)
           ,@raised_error	int
           ,@raised_message	nvarchar(4000)
           ,@trancount		int
           ,@db_name		sysname

    set @db_name = db_name()
    set @raised_error = 0

    -- Wrap transaction logic in TRY/CATCH block
    BEGIN TRY

		set @trancount = @@trancount
		
		begin tran
		save tran sp_cdc_drop_job

		select @database_id = db_id()

		if object_id('msdb.dbo.cdc_jobs') is not null
		begin
			select @job_id = job_id
			from msdb.dbo.cdc_jobs
			where database_id = @database_id
			and job_type = @job_type

			if @job_id is not null
			begin
				-- drop the job
				set @action = N'msdb.dbo.sp_delete_job'
				exec msdb.dbo.sp_delete_job @job_id = @job_id

				-- remove the record from cdc_jobs
				set @action = N'delete from msdb.dbo.cdc_jobs'
				delete from msdb.dbo.cdc_jobs
					where database_id = @database_id and job_id = @job_id

				-- if this is the last job, drop the table
				if not exists (select database_id from msdb.dbo.cdc_jobs)
				begin
					set @action = N'drop table msdb.dbo.cdc_jobs'
					drop table msdb.dbo.cdc_jobs
				end	
			end
		end

		commit tran
		
	END TRY
	
	BEGIN CATCH
	
		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_cdc_drop_job
				commit tran
			end
		end
		
		set @raised_error = ERROR_NUMBER()
		set @raised_message = ERROR_MESSAGE()
		
	END CATCH
	
	if @raised_error = 0
		return 0
		
	raiserror(22835, 16, -1, @db_name, @action, @raised_error, @raised_message)	
    return 1
end

 
Last revision 2008RTM
See also

  sp_cdc_drop_job (Procedure)
sp_MSdrop_cdc (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