Recently, on a Friday evening, we released an update to one of our core applications. As expected, it went smoothly and no issues were noted. On the following Monday morning, however, the story took a different turn when end users started reporting issues. One of the issues encountered was related to a new stored procedure.
In looking at the procedure, we found there were references to a database that existed within the QA environment but did not in Production. It was assumed that when the procedure was created, it would throw an error because the database referenced didn’t exist.
Unfortunately, it does not work like this.
When the procedure is created, only the syntax of the code is parsed for validity. As long as the syntax is correct the procedure will be created without an issue. This can cause an issue because invalid references can slip by.
The real issue will manifest when the procedure is actually called. The syntax will be checked again by the Parser and then sent to the Algebrizer (or Binding). The invalid reference doesn’t physically exist. This causes the Algebrizer the inability to bind and throws an error.
This is also true with altering a procedure that already exists.
We can prove this fairly easily just by creating and/or modifying a procedure with a reference to an object that does not exist.
-- We all have a Scratch Database right?
-- Create a procedure with invalid reference
CREATE PROCEDURE dbo.ProcCreateDemo
SET NOCOUNT ON;
SELECT * FROM DBThatDoesntExist.sys.objects
-- Does it exist?
SELECT name, object_id, type, type_desc, create_date from sys.procedures
SELECT OBJECT_DEFINITION(object_id) from sys.procedures
We can see that the procedure, even with the invalid reference, was created without issue. If we look at the object definition of the procedure, we clearly see the invalid reference.
When the procedure is executed the error will manifest.
What if we alter an existing procedure?
In this example, I’ll adjust the procedure to take out the invalid reference.
Note that the procedure was altered successfully both times. Even though the second ALTER has an invalid reference, the syntax of the command is correct thus the procedure is altered successfully.
We are currently in the process of making adjustments to our release process to ensure that issues like this are caught in the lower environments rather in Production.
© 2015, John Morehouse. All rights reserved.