none
References to sys.sysprocesses generating SQL71502 Warning (unresolved refrerence)

    Question

  • I have converted a VS2010 DB project into SSDT/VS2012.

    I have referenced the dacpacs for both master and msdb. As a test, I copied the master.dacpac and renamed it to TempDB.dacpac, but that did not resolve the issue.

    I do not have extended TSQL verifaction enabled.

    Any procs that reference sys.sysprocesses are generating warning SQL71502 about an unresolved reference. I reference many other system objects just fine, and I see sys.sysporcesses defined in the master.dacpac.

    What am I doing incorrectly?



    Tuesday, February 26, 2013 5:29 PM

Answers

  • Easiest and quuikest fix is to use the dbo name, so select * from dbo.sysprocesses, this resolves correctly and works in all systems.

    Alternatively, there is an unsupported tool I wrote, which you can use to re-sign the dacpac after you changed it, https://dl.dropbox.com/u/20239263/SQLProj/Util/dacchksum.zip

    Otherwise if these are warnings you can supress the 71502 message at the file or project level.


    -GertD @ www.sqlproj.com

    • Marked as answer by matcheligans Wednesday, February 27, 2013 5:29 PM
    Tuesday, February 26, 2013 7:32 PM
    Moderator

All replies

  • Seems there is an ommission in the master.dacpac, currently it looks like this:

    <Element Type="SqlView" Name="[sys].[sysprocesses]">
          <Annotation Type="AliasedToDbo" />
          ...

    It should look like this:

    <Element Type="SqlView" Name="[sys].[sysprocesses]">

          <Annotation Type="AliasedToDbo" />
          <Annotation Type="GloballyScoped" />
          ...


    -GertD @ www.sqlproj.com

    Tuesday, February 26, 2013 6:38 PM
    Moderator
  • Thank you for the quick reply, Gert. What do you feel is the best way for me to rectify this situation? When I manually add the line you suggested to the model.xml, I can no longer reference that dacpac since the checksum has changed:

    Error 199 SQL72025: The reference to C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\100\SQLSchemas\master.dacpac is not supported.


    Tuesday, February 26, 2013 7:24 PM
  • Easiest and quuikest fix is to use the dbo name, so select * from dbo.sysprocesses, this resolves correctly and works in all systems.

    Alternatively, there is an unsupported tool I wrote, which you can use to re-sign the dacpac after you changed it, https://dl.dropbox.com/u/20239263/SQLProj/Util/dacchksum.zip

    Otherwise if these are warnings you can supress the 71502 message at the file or project level.


    -GertD @ www.sqlproj.com

    • Marked as answer by matcheligans Wednesday, February 27, 2013 5:29 PM
    Tuesday, February 26, 2013 7:32 PM
    Moderator
  • Changing the schema from "sys" to "dbo" fixed the issue. Thanks!
    Wednesday, February 27, 2013 5:29 PM