Si en tu aplicación capturas la señal de error puedes personalizar el mensaje. Algo un poco más sofisticado es indagar el número exacto del código de error y responder según corresponda.
Por ejemplo, cito un extracto del código de error 515, puedes encontrar más detalles en la ayuda del sql server:
Cita: Error 515
Severity Level 16
Message Text
Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.
Explanation
This error occurs at run time when an attempt is made to use a null value while inserting or updating a column that does not allow null values.
This message differs from the following message, which indicates that the attempt has been detected at compile time.
error 233: The column '%.*ls' in table '%.*ls' cannot be null.
This error can also occur if the table that is the target of an INSERT or UPDATE statement in a stored procedure or trigger is being dropped and re-created, and one or more of the table column definition(s) have changed from NULL to NOT NULL.