Ran into a primary key error when using the Data Dictionary Utility recently. The reasons were obvious but a little backgorund first.

We sell 3rd party add-ons for Sage 500. In order to ensure we do not accidently use any data ranges values used by these applications, we gave them specific blocks to use and instituted development practices to not use any of these blocked out values. One of the ways we try to do this is by using the upper range of our Data Ranges for these 3rd party applications leaving the lower ranges open.

On a recent project where we were adding new tables to the data dictionary, we ran into a Primary Key error inserting into tsmString while using the Data Dictionary utility. Reviewing a trace it seems that the utility creates strings for each column within the table and gets the next stringno using the following statement:

SELECT S = MAX(StringNo) + 1 FROM tsmString WITH (NOLOCK) WHERE StringNo >= 11110000 AND StringNo < 11119999

The problem with this statement is if you reach the upper end it always returns the highest value and then tries to insert it into tsmString which causes the Primary Key error described.

First enhancement would be to return a proper error message indictaing that there are no more StringNo's available and to contact support for additional ranges.

Second enhancement which would be for shops such as ours that set aside blocks of ranges for 3rd party add-ons to have the Data Dictionary utility look for any available string no's not used. The following code shows how this can be done in SQL:

DECLARE @_lStringNo As Table
(
StringNo INTEGER NULL
);

DECLARE @_lRowKey INTEGER = 11110000 --Lower end of StringNo range assigned

WHILE @_lRowKey <= 11119999 --High end of StringNo Range Assigned
BEGIN
INSERT INTO @_lStringNo
(StringNo)
ValueS(@_lRowKey)

SELECT @_lRowKey = @_lRowKey + 1
END;

--Delete StringNo's used already
DELETE @_lStringNo
WHERE StringNo IN (SELECT StringNo FROM dbo.tsmLocalString WHERE StringNo Between 11110000 AND 11119999)

--Return What is still available
SELECT * FROM @_lStringNo Order By StringNo

The same type of logic can be done within a VB 6 ADO recordset or an ADO.NET recordset if the utility happen to be re-written using that. If no available StringNo's are found, then display the error that none are available and contact support for additional ranges.

by: Louis D. | over a year ago | 6 - General Enhancements