CAD Forum

Code: Select all

--Query to list deleted files that incorrectly shows Deleted=0 in Documents table
--v2 - Handles deleted shared files correctly

--auxilliary temp table							 
CREATE TABLE #TempHistoryDelete( DocID INT )
CREATE TABLE #TempShareFile( DocID INT )
DECLARE @row INT

INSERT INTO #TempHistoryDelete (DocID) Select DISTINCT DocumentID 
FROM HistoryDelete a
WHERE a.time = ( select max(time) from HistoryDelete b where a.DocumentID = b.DocumentID ) and Action = 1

--retreive all shared files
INSERT INTO #TempShareFile (DocID) 
SELECT Hd.DocID
FROM documents d, #TempHistoryDelete Hd, HistoryShare hs, DocumentsInProjects dp, Projects p
WHERE Hd.DocID = Hs.DocumentId and dp.Deleted = 0 and d.DocumentID = dp.DocumentID and p.ProjectID = dp.ProjectID 
 
--exclude shared files where one or more instances are still *not* actually deleted
DELETE FROM #TempHistoryDelete 
WHERE DocID IN ( SELECT TS.DocID FROM #TempShareFile TS ) 

--List mismatched files
SELECT d.DocumentID, p.Path, d.Filename, d.Deleted
FROM  documents d, DocumentsInProjects dp, Projects p
WHERE d.DocumentID IN( SELECT * FROM #TempHistoryDelete) and d.Deleted = 0 
and d.DocumentID = dp.DocumentID and p.ProjectID = dp.ProjectID
ORDER BY p.Path

SET @row=@@rowcount 
IF @row = 0
	Select 'No mismatched records found' as [Result]
ELSE
	Select CAST(@row as VARCHAR) + ' Mismatched records found - please run DeleteMismatch_Fix_v2.sql' as [Result]
GO

DROP TABLE #TempHistoryDelete
DROP TABLE #TempShareFile
GO

Code: Select all

----- Query to list checked out parent files that blocks move or rename of a specific file 
----- Before running query, please update the QUERY PARAMETERS (row 94) below with filename and path to check 
----- 
-------------------- VALIDATION CHECKS BEGIN ---------------------------

------------- Verify that an EPDM database is selected
Declare @SelectedDatabaseName nvarchar(250)
Set @SelectedDatabaseName = (SELECT db_name())

IF OBJECT_ID ('SPU_UnlockFile', 'P') IS NULL
begin
  RAISERROR( 'The selected database "%s" does not appear to be an SOLIDWORKS PDM database. Make sure you select correct database to run script on.', 16, 1, @SelectedDatabaseName )
  Return
end

-------------------- VALIDATION CHECKS DONE ---------------------------

if exists (select 1 from sysobjects where id = object_id(N'[dbo].[SPRM_SubGetParentDocumentIds_SPR623463]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 set noexec on
go

 CREATE PROCEDURE SPRM_SubGetParentDocumentIds_SPR623463
  @iDocID INT,
  @iterLimit int = 100
As
Begin
	Create Table #oTbl (DocumentID INT, DocumentVersion INT, ParentDocID INT, ParentVersion INT);
	Create Table #LevelOne_Docs (DocumentID int, DocumentVersion INT, ParentDocID int, ParentVersion INT);
	Create Table #LevelOneResult_Docs (DocumentID int, DocumentVersion INT, ParentDocID int, ParentVersion INT);

	INSERT INTO #LevelOne_Docs (DocumentID, DocumentVersion) 
	SELECT DocumentID, LatestRevisionNo 
	FROM dbo.Documents
	WHERE DocumentID = @iDocID;

	DECLARE @iter int = 1;
	DECLARE @rc int = 1;

	WHILE (@rc > 0 AND @iter <= @iterLimit)
	BEGIN 
		INSERT INTO #LevelOneResult_Docs (DocumentID, DocumentVersion, ParentDocID, ParentVersion)
		SELECT x1.DocumentID, x1.RevNr, x1.XRefDocument, x1.XRefRevNr
		FROM XRefs x1 INNER JOIN 
			Documents d1 on x1.XRefDocument = d1.DocumentID AND
							--x1.XRefRevNr = d1.LatestRevisionNo AND                    --SPR 700591 child files that going to be renamed might have different latest version number                                                  
							x1.XrType <> 2					-- Should not be circular   -- than parent latest version file.
		WHERE x1.XRefDocument IN (SELECT DocumentID FROM #LevelOne_Docs)

        set @rc = @@rowcount

	    IF (@rc > 0)
	    BEGIN
			-- Copy the nodes from the OneLevel table to the aggregated table
			INSERT INTO #oTbl (DocumentID, DocumentVersion, ParentDocID, ParentVersion)
			SELECT DocumentID, DocumentVersion, ParentDocID, ParentVersion FROM #LevelOneResult_Docs

			TRUNCATE TABLE #LevelOne_Docs

			INSERT INTO #LevelOne_Docs (DocumentID, DocumentVersion, ParentDocID, ParentVersion)
			SELECT l1r.DocumentID, L1R.DocumentVersion, l1r.ParentDocID, L1R.ParentVersion
			From #LevelOneResult_Docs l1r LEFT JOIN 
				#oTbl pt On l1r.DocumentID = pt.ParentDocID --AND	-- Only insert not previously found docs
				--l1r.DocumentVersion = pt.ParentVersion			-- since XrType only applies to the initially built chain  --SPR 700591 child files that going to be renamed might have different latest version number 
				                                                                                                               -- than parent latest version file.
			WHERE pt.DocumentID Is Null

			TRUNCATE TABLE #LevelOneResult_Docs

			SET @iter = @iter+1;
		END
    END

	SELECT DISTINCT DocumentID FROM #oTbl
End

GO

set noexec off
Go

DECLARE @SourceFilePath nvarchar(255)
DECLARE @SourceFilePathID int
DECLARE @SourceFileName nvarchar(255)
DECLARE @SourceFileNameID int
DECLARE @DestinationFilePathID int

----------UPDATE QUERY PARAMETERS-------------------------------------
--------------------------------------------------------------------------------------
--- *** Enter path and file name of the file that fails moving/renaming
--- Do not include the local view path and include the leading and trailing backslash character "\"
--- e.g. 'C:\MyLocalViews\VaultName\Folder\In\MyVault\' should be entered as '\Folder\In\MyVault\'
--- File name should include extension.
--- e.g. 'magnet.sldprt'
SET @SourceFileName=N'BS108 Oring.SLDPRT'         --<File failing to move/rename. "File Name" column in warning dialog
SET @SourceFilePath=N'\Project\Gripper Assembly\'    --<Path to file failing to move/rename. "Found In" column in warning dialog

--------------------------------------------------------------------------------------

--Check that file and folder inputs are valid
DECLARE @SourceFileNameCheckID int
DECLARE @SourceFilePathCheckID int

Set @SourceFileNameCheckID = (Select TOP 1 DocumentID from Documents where Filename = @SourceFileName)     
if( @SourceFileNameCheckID IS NULL )
begin
  RAISERROR( 'Could not find any file with the name "%s" in the vault database. Verify the name entered in the @SourceFileName parameter.', 16, 1, @SourceFileName )
  Return
end

Set @SourceFilePathCheckID = (Select TOP 1 ProjectID from Projects where Path = @SourceFilePath)     
if( @SourceFilePathCheckID IS NULL )
begin
  RAISERROR( 'Could not find any folder path matching "%s" in the vault database. Verify the path entered in the @SourceFilePath parameter. Make sure it is relative to vault root and starts and ends with a backslash character "\"' , 16, 1, @SourceFilePath )
  Return
end
--Checks done


Set @SourceFileNameID =
(SELECT Distinct TOP 1 D.DocumentID
FROM Documents D
INNER JOIN DocumentsInProjects DIP ON DIP.DocumentID=D.DocumentID
INNER JOIN Projects P ON P.ProjectID=DIP.ProjectID
WHERE Filename like @SourceFileName AND Path LIKE @SourceFilePath AND D.Deleted=0 ) --Matching on name and path 

Set @SourceFilePathID =
(SELECT Distinct TOP 1 ProjectID
FROM Projects
WHERE Path = @SourceFilePath AND Deleted=0 )

Set @DestinationFilePathID = @SourceFilePathID
--(SELECT Distinct TOP 1 ProjectID
--FROM Projects
--WHERE Path = @DestinationFilePath AND Deleted=0 ) 


If (object_id(N'tempdb..#ParentDocID') Is Not Null) Drop Table #ParentDocID
If (object_id(N'tempdb..#MoveInput') Is Not Null) Drop Table #MoveInput
DECLARE @row INT

	DECLARE @inputDocID INT;   -- = (SELECT DocID FROM #MoveInput)
	Create Table #MoveInput( DocID int, ProjID int, DestProjID int, NewName nvarchar(260) collate database_default )

	
	CREATE TABLE #ParentDocID (DocumentID INT)

	Insert into #MoveInput 
	Values (@SourceFileNameID, @SourceFilePathID, @DestinationFilePathID,'') 



	DECLARE InputDoc_Cursor CURSOR LOCAL FAST_FORWARD
	FOR SELECT DocID FROM #MoveInput

	OPEN InputDoc_Cursor
	FETCH NEXT FROM InputDoc_Cursor INTO @inputDocID

	WHILE @@FETCH_STATUS = 0
	BEGIN

		INSERT INTO #ParentDocID
		EXEC dbo.SPRM_SubGetParentDocumentIds_SPR623463 @inputDocID
		
		FETCH NEXT FROM InputDoc_Cursor INTO @inputDocID
	END

	CLOSE InputDoc_Cursor
	DEALLOCATE InputDoc_Cursor

	Declare @ParentFiles Table (DocumentID int, Filename nvarchar(255), FilePath nvarchar(255), UserName nvarchar(255), LockDomain nvarchar(255))
	Insert into @ParentFiles (DocumentID, Filename,FilePath, UserName, LockDomain)
	Select distinct d.DocumentID,d.Filename,d.LockPath, U.Username, d.LockDomain from #ParentDocID pd join Documents d on pd.DocumentID = d.DocumentID Join Users U on d.UserID = U.UserID
	where d.UserID != 1
	--We need to insert info about all referenced files that prevent Move/rename of selected file(s) and keep track that this is valid references .
	IF EXISTS (SELECT 1 FROM @ParentFiles)
		 -- Insert Into 
			--#Errors
		  Select distinct 
			pf.Filename,
			mi.DocID,
			pf.FilePath,
			pf.UserName as [Checked out by],
			pf.LockDomain as [Checked out on]
			
			--mi.ProjID,
			--mi.DestProjID, 
			--9, --9=Moerr_ParentLockedElsewhere
			--pf.DocumentID
			
		  From
			#MoveInput mi,
			Documents d,
			@ParentFiles pf,
			XRefs x
		  Where
			mi.DocID <> 0 and
			x.DocumentID = pf.DocumentID and
			d.DocumentID = mi.DocID and
			exists
			(
			  
			  Select 1 From        
					Documents d1
					Join #ParentDocID f on f.DocumentID = d1.DocumentID
				  Where			
					d1.Deleted = 0 and
					d1.LockProject <> 2 --and
	--				d1.LockViewID <> @ViewID
			)
	
	SET @row=@@rowcount 
IF @row = 0
	Begin
	Select 'No checked out parents found for: ' + @SourceFilePath + @SourceFileName as [Result]
	End