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

Homothallic

Any chance the message is correct? Did you deploy the assembly first? – Giff

SSDT creates the appropriate scripts to deploy the assembly and the function. How did you deploy your code? – Giff

what do you mean? we just imported the project from the existing database and this function was as is. It has created an assembly under the assemblies folder. I have imported that assembly as a reference in my project but the error is still the same. – Homothallic

i haven't deployed the assembly anywhere. where do i need to deploy the assembly? – Homothallic

The assembly must exist as a SQL object in your project, just like your functions and tables. – Giff

i created this sql file in my project: CREATE SCHEMA [AddressFormatting] – Homothallic

Try to add dll to the GAC – Enclave

Answer 1

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.

Dianemarie

Hey there. Great info, though highly confusing. I won't argue that it worked since you saw that it did, the O.P. accepted it, yet I cannot reproduce this, and the actions taken, at least on a superficial level, shouldn't have done anything outside of re-write the deployment SQL script, which shouldn't have led to that error. The "target platform" only changes the SQL generated for the deployment script, not anything to do with the DLL itself. If possible, I would like to get to the actual cause, if you don't mind :-). Can you reproduce this, or was it a one-time thing? – Drawshave

@srutzky: i will try to reproduce it and get back to you if i could. just for the info, i did perform all the steps you mentioned as well and it may be a combination of things that fixed this issue. I will try to replicate it and then give you the exact steps if i can. thanks for all your help though. – Homothallic

@user1490835 and user3468420: I figured it out. Changing the "Target Platform" to 2012 and then to 2008 works (and rebuilding while it is set to 2012 is not necessary), but only because of what changing the target platform does: it deletes the $ProjectDir\project_name.dbmdl file! That can be done manually when the project is not currently loaded in Visual Studio. – Drawshave

Answer 2

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!

Drawshave

The item is already added as an existing item under a folder called assemblies which was automatically created. The second option of Model Aware, i can't seem to find that Model Aware property and did that particular post before asking the question. In the properties of the Assembly, all i see is "Build Action", "Copy to Output directory", "File Name" and "Full Path". I am using VS 2015 with SQL 2008 compatibility and not sure if that has anything to do with why this property is now displaying? – Homothallic

@user1490835 Sorry I wasn't able to get to this over the weekend. I see your update and that the "Model Aware" option is in the "References" area and not in the "Assemblies" folder. I just did an import and am seeing the same thing. I also see that someone else answered and it appears to have worked for you. That is great, but I have some misgivings about the actual cause since the "Target Platform" setting only controls the generated deployment script, nothing else. I have an idea of what the actual problem was, but cannot reproduce it myself. Is this something that you can reproduce? – Drawshave

i have created a new solutuin with sql project from scratch and it does not have any issues at all. all built fine but model aware property was automatically set to true which is again strange. i am not sure what should i try to reproduce that error....I have given you the bounty because it may have been your steps that have resolved the issue? might have just required to restart the visual studio or something like that? – Homothallic

@user1490835 Thanks for the bounty, hopefully I deserved it ;-). I might be able to reproduce it. I changed "Build action" of my imported DLL to "Build", did "build solution", and got the same SQL46010: Incorrect syntax near {box} error that you updated the question with. I changed "Build action" back to "none" and did both "clean" and "rebuild" but cannot get rid of that error (it creates an invalid assembly in the obj\{configuration_name} folder. (continued...) – Drawshave

@user1490835 So I then changed "Model Aware" on the Assembly in "References" to "false" and got the same SQL71501: Function: [X].[Y] has an unresolved reference to Assembly [Z] error that you initially reported. I then changed "Model Aware" back to "True" and the "SQL71501" error goes away, leaving me with the "SQL46010" error. I will wipe the objects and re-import to figure out the "why". I think the .sqlproj file is getting messed up and not able to get fixed via changes made in the UI. I don't have any .NET files in the project so I don't know what it is compiling the DLL from ;-). Yet... – Drawshave

@user1490835 Ok, I figured it out (both errors). They are actually two separate things. For your initial error, you can reproduce it easily by changing "Model Aware" to "False" on the Assembly in the "References" folder (not in the Assemblies folder!). You will get the SQL71501 error when building. Change it back to "True" and error goes away :). Changing the Assembly's "Build Action" in the Assemblies folder to "Build" can't be fixed simply by changing it back to "none". I will update my answer with all of this info. – Drawshave