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.
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
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