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