November 25, 2010 at 7:46 am
I have worked with SQL Server for over 10 years.
For the last couple of years, I had chance to upgrade a few systems' databases from 2000 directly to 2008 and 2005 to 2008.
There is one weird issue we found in SQL 2008, which happens occasionally:
Here is what happens:
We have stored procedure with code like
INSERT INTO table1 (....... NotNullCol .....)
SELECT ... t2.NullableCol .... FROM table2 t2, table3 b .....
WHERE .... t2.NullableCol IS NOT NULL ... /*all null */
We are 1000% sure that the return recordset from the SELECT ... statement are valid for table1 if we run the SELECT statement only and go thru each individual return recordset line by line, column by column. Besides, same query runs perfectly on SQL 2000 and 2005 with idetical data, which also proves that.
However, the very same stament will occasionally reporting errors like "CANNOT INSERT NULL value INTO column ... aNotNullCol"
Simlar error also happen on varchar and char columns with error like "Text or binary String will be truncated ....."
It seems the SQL Server some times does some sort of pre-validation, which actually exames more than just the return records the data.
This happens on both SQL 2008 and 2008 with SP1
Did not test on SP2 and SQL 2008 R2 yet.
November 25, 2010 at 8:15 am
Hi
I have seen some issue like this, but have been able to find and fix the issue, most of the time it something wrong with the data.
Is there any chance that you could up load the DDL for the destination table and if possible do a select into with the select portion of your insert statement, select this into a temp table(where 1 = 0) and create the DDL for that table.
It would help to troubleshoot the issue
November 25, 2010 at 11:55 am
I don't think the Data is the issue, as this is only happening with SQL 2008 not on SQL 2005 and 2000. (We tested with identical data which works on SQL 2008, 2005, but will immediately fail once restored to a SQL 2008 instance)
Our workaround is change the code to use a 2-phase approach, first put the results into a temporary table and then insert everything from the temp table directly to the target table. This works fine, which also proves the problem is on the SQL Server 2008 side. As we don't change the logic when insert into the temp table, and don't manipulate the data in the temp table before insert into the target table.
November 25, 2010 at 12:19 pm
Yeek, could you provide an example with CREATE TABLE, INSERTs and a failing SELECT?
I would really love to see this.
Thanks.
November 25, 2010 at 2:42 pm
Sorry, cannot provide you with the sample code, as this is not a consistent and staight forward issue.
I did a bit more research, I suspect it's related to the Eager Spool Bug in SQL 2008 (before CU3 for SP1)
November 25, 2010 at 10:33 pm
Well, then you don't have a problem, do you?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply