SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.vssAddDatabase
	@pDatabaseName varchar(50)
as
/*************************************************************************
Add the projects and scripts for a new database to SourceSafe if a file 
system folder for the database is found and no project currently exists 
in SourceSafe for the database at the path specified by the project 
schema.
**************************************************************************/
declare @cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(100),
	@environmentName varchar(50),
	@ServerName varchar(50),
	@instanceName varchar(50),
	@workingFolder varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(30)

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,
	@environmentName = e.environmentName,
	@serverName = s.serverName,
	@instanceName = s.instanceName,
	@vssShare = p.vssShare,
	@vssHierarchy = p.vssHierarchyRoot
			+ p.projectName + '/'
			+ 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 .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

-- 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 ' 
         + @vssHierarchy + @pDatabaseName + '/' + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus = 0 
	begin
		raiserror(59009,16,1,@procedureName, @vssHierarchy, @pDatabaseName)
		goto ErrorHandler
	end

-- add any missing projects and files for this database to the instance node
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 <> 0 
	goto ErrorHandler

-- audit
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' ' + @pDatabaseName + ' complete successfully'

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