SQL Server 15.0.2155.2 issue with synonym on scalar value function

Question

Hello there, we noticed a strange issue with SQL Server 15.0.2155.2.

We have two main databases on the server. Database A has a scalar value function that regularly gets called from database A and B. We use synonyms to reference the function in database B. This has always worked, until now.

A script we use to update synonyms dropped all synonyms and created new ones. The script did not create a synonym for the function, which is a bit odd. We manually added it and made sure it is correct. Although the synonym ist there, the call from database B does not work. SQL Server says the name cannot be found or is ambiguous. One or 2 years ago we used the same procedure to create all synonyms and it worked perfectly fine. Nothing in this procedure has changed. And even if we added the synonym manually, it didn't work.

For now, we deleted the synonym and created the function in database B, but we never had such issues. I already looked for known bugs and did not find any. However, official documentation still says you can use synonyms on scalar value functions.

Is there anything we missed? Could this be a bug in SQL Server?

Answer 1

It sounds like you are encountering an issue with using synonyms for scalar value functions in SQL Server. According to the documentation, while you can create synonyms for various database objects, including scalar value functions, there are some important considerations to keep in mind:

1. Schema Binding: Synonyms are not schema-bound, meaning they do not enforce the existence of the underlying object until runtime. If the function was modified or dropped after the synonym was created, it could lead to the ambiguity or "not found" errors you are experiencing.
2. Ambiguity: The error message indicating that the name cannot be found or is ambiguous might suggest that there are multiple objects with similar names in the context of the database. Ensure that there are no naming conflicts or that the correct schema is being referenced.
3. Permissions: Ensure that the user context under which the call is made has the necessary permissions to execute the function through the synonym.
4. Linked Servers: If the synonym references a function on a linked server, it cannot be used directly, which may also lead to issues.

Since you mentioned that the script used to drop and recreate synonyms did not include the scalar function synonym, it’s possible that the function was inadvertently affected by changes in the database or that the synonym was not properly pointing to the correct object.

If the issue persists, it may be worth checking for any updates or patches for SQL Server that might address this behavior, as well as reviewing any changes made to the function or its dependencies.

---

References:

• Synonyms (Database Engine)

Answer 2

Since we don't see the code for your procedure and we don't have access to your database, we can't pinpoint exactly what is wrong.

But it sounds like the function is not where you think it is. Maybe someone has moved it to a different schema. Maybe the name has been changed. Keep in mind that MyFunc and myfunc are different names with a case-sensitive collation.

If it would be a bug in SQL Server, it would never have worked. No, something has changed in your environment.

However, you are on SQL 2019 RTM with no CU, and I would recommend that you install CU32, the last cumulative update SQL 2019 to get access to many bug fixes and enhancements. More precisely, you want CU32 + the most recent GDR: https://www.microsoft.com/en-us/download/details.aspx?id=108451, so that you can apply it on your recent build.