error 213, ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

error 213, ?

Author

Topic

kmistic
Starting Member

40 Posts

Posted - 2004-06-22 : 15:24:47

I am trying to make a stored procedure but keep getting the error:
Server: Msg 213, Level 16, State 5, Procedure Serwip_Append, Line 2
Insert Error: Column name or number of supplied values does not match table definition

This is syntax:
CREATE PROCEDURE Serwip_Append AS
insert into Serwip select * from Serwip_import
GO

serwip_import is an exact copy of serwip. Both tables have the same
amount of columns. I am out of things to check, any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 15:28:11

First, you should not use * as it is a performance hit:

INSERT INTO Serwip (Column1, Column2)
SELECT Column1, Column2
FROM Serwip_import

Second, you need to generate the CREATE TABLE statements to see if anything is different. Highlight both tables in Enterprise Manager, right click, select all tasks, then generate SQL script. Just keep the defaults and select preview. Post the script here.

Tara

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-22 : 15:31:51

Does Serwip have an Identity column? If so you will get this error unless IDENTITY INSERT is on.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

kmistic
Starting Member

40 Posts

Posted - 2004-06-22 : 15:32:04

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SERWIP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SERWIP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SERWIP_Import]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SERWIP_Import]
GO

CREATE TABLE [dbo].[SERWIP] (
[box] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eraStore] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ro_num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_DATE] [datetime] NULL ,
[CUST_NO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADV_NO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CATEG_DV] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPOST_DATE] [datetime] NULL ,
[CUS_RO_TOT] [float] NULL ,
[CUST_GOG_COST] [float] NULL ,
[CUST_GOG_SLS] [float] NULL ,
[CUST_LBR_COST] [float] NULL ,
[CUST_LBR_SLS] [float] NULL ,
[CUST_PART_COST] [float] NULL ,
[CUST_PART_SLS] [float] NULL ,
[CUST_SUBL_COST] [float] NULL ,
[CUST_SUBL_SLS] [float] NULL ,
[CUST_TOT_COST] [float] NULL ,
[CUST_TOT_MISC] [float] NULL ,
[CUST_TOT_SLS] [float] NULL ,
[CUST_TOT_TIME] [float] NULL ,
[DEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISP_NO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[G_COST_DV] [float] NULL ,
[G_PRICE_DV] [float] NULL ,
[GOG_COST] [float] NULL ,
[INTL_GOG_COST] [float] NULL ,
[INTL_GOG_SLS] [float] NULL ,
[INTL_LBR_COST] [float] NULL ,
[INTL_LBR_SLS] [float] NULL ,
[INTL_PART_COST] [float] NULL ,
[INTL_PART_SLS] [float] NULL ,
[INTL_SUBL_COST] [float] NULL ,
[INTL_SUBL_SLS] [float] NULL ,
[INTL_TOT_COST] [float] NULL ,
[INTL_TOT_SLS] [float] NULL ,
[INTL_TOT_TIME] [float] NULL ,
[INTR_DAYS] [float] NULL ,
[INV_DATE] [datetime] NULL ,
[MIL] [float] NULL ,
[OP_COUNT] [int] NULL ,
[OP_DESC] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_COMMENT] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_TOT] [float] NULL ,
[RO_TOT_C_CAT] [float] NULL ,
[RO_TOT_I_CAT] [float] NULL ,
[RO_TOT_MIS_DV] [float] NULL ,
[SER_NO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TECH_HRS_C_DV] [float] NULL ,
[TECH_HRS_C_OTL] [float] NULL ,
[TECH_HRS_I_DV] [float] NULL ,
[TECH_HRS_SUM_DV] [float] NULL ,
[TECH_HRS_W_DV] [float] NULL ,
[TOT_BILL_HRS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOT_COST_GOG_DV] [float] NULL ,
[TOT_COST_SUB_DV] [float] NULL ,
[TOT_LBR_COST] [float] NULL ,
[TOT_LBR_COST_DV] [float] NULL ,
[TOT_LBR_SLS] [float] NULL ,
[TOT_MISC] [float] NULL ,
[TOT_PART_COST] [float] NULL ,
[TOT_PART_SLS] [float] NULL ,
[TOT_RO_CST] [float] NULL ,
[TOT_RO_SLS] [float] NULL ,
[TOT_SUBL_COST] [float] NULL ,
[TOT_SUBL_SLS] [float] NULL ,
[TOT_TAX_AMT] [float] NULL ,
[VEH_TEAM_CODE_DV] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WARR_DAYS] [float] NULL ,
[WARR_EFF_RATE] [float] NULL ,
[WARR_GOG_SLS] [float] NULL ,
[WARR_GOG_COST] [float] NULL ,
[WARR_LBR_COST] [float] NULL ,
[WARR_LBR_SLS] [float] NULL ,
[WARR_PART_COST] [float] NULL ,
[WARR_PART_SLS] [float] NULL ,
[WARR_SUBL_COST] [float] NULL ,
[WARR_SUBL_SLS] [float] NULL ,
[AdvNumName] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mileage Band] AS ([dbo].[mileageband3]([mil])) ,
[OneLineRO] AS ([dbo].[onelinero]([tot_ro_sls])) ,
[CustRoCount] AS ([DBO].[Cust RO Count]([CUST_LBR_SLS], [CUST_PART_SLS])) ,
[SurveyResponse] [int] NULL ,
[Cust Warr Ro Count] AS ([dbo].[cwcount]([tech_hrs_W_dv], [tech_hrs_c_dv])) ,
[WorkingDays] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SERWIP_IMPORT] (
[box] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eraStore] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ro_num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_DATE] [datetime] NULL ,
[CUST_NO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADV_NO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CATEG_DV] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPOST_DATE] [datetime] NULL ,
[CUS_RO_TOT] [float] NULL ,
[CUST_GOG_COST] [float] NULL ,
[CUST_GOG_SLS] [float] NULL ,
[CUST_LBR_COST] [float] NULL ,
[CUST_LBR_SLS] [float] NULL ,
[CUST_PART_COST] [float] NULL ,
[CUST_PART_SLS] [float] NULL ,
[CUST_SUBL_COST] [float] NULL ,
[CUST_SUBL_SLS] [float] NULL ,
[CUST_TOT_COST] [float] NULL ,
[CUST_TOT_MISC] [float] NULL ,
[CUST_TOT_SLS] [float] NULL ,
[CUST_TOT_TIME] [float] NULL ,
[DEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISP_NO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[G_COST_DV] [float] NULL ,
[G_PRICE_DV] [float] NULL ,
[GOG_COST] [float] NULL ,
[INTL_GOG_COST] [float] NULL ,
[INTL_GOG_SLS] [float] NULL ,
[INTL_LBR_COST] [float] NULL ,
[INTL_LBR_SLS] [float] NULL ,
[INTL_PART_COST] [float] NULL ,
[INTL_PART_SLS] [float] NULL ,
[INTL_SUBL_COST] [float] NULL ,
[INTL_SUBL_SLS] [float] NULL ,
[INTL_TOT_COST] [float] NULL ,
[INTL_TOT_SLS] [float] NULL ,
[INTL_TOT_TIME] [float] NULL ,
[INTR_DAYS] [float] NULL ,
[INV_DATE] [datetime] NULL ,
[MIL] [float] NULL ,
[OP_COUNT] [int] NULL ,
[OP_DESC] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_COMMENT] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RO_TOT] [float] NULL ,
[RO_TOT_C_CAT] [float] NULL ,
[RO_TOT_I_CAT] [float] NULL ,
[RO_TOT_MIS_DV] [float] NULL ,
[SER_NO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TECH_HRS_C_DV] [float] NULL ,
[TECH_HRS_C_OTL] [float] NULL ,
[TECH_HRS_I_DV] [float] NULL ,
[TECH_HRS_SUM_DV] [float] NULL ,
[TECH_HRS_W_DV] [float] NULL ,
[TOT_BILL_HRS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOT_COST_GOG_DV] [float] NULL ,
[TOT_COST_SUB_DV] [float] NULL ,
[TOT_LBR_COST] [float] NULL ,
[TOT_LBR_COST_DV] [float] NULL ,
[TOT_LBR_SLS] [float] NULL ,
[TOT_MISC] [float] NULL ,
[TOT_PART_COST] [float] NULL ,
[TOT_PART_SLS] [float] NULL ,
[TOT_RO_CST] [float] NULL ,
[TOT_RO_SLS] [float] NULL ,
[TOT_SUBL_COST] [float] NULL ,
[TOT_SUBL_SLS] [float] NULL ,
[TOT_TAX_AMT] [float] NULL ,
[VEH_TEAM_CODE_DV] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WARR_DAYS] [float] NULL ,
[WARR_EFF_RATE] [float] NULL ,
[WARR_GOG_SLS] [float] NULL ,
[WARR_GOG_COST] [float] NULL ,
[WARR_LBR_COST] [float] NULL ,
[WARR_LBR_SLS] [float] NULL ,
[WARR_PART_COST] [float] NULL ,
[WARR_PART_SLS] [float] NULL ,
[WARR_SUBL_COST] [float] NULL ,
[WARR_SUBL_SLS] [float] NULL ,
[AdvNumName] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mileage Band] AS ([dbo].[mileageband3]([mil])) ,
[OneLineRO] AS ([dbo].[onelinero]([tot_ro_sls])) ,
[CustRoCount] AS ([DBO].[Cust RO Count]([CUST_LBR_SLS], [CUST_PART_SLS])) ,
[SurveyResponse] [int] NULL ,
[Cust Warr Ro Count] AS ([dbo].[cwcount]([tech_hrs_W_dv], [tech_hrs_c_dv])) ,
[WorkingDays] [int] NULL
) ON [PRIMARY]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 15:52:02

