quote:

---

Host Guest_ken_ms

Q: cdickey : Can table variables be used with the output of stored procedures?

A: No, though you can return a cursor and get similar functionality.

---

set nocount on
declare 
	@RC								integer,
	@DebugLevel						integer	,
 	@Server							nvarchar(40),
 	@TargetBackupPath				nvarchar(255),
	@InitialRestore					integer,
	@SQLServer						nvarchar(40),	
	@DBName							nvarchar(40),
 	@LocalBackupSharePath			nvarchar(255),
	@ErrorDescription				varchar(255),
	@BackupSetId					integer,
	@PhysicalDeviceName				varchar(255),
	@FileType						varchar(2),
	@NumberOfCursorRows				integer

DECLARE @BackupListCursor CURSOR

Set	@DebugLevel = 0
Set	@Server = 'devlpt13'
Set	@TargetBackupPath = 'c:\'
Set @InitialRestore = 1
Set @SQLServer = 'PrimSQL'
Set @DBName = 'Contact'
Set @LocalBackupSharePath = 'SQL_backups\'
--Set @ErrorDescription = 'Before SP Exec'

Select @ErrorDescription as 'before'
exec @Rc=up_LogShipping_GetBackupList 
	@DebugLevel, @Server, @TargetBackupPath, @InitialRestore, @SQLServer, @DBName, @LocalBackupSharePath, 
	@InsideSPErrorDescription = @ErrorDescription OUTPUT, @BackupListCursor= @BackupListCursor OUTPUT,
	@NumberOfCursorRows = @NumberOfCursorRows OUTPUT

Select @ErrorDescription as 'after'
IF Cursor_Status('variable', '@BackupListCursor') <= 0
   	BEGIN
	Print 'Nothing in Cursor'
END
ELSE
	BEGIN
	print 'Cursor Populated'
	while (Cursor_Status('variable', '@BackupListCursor') > 0 )
		and (@NumberOfCursorRows > 0)
		begin
		fetch next from @BackupListCursor INTO @BackupSetId, @PhysicalDeviceName, @FileType
		select @BackupSetId, @PhysicalDeviceName, @FileType
-- 		exec @ReturnCode=LogShip.dbo.up_LogShipping_CopyBackup 	
-- 					@DebugLevel = @DebugLevel,
-- 					@Server = @SourceServer, 
-- 					@LogToRestore = @LogToRestore, 
-- 					@TargetBackupPath = @TargetBackupPath,
-- 					@ErrorDescription = @ErrorDescription
		select @NumberOfCursorRows = @NumberOfCursorRows -1
	end
END
print 'got here'
CLOSE @BackupListCursor
DEALLOCATE @BackupListCursor

set quoted_identifier off	
use [LogShip]
go

-- ********************************************   Drop if Exists   *************************************************
if exists (select 'SP exists' from sysobjects where name = 'up_LogShipping_GetBackupList' and xtype = 'p')
	drop procedure up_LogShipping_GetBackupList
go

-- ********************************************   Create Procedure  ************************************************
create procedure up_LogShipping_GetBackupList
	@DebugLevel						integer	= 1,	--This input parametre is used to drive the amount of debug 
													--information generated. Initial levels are:
														--0 = none
														--1 = high - display TRACKING level information
															-- i.e. enter and exit loops and calling externals
														--2 = medium - as high, with timings
														--3 = detailed - displays variables are setting
 	@Server							nvarchar(40),
 	@TargetBackupPath				nvarchar(255),
	@InitialRestore					integer,
	@SQLServer						nvarchar(40),	
	@DBName							nvarchar(40),
 	@LocalBackupSharePath			nvarchar(255),
	@InsideSPErrorDescription		varchar(255) OUTPUT,	--This variable is used to pass error description to 
															--raiserror function
	@BackupListCursor				CURSOR VARYING OUTPUT,
	@NumberOfCursorRows				integer	OUTPUT


as

-- ********************************************   set Environment setting for SP ***********************************
set transaction isolation level read committed 	--Explicitly enforce preferred isolation level
set quoted_identifier off	
set nocount on					--Reduce unnecessary work and network usage

-- ********************************************   declare Variables  ***********************************************
declare 
-- control variables
	@ReturnCode			integer,		--This variable is used to Send a return code to the callin SP
	@CMDString			varchar(700),	--This variable is used for most SQL Commands gnerated for exec ()
-- reporting variables
	@LoopPointInTime	datetime,		--This variable used to store a point in time, used for duration 
										--outside a loop
-- local variables
	@LastBackupSetID	integer

