Column name r values doesnt match table definitio

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

General SQL Server Forums

New to SQL Server Programming

Column name r values doesnt match table definitio

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-17 : 09:45:45

I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.

Error Message:

Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22
Column name or number of supplied values does not match table definition.USE [gg]
GO

/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),
@eventType varchar(10), @alert_Type varchar(10), @outageComponent varchar(10),
@remedyTicket varchar(15), @Severity_Cd varchar(2), @Status_Cd varchar(2),
@Planned_Outage_Ind varchar(1), @Outage_Start_Time DATETIME, @Impacted_800_Num varchar(200),
@Bus_Hrs_Ind varchar(1),
@Expected_End_Time DATETIME, @Actual_End_Time DATETIME, @Addl_Steps varchar(2000), @BSP_Name varchar(30),
@Caller varchar(30), @Phone varchar(12), @Internal_Comments varchar(2000), @Internal_Summary varchar(255),
@Issue_Desc varchar(2000), @TimetoServicetheCustomer varchar(20), @QualityofService varchar(20), @AvailabilityofTools varchar(200),
@SystemLatencies varchar(200), @Addl_Notes varchar(2000), @id int output

AS
BEGIN
Insert into Alert_Event values(@eventCreatedDate, @eventOrigin, @eventType, @alert_Type, @outageComponent, @remedyTicket, @Severity_Cd, @Status_Cd, @Planned_Outage_Ind, @Outage_Start_Time, @Impacted_800_Num, @Bus_Hrs_Ind, @Expected_End_Time, @Actual_End_Time, @Addl_Steps, @BSP_Name, @Caller, @Phone, @Internal_Comments, @Internal_Summary, @Issue_Desc, @TimetoServicetheCustomer, @QualityofService, @AvailabilityofTools, @SystemLatencies, @Addl_Notes,NULL,NULL,NULL,NULL)
set @id=SCOPE_IDENTITY()
Insert into Alert_IncMgmt values (@id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'CHQ00000')
Insert into Alert_ProbMgmt values (@id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

RETURN @id

END

GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-17 : 09:51:24

supply a column list and exclude the identity column. Then make sure your values match your columns one-for-one.

insert <table> (<columnList>)
values (<valueList>)

Be One with the Optimizer
TG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 09:51:30

You just have to carefully count the number of columns, look at each column and compare that to the corresponding value in the values clause to make sure that you are supplying values for all the columns with two exceptions - don't provide a value if the column is an identity column, and don't provide a value if the column is a computed column.

A better and more robust approach is to specify the destination column names as well in the insert statement. For example:

INSERT INTO Alert_Event
(
	eventCreatedDate,
	eventOrigin,
	eventType,
	alert_Type
	-- your other columns
)
VALUES
(
	@eventCreatedDate,
	@eventOrigin,
	@eventType,
	@alert_Type
	--  your other values here
);

Formatting like I did would also help you in debugging.

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-17 : 10:25:19

Got it! Thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources