1

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?

Improve this question
7
  • You need to get the real error, not the friendly error that is exposed to end users on the web page.
    – Aaron Bertrand
    Feb 22, 2017 at 2:39
  • As per Aaron, to find the real error , please look at the SQL error log and let us know what's recorded and also look at the windows logs for both database server and the web server
    – entDba
    Feb 22, 2017 at 2:41
  • Looking ahead - how are you generating the primary key for new rows? Still selecting the current max ID and adding 1?
    – RDFozz
    Feb 22, 2017 at 2:48
  • I looked at the logs and they didn't offer much. The only difference was connection/user information was available through the logs that was not available on the front end. This was a one time deal; all records will be added by the end user going forward. There must be query/procedure that creates the primary key once the fields are populated and the record saved. It's frustrating because we contacted support before we executed the statement and they said use MAX to determine the next primary key value.
    – BrownEyeBoy
    Feb 22, 2017 at 2:55
  • So, it sounds like this is a third-party application, and you don't (maybe can't) know the guts of the code. I don't suppose you've talked to support about this error? If nothing else, they may be able to help you find details of the SQL error; that's normally available one way or another.
    – RDFozz
    Feb 22, 2017 at 3:11

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

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

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