"Question: How could I set Allow Zero Length in SQL Server? I upsized my Access database tables with table property 'Allow Zero Length' set to NO. After linking tables from SQL Server to Access database I found that table property 'Allow Zero Length' was set to YES. How could I put 'Allow Zero Length' back to NO?
There are 2 database properties in SQL Server that control 'Allow Zero Length' property in Access for linked tables:
- ANSI nulls
- ANSI null default
Default settings for those properties in SQL Server in a newly upsized database depend on model database. The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages.
To change 'ANSI null default' for this particular database check a box using Enterprise Manager (EM).
There is no such a box in EM for 'ANSI nulls'. So the only way to change both properties is to run T-SQL statements in Query Analyzer (QA)
EXEC SP_DBOPTION N'Northwind_NOAllowZeroLength', N'ANSI null default', N'true'
GO
EXEC SP_DBOPTION N'Northwind_NOAllowZeroLength', N'ANSI nulls', N'true'
GO
Example
Execute the following T-SQL statements in (QA)
CREATE DATABASE Northwind_NOAllowZeroLength
GO
EXEC SP_DBOPTION N'Northwind_NOAllowZeroLength', N'ANSI null default', N'true'
GO
EXEC SP_DBOPTION N'Northwind_NOAllowZeroLength', N'ANSI nulls', N'true'
GO
Run Microsoft Upsizing Wizard and select option 'use existing database'
Select Northwind_NOAllowZeroLength and upsize your database. Then link tables via ODBC and notice that 'Allow Zero Length' property in Access is set to NO