November 7, 2008 at 12:05 pm
Hello,
As I was working on upgrading a SQL 2000 database to 2008, I came across something very strange.
I was executing a piece of code that exists in our production environment against the SQL 2008 box, and received the following error:
Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
When I examined the code in question it seemed obvious that the error would be thrown. I was puzzled that this had made it to production. That’s when I realized: running the exact same code in SQL 2000 did not throw the error.
I have created some sample code to illustrate the scenario:
-----------------------------------------------------------
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
CREATE TABLE #Temp1
(
C1VARCHAR(10)
, C2VARCHAR(10)
, C3VARCHAR(10)
)
CREATE TABLE #Temp2
(
SomeColumnVARCHAR(10)
)
INSERT INTO #Temp2 (SomeColumn)
VALUES ('Some Value')
-- Following insert should throw an error
INSERT INTO #Temp1
SELECT TOP 1
'C1'
, 'C2'
FROM
#Temp2
ORDER BY
SomeColumn
SELECT *
FROM #Temp1
-----------------------------------------------------------
As you can see I am inserting 2 columns into the table without naming the columns to be inserted into. But if you run this code in a SQL 2000 environment, it does not throw an error. It seems to be related to the ORDER BY clause, but I cannot see the link. I could not find any online documentation related to this.
I’m interested to know if you have seen anything like this. It’ll ruin my weekend if I’m not able to get to the bottom of this by this evening!
Thanks!
ASP
November 7, 2008 at 2:39 pm
Your friday afternoon stumper is way better than mine :ermm:.
I can tell you it's the order by on a field from a table you're not actually selecting from that allows it to happen.
It will fail if you
A. Remove the ORDER BY somecolumn.
B. Change 'C2' or 'C1' to SomeColumn
C. Order by a static value (ORDER BY 1) and not SomeColumn
D. Remove the FROM table and just ORDER BY 1
E. Add a NOT NULL constratint to C3 on #temp1 (Although for different reasons obviously.)
F. Explicity add the field names to your insert. (INSERT INTO #Temp1(C1,C2,C3))
What I can't tell you, and what I'm now very interested in, is why this combination of factors somehow circumvents the checks in the first place.
Also... due to the fact that:
Documentation is hard to find online for it.
It's fixed in 2005+
It takes a very specific set of circumstances to bring about
It's pretty illogical in the first place
I'm inclined to throw the bug card.
November 7, 2008 at 4:00 pm
Yeah sigh. I'm just surprised that I am not able to find a single mention of this 'bug' anywhere. Of course I can't boast about my discovery because then I will have to own up to the poor coding practice of the coumn-less insert statement which led me here.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply