OK, so I don't really do SQL Server, but I need to set a default text value in a column on a table. Should be easy, right? Obviously not so for me.

The field is a text field, max 16 chars, I have set don't allow nulls and I want the field to default to "None Reported" when new records are created.

How can I do this?

I've tried ("None Reported") in the Default Value column and it won't save. Using ('None Reported') allows me to save the table, but doesn't insert the default into the row.

Sorry to be such a moron but in my 2 SQL Server books it don't seem to address such a simple issue.

Thanks for your help!

How are you inserting ? If you are using enterprise manager - you will have to refresh to see the defaults being populated. If you use query analyzer when you re-select you will see them.

I think you meant to use the char or varchar data types not the text data type.

This is how a text data type is defined within sql server:

text
Variable-length non-Unicode data with a maximum length of
2^31 - 1 (2,147,483,647) characters.

Consider using char, varchar, nchar, nvarchar columns for character data (text columns are more appropriate for very large character data >> 4000 - 8000 characters).

A1 You can use Alter Table to add a default constraint to _char columns. for example:
ALTER TABLE [dbo].[YourTableName]
ADD CONSTRAINT [Default_ForYourVarCharDataColumnName] DEFAULT ('None Reported') FOR [YourVarCharDataColumnName]

To work with a detailed example run the following in tempdb:

Use TempDB
Go

CREATE TABLE [Test] (
[IDColumn] [int] IDENTITY (1, 1) NOT NULL ,
[VarCharData] [varchar] (32) NOT NULL,
[InsertedBy] [varchar] (32) NOT NULL
)
GO

SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]

-- insert some values:
INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
VALUES('Mary had a lamb.', 'Tom')
INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
VALUES('The lamb had a fleece.', 'Dick')
INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
VALUES('The fleece was white as snow.', 'Harry')
Go
-- the following insert should fail (no default exists for VarCharData)
INSERT INTO [tempdb].[dbo].[Test]([InsertedBy])
VALUES('Joe')
Go
-- view table rows
SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]

-- Now add the 'None Reported' default (a constraint for the [VarCharData] column)
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [Default_Test_VarCharData] DEFAULT ('None Reported') FOR [VarCharData]
Go

-- the following should now no longer fail (the default 'None Reported' should appear in the [VarCharData] column)
INSERT INTO [tempdb].[dbo].[Test]([InsertedBy])
VALUES('Joe')
Go

-- view table rows Note: (the default 'None Reported' should appear in the [VarCharData] column)
SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]

Hey guys, thanks for the help. I'm afraid you have confirmed my idea that simple is not always what a DBA looks for--AUGH!

I am actually trying to make the change via the MS Access (OK, so I've blown my cover and you know I'm a complete hack) interface which gives me a table designer view of SQL Server. Those nice guys at Microsoft (Gack!) try to make it look like I don't actually have to write SQL, stored proceedures or scripts to do this.

I do also have access to Enterprise Manager.

I'll see if I can make sense out of your suggestions. Wish me luck!