SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vssPromoteDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vssPromoteDatabase]
GO


CREATE procedure dbo.vssPromoteDatabase
	@pDatabaseName varchar(30)
as
/***************************************************************************
Procedure checks out all objects in the database and archives everything in 
the local working folder hiererchy then gets the latest SourceSafe scripts 
from the predecessor (parent) environment to the working folders and checks 
this in, adding all new objects. Then since oblects that have been dropped
in the version being promoted will not get checked in, find all files that 
did not get checked in and delete them from SourceSafe. 
Note that all archived files are removed from the working folders.
****************************************************************************/
declare @cmd nvarchar(1200),  
	@returnstatus int,
	@procedureName varchar(255),
	@workingFolder varchar(255),
	@archiveFolder varchar(255),
	@vssShare varchar(255),
	@vssParentHierarchy varchar(255),
	@vssHierarchy varchar(255),
	@vssAddHierarchy varchar(255),
	@vssUserName varchar(50)

set nocount on

set @procedureName = db_name() + '.' 
		+ user_name(objectproperty(@@procid,'OwnerId')) 
		+ '.' + object_name(@@procid)

-- get file system path 
select 	@workingFolder = s.workingFolderRoot
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ s.instanceName + '\'
			+ @pDatabaseName + '\',
	@archiveFolder =  s.workingFolderRoot
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ s.instanceName + '\'
			+ 'archive\'
			+ @pDatabaseName + '\',
	@vssShare = p.vssShare,
	@vssHierarchy = p.vssHierarchyRoot
			+ e.environmentName + '/' 
			+ s.serverName + '/' 
			+ s.instanceName + '/'
			+ @pDatabaseName + '/',
	@vssParentHierarchy = p.vssHierarchyRoot
			+ e1.environmentName + '/' -- parent environment
			+ s.serverName + '/' 
			+ s.instanceName + '/'
			+ @pDatabaseName + '/',
	@vssAddHierarchy = p.vssHierarchyRoot
			+ e.environmentName + '/'
			+ s.serverName + '/' 
			+ s.instanceName + '/',
	@vssUserName = n.vssUserName
