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