SQL 71501 has an unresolved assembly reference

Question

We are using SSDT tools with Visual Studio 2015 and Target Platform set to SQL Server 2008. We are stuck with this function which is throwing an error and need assistance on 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

Under the Assemblies folder, for AddressFormatting.dll, i set the BuildAction to Build and then under the References i managed to see the Model Aware property which i have now set to True. After this now i get the following error:

SQL46010: Incorrect syntax near

The file where it's pointing the error is AddressFormatting.dll

Answer 1

This problem is entirely within SSDT and has nothing to do with Schemas or loading Assemblies into the GAC (both of which are mentioned in comments on the question). For some reason, even though you brought the Assembly from SQL Server down into your project, SSDT is not seeing it. You mention in a comment on the question that you "imported that assembly as a reference in my project", but that probably should have happened automatically as it does in my testing.

You need to go to the "References" folder in "Solution Explorer" and change the "Model Aware" property to "True" for the Assembly (this step resolved the same error in this S.O. question: How to resolve VS2013 Error SQL71501: Procedure X has an unresolved reference to Assembly Y? ). If you add the reference manually, "Model Aware" probably defaults to "False".

I was able reproduce this error (and the fix) by importing a project (the "Model Aware" setting was already set to "True") and setting it to "False" to get the error and back to "True" to remove the error.

---

Also, if for some reason you do get stuck with that error and the "proper" settings don't seem to work, even after doing a "Clean Solution", "Rebuild Solution", and even closing down and restarting Visual Studio, then you need to delete the $(ProjectDir)\project_name.dbmdl file. In order to delete that file, the project cannot be open in Visual Studio as it will be locked by Visual Studio. Or, you could go to Project Properties, and on the "Project Settings" tab, change the "Target platform" to another version and then back to what it was (similar to what is described in the other answer, but there is no need to do a "build" or "rebuild" before flipping it back to the original version), as that has a side-effect of deleting that .dbmdl file.

Please note that the .dbmdl file is not written until the project is closed. So, if the file did not exist when you first opened the project, or if you switch the "Target platform", then you won't see the .dbmdl file until you either close the solution, unload the project, or exit Visual Studio. Doing a "Save All" does not write this file to disk!

Answer 2

I also had this issue and it was resolved mysteriously by changing the SQL compatiblity level in Visual Studio to 2012, recompiling the project and then changing it back to compatibility level of 2008 and then do a clean project and then recompile.