February 28, 2013 at 8:19 pm
I'm trying to write a check constraint that checks that the current column value exists in a library table in a library database.
USE Lib
GO
CREATE FUNCTION [dbo].[fnIsValidResource] (@gResource UNIQUEIDENTIFIER)
RETURNS BIT
AS
BEGIN
RETURN 1
END
GO
USE MaintDB
GO
CREATE TABLE [dbo].[Als]
(
[gPK] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID(),
[grObjType] UNIQUEIDENTIFIER NOT NULL CHECK (Lib.dbo.IsValidResource(grObjType)=1)
)
GO
When I try to execute the CREATE TABLE command, I get the error:
Msg 4121, Level 16, State 1, Line 4
Cannot find either column "Lib" or the user-defined function or aggregate "Lib.dbo.IsValidResource", or the name is ambiguous.
Can someone please explain why this doesn't work and how to make it work?
Thanks,
Greg
February 28, 2013 at 8:30 pm
Create a synonym in the database you are trying to run the UDF referencing the UDF in the other database. Use the two-part name of the udf synonym when calling it.
February 28, 2013 at 9:08 pm
Thanks for the suggestion. I tried it and noticed that the name of the function in the Lib database had been changed, so I fixed the name in the check constraint I got the following error:
Msg 4120, Level 16, State 1, Line 5
A user-defined function name cannot be prefixed with a database name in this context.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
It looks like I can't call a function in another database as part of a check constraint, so I'll just have to create the function in the MaintDB database.
Thanks,
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply