Intended Audience: EPLAN P8 Users with SQL Parts Database
I was adding data to my parts database recently when I noticed that about 1500 entries contained ERP numbers. ERP numbers are designed to serve as an alias for customer part numbers and these are not something I use so I wanted to delete them. I quickly discovered that EPLAN protects these entries in the parts manager and they can not be deleted more than one at a time from within EPLAN. I simply do not have the patience to manually delete 15o of these so manually deleting 1500 of these is simply out of the question. I first considered doing a data export & import thinking that I would over-write the ERP numbers, but EPLAN will not overwrite actual data with nothing which it what a deleted cell is. However, using a few lines of SQL code we can quickly over-write all the records in the erpnr field in the database with a blank or “null” charachter.
WARNING: this will over-write all data in the “erpnr” field and possibly much much more if you are not careful.
In my case I am using an SQL Express database installed locally and I have admin privileges to it which is necessary for what I about to do.
This should go without saying but BACKUP your data before attempting this, I assume no responsibility for you erasing your database. Proceed at your own risk.
Launch EPLAN and determine the name of the database that you are connected to and write it down.
Option > Settings > User > Management > Parts
In my case the database is named MASTER_PARTS but it could be anything.
Close EPLAN to break the connection to the SQL server and launch Microsoft SQL server, Management Studio… You did install the version with management studio right? If you are having problems with this step, now might be a good time to call your IT support specialist.
Open the application and look for the database stack, the databases have icons that look like cylinders.
Click on the DB icon and expand the DB to see the tables
If you get an error you have not opened the application with Admin privileges
This is what you should see, we will now edit the Parts Table (tblPart)
In “tblPart” you will see all the columns folder that hold the various data types found in the table, these data types should be familiar to you from performing csvimportexport activities, if not I have them listed on my website, and you can find them in the help file.
We want to find and edit the field “erpnr”, we won’t do this directly like in excel but rather indirectly through a SQL Query.
Click “New Query” to create a new Query, a window should appear on the right side of the screen where we will enter the code to get rid of the unwanted data. We are not going to delete or erase the data, rather we are going to overwrite the entire column of data with the NULL or empty set.
We will now enter the query command, where your “Table Name” may be different:
UPDATE Table Name SET erpnr = Null;
DOUBLE CHECK THIS ENTRY
DOUBLE CHECK THIS ENTRY AGAIN!
Drag select the text in the command like you are going to change the font (its an SQL thing) then click the [! Execute] button to run the query.
The query should run and overwrite all the records in the column with the “null” set, you should also see a text message that indicates success and how many data entries were edited.
Close out the application and launch EPLAN. Open the parts manager and check to see that all the entries have been properly updated.
That should do it,
Have a nice day and let me know if you have any other questions.