Thursday, March 29, 2012 12:23 PM
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?
P.S.: This similar post was of no help for me:
Thursday, March 29, 2012 1:02 PM
Try changing it to use:
SELECT low FROM master.dbo.spt_values
SSDT will not support default schema syntax like that.
Thursday, March 29, 2012 1:26 PM
the reference error still resides even when implicitly using the dbo-schema.
Thursday, March 29, 2012 1:41 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].
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:56 PM
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?
Friday, March 30, 2012 1:59 AM
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.
- Marked As Answer by Torsten Tiedt Friday, March 30, 2012 7:49 AM
Friday, March 30, 2012 7:50 AM
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.
Friday, January 18, 2013 10:49 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, March 22, 2013 7:43 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..