In this blog post, let’s learn about the error message “150 – Time value %d used with MAX_DURATION is not a valid value; MAX_DURATION wait time must be greater or equal to 0 and less or equal to %d.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
150 – Time value %d used with MAX_DURATION is not a valid value; MAX_DURATION wait time must be greater or equal to 0 and less or equal to %d.
Reason for the Error
Consider the following code snippet as an example for the error message “Time value%d used with MAX_DURATION is not a valid value; MAX_DURATION wait time must be greater or equal to 0 and less or equal to%d”:
WAITFOR DELAY '00:00:70', MAX_DURATION 80;
In this example, we’re using the WAITFOR command to delay execution for 70 seconds with a maximum length of 80 seconds. However, the maximum duration value of 80 seconds is less than the actual delay value of 70 seconds, which is invalid. As a result, SQL Server will generate the error message “Time value%d used with MAX_DURATION is not a valid value; MAX_DURATION wait time must be greater than or equal to 0 and less than or equal to%d.”
Solution
To correct this problem, we need to ensure that the maximum duration value is more than or equal to the actual delay value. In the preceding example, we may alter the code as follows:
WAITFOR DELAY '00:00:70', MAX_DURATION 90;
Here, the maximum duration value has been raised from 70 seconds to 90 seconds, which is longer than the real delay value. The error should be fixed as a result, and the code should run smoothly after that.