from Admin.dbo.SqlDatabase d 
inner join Admin.dbo.ProjectNode n
on d.id = n.sqlDatabase_id
inner join Admin.dbo.SQlServer s
on s.id = n.sqlServer_id
inner join Admin.dbo.Environment e
on e.id = n.environment_id
inner join Admin.dbo.Environment e1
on e.id = e1.parentEnvironment_id 
inner join Admin.dbo.Project p 
on p.id = n.project_id
where d.databaseName = @pDatabaseName
--and s.id = Admin.dbo.thisSQLServer_id() cannot use function in SQL Server 7
and s.serverName = reverse(substring(reverse(@@servername), 
				charindex('\',reverse(@@servername)) + 1, 
                        	datalength(reverse(@@servername))))
and s.instanceName = substring(@@servername, 
               	                charindex('\',@@servername) + 1, 
                                datalength(@@servername))
and n.IsActive = 1

-- valid project node must exist 
if @@rowcount = 0
	begin
		raiserror(59002,16,1,@procedureName,@pDatabaseName)
		return -1
	end

-- is wrong user asking
if substring(system_user,charindex('\', system_user) + 1, datalength(system_user)) <> @vssUserName
	begin
		set @vssUserName = substring(system_user,charindex('\', system_user) + 1, datalength(system_user))
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

-- file system working folder for the database must exist
set @cmd = 'dir ' + @workingFolder
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0 
	begin
		raiserror (59007,16,1,@procedureName, @WorkingFolder,@@servername)
		goto ErrorHandler
	end

-- vss project must exist - must be created by vss admin 
-- NOTE! if you put a password on vss, this will hang (tries to prompt for password)
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror(59008,16,1,@procedureName, @vssHierarchy, @vssShare)
		goto ErrorHandler
	end

-- see if any files are checked out in this project
-- must do this so we can delete all unchecked in later
set @Cmd = @vssShare + 'win32\ss Status ' + @vssHierarchy 
	 + '\ -I-N -R -Y' + @vssUSerName
exec @returnstatus = master..xp_cmdshell @Cmd, NO_OUTPUT
if @returnstatus <> 0 
	begin
		if @returnstatus = 1
			raiserror(59010,16,1,@procedureName, @vssHierarchy)
		goto ErrorHandler
	end

-- see if any files are checked out in parent project
set @Cmd = @vssShare + 'win32\ss Status ' + @vssParentHierarchy 
	 + '\ -I-N -R -Y' + @vssUSerName
exec @returnstatus = master..xp_cmdshell @Cmd, NO_OUTPUT
if @returnstatus <> 0 
	begin
		if @returnstatus = 1
			raiserror(59010,16,1,@procedureName, @vssHierarchy)
		goto ErrorHandler
	end

-- checkout the sourcesafe database project of current environment
set @Cmd = @vssShare + 'win32\ss Checkout ' + @vssHierarchy 
	 + ' -GL' + @workingFolder 
	 + ' -C' + @@serverName + '.' + @procedureName
	 + '\ -I-N -R -Y' + @vssUSerName
exec @returnstatus = master..xp_cmdshell @Cmd--, NO_OUTPUT
if @returnstatus <> 0 
	goto ErrorHandler

-- make sure the archive folder hierarchy is in place
set @CMD = 'xcopy ' + @workingFolder + '*.* ' 
	 + @archiveFolder + ' /T /E /Y'
exec @returnstatus = master..xp_cmdshell @CMD, NO_OUTPUT
if @returnstatus <> 0 
	goto ErrorHandler

-- copy the most recent local copy of all files to the local archive 
set @CMD = 'xcopy ' + @workingFolder + '*.* ' 
	 + @archiveFolder + ' /K /R /S /Y'
exec @returnstatus = master..xp_cmdshell @CMD, NO_OUTPUT
if @returnstatus <> 0 
	goto ErrorHandler

set @CMD = 'del ' + @workingFolder + '*.* /F /S /Q'
exec @returnstatus = master..xp_cmdshell @CMD, NO_OUTPUT
if @returnstatus <> 0 
	goto ErrorHandler

-- get the parent sourcesafe database project
set @Cmd = @vssShare + 'win32\ss Get ' + @vssParentHierarchy 
	 + ' -GL' + @workingFolder 
	 + ' -C' + @@serverName + '.' + @procedureName
	 + '\ -I-N -R -Y' + @vssUSerName
exec @returnstatus = master..xp_cmdshell @Cmd--, NO_OUTPUT
if @returnstatus <> 0 
	goto ErrorHandler


-- add parent hierarchy to SourceSafe 
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror(59008,16,1,@procedureName, @vssHierarchy, @vssShare)
		goto ErrorHandler
	end

set @Cmd = @vssShare + 'win32\ss Checkin ' + @vssHierarchy 
	 + ' -GL' + @WorkingFolder 
	 + ' -C' + @@serverName + '.' + @procedureName
	 + ' -I-N -R -Y' + @vssUserName 
exec @returnstatus = master..xp_cmdshell @Cmd
if @returnstatus <> 0 
	goto ErrorHandler

-- add any new objects
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssAddHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror(59008,16,1,@procedureName, @vssAddHierarchy, @vssShare)
		goto ErrorHandler
	end

set @Cmd = @vssShare + 'win32\ss Add ' + @WorkingFolder 
	 + ' -C' + @@serverName + '.' + @procedureName
	 + ' -I-N -R -B- -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
if @returnstatus not in (0,100) 
	goto ErrorHandler

-- remove anything not checked in
-- everything not checked in had no database object in current
create table #DeleteList(FileName varchar(255))
set @Cmd = @vssShare + 'win32\ss Status ' + @vssHierarchy 
	 + '\ -I-N -R -Y' + @vssUSerName
Insert #DeleteList exec @returnstatus = master..xp_cmdshell @Cmd
if @returnstatus <> 0 
	begin
		if @returnstatus = 1
			begin
select * from #deleteList				


			end
		else
			goto ErrorHandler
	end

return

ErrorHandler:
if @cmd is not null
	begin
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName + ' failed with returnstatus ' 
			+ cast(@returnstatus as varchar(10)) + ' at: 
			' + @cmd
		raiserror(59001,16,1,@procedureName)
	end
else
	raiserror(59000,16,1,@procedureName)	

return -1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

