Databases
StackOverflow2013
Postsdbo
PODid SQL 2008 SP3 break INSERT HASHBYTES for anyone else? (it turns out to be an issue with an INT parm)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODid SQL 2008 SP3 break INSERT HASHBYTES for anyone else? (it turns out to be an issue with an INT parm)
    text
    copied!<p><strong>EDIT:</strong> It turns out that the problem was with an input parm. Apparently, if you have an input parm of type INT with a default value of NULL (i.e. @MaxSeqKey int = NULL) and you use that parm in a WHERE clause it can SOMETIMES cause the "hang" issue. What is really weird is that it has been running fine for several months and suddenly broke when SP3 was applied. Since this is part of a batch job we have not run it in test since it was moved to prod. Our test server also has SP3 on it so I ran it in test after I posted this question. Behold! It works fine in test. Go figure. The work around our DBAs found was to DECLARE an internal INT variable in the proc and SET the internal variable equal to the parm. Once again, go figure. Whatever. It works so I wanted to share it with the community.</p> <p>SQL 10.0.5500 (2008 SP3, NOT 2008R2)</p> <p>We have a batch job that kicks off a stored proc. The stored proc has the following TSQL in it.</p> <pre><code>BEGIN TRANSACTION UPDATE h SET h.PreviousHash = h.CurrentHash ,h.CurrentHash = HASHBYTES('SHA1', ISNULL(m.[Name],'') + ISNULL(m.[Address],'') + ISNULL(m.[City],'') + ISNULL(m.[State],'') + ISNULL(m.[Zip],'') ) FROM [Hash] h INNER JOIN m WITH(NOLOCK) ON m.SequenceKey = h.SequenceKey WHERE h.SequenceKey &lt;= @MaxSeqKey INSERT INTO [Hash] (SequenceKey, CurrentHash, PreviousHash, Name, [Address], City, Zip) SELECT m.SequenceKey ,HASHBYTES('SHA1', ISNULL(m.[Name],'') + ISNULL(m.[Address],'') + ISNULL(m.[City],'') + ISNULL(m.[State],'') + ISNULL(m.[Zip],'') ) ,NULL --PreviousHash - this indicates a "new" record ,m.Name ,m.[Address] ,m.City ,m.Zip FROM Merchants m WITH(NOLOCK) LEFT JOIN [Hash] h ON h.SequenceKey = m.SequenceKey WHERE h.SequenceKey IS NULL AND m.SequenceKey &lt;= @MaxSeqKey COMMIT TRANSACTION </code></pre> <p>This is the first part of the proc. This was running fine every night until they installed SQL 2008 SP3 this past weekend. At first we thought the UPDATE followed by the INSERT to the same table was the problem but we commented out the UPDATE statement and it still got hung up (it doesn't crash, it just hangs.) We ran the INSERT statement by itself and it runs fine. It seems to only be a problem when run in a stored proc. Since we have other similar statements in stored procs all over our system that just don't use the HASHBYTES function, we can only assume that this is the problem. I would appreciate it if anyone out there would be willing to take the time to see if they can replicate this.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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