use of default values for tables in SQL 2008

• peterk1961

  SSC-Addicted

  Points: 493

  More actions

  May 6, 2009 at 2:25 pm

  #103730

  Hi,

  We are in the process of upgrading from SQL 2000 to SQL 2005.

  One of the process is loading a flat file into a table. In SQL 2005, we could define a default value 'getdate()' for a field 'dateentered' and dateentered field didn't have to be created in the pipeline. Now we want to upgrade to SQL 2008. The same package that was created in 2005, we are trying to test it in 2008. The package fails with an error message that 'dateentered' field does not allow nulls even though a default value of 'getdate()' is defined for the field.

  Is there some thing different that needs to be done for 2008 or in 2008 I have to define the field as nullable or pass along a value for 'dateentered' in the pipeline, that is the only option.

  Thanks for your time.

  Peter

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 6, 2009 at 4:17 pm

  #990050

  Hi

  I just tried on SQL Server 2008 and this seems to work:

  CREATE TABLE TestDefault (Id INT)

  GO

  INSERT INTO TestDefault SELECT 1

  GO

  ALTER TABLE TestDefault ADD Dt DATETIME NOT NULL DEFAULT GETDATE()

  GO

  SELECT * FROM TestDefault

  Greets

  Flo