bnemec wrote: ↑Fri May 13, 2022 11:28 am
This might be a stupid question, but I'm trying to follow along for exercise in trouble shooting. When you said you queried SQL for the paths of the two files, I'm assuming you were getting the full path (project table) by those Doc IDs, not file name? I've chased my tail over and over because I think of a file by it's full path but PDM knows a "file" by DocumentID.
In other words, does the DocID in the where used sql query match the DocID of the real path in the vault view? There's been times when PDM and I were talking about different DocIDs but same filename. And we don't have duplicate file names.
I am still new at PDM and I was put my role in a hurry, so I still have to attend the PDM administration course.
This said I have some experienced DB guy in my team and I have been working with this company PDM for about a year including design task, users training and troubleshooting.
Backon topic I used the SQL query below, attached to the KB Solution I quoted.
This query returned me six files hindering the move operation and their supposed path according to the DB.
three files are actually checked out, but three are supposed to be checked in. I wrongly assumed two files were deleted because when I looked inside their supposed folder I could not find them, but they were eventually moved in a sub-folder.
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
Former Mechanical Engineer (UG-NX ), now a miserable SW CAD/PDM admin... debugging Solidworks since 2014. Please save me from ThE pLaTfOrM...
All the opinions are my own.
SW is bad: a fact not an opinion.
