SQL71501: Function has an unresolved reference to Assembly
Question
Thursday, March 29, 2012 1:51 PM
I upgraded three projects from the now older-style DB projects to SSDT. I'm using VS 2010 Premium with the SSDT add-on.
One was a SQLCLR project. My main DB project can no longer resolve references to functions in the CLR project (SQL71501 errors).
Another project was for the master DB. The Permissions file was causing errors, but I moved the generated T-SQL into the post-build file, and that project seems to build OK now.
I've removed and re-added the CLR project as a DB reference in the DB project ("same database").
I've confirmed the CLR project has "Generate DDL" selected.
The CLR project is showing references to the .NET classes, it builds without errors, and generates a DLL as expected.
A website in the same solution that references the same CLR project is also unable to resolve references to functions it contains, even though the Bin folder contains the generated DLL.
When the CLR project was first converted, it was configured to use SQL 2012. I've switched it to SQL 2008 R2, which is what the other projects are using.
The CLR project is configured to sign the generated DLL, but tutning off signing didn't change the results.
Any ideas on what might be wrong would be appreciated.
Check out my book: Ultra-fast ASP.NET: Building Ultra-Fast and Ultra-Scalable Websites using ASP.NET and SQL Server
All replies (6)
Saturday, May 5, 2012 6:11 AM âś…Answered
I resolved the conversion issue as follows:
1. Created a new SSDT database project
2. Imported the existing DB into that project, which recreated all of the script files and changed the way they are organized/grouped
3. Did some cleanup on a few of the script files, including the post-deploy script
4. Made sure master DB stuff was done right (creating logins)
5. Made sure SQLCLR was enabled and configured correctly in the SSDT project's SQLCLR property page
6. Copied the C# source code for the SQL CLR into the top level of the project
7. Added the references needed by the SQL CLR code
8. Compiled
9. Published
In the end, it works great. Just a royal pain to figure out.
Check out my book: Ultra-fast ASP.NET: Building Ultra-Fast and Ultra-Scalable Websites using ASP.NET and SQL Server
Thursday, April 12, 2012 5:56 PM
Hi Rick,
Can you verify that the target .NET framework for your SQLCLR project corresponds with same the .NET framework set for the database project?
For your database project, the target .NET framework is defined in the project properties under the "SQLCLR" tab.
Thanks,
a/c
Friday, May 4, 2012 7:41 PM
I wonder...did this get resolved? I am facing an identical issue!
KevinB
Wednesday, May 9, 2012 1:13 PM | 3 votes
I dont have the luxury of keeping a reverse engineered database project, but I did a reverse-engineer as a throwaway. In doing so, I compared sqlproj content (directly viewing the .sqlproj xml-structured file), and found the following "missing" from my converted project as compared to the imported project. Adding them manually to my converted project solved my issue.
<SqlAssemblyName>Rma.Pass.Database.SqlClrUtility</SqlAssemblyName>
<IsModelAware>True</IsModelAware>
<GenerateSqlClrDdl>False</GenerateSqlClrDdl>
<SqlOwnerName>dbo</SqlOwnerName>
<SqlPermissionSet>SAFE</SqlPermissionSet>
KevinB
Monday, June 10, 2013 9:12 PM
This post pointed me in the right direction. I manually deleted the old reference and added a new reference to my dll, and then changed the property Model Aware to True from the default of False, and the error went away.
Wednesday, October 9, 2013 1:24 PM | 3 votes
This also pointed me in the right direction.
Went to the db project that was referencing the CLR project, right click the reference showing issue, changed "Model Aware" to True and the errors went away.
I can't find any documentation on this reference property "Model Aware" though so unable to explain the underlying behaviour.
.Net Developer