-- ********************************************   set Variables  ***************************************************
set @ReturnCode = 0 --success by default, to be overwritten by failure
Set @InsideSPErrorDescription = 'In Stored Procedure'	
-- ********************************************   Pre-Condition Checks  ********************************************
if ((@DebugLevel not in (0,1,2,3)) or (@DebugLevel is null))
	begin	--Pre-condition check #1 begin
	set 	@ReturnCode = 2	--Error Type 2 : Pre-Condition failure
	set 	@InsideSPErrorDescription = '*** ERROR LINE : Pre-Condition Check Failure for Stored procedure : up_LogShipping_GetBackupList '+
					'- Invalid @DebugLevel Specified - valid values are 0,1,2 or 3 ***'
	goto 	ErrorLabel
end	--Pre-condition check #1 begin

-- ********************************************   Tracking Displays  ***********************************************
if @DebugLevel >= 1
	select ' *** TRACKING LINE : Inside SP : up_LogShipping_GetBackupList - Create table : tempdb..#Results ***'

-- ********************************************   Create Temp Tables  **********************************************

create table #Results 
(	backup_set_id 			integer 		NOT NULL, 
	physical_device_name 	varchar(255) 	NOT NULL, 
	FileType 				varchar(2) 		NOT NULL)

if @DebugLevel >= 1
	select ' *** TRACKING LINE : Inside SP : up_LogShipping_GetBackupList - Create table : tempdb..#FileListInfo ***'

--Create temp table that will be populated with the file details for a full database backup, when it is necessary
create table #FileListInfo 
(	LogicalName 	sysname		null,
	PhysicalName 	sysname 	null,
	Type 			varchar(20) null,
	FileGroupName 	sysname 	null,
	FileSize 		bigint 		null ,
	FileMaxSize 	bigint 		null)

