February 25, 2010 at 3:14 pm
We are migrating from sqlserver 2000 to sql server 2005 and Now i am stcuk with this strange thing which was supported in SQL 2000 but not in SQL 2005.
Though the fix for this problem is prity simple but we are having thousands of SPs and we cannot check and fix this in each SP individually.
The problem is
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)
INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3
From Table2
I know in the above example I am not providing the value for column4 but this statement runs fine in SQL 2000 and it inserts Null to the column4 but in SQL 2005 it generates the Insert Error: "Column name or number of supplied values does not match table definition"
I tried the above statement in SQL 2005 with compatibility 70 and 80 also but it didn't work.
Is there any Server setting in SQL 2005 which can allow this type of staements?
February 26, 2010 at 11:38 am
I get the same error in SQL 2000 as I expected. Is it possible that you are assuming incorrectly that the procedure ran successfully in SQL Server 2000? I am willing to bet that it did not.
Regards,
Toby
February 26, 2010 at 12:28 pm
If you know that you want null in column 4 just change your insert.
INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3,
null
From Table2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2010 at 4:12 pm
HI Toby White
Thanks for looking into it..............
You are right the above query is not working even in SQL 2000 but I missed an Order by clause in query to mention
If you add an order by clause in query it will start working in SQL 2000.
It is very annoyin... it looks like one of SQL 2000 bugs or may be there is some server settings which allows this kind of behaviour
I hope its Server settings otherwise I will have to go through each SP to locate and fix this for SQL 2005
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)
INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3
From Table2
order by column1
February 28, 2010 at 4:18 pm
Hi SeanLange
You are right simply null can be inserted....
But its not a matter of 1 SP we have thousands of SPs to be migrated from SQl 2000 to SQl 2005.
It will be very daunting and time consuming work to go through each SP to check and fix it for SQL2005.
March 1, 2010 at 7:35 am
The ORDER BY makes no difference. I run the following code and get the same error as will be shown. The only way your insert is going to work is if #Table1 has an identity column, but that isn't different whether on 2000 or 2005. Please try running the following code yourself and post the results:
DECLARE @Version Varchar(4000)
SELECT @Version = @@Version
PRINT @Version
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)
SELECT *
INTO #Table2
FROM #Table1
INSERT #table2
SELECT 1,'two',3,4
INSERT INTO #Table1
SELECT
Column1
,Column2
,Column3
FROM #Table2
ORDER BY column1
DROP TABLE #Table2
DROP TABLE #Table1
---------------------------The Messages follow-----------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
(0 row(s) affected)
Msg 213, Level 16, State 4, Line 20
Insert Error: Column name or number of supplied values does not match table definition.
March 1, 2010 at 1:44 pm
Hi Toby
You are right again but i made a little modification in ur query and it started working in SQL 2000, I have added table alias as prefix for columns. Here I am using table1 and table2 as example but the real query is based on more than 1 table using join so table alias has to be used as column prefix.
DECLARE @Version Varchar(4000)
SELECT @Version = @@Version
PRINT @Version
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)
SELECT *
INTO #Table2
FROM #Table1
INSERT #table2
SELECT 1,'two',3,4
INSERT INTO #Table1
SELECT
T2.Column1
,T2.Column2
,T2.Column3
FROM #Table2 T2
ORDER BY column1
DROP TABLE #Table2
DROP TABLE #Table1
-------------------------------
Microsoft SQL Server 2000 - 8.00.2050 (Intel X86)
Mar 7 2008 21:29:56
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
March 1, 2010 at 4:59 pm
This is some seriously odd behavior indeed. I am looking into it further.
Regards,
Toby
March 1, 2010 at 5:53 pm
I have been unable to make the insert succeed on SQL2k5 with any combination of changes in database settings. I have also failed to find reference to this in SQL2k anywhere online. I am going to have to agree with the other poster that you need to either specify the column list in the insert or the select statement or both. The best thought I have is to use the SQL Upgrade Adviser which should identify all of the offending procedures you will need to fix. Sorry about the bad news :crying:
Toby
March 1, 2010 at 10:04 pm
Anyways, Thanks Toby
It is indeed a bad news but anyhow I have to get it done....... poor me :crying:
:ermm::(
March 1, 2010 at 10:17 pm
if column 1 was was an identity(), or column 4 is a calculated column, your original statement would have worked with no errors...maybe you left something out? identities and calculated columns are ignored when you use an insert without naming columns. so if you forgot to add the identity property to the table on the new server, you'd get the error where no error had gone before....
hate to sound like your girlfriend, but there's something your not telling us. don't use pseudocode, script the actual table and show us that; the actual problem is getting lost due to that, i think.
Lowell
December 24, 2012 at 10:07 pm
I am getting this same error message ...Column name or number of supplied values does not match table definition.
My table definition is as follows ...
create table GwenF_department(
departmentnamevarchar(25) Primary key,
depttype varchar(10),
DIrectorname varchar(35),
Hiredate date,
Salary int,
RaiseFactor int,
SalaryAdjustment as ( salary * RaiseFactor) ,
descriptionvarchar(50));
and i"m inserting 8 similar rows to this one ...
insert into GwenF_department values('Marketing','MIS','Billy Williams','1972/Apr/01','98000','10',Null,'To pack the seats at Wrigley Field');
December 24, 2012 at 10:19 pm
imaceo58 8163 (12/24/2012)
I am getting this same error message ...Column name or number of supplied values does not match table definition.My table definition is as follows ...
create table GwenF_department(
departmentnamevarchar(25) Primary key,
depttype varchar(10),
DIrectorname varchar(35),
Hiredate date,
Salary int,
RaiseFactor int,
SalaryAdjustment as ( salary * RaiseFactor) ,
descriptionvarchar(50));
and i"m inserting 8 similar rows to this one ...
insert into GwenF_department values('Marketing','MIS','Billy Williams','1972/Apr/01','98000','10',Null,'To pack the seats at Wrigley Field');
Try this instead since you have a compute column in the table definition:
insert into GwenF_department(
departmentname, depttype, DIrectorname, Hiredate, Salary, RaiseFactor, description)
values
('Marketing','MIS','Billy Williams','19720401','98000','10','To pack the seats at Wrigley Field');
Note that I left out the column SalaryAdjustment since it is a computed column.
December 25, 2012 at 12:26 am
Lynn, thank you....
Now after inserting rows into GwenF_department , and executing the function .....
drop function dbo.udfSalaryRaise;
CREATE FUNCTION udfSalaryRaise()
RETURNS @Results TABLE (
departmentname varchar(25),
depttypevarchar(10),
SalaryAdjustment numeric
)
AS
BEGIN
Insert @Results (departmentname,depttype,SalaryAdjustment)
select departmentname, depttype,SalaryAdjustment from GwenF_department
If 1 = 0
Return
Else
Insert @Results(departmentname,depttype,SalaryAdjustment)
Values ('Marketing','Sales','SalaryAdjustment')
RETURN
END;
select * from dbo.udfSalaryRaise()
go
.....this is what I'm getting ....
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
December 25, 2012 at 1:04 am
imaceo58 8163 (12/25/2012)
Lynn, thank you....Now after inserting rows into GwenF_department , and executing the function .....
drop function dbo.udfSalaryRaise;
CREATE FUNCTION udfSalaryRaise()
RETURNS @Results TABLE (
departmentname varchar(25),
depttypevarchar(10),
SalaryAdjustment numeric
)
AS
BEGIN
Insert @Results (departmentname,depttype,SalaryAdjustment)
select departmentname, depttype,SalaryAdjustment from GwenF_department
If 1 = 0
Return
Else
Insert @Results(departmentname,depttype,SalaryAdjustment)
Values ('Marketing','Sales','SalaryAdjustment')
RETURN
END;
select * from dbo.udfSalaryRaise()
go
.....this is what I'm getting ....
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Look at the INSERT statement in the ELSE portion of your IF control block, you are trying to insert a string into a column declared as a numeric value. The string 'SalaryAdjustment' cannot be converted to a numeric value.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply