I’ve written before about some of the bad SQL programming practices I’ve discovered in various programs. I’ve found yet another example that has been causing me grief for a couple of weeks.
We’ve got a scheduled job that runs nightly and does some archiving – moving records from a live database to an archive database then deleting the records from the live database. It’s been running fine for months, but recently started failing with the following error:
[SQLSTATE 01000] (Message 0) Incorrect syntax near ‘4’. [SQLSTATE 42000] (Error 102). The step failed.
Hmm. The job has one step and that step calls a stored procedure. As far as I could tell, nothing about the procedure had changed. When I tried to run the procedure manually, I got the same error. I figured it might be caused by some bad data and I started looking into the commands the stored procedure was issuing.
That caused me to go down quite the rabbit hole. That procedure called another procedure, which in turn called another procedure. That procedure called another procedure in a different database, which in turn called a fifth procedure. And each one of these procedures generated dynamic SQL. Tracking down which command was generating the error was time consuming, to say the least.
But track it down I finally did. The problem lay in some dynamic SQL that was being generated to create constraints on some tables. The code looked like it was supposed to examine the existing constraint and, if it was present, check to see if what it was trying to add was already there. But something wasn’t working and the upshot was that the constraint was just getting added to all the time. We ended up with constraints like this:
ALTER TABLE [dbo].[Table_015] WITH CHECK ADD CONSTRAINT [Table_015_BranchID_CK] CHECK (([BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(15) OR [BranchID]=(45) OR [BranchID]=(76) OR [BranchID]=(951) OR [BranchID]=(992) OR [BranchID]=(43) OR [BranchID]=(903) OR [BranchID]=(953) OR [BranchID]=(961))) GO
Wow. The actualy cause of the error was that the variable used to build the synamic SQL was declared as nvarchar(4000) and the string had finally passed the 4000 character mark, thus getting truncated. 4000 characters is the maximum for the nvarchar datatype (unless you use nvarchar(max) and I didn’t want to try that in case it caused other problems elsewhere). So my solution was to modify all the existing constraints to remove the duplicate conditions, which will shrink the size of the command to under 4000 characters. I’m sure this problem will crop back up again, but I have alerted the software manufacturer, so hopefully they will fix it in a future version.
Wow, that’s ugly. Nice find.
Were all the SP’s actually needed or could you rewrite one SP to do what you actually needed it to do?
It was actually even uglier than this. Turns out, even after I changed the constraints, the error was popping back up. What was happening was one of the other stored procedures in the chain was re-creating the constraints based on some strange logic. The constraints were placed on a each group of five groups of 10 tables. Each group was then reference by a view that UNION ALL’ed the 10 tables together. One of the other procedures tried to do an update against the view. But the constraints that were created were not created correctly so among each group of 10 tables, there was no partioning column. For example, the [BranchID]=(15) constraint might have been placed on two of more tables in the view, rendering the view un-updatable. Ugh. Because of all the dynamic SQL involved, we found it easier to make five new tables to replace the five groups of 10 tables and change the views to point to the single tables.
Wow that makes my head hurt.
Sounds like you found a band-aid fix for a band-aid fix on a band-aid fix that was supposed to fix a bug.