Reference master..spt_values fails

Question

Hi,

although I reference the master database in SSDT, the following SQL gives me an unresolved reference error:

SELECT low FROM master..spt_values

Why and how to resolve this?

Thank you!

P.S.: This similar post was of no help for me:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/87537718-fc1a-41f9-9ca6-38243b28873a/

Answer 1

Try changing it to use:

SELECT low FROM master.dbo.spt_values

SSDT will not support default schema syntax like that.

Simon

Answer 2

Hi Simon,

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

Thanks anyway!

Answer 3

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.

Answer 4

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!

Answer 5

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

Answer 6

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!

Answer 7

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.

Answer 8

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..