Function name is ambigious in SQL server

Question

Based on this question, I've created a function and added it to my database. Now I want to use that function as a calculated column:

-- Based on https://stackoverflow.com/a/1012802/10406502 (work by Even Mien)
CREATE FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END
GO

-- Table
CREATE TABLE [dbo].[Trailer]
(
    [ID] INT NOT NULL PRIMARY KEY IDENTITY,
    [ID_Hauler] INT NULL,
    [RegistrationNumber] NCHAR(9) NOT NULL,
    [RegistrationNumberSimplified] AS [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),
    [MaxLoad] FLOAT NULL,

    CONSTRAINT [FK_Hauler_Trailer] FOREIGN KEY ([ID_Hauler]) REFERENCES [Hauler]([ID]),
    CONSTRAINT [UC_RegistrationNumber] UNIQUE ([RegistrationNumberSimplified])
)

However, the line where I reference the function throws an error:

"Berechnete Spalte: [dbo].[Trailer].[RegistrationNumberSimplified]" enthält einen nicht aufgelösten Verweis auf ein Objekt. Entweder ist das Objekt nicht vorhanden, oder der Verweis ist mehrdeutig, da er auf die folgenden Objekte verweisen könnte: [dbo].[StripCharacters] oder [dbo].[Trailer].[dbo]::[StripCharacters].

"Either the object doesn't exist, or the reference is ambigious, because it could mean either [dbo].[StripCharacter] or [dbo].[Trailer].[dbo]::[StripCharacter]."

I've also tried to let the server guess the namespace of the function. In that case, the database throws an error:

(57,1): SQL72014: .Net SqlClient Data Provider: Meldung 195, Ebene 15, Status 10, Zeile 11 'StripCharacters' is not a recognized built-in function name.

What's the problem here?

I also found this question, but the answer doesn't help me, because I don't use database references.

StripCharacters exists in two different schamas the first under dbo and the second under [dbo].[Trailer].[dbo] — Amira Bedhiafi, Aug 29 '19 at 14:00
@AmiraBedhiafi But why? The error occurs in a Visual Studio database project and the only place, where I define that function is in an SQL file with the exact same code as in Amy's post in the linked question. — André Reichelt, Aug 29 '19 at 14:03
Try to drop the [dbo].StripCharacters and run your query to know more about the issue — Amira Bedhiafi, Aug 29 '19 at 14:08
Amy's function is called `fn_StripCharacters`, not `StripCharacters` — Thom A, Aug 29 '19 at 14:08
Even so, if you fix that, you'll get this [error](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4d7cbaec3909090602f66212944a4dfd), because it's an `nvarchar(MAX)`. Change that and you get this [error](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3e0ca8c151063e9298aff35e4b424aa4) (which I'd already warned you about in your other question). — Thom A, Aug 29 '19 at 14:10
@AmiraBedhiafi We are not even on the database yet. The error comes from Visual Studio itself. Hence, I can't publish the changes to the database and therefore can't drop anything, because it doesn't even exist yet. — André Reichelt, Aug 29 '19 at 14:15
@Larnu I've changed the name, because we generally don't use prefixes. Other than that, the function's code is identical. — André Reichelt, Aug 29 '19 at 14:16
Are you *sure*? If you have got the name wrong you get the error you describe: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=437b4a8b6117ff626baeeea81473447a) — Thom A, Aug 29 '19 at 14:17
@Larnu Yes. Even, if I change the function name to fn_, I get the following error: SQL71501: "Berechnete Spalte: [dbo].[Trailer].[RegistrationNumberSimplified]" enthält einen nicht aufgelösten Verweis auf ein Objekt. Entweder ist das Objekt nicht vorhanden, oder der Verweis ist mehrdeutig, da er auf die folgenden Objekte verweisen könnte: [dbo].[fn_StripCharacters] oder [dbo].[Trailer].[dbo]::[fn_StripCharacters]. — André Reichelt, Aug 29 '19 at 14:24
The problem you're trying to fix it fruitless anyway though, @AndréReichelt, for the reasons I stated above. Even if you do figure out why this is happened (which I can't replicate other than ensuring I have an invalid object name) you'll have a problem when you try to create the `CONSTRAINT` as the value is not deterministic. You can't fix that with the function you have — Thom A, Aug 29 '19 at 14:30
@Larnu I see your point. But the same error still appears, when I change my code to `[RegistrationNumberSimplified] NCHAR(9) NOT NULL DEFAULT [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),`. — André Reichelt, Aug 29 '19 at 14:34
@Larnu - this is easy to fix. https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1aeaf49300b49a9e97247f526853128d — Martin Smith, Aug 30 '19 at 07:32
Interesting that a `WHILE` can be deterministic, for using a tally is not, @MartinSmith :/ — Thom A, Aug 30 '19 at 07:43
@MartinSmith @Larnu I realized that already. The function `WITH SCHEMABINDING` is considered to be deterministic. `SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[StripCharacters]'), 'IsDeterministic')` returns 1. — André Reichelt, Aug 30 '19 at 08:11
@Larnu Would you like to try, if your approach from yesterday is considered to be deterministic as well? — André Reichelt, Aug 30 '19 at 08:11
I did try @AndréReichelt . It's likely because I was using an inline table-value function as part of the process to strip the characters out. I didn't delve too far in in honesty. — Thom A, Aug 30 '19 at 08:22
@Larnu That could be the reason. At first glance, I thought that reinitializing the table for every call was a bottleneck. — André Reichelt, Aug 30 '19 at 08:40

score 0 · Answer 1 · answered Aug 29 '19 at 17:40

The problem you are having is that the function is returning a value too large to be used as a key column. The max would be NVARCHAR(850) or VARCHAR(1700). This would work:

-- Based on https://stackoverflow.com/a/1012802/10406502 (work by Even Mien)
ALTER FUNCTION [dbo].[StripCharacters]
(
    @String NVARCHAR(850), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(850) WITH SCHEMABINDING 
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String
END
GO

-- Table
CREATE TABLE [dbo].[Trailer]
(
    [ID] INT NOT NULL PRIMARY KEY IDENTITY,
    [ID_Hauler] INT NULL,
    [RegistrationNumber] NCHAR(9) NOT NULL,
    [RegistrationNumberSimplified] AS [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),
    [MaxLoad] FLOAT NULL

    CONSTRAINT [FK_Hauler_Trailer] FOREIGN KEY ([ID_Hauler]) REFERENCES [Hauler]([ID]),
    CONSTRAINT [UC_RegistrationNumber] UNIQUE ([RegistrationNumberSimplified])
);

Unfortunately, that doesn't help. Even, if I change the return type to CHAR(10), the error doesn't go away. — André Reichelt, Aug 30 '19 at 06:48

score 0 · Accepted Answer · answered Aug 30 '19 at 07:07

What a strange problem! After copying the source code of the function into a newly created scalar function file in my database project, and then deleting the original file and renaming the newly created one afterwards, it finally works. Because I didn't change any code at all, I guess, that this was some pretty weird bug of Visual Studio.

Function name is ambigious in SQL server

2 Answers2

Linked