SSSDT Procedure has undresolved reference to object sp_executesql

Gary Furash 1 Reputation point
2020-11-26T18:21:38.04+00:00

Configuration: VS Enteprise 2019, 16.8.2, Latest SQL Server Data Tools (SSDT), Windows 10

Issue: including any of the following statements in a stored procedure generates the following warning

SQL71502 Procedure ... has an unresolved reference to object [schema].sp_executesql

Examples:
EXEC @ExecRet = master.sys.sp_executesql @tsql , N'@val VARCHAR(MAX) OUTPUT', @val = @TmpStr OUTPUT;
OR
EXEC @ExecRet = sp_executesql @tsql , N'@val VARCHAR(MAX) OUTPUT', @val = @TmpStr OUTPUT;

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,713 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2020-11-27T07:16:15.663+00:00

    The SP sp_executesql is a system SP in system database "master"; so you have to add a reference to the system DB in your SSDT project, see
    Selecting the Database to Reference

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-11-27T07:24:40.54+00:00

    Hi @Gary Furash ,

    Please try to add a database reference to master as below:

    1. Under the project, right-click References.
    2. Select Add database reference....
    3. Select System database.
    4. Ensure master is selected.
    5. Press OK.

    Reference:Warning SQL71502 - Procedure <name> has an unresolved reference to object <name>

    Best regards
    Melissa

    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.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments