SQL 71501 has an unresolved assembly reference

Question

We use SSDT tools with Visual Studio 2015 and the Target Platform for SQL Server 2008. We are stuck with this function, which throws an error and needs help with what we can do to fix it.

SQL71501: Function: [dbo].[GetFormattedAddress] has an unresolved reference to Assembly [AddressFormatting] CREATE FUNCTION [dbo].[GetFormattedAddress] (@AddressID INT, @CompleteAddress BIT) RETURNS NVARCHAR (4000) AS EXTERNAL NAME [AddressFormatting].[AddressFormatting.UserDefinedFunctions].[GetFormattedAddress] GO 

- UPDATE

In the Assemblies folder, for AddressFormatting.dll, I install BuildAction for the assembly, and then through the links I was able to see the Model Aware property, which I now set to True. After that, now I get the following error:

SQL46010: incorrect syntax next to

The file where the error is displayed contains AddressFormatting.dll

Answer 1

I also had this problem, and it was solved by a riddle by changing the SQL compatibility level in Visual Studio until 2012, recompiling the project, and then changing it to the 2008 compatibility level, and then running a clean project and then recompiling.

Answer 2

This issue is completely SSDT related and has nothing to do with schemas or loading assemblies in the GAC (both of which are mentioned in the comments to the question). For some reason, even though you brought the assembly from SQL Server to your project, SSDT does not see this. You mentioned in a comment on the question that you “imported this assembly as a reference in my project,” but it probably should have happened automatically, as in my testing.

You need to go to the “Links” folder in “Solution Explorer” and change the “Aware Aware” property to “True” for assembly (this step resolved the same error in this SO question: How to resolve VS2013 error SQL71501: Procedure X has an unresolved link to Assembly Y? ). If you add the link manually, the Aware Model is probably set to False by default.

I was able to reproduce this error (and fix) by importing the project (the “Aware Model” parameter was already set to “True”) and setting it to “False” to get the error and return to “True” to remove the error.

---

In addition, if for some reason you are stuck with this error, and the “correct” settings do not seem to work, even after executing the “clean solution”, “Reconstructing the solution”, and even closing and restarting Visual Studio, then you need to remove file $ (ProjectDir) \ project_name.dbmdl . To delete this file, the project cannot be opened in Visual Studio because it will be blocked by Visual Studio. Or you can go to "Project Properties", and on the "Project Settings" tab, change the "Target Platform" to another version, and then return to what it was (similar to what is described in another answer, but there is no need to do " assembly "or" rebuild "before flipping it back to its original version), as this has the side effect of deleting this .dbmdl file.

Please note that the .dbmdl file is not written until the project is closed. Thus, if the file did not exist when you first opened the project or when you switched to the "Target Platform", you will not see the .dbmdl file until you close the solution, unload the project or exit Visual Studio. Doing "Save All" does not write this file to disk!