Create procedure error on computed column.

I have the following script that was generated using SMO:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[proc_InsertCaseNote]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Erin D. Rowley

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[proc_InsertCaseNote]

-- Add the parameters for the stored procedure here

@.ReasonCodeSubCategoryID int,

@.OrderGroupID uniqueidentifier,

@.NoteText text,

@.CustomerEmail varchar(75),

@.EmployeeFirstName varchar(255),

@.EmployeeLastName varchar(255)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)

values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY

END

GO

But when I try to run it (in SQL Management Studio) I get the following error:

Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18

The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any ideas on how to debug this problem?

Thank you.

Kevin

Please post the table DDL.|||

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

|||

Arnie Rowland wrote:

Please post the table DDL.

Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.

Kevin

|||

Louis Davidson wrote:

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.

Kevin

|||

Right click the table in SSMS, click "Script table to..."

The error is not really all that strange, it is not letting your procedure do something that won't work.

|||

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

|||

Tom Werz wrote:

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

The table looks like:

/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]

The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.

Reproduced here for reference.

USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY
END

Thank you for your suggestions.

Kevin