I ran a compare between the two in SQL Compare and they are equal, so the statement should work. I was able to successfully create the stored procedure on my SQL Server. What version and service pack do you have? Does the error happen when you create it or when you execute it?

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-06-22 : 15:57:35

when i try to create it.

How did you create it? What Syntax?

BTW, I tried this way:

INSERT INTO Serwip (Column1, Column2)
SELECT Column1, Column2
FROM Serwip_import

And i recieve these error messages (Query Analyzer).

Server: Msg 271, Level 16, State 1, Procedure Serwip_Append, Line 2
Column 'Mileage Band' cannot be modified because it is a computed column.
Server: Msg 271, Level 16, State 1, Procedure Serwip_Append, Line 2
Column 'OneLineRO' cannot be modified because it is a computed column.
Server: Msg 271, Level 16, State 1, Procedure Serwip_Append, Line 2
Column 'CustRoCount' cannot be modified because it is a computed column.
Server: Msg 271, Level 16, State 1, Procedure Serwip_Append, Line 2
Column 'Cust Warr Ro Count' cannot be modified because it is a computed column.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 15:58:35

I used this:

CREATE PROCEDURE Serwip_Append AS
insert into Serwip select * from Serwip_import
GO

I had to change the computed columns though when I created the tables. I had those columns just be INT. So I'm thinking the problem is with the computed columns then.

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-06-22 : 16:05:27

I think so too.

Thank you so much for your help. Any ideas what i can do?

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-22 : 16:07:59

quote:
I had to change the computed columns though when I created the tables. I had those columns just be INT. So I'm thinking the problem is with the computed columns then.

That is it. You can't insert into a computed column, because it is computed.