Duplicate key error on a range copy statement

Article
11/13/2009

An ISV ran across an interesting problem a few days ago in his Dexterity/Dynamics 9.0 solution.

The goal of a particular procedure was to copy records from one table into a nearly identical duplicate temporary table. The duplicate table contained all of the original table fields plus a few new ones. The temp table was created by using the Duplicate feature in Dexterity so the keys of the new table were identical to the original table.

The ISV code was using was essentially:

Dexterity Code

 range copy table MyTable to table MyTableTemp;
check error;        {check error for debugging purposes}
warning str(err()); {for debugging purposes}

You would expect that this would work fine however what what we found that no records were copied and the error message given on the check error statement was:

A range copy statement on table MyTable has created a duplicate key.

The err() function returned 17 - DUPLICATE which was consistent with the check error statement.

Puzzling. I admit that my first instinct was that there WAS a duplicate being generated. I've seen that a number of times and have done it myself once or twice. Take a table and range copy it into another table that has different (unique) keys and depending on your data this will work OK sometimes and sometimes not.

But why don't the non-duplicates get inserted? The reason you don't end up with any data is because SQL rolls back the records on the insertion failure and so you don't get any records in the target table.

So why isn't this the solution? Because the ISV duplicated the original table meaning the original keys were duplicated as well (and he assured me he didn't add any new ones) and so if the original table allowed the data the duplicate should also.

But yet we apparently were getting a duplicate created.

After determining that there wasn't a code issue and the table definitions were all OK, the next step was to turn on the SQL Server Profiler to see what SQL was complaining about.

To test this, I duplicated the RM_Customer_MSTR table and added the Vendor ID field to the end of the table. I also set the physical name of the table to "temp" to make sure my test was the same as the ISV's.

The Dexterity statement used and the SQL Statement generated by the range copy operation is shown below. I removed most of the columns since you don't need to see them all to see the issue.

Dexterity Code and SQL Trace results

range copy table RM_Customer_MSTR to table RM_Customer_MSTR_Temp;

INSERT INTO ##2033653 (CUSTNMBR, CUSTNAME, CUSTCLAS, <removed>, INCLUDEINDP)
SELECT CUSTNMBR, CUSTNAME, CUSTCLAS, <removed>, INCLUDEINDP FROM TWO.dbo.RM00101

The one thing I noticed is that the VENDORID is not included in the insert/select statement generated. So in that case, on a SQL insert where a column is ignored then SQL fills it with the bound default (if available) or null if not. Well, this is a temp table so there is NOT a bound default and so the column should be getting a null which isn't allowed by the constraints that Dexterity puts on table columns during table generation.

So where is the duplicate? There isn't one.

Turning on the Error Messages in our SQL Profiler during our range copy statement, SQL tells us the actual issue which is what I was thinking it to be after verifying the table keys.

Cannot insert the value NULL into column 'VENDORID', table 'tempdb.dbo.##2033653; column does not allow nulls. INSERT fails.

Well that explains it. The new columns are ignored and get the column default which is null. Nulls are not allowed and so it fails with no inserts.

In this case, the Dexterity error message given is completely incorrect and misleading - one of the very few times I can think of that has been the case.

There are several solutions that I can think of depending on circumstances.

If this wasn't a temp table and instead a permanent table, then you can make sure that your columns are bound to the appropriate defaults. So in this case, the binding constant for the VENDORID column is GPS_CHAR. Since the column has a legal default this would just work.
Loop through the original table in a while statement and copy the records one by one to the target table. This will work because Dexterity will supply the default for the new columns. Of course this is slow since these are single inserts in a loop. Very inefficient but it would work.
Use pass through SQL and write the correct code yourself. In this case, I added the VENDORID column to the list of column fields in the target table and set the default to be an empty string (which is not NULL). This was the solution we employed for the ISV.

INSERT INTO ##2033653 (CUSTNMBR, CUSTNAME, CUSTCLAS, <removed>, INCLUDEINDP, VENDORID)
SELECT CUSTNMBR, CUSTNAME, CUSTCLAS, <removed>, INCLUDEINDP,'' FROM TWO.dbo.RM00101
Upgrade to 10.0. The range copy statement was enhanced for 10.0 to handle the new columns in the target table. In this case, Dexterity would generate the SQL statement from #3 above and the range copy operation would work successfully.

So the next time you are using Dexterity to perform a range copy operation, think about your keys on the target table to make sure that you couldn't get a legitimate duplicate. And if you are 9.0 and have new columns in the target table, consider using methods #2 or #3 above instead of a straight range copy statement.

Best regards,

Patrick
Developer Support

Duplicate key error on a range copy statement

Additional resources