vb7guy
asked on
SQL Error Msg 4988 when Creating Table with persisted Computed column
Hi,
I'm trying to create a table in SQL 2008 that has a Computed column with Presisted property. The Computed formula calls a user defined function in dbo schema. However, table it self resides in a different schema.
When I execute the following (see the code). I get this error telling me that my table has different owner then the underlaying function. I even deleted the function and recreated it using the same user login that is creating the table. How do I fix this. I Can not put the function into the same schema as the table. This function must reside in dbo schema because of replication.
Msg 4988, Level 16, State 1, Line 2
Cannot persist computed column 'JoinedName'. Underlying object 'JoinName' has a different owner than table 'Users'.
I'm trying to create a table in SQL 2008 that has a Computed column with Presisted property. The Computed formula calls a user defined function in dbo schema. However, table it self resides in a different schema.
When I execute the following (see the code). I get this error telling me that my table has different owner then the underlaying function. I even deleted the function and recreated it using the same user login that is creating the table. How do I fix this. I Can not put the function into the same schema as the table. This function must reside in dbo schema because of replication.
Msg 4988, Level 16, State 1, Line 2
Cannot persist computed column 'JoinedName'. Underlying object 'JoinName' has a different owner than table 'Users'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET ARITHABORT ON
GO
CREATE TABLE [PublicFacing].[Users](
[UserId] [int] NOT NULL,
[RegistrantId] [int] NOT NULL,
[IsPublic] [bit] NOT NULL,
[Password] [varchar](50) NULL,
[PasswordMD5] [char](32) NULL,
[PasswordActivation] [datetime] NULL,
[ReplicationGUID] [uniqueidentifier] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NULL,
[Status] [smallint] NULL,
[FirstName] [varchar](80) NULL,
[MiddleName] [varchar](80) NULL,
[LastName] [varchar](80) NULL,
[Suffix] [varchar](80) NULL,
[Prefix] [varchar](15) NULL,
[JoinedName] AS ([dbo].[JoinName]([FirstName],[MiddleName],[LastName],[Suffix])) PERSISTED,
[AddressLine1] [varchar](60) NULL,
[AddressLine2] [varchar](60) NULL,
[USCity] [varchar](60) NULL,
[USState] [smallint] NULL,
[USZip5] [int] NULL,
[USZip4] [smallint] NULL,
[Country] [smallint] NULL,
[LastStatusChange] [datetime] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RegistrantId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [PublicFacing].[Users] ADD CONSTRAINT [DF_Users_ReplicationGUID] DEFAULT (newid()) FOR [ReplicationGUID]
GO
ALTER TABLE [PublicFacing].[Users] ADD CONSTRAINT [MSrepl_tran_version_default_C3AE8422_2E32_4716_8BDE_71A88E4EE9DE_791673868] DEFAULT (newid()) FOR [msrepl_tran_version]
GO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER