Violation of PRIMARY KEY constraint PK##

(0) Share

Report

Posted on by Beat Bucher GP Gee...

28,058 Moderator

The other day a user came to see me with a very strange error message. In fact the message is not that strange would it be associated with a regular table or transaction... I already dealt with PK violations, but this time it was affecting a temp table in the system (dbo.##0342219).

It all starts with the Item Maintenance window, where the user wanted to search for a specific set of Items starting with the string 'SOFT'. Using the lookup glass in the Item Number field, it opens the Items search box, and then using the find icon, opens the 'Smart-list' filter dialog box. There entering the information to search for Item Numbers that begins with 'SOFT', after clicking on OK, the system returns an error message :

Since it happens not in all GP companies, I can assume that it is depending maybe on some corrupted IV00101 entries... and I traced with the SDT and created a DexSQL.log file to analyse the operation. It is a very common SQL query that is calling up the IV00101 and IV00115 with a parse on ITEMNMBR LIKE 'SOFT%'...
I did a test with a different company where the search dialog box does not use the 'Smart-List' options, but rather a basic 'Find' option :

And this time the search returned all the values correctly with no errors. Curious about the query behind this search, I traced it as well with the SDT and figured out that the SQL query was even simpler, because it was looking only for the IV00101 table (and did not care about the IV00115).

Also, the test made in other companies with the 'SmartList' like search option, did not generate any PK errors, but the data set is also slightly smaller or different.

I know that the search options are set by user and by company, but could not remember where to change it, to carry on other tests. Does anybody remember where to switch this ?

Does anybody else had such strange temp table PK error and how could it be fixed ?

Thanks in advance for your hints

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (1)

Tim Foster 8,515 on at

Like (1)

Report

Béat,

Generate the error message and the open query analyzer. Switch to the temdb and run the following command:

sp_help ##temp

Replace ##temp with the table number in the error message. You will see everything you need about the temp table there.

From what you have described, I think you have some "rogue" records in IV00115 that are creating duplicates that shouldn't be there.

Tim Foster

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

Hey Tim,

Tried this out and got an SQL error back... The object '##2133053' does not exist in database 'tempdb' or is invalid for this operation.

I can see the temp table in the tempdb database (##2133053), but even a select on it doesn't return any record...

Now that led me to another trick about the PKeys.. sp_helpconstraint can show you the constraints related to a table. I did run this against the IV00115 and it tells me that the Primary Key PKIV00115 is built on the following fields : ITEMNMBR, MANUFACTURER, MNFCTRITMNMBR, PRIMARYITEM.

Thus I've to figure out now if there is any duplicate records included in that PK constraint...

Beat

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Beat, do a simple "Select *" on both tables, and verify if for some reason you have an invalid/blank record, those tables you are seeing are being created by the stored procedures associated with the lookup.

Was this reply helpful? Yes No
Tim Foster 8,515 on at

Like (1)

Report

Béat,

I was able to see everything in Profiler and examine the query and see the temp table and execute the sp_help. The PK for the temp table is ITEMNMBR and Master_ID. Master_ID was always set to nothing (''). So if IV00101 LEFT OUTER JOIN IV00115 produces duplicates...

Tim

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Francisco,

My IV00115 contains 6480 records and there is no blank line in it. The field MNFCTRITMNMBR is most of the time empty (but contains at least a dash "-"). About 600 of my items start with SOFT and there doesn't seems to be any duplicates.

Tim,

I did a search with a LEFT OUTER JOIN on both tables and IV00101 returns much more 'SOFT' item number than IV00115, which means not all records have an entry in the Manufacturing item table (1339 rows vs. 587 rows).

The problem seems really related to some of those 1339 item numbers begining with SOFT, because a search filter with something else doesn't trigger the PK error.

Beat

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

I used this script to find duplicate entries in IV00115, but no luck. Returns 0 records.

select ITEMNMBR, MANUFACTURER, MNFCTRITMNMBR, PRIMARYITEM, count(*) as Nb_of_occurence from IV00115

--where ITEMNMBR like 'SOFT%'

group by ITEMNMBR, MANUFACTURER, MNFCTRITMNMBR, PRIMARYITEM

having count(*) > 1

Was this reply helpful? Yes No
Tim Foster 8,515 on at

Like (0)

Report

What does this script return?

SELECT ITEMNMBR,COUNT(ITEMNMBR) AS NITEM

FROM IV00115

WHERE ITEMNMBR LIKE 'SOFT%'

GROUP BY ITEMNMBR

HAVING COUNT(ITEMNMBR)>1

The last part of the query that the SmartList uses is :

FROM IV00101 (nolock) LEFT OUTER JOIN

IV00115 (nolock) ON IV00101.ITEMNMBR=IV00115.ITEMNMBR

WHERE ((UPPER(ISNULL(IV00101.ITEMNMBR,'')) LIKE 'SOFT%'))

This is beginnning to look like a bug...

Tim

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I traced the same on my environment with Fabrikam and it does not happen

whit your dexsql log, go back and retrieve which procedures are ran before the select

you will see something like "create table ##59595403" and a bunch of instructions

then "Alter Table or create constraint or primary key" then based on that table it will create some procedures like ##59595403SS_1 those are the procedures that might be inserting the data before the comparison.

Or if you want send me the dexsql.log and I'll check to see if there is somethign wrong, as well it would be good Idea to ask you for GP version and service pack, I am on 2010 R2 btw

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Hi Tim,

The snipet of code you posted returned actually 3 duplicate entries...

ITEMNMBR NITEM
SOFTW-00004-00 2
SOFTW-00134-00 2
SOFTW-00289-00 2

When looking at the details of those entries, I actually don't find any of them which would break the PK rule for uniqueness...

ITEMNMBR ITEMNMBR MANUFACTURER MNFCTRITMNMBR PRIMARYITEM
SOFTW-00004-00 SOFTW-00004-00 VANDYKE SCRT-0009-0002 1
SOFTW-00004-00 SOFTW-00004-00 VANDYKE VAN61884 0
SOFTW-00134-00 SOFTW-00134-00 FTI - 0
SOFTW-00134-00 SOFTW-00134-00 FTI -- 1
SOFTW-00289-00 SOFTW-00289-00 ATTACHMATE 186885.1400 1
SOFTW-00289-00 SOFTW-00289-00 WRQ 000-039059 0

Even the second Item has a different (--) Manufacturer Item Nbr, probably because the system would not accept an identical nbr twice in GP...

Francisco,

We're running on GP2010 R2 since a end of July this year. I can't remember having a user complaining about this search feature in the Item Maintenance, although it is probably not widely used. I looked into the DexSql.log and could only find a call for the temp SP : {CALL ##zDP_0342219F_1(NULL,NULL,NULL,NULL)}. There was nothing about creating a temp table or so. The table name only shows up in a series of queries in the log.

Beat

Was this reply helpful? Yes No
Verified answer

Tim Foster 8,515 on at

Like (0)

Report

Béat,

There isn't a problem with IV00115 - you're right, no key violation there.

The problem is the Smartlist SQL - The last part of the query that the SmartList uses is :

FROM IV00101 (nolock) LEFT OUTER JOIN

IV00115 (nolock) ON IV00101.ITEMNMBR=IV00115.ITEMNMBR

WHERE ((UPPER(ISNULL(IV00101.ITEMNMBR,'')) LIKE 'SOFT%'))

The IV00115 table shouldn't be in the query (just like the non-Smartlist lookup window). Either the programmer called out the Dex that runs the SmartList incorrectly or the the Stored procedure itself is broken. Either way - BUG!!!!

There are no records in table IV00115 in the Fabrikam Company. If you insert records correctly this bug will show up. The temp table is created on the Dynamics database, I can see the create statement and the Inserts in SQL Profiler. That is where I'm getting my information from.

Tim

Was this reply helpful? Yes No

Community site session details

Violation of PRIMARY KEY constraint PK##

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

Community site session details

Violation of PRIMARY KEY constraint PK##

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates