On a project to extend a certain piece of functionality from one site to another, I was asked a question by a third-party vendor why a stored procedure that they had taken from a sister site that was working just fine would not work on this site. The procedure looked correct, but every time they try to run it in the development environment they received an error that for the column dateLastModified it could not be null. Looking at the stored procedure there was indeed no mention of a value for that column, but on the first site it was working?
The vendor’s fix was to change the stored procedure to put in a default value for this column as “getDate()”.
This did fix the issue and did allow the procedure to work correctly, but why was that needed? There had to be something deeper going on. A review of the schema showed that on the first site where the unmodified procedure worked, there was this definition for it:
Ah ha! Now I see it. The column for the first site had a default value set. The better route, and what we did ultimately, was to update the schema on the second site for this column to match the definition of the first one and then rolled the changes to the procedure back. The operations completed normally.
This was a better solution as it kept the database schema more consistent between the two sites which makes it easier to update and test both.