Sql-server – Table update is causing front end SQL statement to fail

insertsql serverupdate

We were in a situation where we needed to add many rows of data to our database and decided the most efficient method was to use an SQL INSERT INTO statement. The table was very simplistic. A primary key column plus an additional seven columns, three being date columns. The table doesn't have any constraints or columns with the IDENTITY property. We used the MAX function to determine the highest primary key id assigned and then added 1 to that value for the new row(s) to be added.

Everything appeared to go well on the back end and the front end. However, now when end users enter a new a record via the front end they are getting the error message "SQL Statement failed, this may be caused by network or database issues". This is relevant because it is only happening on a field that is directly associated with the table we updated.

Can anyone offer any insight to what may be happening?

Best Answer

Turned on sql profiler and learned there was a sequence table that is used to determine the next primary key id. Updated this table and deleted the cache. Performed a restart on the server and the problem was solved. In short it was primary key violation.

Thanks for the sql trace idea that led to sql profiler.