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
  • What error message do you get? – Randolph West Aug 8 '16 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. – Martin Smith Aug 8 '16 at 17:55
  • 2
    @MartinSmith Or it might be using a sequence object. – Kenneth Fisher Aug 8 '16 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 '16 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. – Kenneth Fisher Aug 10 '16 at 3:10
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.

  • 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 '16 at 8:05
  • Pretty sure just going to find an insert with an existing key. – paparazzo Aug 10 '16 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 '16 at 9:10
  • Can't hurt. But I suspect the app just has a variable in memory it adds 1 to. – paparazzo Aug 10 '16 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 '16 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
  • 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. – Scott Hodgin Aug 10 '16 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, privacy policy and cookie policy

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