Hello
Following the renewal of our machine park, we have several stations that are no longer used but that still appear in the list of MyPDMTools task hosts. I want to delete these hosts so as not to pollute the already full list, and therefore to do so run a SQL query in a VBA script.
The query is: StrSQL = "DELETE FROM dbo. Hosts WHERE dbo. Hosts.HostID = " & N_Host (with N_Host the number of the PC I want to delete)
But this request does not work because other tables mention these "HostIDs". So I add other queries in the preamble of this query, which in the end gives (I just copy the query lines)
StrSQL="DELETE FROM dbo. TaskInstances WHERE dbo. TaskInstances.InitHostID =" & N_Host
StrSQL="DELETE FROM dbo. TaskHosts WHERE dbo. TaskHosts.HostID =" & N_Host
StrSQL="DELETE FROM dbo. Hosts WHERE dbo. Hosts.HostID =" & N_Host
But an error message tells me that the first query cannot execute: "The DELETE statement conflicts with the REFERENCE constraint "FK_TaskSelection_TaskInstances". The conflict occurred in the database "XXXX", table "dbo. TaskSelection", column 'TaskInstanceID' .
I understand that I would have to clean up the TaskSelection table first but I don't know how to target the right entries (the ones that mention the TaskInstanceID I'm trying to delete?).
In short, if anyone has an idea or even a complete query it would help me greatly.
Another solution: maybe there is something simpler than deleting the entries? For example, replace the value of the HostID with an "empty" ID?
Thank you for your help.
After discussion with visiativ the operation is really very simple: in the sql manager you just have to open the dbo table. Hosts and delete the line corresponding to the computer you want to delete. Tested and validated!
1 Like
Hello @romain.jouanny ,
So how do we delete the useless rows in the dbo table. Hosts? (NB: I'm not a SQL studio pro...)
Deleting a line has no other impact in EPDM (which could possibly create bugs)??
Thank you
You have to select the whole line (by clicking on the first empty box), then right button --> delete.
According to the visiativ speaker who showed me live there is no impact, at worst if you delete an unwanted host you can easily declare it again as a host via the usual procedure (on the pc, right click on the EPDM ball in the taskbar --> Task host configuration).
Thank you @romain.jouanny for this feedback.
So if I understood everything correctly, you have to go to the dbo table. Hosts, right-click and select "Edit top 200 rows",
From there, SQL shows the values contained in the table and you just have to right-click at the end of the line and choose "Delete"
Is that right??
Sorry to be so annoying but I prefer to cover my back before breaking EPDM...
Thank you.
1 Like
Yes, that's exactly right! I had indeed skipped the "Edit top 200 rows" step.
No worries about the details, I'm also very cautious when it comes to touching the base live!
2 Likes
Hello @romain.jouanny
So I started this morning and I tried to delete an old workstation that is still lying around in the PCs listed for tasks.
I did, however, receive this wonderful message:
So it seems that I'm facing your initial problem (or one that looks a lot like it).
You are sure that you have not done any additional work to remove the computers from the dbo list. Hosts?? Like deleting the stain history or other table cleaning??
Like having changed the number of task registrations as explained in point 5 here: https://www.javelin-tech.com/blog/2020/06/solidworks-pdm-monitor-tasks/ (which should logically result in cleaning the dbo.taskinstances table of all the old tasks and maybe unblocking the deletion of the old host)
Thank you in advance
Hello, indeed this is probably due to the task history in which a task that ran on this post still appears. For my part, I limit this history to the last 400 tasks, which would explain why I didn't have this problem.
You can start by checking if this host actually appears in the Task List in the Admin console, and then delete the tasks in question?
In any case, I'm sorry to see that it doesn't work for you when I was confident and told you to go without fear :(
Hello
After changing the number of tasks saved as explained in point 5 here: https://www.javelin-tech.com/blog/2020/06/solidworks-pdm-monitor-tasks/ , (I went to 200 successful and 200 failed) I was actually able to remove my old unwanted hosts.
It should be noted that limiting the number of successful and failed task registrations has an incredible impact on the responsiveness of the task list in the EPDM admin tool. It had become almost unusable for me with access times of the order of a minute or more just to select a line in the history and see its properties (when it wanted to display something).
Now with a list limited to 400 entries I find a perfectly usable tool (NB I had 'only' 24000 entries before this parameter modification which is not huge either).
Thank you again.
1 Like
Oh yes indeed I think that's what pushed me to clean up the list too! Good news if it works!