I have found at least one case where the SQL Server 6.0 documentation is
wrong, and the default nullability will not always be used.
I was calling a stored procedure from Access which worked in testing,
and worked for a while in production. Eventually, the procedure stopped
returning results. It would start working again for any given user when
they exited Access and restarted it.
The problem turned out to be that a temporary table created and used in
the sp has a field which must often allow null values, but I had not
specified the nullability. Sometimes the field would allow nulls,
and sometimes not. Access was unaware that and error had occurred in
the sp. Of course, I will now start including error handling and
raiserror in my stored procedures so this kind of problem will be easier