Error on backup - ExecuteSQL error...Syntax error in INSERT INTO Device_Partnumber


 Product(s):Promis.e, Bentley Substation
 Version(s):through 08.11.12.137
 Environment:N\A
 Area:Project Management
 Subarea:Backup and Restore Project

Error or Warning Message

When attempting to [[Backup Project|backup]] a particular project, the following error messages occur or similar are displayed and the project is not backed up.

Internal Error
Error Message:  ExecuteSQL error, insert into Device_Partnumber
(id,device_id,partnumber,quantity,mainpartnoflag,balloon,bomflag) values(123,,'MyPart Number',1,1,'','')
Syntax error in INSERT INTO statement.
TargetSite: Void BackupMSSQLProject(Int32, System.String, System.String)
DeclaringType: ECT.ECAD.DAL.BackupData
Attributes: private
MemberType: Method

Information
System Exception: ExecuteSQL error, insert into Device_Partnumber
(id,device_id,partnumber,quantity,mainpartnoflag,balloon,bomflag) values (123,,'MyPart Number',1,1,'','')
Syntax error in INSERT INTO statement.  --> System.Data.OleDb.OleDbException:  Syntax error in INSERT INTO statement.
   at MDBProxy.ConnectionManager.ExecuteSQL(Int32, nConnectionID, String strSQL)
   at MDBProxy.LocalTools.ExecuteSQL(Int32, nConnectionID, String strSQL)
   --- End of inner exception stack trace ---
   at MDBProxy.LocalTools.ExecuteSQL(Int32, nConnectionID, String strSQL)
   at ECT.ECAD.DAL.BackupData.BackupMSSQLProject(Int32 projectId, String connectString, String path)

Explanation

The "Syntax error in INSERT INTO statement" portion of the messages indicate the problem is occurring as the software is trying to insert data from the SQL project database into the database in the backup file. The problem could be that the data violates some constraint, causing the insertion to fail.

In the example messages above, there is no value present to be inserted into the device_id column, which violates a constraint of the database. It also indicates that an instance of the part number exists in the project with no association to any device ID, which should not be the case.

How to Avoid

Option 1 - Remove unplaced devices from the project

  1. Perform the steps described in the [[Remove Unplaced Devices]] article.

  2. Back up the project.

Option 2 - Copy the project

  1. Use [[Project Manager]] to copy the project.
  2. Back up the copy.

Option 3 - Copy the pages

  1. Open [[Project Manager]].
  2. Create a new project based on the same project options as the problem project.
  3. Select all the pages in the problem project.
  4. Right-click the selection and choose Copy.
  5. Specify the new project as the destination project and set remaining options. Generally in this situation, choose Keep Existing and Maintain / Complete where applicable.
  6. Click OK.
  7. Back up the new project.

 Original Author:Matt_P