Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I see a couple of potential errors that could be preventing the commit but without knowing more about the structure these are just guesses:</p> <ol> <li><p>The update clause in the nested if is trying to update a column (or set of columns) that must be unique. Because the check only verifies that at least one item exists but does not limit that check to making sure only one item exists</p> <pre><code>IF (0 &lt; (SELECT ...) ) BEGIN </code></pre> <p>vs.</p> <pre><code>IF (1 = (SELECT ...) ) BEGIN </code></pre> <p>you could be inserting non-unique values into rows that must be unique. Check to make sure there are no constraints on the attributes the update runs on (specifically look for primary key, identity, and unique constraints). Likelyhood of this being the issue: Low but non-zero.</p></li> <li><p>The application is not passing values to all of the parameters causing the @message string to null out and thus causing the logging method to error as it tries to add a null string. Remember that in SQL anything + null = null so, while you're fine to insert and update values to null you can't log nulls in the manner the code you provided does. Rather, to account for nulls, you should change the setter for the message variable to the following:</p> <pre><code>SET @message = 'bbgs_cc_setStmtStatus: Saved registry key [' + COALESCE(@registryKey, '') + '] as status [' + COALESCE(@registryVal,'') + '].'; </code></pre> <p>This is far more likely to be your problem based on the reported error but again, without the app code (which might be preventing null parameters from being passed) there isn't any way to know.</p></li> </ol> <p>Also, I would note that instead of doing a </p> <pre><code>IF (0 &lt; (SELECT count(*) ...) ) BEGIN </code></pre> <p>I would use</p> <pre><code>IF (EXISTS (SELECT 1 ...) ) BEGIN </code></pre> <p>because it is more efficient. You don't have to return every row of the sub-query because the execution plan will run the FROM statement first and see that rows exist rather than having to actually evaluate the select, count those rows, and then compare that with 0.</p> <p>Start with those suggestions and, if you can come back with more information, I can help you troubleshoot more.</p>
    singulars
    1. This table or related slice is empty.
    1. POCurrent transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDaniel King
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCurrent transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload