CODEWITHSUNDEEP
sql-serversql-server-2008
Melinda
Melinda

Reputation: 1531

SQL Server 2008 DATETIME column data type does not default to current date with getdate()

In SQL 2008, I have a table that I created where I have 2 NOT NULL columns. ID which is an identity column and a DATETIME column which is a datetime datatype and in the default value or binding I've set it to getdate() but I'm getting the error below when executing the following SP. Any suggestions/direction would be appreciated. Thanks.

When I execute the following SP I get the error below:

USE [MachoPOSt]
GO
/****** Object:  StoredProcedure [dbo].[sbssp_InsertTblArchivedMessages]    Script Date: 03/08/2013 

14:16:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sbssp_InsertTblArchivedMessages]
(
      @xmlString varchar(max),
      @fromToMach bit
)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @idoc int, @lastId int
      EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

      INSERT INTO [dbo].[tblArchivedMessages]
      SELECT * 
      FROM OPENXML(@idoc, '/ATM', 2) WITH [dbo].[tblArchivedMessages]

      EXEC sp_xml_removedocument @idoc

      SET @lastId = (SELECT IDENT_CURRENT('tblArchivedMessages'))

      UPDATE [dbo].[tblArchivedMessages] 
        SET FromToMach = @fromToMach 
        WHERE ID = @lastId
END

Here is the ERROR:

Msg 515, Level 16, State 2, Procedure sbssp_InsertTblArchivedMessages, Line 14 Cannot insert the value NULL into column 'DateTime', table 'MachoPOSt.dbo.tblArchivedMessages'; column does not allow nulls. INSERT fails. The statement has been terminated.

Upvotes: 2

Views: 1228

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11813

As Martin already said, you will have to specify all columns. To make the typing a little easier, you can use drag&drop in SSMS:

  1. Connect Object Explorer to your server.
  2. find the database and table
  3. under the table find the column folder
  4. drag the entire folder into an open query window

This will automatically insert the complete comma separated list of all columns. After that you just have to delete the two you don't need.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453707

You need to use an explicit column list that excludes the datetime column.

The default only takes effect if you don't supply a value at all or use the default keyword. You must be inserting NULL from that error message.

So your query would be something like

INSERT INTO [dbo].[tblArchivedMessages]
            (col1,
             col2)
SELECT col1,
       col2
FROM   OPENXML(@idoc, '/ATM', 2) WITH [dbo].[tblArchivedMessages]

Upvotes: 2

Related Questions