SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.vssGetLatest 
	@pDatabaseName varchar(50)
as
/***********************************************************************
Procedure archives everything in the local working folder hiererchy for
a database and refreshes the woring folder from SourceSafe. Note that
all archived files are removed from the working folders.
************************************************************************/
declare @Cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(50),
	@WorkingFolder varchar(255),
	@ArchiveFolder varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(50)

set nocount on

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

-- get file system paths and SourceSafe metadata 
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 + '/',
	@vssUserName = n.vssUserName
from Admin.dbo.SqlDatabase d 
inner join Admin.dbo.ProjectNode n
on d.id = n.sqlDatabase_id
inner join .SQlServer s
on s.id = n.sqlServer_id
inner join Admin.dbo.Environment e
on e.id = n.environment_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

-- 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)
-- if the database folder already exists, this procedure cannot be used 
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

-- 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

-- go get the sourcesafe project
set @Cmd = @vssShare + 'win32\ss get ' + @vssHierarchy 
	 + ' -GL' + @WorkingFolder + ' -I-N -R -Y' + @vssUserName
exec @returnstatus = master..xp_cmdshell @Cmd
if @returnstatus <> 0 
	goto ErrorHandler

return

ErrorHandler:
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' ' + @pDatabaseName + ' failed with returnstatus ' 
	+ cast(@returnstatus as varchar(10)) + ' at: 
	' + @cmd
raiserror(59001,16,1,@procedureName)

return -1




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO