Stored procedure works on SQL Server 2000 but not on SQL Server 2008R2

Question

I transfer database from SQL Server 2000 to SQL Server 2008 R2 SP2 and all working fine except one stored proc. The error appears in the following piece of code:

declare @Dates table (
    ForDate    smalldatetime,
    DayStart   int,
    PRIMARY KEY CLUSTERED (ForDate)
)

INSERT INTO @Dates
   SELECT M.ForDate
   FROM @Master M
   GROUP BY M.ForDate

The error is:

Msg 213, Level 16, State 1, Procedure REP_Media, Line 115
Column name or number of supplied values does not match table definition.

I realized that the error occurs because there is no value supplied for the column DayStart in the @Dates table variable. I can add NULL into inner select and all works fine.

But the same procedure works on SQL Server 2000, and not on SQL Server 2008 R2 SP2, why? What should be done for normal operation without changing procedure (some server options or other solution)?

I try to use SET ANSI_NULL_DFLT_ON ON in procedure definition and in database options, but without any success.

Answer 1

There isn't anything that you can do apart from update the SQL.

SQL Server in recent versions simply won't allow you to do an insert...select with fewer items in the SELECT list than there are in the column list. You don't have an explicit column list so it is implied to be all columns other than identity, timestamp, computed.

I.e. (ForDate, DayStart)

Even if DayStart is nullable you would still need to change the syntax to use an explicit column list in order to get that applied from your single column SELECT source

INSERT INTO @Dates (ForDate)
SELECT M.ForDate
FROM @Master M
GROUP BY M.ForDate

This does seem to be a change in behaviour from SQL Server 2000 (also found here) but I don't see that 2000 behaviour documented and using SQL Server 2000 compatibility mode doesn't help.