none
<view> has an unresolved reference to object [msdb].[dbo].[sysjobs]. RRS feed

  • Question

  • Hi,

    I've a SSDT project and I've included a msdb and a master system reference. I was working well but when i try to build the code in VSTS an error is produced. The dacpac is no availaible on C:\.. etc.

    So My assumption was to add the master and msdb dacpac to the project but now errors occurs in the SSDT project .

    example:

    <view> has an unresolved reference to object [msdb].[dbo].[sysjobs].

    Seting back he reference to C:\.. is not even working anymore.

    One thing that was strange is that when I removed the database reference in SSDT (To create one in my project) gave an object reference error.

    BTW, If I rename the database in de database reference to msdb2 for instance. It is working...



    And I've multiple database projects with separate references to msdb and master dacpacs
    • Edited by Hennie7863 Monday, July 9, 2018 7:12 AM
    Monday, July 9, 2018 6:52 AM

All replies

  • check if this helps

    http://madebysql.blogspot.com/2011/03/how-to-resolve-references-to-system.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 9, 2018 7:09 AM
  • Yep, that is exactly what I did. I copied the dacpacs from the Program files into my project. Added it as dacpac location in my project.

    But no luck..

    Strangely enough is when I rename the database name in the database reference, eg msdb2 and rename the query to msdb2.dbo.sys... etc. it works. But this is not workable...


    Maybe it has something to do with multiple database projects in my solution?

    • Edited by Hennie7863 Monday, July 9, 2018 7:17 AM
    Monday, July 9, 2018 7:16 AM
  • Yep, that is exactly what I did. I copied the dacpacs from the Program files into my project. Added it as dacpac location in my project.

    But no luck..

    Strangely enough is when I rename the database name in the database reference, eg msdb2 and rename the query to msdb2.dbo.sys... etc. it works. But this is not workable...


    Maybe it has something to do with multiple database projects in my solution?

    looks like multi-hop issue discussed here

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6fe239f2-8a57-49c2-a2cd-2d83a98dd999/unresolved-reference-to-object-msdbsysroutes?forum=ssdt


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 9, 2018 7:27 AM
  • I solved it as i seems to me now...pff..

    I copied the master and msdb dacpac's in both Database project directories and added it as a normal database reference. Suppress Refference Warnings to True for all of them...

    After I solved some reference errors (because i wasn't system database anymore) I managed to build the code.

    In VSTS it was still giving he same build errors.

    Looking in the sqlproj showed me that the refeence to C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac was still there (?!?!).. That's why the object reference errors showed up!!

    I removed these from the sqlproj file :

      <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
          <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
        </ArtifactReference>
        <ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\msdb.dacpac">
          <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\msdb.dacpac</HintPath>
        </ArtifactReference>

    And left only the snippet in the file (example):

     <ArtifactReference Include="msdb.dacpac">
          <HintPath>msdb.dacpac</HintPath>
          <SuppressMissingDependenciesErrors>True</SuppressMissingDependenciesErrors>
          <DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
        </ArtifactReference>

    And that solved the problems in VSTS.

    Build successful

    Hennie

    Monday, July 9, 2018 8:57 AM
  • I know is an old thread but maybe it helps someone.

    This issue occurred for me when I upgraded to a new SQL version (from 2012 to 2016).

    Following the answer from Hennie, i checked the project file and noticed there were two references for msdb database, one for SQL 2012 and one for SQL 2017. Removed the old reference (for SQL 2012) and the project built successfully.

    Friday, September 13, 2019 6:57 AM