SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.vssVerifyProject
	@pProjectName varchar(30)
as
declare	@vssUserName varchar(30),
        @instanceName varchar(30),
	@serverName varchar(30),
	@databaseName varchar(30),
	@environmentName varchar(30),
	@prevEnvironmentName varchar(30),
	@workingFolderRoot varchar(100),
	@vssShare varchar(255),
	@vssHierarchyRoot varchar(255),
	@cmd nvarchar(1200),
	@returnstatus int,
	@procedureName varchar(30),
	@workingFolder varchar(400),
	@vssHierarchy varchar(400)

set nocount on

set @procedureName = db_name() + '.' 
                   + user_name(objectproperty(@@procid,'OwnerId')) 
                   + '.' + object_name(@@procid)
set @serverName = reverse(substring(reverse(@@serverName), 
                              charindex('\', reverse(@@servername)) + 1, 
                              datalength(reverse(@@servername))))
set @instanceName = substring(@@serverName, 
                              charindex('\', @@servername) + 1, 
                              datalength(@@servername))

select  @vssShare = p.vssShare,
	@vssHierarchyRoot = p.vssHierarchyRoot,
	@vssUserName = n.vssUserName
from Admin.dbo.ProjectNode n
inner join admin.dbo.project p
on n.project_id = p.id
inner join Admin.dbo.SqlServer s
on n.sqlServer_id = s.Id
where p.projectName = @pProjectName
-- no function in 7
-- and s.id = Admin.dbo.thisSQLServer_id() 
and s.serverName = @serverName
and s.instanceName = @instanceName
and n.isActive = 1

if @@rowcount < 1
	begin
		raiserror (59003, 16, 1, @procedureName, @@servername, @pProjectName)
		goto ErrorHandler
	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

-- make sure current node has a valid SourceSafe user
-- only verify an ini file for this user (vss 6.0 stores in 8.3 format)
-- any vss command from invalid user will try to throw a login prompt
set @cmd = 'dir ' + @vssShare + 'users\' + substring(@vssUserName,1,8) + '\ss.ini' 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	begin
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

-- verify the hierarchy
set @cmd = @vssShare + 'win32\ss Cp ' 
         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
if @returnstatus <> 0 
	-- keep going, just report the error
	raiserror (59008, 16, 1, @procedureName, @vssHierarchy, @vssShare)

declare cur cursor 
for
select  s.serverName,
	s.instanceName,
	s.workingFolderRoot,
        d.databaseName,
   	e.environmentName	
from Admin.dbo.ProjectNode n
inner join admin.dbo.project p
on n.project_id = p.id
inner join Admin.dbo.Environment e
on n.environment_id = e.id
inner join Admin.dbo.SqlServer s
on n.sqlServer_id = s.Id
inner join Admin.dbo.SqlDatabase d
on n.sqlDatabase_id = d.Id
where p.projectName = @pProjectName
and n.isActive = 1
-- loop here to check all dbs

open cur
fetch next from cur into @serverName,
			 @instanceName,
			 @workingFolderRoot,
        		 @databaseName,
			 @environmentName

print ''
print 'Project __________________________: ' + @pProjectName	
print 'SourceSafe Share _________________: ' + @vssShare

while @@fetch_status = 0
	begin
		if @environmentName <> @prevEnvironmentName or @prevEnvironmentName is null
				print space(5) + 'Environment _________________: ' + @environmentName

		-- see if the node database exists on current SQL Server
		if db_id(@databaseName) is null
			-- keep going, just report the error
			raiserror (59006,11,1, @procedureName, @databaseName, @@serverName)
		print space(10) + 'SQL Server Database ____: ' + @@servername + '.' + @databaseName

		set @prevEnvironmentName = @environmentName
		-- local working directory 
		set @workingFolder = @workingFolderRoot
                		   + @pProjectName + '\'
		                   + @environmentName + '\' 
                		   + @serverName + '\'
                		   + @instanceName + '\'
		                   + @databaseName + '\'

		-- project hierarchy within SourceSafe 
		set @vssHierarchy = case when substring(@vssHierarchyRoot, 
                		                        datalength(@vssHierarchyRoot), 
                                		        1) = '/'
		                        then @vssHierarchyRoot 
                		        else @vssHierarchyRoot + '/'
		                        end
                		  + @environmentName + '/' 
		                  + @serverName + '/' 
		                  + @instanceName + '/' 
		                  + @databaseName + '/' 

		-- verify working folder in the file system
		set @cmd = 'dir ' + @workingFolder
		exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
		if @returnstatus <> 0 
			-- keep going, just report the error
			raiserror (59007,11,1, @procedureName, @workingFolder, @serverName)
		print space(10) + 'Local Working Folder ___: ' + @workingFolder

		-- check the sourcesafe project
		set @Cmd = @vssShare + 'win32\ss Cp ' 
                         + @vssHierarchy + ' -I-N -Y' + @vssUserName 
		exec @returnstatus = master.dbo.xp_cmdshell @cmd, no_output
		if @returnstatus <> 0 
			-- keep going, just report the error
			raiserror (59008,11,1, @procedureName, @vssHierarchy, @vssShare)
		print space(10) + 'SourceSafe Project _____: ' + @vssHierarchy	
		print ''

		fetch next from cur into @serverName,
					 @instanceName,
					 @workingFolderRoot,
		        		 @databaseName,
		 			 @environmentName

	end	

deallocate cur

return

ErrorHandler:
raiserror(59000,16,1,@procedureName)

return -1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

