SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure dbo.vssCompareDatabaseToVss
	@pDatabaseName varchar(30),
	@pEnvironmentName varchar(30),
	@pTempFolder varchar(255)

as
/***************************************************************************
A local database can be compared to any environments SourceSafe copy of that
database. Compare the contents s SQL Server database to a project hierarchy 
in Visual SourceSafe. Scripts the database to the specified temporary work 
area and the compares objects and contents between the temp area and the 
corresponding SourceSafe store of the specified Environment.  

Notes: The temp area is always cleared at the beginning of this procedure. 
Will not work with UNC paths.
****************************************************************************/
declare @Cmd nvarchar(1200),
	@returnStatus int,
	@procedureName varchar(255),
	@vssHierarchy varchar(255),
	@vssShare varchar(255),
	@vssUserName varchar(30),
	@line varchar(255),
	@diffCount int

set nocount on

-- make sure there is a trailing whack on the path
if (substring(@pTempFolder,datalength(@pTempFolder),1) <> '\')
	set @pTempFolder = @pTempFolder + '\'

set @diffCount = 0

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

-- get info if local and specified are both in matadata 
select @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
inner join Admin.dbo.ProjectNode l -- local
on 	(n.project_Id = l.project_Id
	and n.sqlDatabase_Id = l.sqlDatabase_id)
inner join Admin.dbo.SQlServer sl
on l.sqlServer_id = sl.Id	
where d.databaseName = @pDatabaseName
--and s.id = Admin.dbo.thisSQLServer_id() cannot use function in SQL Server 7
and e.environmentName = @pEnvironmentName
and sl.serverName = reverse(substring(reverse(@@servername), 
				charindex('\',reverse(@@servername)) + 1, 
                        	datalength(reverse(@@servername))))
and sl.instanceName = substring(@@servername, 
               	                charindex('\',@@servername) + 1, 
                                datalength(@@servername))
and n.IsActive = 1
and l.IsActive = 1

-- valid project node must exist 
if @@rowcount = 0
	begin
		raiserror(59002,16,1,@procedureName,@pDatabaseName)
		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

-- file system temporary working folder must exist
set @cmd = 'dir ' + @pTempFolder
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0 
	begin
		raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
		goto ErrorHandler
	end

-- vss project must exist and be visible to local system
-- NOTE! if you put a password on vss, this will hang (tries to prompt for password)
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

-- everything is in place, so script the database
-- keep the worthless stored procedure output off the results pane
create table #junk (junk varchar(255))
insert #junk exec @returnstatus = Admin.dbo.dmoScriptDatabase @pDatabaseName, @pTempFolder
if @returnstatus <> 0 
	goto ErrorHandler

insert #junk exec @returnstatus = Admin.dbo.dmoScriptTables @pDatabaseName, @pTempFolder
if @returnstatus <> 0 
	goto ErrorHandler
drop table #junk

set @pTempFolder = @pTempFolder + @pDataBaseName + '\'

-- compare objects 
set @Cmd =  substring(@pTempFolder,1,2) + '&cd '
         + substring(@pTempFolder,3,datalength(@pTempFolder)) 
         + '&' + @vssShare + 'win32\ss Diff ' --+ @vssHierarchy --is current 
	 + ' -R -B -Y' + @vssUserName
exec @returnstatus = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
if @returnstatus = 1 
        begin
                print ''
                print 'Unmatched Objects found!'
                print ''
                create table #ObjectDifferences(output varchar(255))
                insert #ObjectDifferences exec master.dbo.xp_cmdshell @Cmd
		delete #ObjectDifferences where output like 'Against:%'
                update #ObjectDifferences set output = '' where output is NULL
		update #ObjectDifferences set output = 'SourceSafe files not in the database:' 
			where output like 'SourceSafe files not in the current folder:%'
		update #ObjectDifferences set output = 'Local database objects not in environment ' 
							+ @pEnvironmentName + ' in SourceSafe :' 
			where output like 'Local files not in the current project:%'
                select * from #ObjectDifferences
		drop table #ObjectDifferences
        end
else
        begin
                if @returnstatus <> 0 
                        goto ErrorHandler
                else
                        begin         
                                print ''
                                print 'No unmatched objects found.'  
                        end        
        end        

-- compare contents
-- get list of all files in the working folder
create table #fileList (line varchar(255))
set @Cmd = 'dir ' + @pTempFolder + ' /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(@pTempFolder) + 1,
		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,'\','/')
                    + ' ' + @pTempFolder + @line + ' -DS -Y' + @vssUSerName
               exec @returnstatus = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
               if @returnstatus = 1 
                        begin
                                set @diffCount = @diffCount + 1
                                print ''
                                print 'Internal Object Differences 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 internal object differences found.'  
	end        

print ''
print 'Compare complete'

return 

ErrorHandler:
if (@Cmd is not null)
	begin
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName + ' ' 
			+ @pEnvironmentName + ' ' + @pTempFolder 
			+ ' failed with returnstatus ' 
			+ cast(@returnstatus 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