none
Reference master..spt_values fails

Answers

  • Hello Torsten,

    Sorry for the inconvenience this has caused to you.

    Since master.dbo.spt_values is an undocumented internal view, the master.dacpac doesn't include it, and that's why it can't be resolved. We are considering adding it and other undocumented elements to the master.dacpac.

    Thanks,

    Yang



    YangG(MSFT), SDE

    • Marked as answer by Torsten Tiedt Friday, March 30, 2012 7:49 AM
    Friday, March 30, 2012 1:59 AM

All replies

  • Try changing it to use:

    SELECT low FROM master.dbo.spt_values
    

    SSDT will not support default schema syntax like that.

    Simon

    • Proposed as answer by Simon12345 Thursday, March 29, 2012 1:03 PM
    • Unproposed as answer by Torsten Tiedt Thursday, March 29, 2012 1:26 PM
    Thursday, March 29, 2012 1:02 PM
  • Hi Simon,

    the reference error still resides even when implicitly using the dbo-schema.

    Thanks anyway!

    Thursday, March 29, 2012 1:26 PM
  • Have you set a variable for the master DB?

    Check that the properties field "Database Variable Name" is blank on the master database reference.

    Also trying qualifiying the name [master].

    Simon


    Forget the above - that table name doesn't resolve for me either. I can only assume it is not included in the master reference DB for some reason. Seems odd though. You may have to surpress the warning.
    • Edited by Simon12345 Thursday, March 29, 2012 1:44 PM
    Thursday, March 29, 2012 1:41 PM
  • Hi Simon,

    the Database Variable Name is not blank but set to the literal value "master". SSDT says I should reference the master db like this:

    SELECT * FROM [master].[Schema1].[Table1]

    It is not possible to use a database variable like $(master) for a system database.

    Maybe a MSFT employe can help?

    Thank you!

    Thursday, March 29, 2012 1:56 PM
  • Hello Torsten,

    Sorry for the inconvenience this has caused to you.

    Since master.dbo.spt_values is an undocumented internal view, the master.dacpac doesn't include it, and that's why it can't be resolved. We are considering adding it and other undocumented elements to the master.dacpac.

    Thanks,

    Yang



    YangG(MSFT), SDE

    • Marked as answer by Torsten Tiedt Friday, March 30, 2012 7:49 AM
    Friday, March 30, 2012 1:59 AM
  • Hello YangG,

    that was an interesting piece of information that helped me to understand why the error occurs. Also it would be nice if undocumented objects would also be added to the dacpacs as suggested by you.

    Many thanks!

    Friday, March 30, 2012 7:50 AM
  • We fixed this by simply creating a seperate (same server) database project "MasterSpt" and including the definitions for those missing objects. Reference the object by [$(MasterSpt)].[dbo].[spt_values] which will allow no-warning build -  Then just set the value for the database name at deploy time to "master" in all cases.

    Work around not ideal but it works.

    Friday, January 18, 2013 10:49 AM
  • It looks like you cannot use this as a workaround in sql 2012 as you cannot script the object...

    Is there a sql 2012 solution?

    spt_values is changed to be an internal view and when i right click and try and script it

    there is no option to do so..

    Friday, March 22, 2013 7:43 AM
  • Hi YangG(MSFT), SDE

    I've got the latest ssdt September 2013 package but it seems like the spt_values was not included in there.  Something wrong that I did with the update maybe?

    rm

    Thursday, September 19, 2013 7:14 PM
  • You may find this link useful, which shows how to add a reference into the DACPAC for spt_values which will resolve the issues for you...

    http://social.msdn.microsoft.com/Forums/en-US/f6532bea-18aa-4708-a9cf-4e61f918c47b/sql03006-unresolved-reference-to-object-masterdbosptvalues

    Thanks,

    Dan

    Thursday, December 05, 2013 2:44 PM
  • This link is no good if you use SQL 2012 as per killerchef's post above.
    Tuesday, July 08, 2014 1:02 AM