2

I need to figure out how to insert into this table without breaking the application that runs on it. When I run an insert statement outside the application, I select the max uid (which is the indexed primary key) and add one, then insert the data with that uid.

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

Something does not make it down to the application though and it will not allow new inserts into the table through the application until I delete the rows I inserted through SQL. It gives an SQL error that the primary key customer_salesrep_uid is already taken. The application does not seem to know about the inserts I made outside of it and the increases to the uid field. There must be a counter somewhere that I need to update? The index should update by itself on insert.

Update 8/10 re comments: Thank you all for your advice. I will try the profiler and also will try inserting in test with proper locks, with a lower uid than max, and with a much higher uid than max and see if I can glean any further clues from that.

Table Structure:

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
    customer_salesrep_uid   int no  4   10      0       no  (n/a)   (n/a)   NULL
    company_id  varchar no  8                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    customer_id decimal no  9   19      0       no  (n/a)   (n/a)   NULL
    salesrep_id varchar no  16                  no  yes no  SQL_Latin1_General_CP1_CI_AS
    commission_percentage   decimal no  9   19      4       no  (n/a)   (n/a)   NULL
    primary_salesrep_flag   char    no  1                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    row_status_flag int no  4   10      0       no  (n/a)   (n/a)   NULL
    date_created    datetime    no  8                   no  (n/a)   (n/a)   NULL
    created_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS
    date_last_modified  datetime    no  8                   no  (n/a)   (n/a)   NULL
    last_maintained_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS

No Identity columns

Indexes:

    index_name  index_description   index_keys
    ak_customer_salesrep    nonclustered, unique, unique key located on PRIMARY company_id, customer_id, salesrep_id
    pk_customer_salesrep    clustered, unique, primary key located on PRIMARY   customer_salesrep_uid
11
  • What error message do you get? Aug 8, 2016 at 17:48
  • 8
    Maybe the application just caches the current max in memory and assumes that it is the only thing that will insert to the table? So when you do your inserts it clashes? have you tried restarting the application after your manual inserts? In any event looks like something you need to resolve at the application level. Aug 8, 2016 at 17:55
  • 2
    @MartinSmith Or it might be using a sequence object. Aug 8, 2016 at 18:04
  • 4
    fire up an XE or trace session, and insert from the app to see if it is retrieving the key from another table. I have an application where all keys are retrieved and updated from a "Keys" table. I would have the same issue you described if i was to insert rows into a table without updating this keys table to match.
    – Bob Klimes
    Aug 8, 2016 at 18:21
  • 2
    You could always add rows with a value below the minimum. Negative values probably. That won't affect the application inserts since it's going for the top. You will need to watch out in case that causes other issues but it might work. Try it in a test environment first obviously. Aug 10, 2016 at 3:10

4 Answers 4

1

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

You could run a trace using SQL Profiler to catch the exact statement that your application is executing.

Also, a slight alteration to Scott's suggestion, I wouldn't use a TABLOCK, instead, something like:

BEGIN TRANSACTION
SELECT @customer_salesrep_uid = max(customer_salesrep_uid) 
    FROM YourTable WITH (XLOCK, READPAST)...

This should reduce the risk of locks on the table.

5
  • 1
    It's usually best not to run profiler in a production environment as it can cause some overhead. This should be run on a test environment of available. Anyway, MSDN is usually the best place to get info about this stuff - msdn.microsoft.com/en-us/library/ff650699.aspx
    – TaylorN
    Aug 10, 2016 at 8:05
  • Pretty sure just going to find an insert with an existing key.
    – paparazzo
    Aug 10, 2016 at 8:54
  • Yes but how does it get the next value to insert? Some sort of internal sequence function? Running a trace will provide more insight than OP currently has.
    – TaylorN
    Aug 10, 2016 at 9:10
  • Can't hurt. But I suspect the app just has a variable in memory it adds 1 to.
    – paparazzo
    Aug 10, 2016 at 9:11
  • Thank you all. I will try these suggestions when I have a chance and hopefully it is something I can touch from SQL
    – deflator
    Aug 10, 2016 at 19:20
1

They could have a custom-made sequence implementation in the form of a dedicated table with an integer column to store the current sequence value. Their inserts could be wrapped into a transaction and go like this:

BEGIN TRANSACTION;

DECLARE @Sequence TABLE (NewValue int);

UPDATE dbo.Sequence
SET CurrentValue += 1
OUTPUT inserted.CurrentValue INTO @Sequence (NewValue);

INSERT ...  /* using @Sequence.NewValue */

COMMIT TRANSACTION;

Depending on how many other tables could use this method, there might be multiple such tables – or there could be just one but with either a row or a column per table.

0

Perhaps the application is sneaking in between your select and your insert.

It sounds like you need to put your select/insert into a transaction and apply a table lock during the process. EX:

BEGIN TRANSACTION
select @customer_salesrep_uid = max(customer_salesrep_uid) from YourTable WITH (TABLOCKX,HOLDLOCK)
set @customer_salesrep_uid += 1
insert into YourTable... new row...
COMMIT
1
  • If there is a TEST version of this application/table, you might try inserting a new rows that are WAY higher than the max row that currently exists on the table. Then allow the application to run as normal and see what UID values the application puts out. If they are close to the max value that existed before you inserted your new rows, then it's pretty obvious the application is not using the table to get the next available value and I'm afraid you're pretty much out of luck. Aug 10, 2016 at 9:18
0

If you're worried about not having SQL support, just wait until they realise you're hacking data into the DB. I'd put money on them refusing you any other support from that point on.

That said, it sounds like somewhere there is a "max ID" table for all the other tables and you're hitting a problem there where its idea of "max ID" is wrong because you've taken it. Of course that's just a guess.

Also, the max+1 is really the wrong approach both from you and the vendor. It causes contention if you can do it right and duplicate errors when you can't. The best way is sequences or identity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.