SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


create procedure dbo.vssCompareWorkToVss
	@pDatabaseName varchar(30)
as
/***************************************************************************
Compare the contents of all folders in a local working folder hierarchy 
to the corrresponding project hierarchy in Visual SourceSafe. First compare
objects, then compare contents

This procedure does not script the database. Will not work with UNC paths.
****************************************************************************/
declare @Cmd nvarchar(1200),
	@returnStatus int,
	@procedureName varchar(255),
	@WorkingFolder varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(30),
	@line varchar(255),
	@diffCount int

set nocount on

set @diffCount = 0

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,
	@vssShare = p.vssShare,
	@vssHierarchy = 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 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 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)
		return -1
	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 + ' -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

-- compare objects 
set @Cmd =  substring(@WorkingFolder,1,2) + '&cd '
         + substring(@WorkingFolder,3,datalength(@WorkingFolder)) 
         + '&' + @vssShare + 'win32\ss Diff ' --+ @vssHierarchy 
	 + ' -R -B -Y' + @vssUserName
exec @returnstatus = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
if @returnstatus = 1 
        begin
                print ''
                print 'Object Differences were found! '
                print ''
                create table #ObjectDifferences(output varchar(255))
                insert #ObjectDifferences exec master.dbo.xp_cmdshell @Cmd
                update #ObjectDifferences set output = '' where output is NULL
                select * from #ObjectDifferences
		drop table #ObjectDifferences
        end
else
        begin
                if @returnstatus <> 0 
                        goto ErrorHandler
                else
                        begin         
                                print ''
                                print 'No Object Differences found.'  
                        end        
        end        

-- compare contents
-- get list of all files in the working folder
create table #fileList (line varchar(255))
set @Cmd = 'dir ' + @WorkingFolder + ' /A /S /B'
insert #fileList exec master.dbo.xp_cmdshell @Cmd
-- a little clean up of the list
-- only .sql files
delete #fileList 
where line not like '%.sql' 
or line is null

-- position just past the database name
update #fileList 
set line = substring(line,
		datalength(@workingFolder) + 2,
		datalength(line))

-- compare each file to it's VSS mate
set @line = (select top 1 line
                from #fileList 
                order by line)
while @line is not null
        begin
               set @Cmd =  @vssShare + 'win32\ss Diff ' --+ @vssHierarchy 
		    + replace(@line,'\','/')
                    + ' ' + @workingFolder + '\' + @line + ' -DS -Y' + @vssUSerName
               exec @returnstatus = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
               if @returnstatus = 1 
                        begin
                                set @diffCount = @diffCount + 1
                                print ''
                                print 'Content Differences were found!'
                                print ''
                                create table #ContentDifferences(output varchar(255))
                                insert #ContentDifferences exec master.dbo.xp_cmdshell @Cmd
                                update #ContentDifferences set output = '' where output is NULL
                                select * from #ContentDifferences
				drop table #ContentDifferences
                        end
                else 
               		if @returnstatus <> 0 
	                        goto ErrorHandler

                delete #fileList where line = @line
                set @line = (select top 1 line
                                from #fileList 
                                order by line)
        end

if @diffCOunt = 0
	begin         
		print ''
		print 'No Content Differences found.'  
	end        

print ''
print 'Compare complete'

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