-- ******************************************** 1. Build List of Backups Needed  ***********************************
--if @InitialRestore flag have been set (1=True) to init database, then either do full back on the Source Server or 
--ensure that most recent full backup is available
if @InitialRestore = 1 --1 = True
	begin --begin @InitialRestore
	--Retrieve: 	most recent (max backup_set_id) 
	--		full backup (sSet.type = 'D')
	--		information from Source Server @SQLServer 
	--		for db @DBName
	set @CMDString = 'select sSet.backup_set_id,sMed.physical_device_name,sSet.type from ['+@SQLServer
			+'].msdb.dbo.backupset sSet INNER JOIN ['+@SQLServer+'].msdb.dbo.backupmediafamily sMed ON'
			+' sMed.media_set_id = sSet.media_set_id where sSet.backup_set_id = '+
			'(select MAX(sSet.backup_set_id) from ['+@SQLServer+'].msdb.dbo.backupset sSet,['
			+@SQLServer+'].msdb.dbo.backupmediafamily sMed where sSet.database_name = '''
			+@DBName+''' and sSet.type =''D'' and sSet.media_set_id = sMed.media_set_id)'

	--Debug Displays
	if @DebugLevel >= 3
		begin	--Debug #2 begin <<select ... union all done to create more presentable output in LogFile>>
		select ' *** DEBUG LINE : Inside SP : up_LogShipping_GetBackupList - display Fetch Full Backup SQL ***' union all
		select '>>>>>>>>>>>>>>> display variables values <<<<<<<<<<<<<<<<<'  union all
		select '     Variable @CMDString Value : ' +isnull(@CMDString,'Value was NULL!!')  union all
		select '>>>>>>>>>>>>>>>                          <<<<<<<<<<<<<<<<<'  
	end	--Debug #2 End

	--insert Log Backup List into Temp table
	insert into #Results
	exec (@CMDString)
	select @ReturnCode= @@Error
	if @ReturnCode <> 0
		begin --IF @ReturnCode <> 0
		set 	@InsideSPErrorDescription = '*** ERROR LINE : Error executing "Insert Backup List into Temp table" for @InitialRestore = 1 using variable : @CMDString ***'
		goto 	ErrorLabel
	end--IF @ReturnCode <> 0

--To Do : Add Error Checking

--Since this IS an initial restore (@InitialRestore = 1) i.e. override last succesful restored backup set id 
--with full backid -1 i.e. restore the last full backup as well	*/
	set @LastBackupSetID = (select min(backup_set_id) -1 from #Results)
end --end @InitialRestore

--IF it is NOT an initial restore then check logship db to get the last succesfull restored backup set
else
	begin --Else @InitialRestore
	set @LastBackupSetID	= (select ibackup_set_id from Logship.dbo.tblBackup where ID = (select MAX(ID) 
					from Logship.dbo.tblBackup where strDBName = @DBName))
	select @ReturnCode= @@Error
	if @ReturnCode <> 0
		begin --IF @ReturnCode <> 0
		set 	@InsideSPErrorDescription = '*** ERROR LINE : Error setting variable : @LastBackupSetID to "last succesfull restored backup set" ***'
		goto 	ErrorLabel
	end--IF @ReturnCode <> 0

end--Else @InitialRestore

--Debug Displays
if @DebugLevel >= 3
	begin	--Debug #3 begin <<select ... union all done to create more presentable output in LogFile>>
	select ' *** DEBUG LINE : Inside SP : up_LogShipping_GetBackupList - display Last Backup Set ID ***' union all
	select '>>>>>>>>>>>>>>> display variables values <<<<<<<<<<<<<<<<<'  union all
	select '     Variable @LastBackupSetID Value : ' +isnull(cast(@LastBackupSetID as varchar(50)),'Value was NULL!!') union all
	select '>>>>>>>>>>>>>>>                          <<<<<<<<<<<<<<<<<' 
end	--Debug #3 End

-- ********************************************   Pre-Condition Checks  ********************************************
--Check the Backup Set value to ensure it has been set....
IF @LastBackupSetID IS NULL
	begin --IF @LastBackupSetID
	set 	@InsideSPErrorDescription = '*** ERROR LINE : Error determining previous backup id - variable : @LastBackupSetID is NULL ***'
	set		@ReturnCode = 3	--Variable has NULL
	goto 	ErrorLabel
end--IF @LastBackupSetID

-- ********************************************   Builds list of required Log Backups  *****************************
/* Get all the log backups after last restore id to copy */
--Retrieve: 	most recent (after latest backups set @LastBackupSetID) 
--		log backups (sSet.type = 'L')
--		information from Source Server @SQLServer 
--		for db @DBName
set @CMDString = 'select sSet.backup_set_id, sMed.physical_device_name, sSet.type from ['+@SQLServer
		+'].msdb.dbo.backupset sSet,['+@SQLServer+'].msdb.dbo.backupmediafamily sMed where sSet.database_name '+
		'= '''+@DBName + ''' and sSet.type =''L'' and	sSet.media_set_id = sMed.media_set_id and '+
		'sSet.backup_set_id > '+STR(@LastBackupSetID,12,0)+'order by backup_start_date ASC'

--Debug Displays
if @DebugLevel >= 3
	begin	--Debug #4 begin <<select ... union all done to create more presentable output in LogFile>>
	select ' *** DEBUG  LINE : Inside SP : up_LogShipping_GetBackupList - display Fetch Log Backup SQL *** ' union all
	select '>>>>>>>>>>>>>>> display variables values <<<<<<<<<<<<<<<<<'  union all
	select '     Variable @CMDString Value : ' +isnull(@CMDString,'Value was NULL!!')  union all
	select '>>>>>>>>>>>>>>>                          <<<<<<<<<<<<<<<<<'  
end	--Debug #4 end

--insert Backup List into Temp table
insert into #Results
exec (@CMDString)
select @ReturnCode= @@Error
if @ReturnCode <> 0
	begin --IF @ReturnCode <> 0
	set 	@InsideSPErrorDescription = '*** ERROR LINE : Error executing "Insert Backup List into Temp table" using variable : @CMDString ***'
	goto 	ErrorLabel
end--IF @ReturnCode <> 0

--Debug Displays
if @DebugLevel >= 2
	begin	--Debug #5 begin
	select ' *** DEBUG LINE : Inside SP : up_LogShipping_GetBackupList - display temp table #Results contents ***'
	select backup_set_id, cast(physical_device_name as varchar(100)), FileType from #Results order by backup_set_id
	select @ReturnCode= @@Error
	if @ReturnCode <> 0
		begin --IF @ReturnCode <> 0
		set 	@InsideSPErrorDescription = ' Error displaying contents of #Results for debugging '
		goto 	ErrorLabel
	end--IF @ReturnCode <> 0
end	--Debug #5 end

set @BackupListCursor = cursor for 
select backup_set_id, physical_device_name, FileType from #Results order by backup_set_id
open @BackupListCursor
select @@Cursor_Rows
select @NumberOfCursorRows = @@Cursor_Rows
-- set @TargetBackupPath = @LocalBackupSharePath+ @TargetBackupPath
-- 
-- --Debug Displays
-- if @DebugLevel = 3
-- 	begin	--Debug #6 begin
-- 	select ' *** DEBUG LINE : Inside SP : up_LogShipping_GetBackupList - display backup target path ***' union all
-- 	select '>>>> Complete backup target path is : ' +isnull(@TargetBackupPath,'Value was NULL')+ ' <<<<'
-- end	--Debug #6 end




-- ********************************************   General Error Label  *********************************************
ErrorLabel:

-- ********************************************   Return Execution Result  *****************************************
return @ReturnCode --0 = success

-- ********************************************   End Of Stored Procedure  *****************************************
go