SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.vssCheckIn
	@pDatabaseName varchar(30)
as
/***********************************************************************
Procedure Checks in to SourceSafe all scripts in the local working 
folders. All new scripts will be added to SourceSafe
************************************************************************/
declare @Cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(50),
	@WorkingFolder varchar(255),
	@vssCheckinHierarchy varchar(255),
	@vssAddHierarchy 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 path and SourceSafe project metadata
select 	@workingFolder = s.workingFolderRoot
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ s.instanceName + '\'
			+ @pDatabaseName,
	@vssShare = p.vssShare,
	@vssAddHierarchy = p.vssHierarchyRoot
			+ e.environmentName + '/'
			+ s.serverName + '/' 
			+ s.instanceName + '/',
	@vssCheckinHierarchy = 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

-- 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)
-- if the database folder already exists, this procedure cannot be used 
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssCheckinHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror(59008,16,1,@procedureName, @vssCheckinHierarchy, @vssShare)
		goto ErrorHandler
	end

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

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

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