sql71501 trigger has an unresolved reference to object

Connie McBride 1 Reputation point
2020-09-04T17:20:36.81+00:00

Comment: It is not related to c#.
CLR debugging in visual studio 2019
I have a sql/clr project I created and have maintained in visual studio 2008. I am attempting to update it to visual studio 2019, community edition but I seem to be missing 'something'. I am running visual studio 2019 as Administrator. my login has sysadmin security.

I went to SQL server Objects, right clicked on the database instance I am working with (sqlserver 17), and checked 'allow SQL/CLR debugging'.
Application debugging is also checked.
In project, I added a database reference to master database, set the database name to my development database, and checked 'suppress errors caused by unresolved references in the referenced project'
In project settings, I set the target platform. permission level is set to unsafe in sqlCLR properties target framework is 4.5 optimize code is not checked
In sqlclr build page. on the debug page, I set the debug database to one that exists(different from my development database) and connection test returns success.
I cleared all the deployment option check boxes. on the 'advanced' tabs, I cleared most of the check boxes on the general tab, all the boxes in the drop tab, left the 'ignore' tab alone.

If I build with the 'generate DDL' flag UNCHECKED on SQLCLR tab, then project builds, but it will not deploy. doing a build | deploy adds my assembly to the database assemblies list, but none of my stored procedures and triggers are added.

If I build with the 'generate DDL' flag CHECKED then the project does not build, it throws 'error SQL71501 : trigger [dbo].[triggername] has an unresolved reverence to object [dbo].[tablename that exists in the database].

so, any ideas about what I am missing?

I do not want the structures (tables / views) messed with in this project (they are part of another project), I do need the stored procedures/functions/triggers/etc to be maintained by this project, so I can debug them

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,578 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Miamiao-MSFT 4,216 Reputation points
    2020-09-07T09:57:19.677+00:00

    Hi @Connie McBride ,

    1.Using domain\Administrator to restart your SQL Engine service;

    2.Your error “SQL71501 : trigger [dbo].[triggername] has an unresolved reverence to object [dbo].[tablename that exists in the database].” Shows the trigger is not in your db, so please check you have use one correct db and
    Then change the debug connect string as :
    Data Source=xx\xxxx;Initial Catalog=dbname;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False

    More information: deploying-clr-database-objects
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    BR,
    Mia

    No comments

  2. Miamiao-MSFT 4,216 Reputation points
    2020-09-08T01:22:32.147+00:00

    Hi @Connie McBride ,

    Is the reply helpful?

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    BR,
    Mia

    No comments