SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure dbo.adminAddProjectNode
	@pProjectName varchar(30),
	@pEnvironmentName varchar(30),
	@pServerName varchar(60),
	@pDatabaseName varchar(30)
as
/*****************************************************************************
Populate the dbChangeControl metadatabase entries for the specified node, add the 
SourceSafe project hierarchy levels for this server, and set the SourceSafe 
working folders to facillitate project participation for this SQL Server. 
Requires that the local working folders and the SourceSafe Project and Environment 
levels are already in place. Fails if attempt to add node for a remote server
that is not found in SourceSafe. You can add the database to the database here
are it can aleady have been added
******************************************************************************/
declare @cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(100),
	@ServerName varchar(50),
	@instanceName varchar(50),
	@workingFolder varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(30),
	@error int,
	@rowcount int

set nocount on

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

set @serverName = reverse(substring(reverse(@pServername), 
				charindex('\',reverse(@pServername)) + 1, 
                        	datalength(reverse(@pServername))))

set @instanceName = substring(@pServername, 
               	                charindex('\',@pServername) + 1, 
                                datalength(@pServername))

--add the database if necessary
insert Admin.dbo.SqlDatabase 
	(databaseName)
select
	(@pDatabaseName)
where not exists (select 1 
			from Admin.dbo.SqlDatabase 
			where databaseName = @pDatabaseName)
if @@error <> 0
	goto ErrorHandler

-- create the project node
insert Admin.dbo.ProjectNode
	(project_id,
	environment_id,
	sqlServer_id,
	sqlDatabase_id)
select p.id,
	e.id,
	s.id,
	d.id
from Admin.dbo.Project p
cross join Admin.dbo.Environment e
cross join Admin.dbo.SqlServer s
cross join Admin.dbo.SqlDatabase d
where p.ProjectName = @pProjectName
and e.environmentName = @pEnvironmentName
and s.serverName = @serverName
and s.instanceName = @instanceName
and d.DatabaseName = @pDatabaseName
	
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
	goto ErrorHandler

-- valid project hierarchy must exist 
if @rowcount = 0
	begin
		raiserror(59003,16,1,@procedureName, @pServername, @pProjectName)
		goto ErrorHandler
	end

-- get file system path 
select 	@workingFolder = s.workingFolderRoot
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\',
	@instanceName = s.instanceName,
	@vssShare = p.vssShare,
	@vssHierarchy = p.vssHierarchyRoot
			+ e.environmentName + '/',
	@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.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 e.environmentName = @pEnvironmentName
and s.serverName = @serverName
and s.instanceName = @instanceName
and n.IsActive = 1

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

-- File system hierarch must exist
set @cmd = 'dir ' + @workingFolder 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if (@returnStatus <> 0)
	begin
		exec @returnstatus = Admin.dbo.adminMakeWorkingFolders @pProjectName, @pDatabaseName
		set @error = @@error
		if @error <> 0 or @returnstatus <> 0 
			goto ErrorHandler
	end

-- project and environment must already have been created by vssadmin
-- environment project permissions in vss will protect sourcesafe from this procedure
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

-- add missing SourceSafe Project Hierarchy server and/or instance levels for this instance
-- only verify and set working folder in this user's ss.ini if adding node for another instance
set @vssHierarchy = @vssHierarchy + @serverName + '/' 
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		if (@pServerName = @@servername)
			begin
				set @Cmd = @vssShare + 'win32\ss Create ' 
					+ ' -C' + @@servername + '.' + @procedureName + ' '
					+ @vssHierarchy + ' -I-N -Y' + @vssUserName 
				exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
			end
		else
			raiserror(59011,16,1,@procedureName, @vssHierarchy, @pServername)
		if @returnstatus <> 0 
			goto ErrorHandler
			

		set @cmd = @vssShare + 'win32\ss Cp ' 
		         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
		exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
		if @returnstatus <> 0 
			goto ErrorHandler
	end

-- set the working folder as the projects working folder
set @cmd = @vssShare + 'win32\ss Workfold ' 
         + @workingFolder + ' -Y' + @vssUserName
exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
if @returnstatus <> 0 
	goto ErrorHandler

-- now add intance level
set @vssHierarchy = @vssHierarchy + @instanceName + '/'
set @workingFolder = @workingFolder + @instanceName + '\'
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		if (@pServerName = @@servername)
			begin
				set @Cmd = @vssShare + 'win32\ss Create ' 
					+ ' -C' + @@servername + '.' + @procedureName + ' '
					+ @vssHierarchy + ' -I-N -Y' + @vssUserName 
				exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
			end
		else
			raiserror(59011,16,1,@procedureName, @vssHierarchy, @pServername)
		if @returnstatus <> 0 
			goto ErrorHandler
	
		set @cmd = @vssShare + 'win32\ss Cp ' 
		         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
		exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
		if @returnstatus <> 0 
			goto ErrorHandler
	end

-- set the working folder as the projects working folder
set @cmd = @vssShare + 'win32\ss Workfold ' 
         + @workingFolder + ' -Y' + @vssUserName
exec @returnstatus = master.dbo.xp_cmdshell @cmd--, no_output
if @returnstatus <> 0 
	goto ErrorHandler

return

ErrorHandler:
if @cmd is not null
	begin
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName + ' failed with returnstatus: ' 
			+ nullif(cast(@returnstatus as varchar(10)),'?') + ' error:' + 
			+ nullif(cast(@error 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