New SQL Table

I’m using Navision Attain 3.01B SQL Server Option. Using Navision, I’ve created a number of tables. I’m then using SQL Server to populate the fields in this table, then using Navision codeunits to process that data and validate it and put in proper tables (customer, vendor, et al). My problem is: By default, a table created in Navision will not allow Null Fields (Null is not checked in table design in SQL). When using SQL SErver tools to fill in these tables, this is necessary, so I’ve had to go in and manually change the table definitions in SQL. I can’t find anything in the Navision table to correspond to “Allow Nulls”, but was curious is anyone else had run into this, and if there was a better way then what I’m doing. I’m running SQL 2000, and using DTS to populate the tables, if it matters. Daniel Day Developer Elliott Davis Technology Solutions

Navision sets NOT NULL in the SQL table by design (except for Navision field type BLOB, which equals to IMAGE in SQL) and you can’t change this. You’ll have to keep on using SQL Enterprise Manager to modify the table properties. John

I have the same problem as you have. But when I create a new database with Attain 3.01B there is a option called “ANSI NULL default”. As I can understand the help it says that NULL would be allowd if the option is checked. This doesn’t give any change though. Anyone know what this option is for? Best regards Daniel

The option ANSI_NULL_DEFAULT database and session setting controls the default nullability of a column when not explicitly defined in the CREATE TABLE or ALTER TABLE statements. So, if you say: CREATE TABLE MyTab (MyField INTEGER) Will MyField accept NULL or not? The SQL Server default will not allow NULL. ANSI states that NULL should be allowed, so this setting allows for ANSI compatibility when turned ON. If you give a status for the field (either NULL or NOT NULL) when you create or alter it, then that is always used regardless of this setting. This is what Attain does. It is the most robust method SQL-programming wise, because it doesn’t depend on environment settings that the program has no control over. Allowing NULL is a problem for many reasons (because NULL does not have one universally agreed upon meaning), but the biggest issue for Attain is filtering. If column MyField allowed and contained NULL values would you expect the rows where it contained NULL to be retrieved if you made a filter like this? …WHERE MyField <> ‘xyz’ They would not be retrieved because the expression <> ‘xyz’ will not match NULL. It would have to read: …WHERE MyField <> ‘xyz’ OR MyField IS NULL or … WHERE ISNULL(MyField, ‘’) <> ‘xyz’ Extend this to complicated filtering and it becomes a mightmare and performance problem. You are stuck with having to change the NULL status externally. Attain will not complain about a column being manually changed to allow NULL, but it will not correctly handle a NULL value when filtering for the reason I gave above. Also, it will never write a NULL value back to a field in an INSERT or UPDATE statement. It will use the default values of ‘’, 0 etc. The NULL will be lost.