Tell me if you’ve heard this one before:<\/p>\n
\nI changed data from my application, but when I checked the database, I couldn’t see the change!<\/p>\n<\/blockquote>\n
I’ve seen this. Loads<\/strong>. It can be quite perplexing for folks because they expect to see an error message if the insert, update, or delete failed. I put this post together to provide some things you can investigate if this happens to you – you are sure that you updated the data, but when you check using SQL Server Management Studio (SSMS), your change isn’t there. For the remainder of the post, I’m going to use the word “update” to mean any change to the data, even
MERGE<\/code><\/a> {shudder}.<\/p>\nThe causes behind this issue can usually be lumped into three categories:<\/p>\n
1. Misdirection<\/h3>\n
Most commonly, the problem is that app is updating a table that is in a different location than the one you’re checking. Some examples of things you should verify:<\/p>\n
\n
- That the connections for the application and SSMS:\n
\n
- Point to the same server\/instance –
LOCALHOST<\/code> andLOCALHOST\\SQLEXPRESS<\/code> are not the same, and if the app is running on a different machine or a different domain, it may not be safe to trust identical DNS.<\/li>\n- Point to the same database – in many cases we might have the same table in multiple databases, even on the same server.<\/li>\n
- Use the same authentication method and credentials – since the user’s default schema or the login’s default database might point you in different directions.<\/li>\n
- Do not use the dreaded User Instances or
AttachDbFileName<\/code> features<\/a>, since even if all the other connection string attributes are the same, you are definitely looking at two independent copies of the database.<\/li>\n<\/ul>\n<\/li>\n- That both the update query and the validation query:\n
\n
- Explicitly use schema references<\/a> for all objects.<\/li>\n
- Do not inject database, server, or table names based on criteria that may differ.<\/li>\n<\/ul>\n<\/li>\n
- That there isn’t some background job that runs and cleans up data it deems non-compliant for whatever reason.<\/li>\n
- That you’re not assuming some large value has been corrupted because SSMS doesn’t show the whole value<\/em>. In different contexts, it may only show 256, 8,192, 43,697, or 65,535 characters. This is usually not a problem with the data or the operation<\/strong> but rather just the way the tool presents data. You can check with
DATALENGTH<\/code> that all of the data is there, orRIGHT<\/code> to verify that the end<\/em> of the string is intact. See Validate the contents of large dynamic SQL strings<\/a> for more information, and have a generous look around the maximum characters settings in Tools > Options > Query Results > SQL Server > Results to Grid (max 64K) | Results to Text (max 8K).<\/li>\n<\/ul>\n2. It’s not always caching…<\/h3>\n
…but, sometimes, it’s caching. Check if any of the following could be true:<\/p>\n
\n
- You’re querying from a readable secondary or replication subscriber, and the change just hasn’t made it there yet.<\/li>\n
- You’re using snapshot or
NOLOCK<\/code> and are seeing an earlier (or perhaps even invalid) version of the row.<\/li>\n- You’re not using SSMS to verify. Another application (even the same application!) might display cached results (even data it just updated!). Make sure you have a way to bust the cache and force the application to reload data from the source, and if the source is a secondary of any kind, that you’ve waited long enough.<\/li>\n<\/ul>\n
3. Swallowed exceptions<\/h3>\n
Sometimes the issue is that we assume<\/em> the statement succeeded simply because we didn’t see an error message. This is not always a safe assumption! Not all errors bubble up to the caller, for example:<\/p>\n
\n
- If the query sent to SQL Server actually went through an intermediary, like an API or middle tier, it may not expose exceptions by default.<\/li>\n
- If the query is executed asynchronously, the caller may never see the exception, and doesn’t wait for success or failure. The update may have still been happening when you checked and, in the event of blocking, may still be blocked as you are reading this.<\/li>\n
- There may be a trigger that rolled it back (and the application ignored the exception), or an
INSTEAD OF<\/code> trigger that simply didn’t end up performing the update, in which case the application wouldn’t even have an exception to ignore.<\/li>\n- You may have
TRY\/CATCH<\/code> or other error handling \/ rollback mechanisms in your code (in the query or in the application).CATCH<\/code> could easily be ignoring the exception or raising a generic exception of a lower severity, or a rollback could be happening without raising any exceptions.<\/li>\n- At an even more basic level, your code or procedure may have a debug or auto-rollback flag inadvertently set to true.<\/li>\n<\/ul>\n
Conclusion<\/h3>\n
There are many reasons why it may seem<\/em> like an update succeeded, but validation suggests it didn’t. Usually it is the case that either you weren’t checking the right place, you checked too quickly, or there was<\/em> a failure. Hopefully the above gives a healthy set of things to check if you are ever in this scenario.<\/p>\n","protected":false},"excerpt":{"rendered":"
Tell me if you’ve heard this one before: I changed data from my application, but when I checked the database, I couldn’t see the change! I’ve seen this. Loads. It can be quite perplexing for folks because they expect to see an error message if the insert, update, or delete failed. I put this post……<\/p>\n","protected":false},"author":341115,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531,143539],"tags":[147174],"coauthors":[158980],"class_list":["post-98571","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","category-theory-and-design","tag-t-sql-coding"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98571","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=98571"}],"version-history":[{"count":19,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98571\/revisions"}],"predecessor-version":[{"id":98760,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98571\/revisions\/98760"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98571"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}