Solved

Schema Not Showing in SSDT Database Project

Posted on 2016-09-14
26
83 Views
Last Modified: 2016-10-05
Hi guys,

I am creating a SSDT Database project. In SQL Server i have got different schema defined. However all those schema are not visible in SSDT. When i try to Create table like [Staging].[AddisonLeeAirportLookUp], it comes up with error message
Error	1	SQL71501: Table: [Staging].[AddisonLeeAirportLookUp] has an unresolved reference to Schema [Staging].	C:\Users\ali.shah\documents\visual studio 2013\Projects\Technical.Reporting.LegacyDataWarehouse\Technical.Reporting.LegacyDataWarehouse\Staging\AddisonLeeAirportLookUp.sql	1	14	Technical.Reporting.LegacyDataWarehouseStagingDB

Open in new window


However with dbo schema everything works. but i need the staging schema.

regards
0
Comment
Question by:shah36
  • 11
  • 8
  • 4
  • +1
26 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41798072
Does the login you're using have permissions for the Staging schema?
0
 

Author Comment

by:shah36
ID: 41798089
Yes i can use this Schema in SSMS. I have logged in with same credentials in SSDT as of SSMS
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41798142
change authorization and then try.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:shah36
ID: 41798158
Hi Pawan,
Where do i need to change authorization?

regards
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41798177
Sample..

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;    
GO
0
 

Author Comment

by:shah36
ID: 41799474
I have tried the following statement
ALTER AUTHORIZATION ON LegacyReportingDatabase_New.Staging TO dbo

Open in new window


but get the following error message
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'Staging', because it does not exist or you do not have permission.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41799481
Close the SSMS and try again.
0
 

Author Comment

by:shah36
ID: 41799484
same error again. i tried running it on Master database too
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41799637
Try to use sp_changedbowner stored procedure.
0
 

Author Comment

by:shah36
ID: 41799941
Well i might have done it wrong but here what i did
EXEC sp_changedbowner 'Staging'

Open in new window

and the error i got is
Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'Staging', because it does not exist or you do not have permission.

Open in new window

0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41827863
shah36, do you have this sorted out or do you still need help on this question?

For what I see, you need to provide us some more information.
What's the login that you're using in SSDTand and how are you trying to create the table?
0
 

Author Comment

by:shah36
ID: 41829553
Hi Vitor,

Thanks a lot for your reply. However when i did not get a response i used a separate database for staging instead of creating a schema.

But it would still be good to sort this out.
The login i am using in SSDT as the same as SQL Server (SQL Server authentication). And i am using ADD-Table from the context menu in SSDT

regards
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829564
In SQL Server i have got different schema defined. However all those schema are not visible in SSDT.
Can you post a screen shot of your SQL Server Management Studio (SSMS) with all the existing schemas for the database?
0
 

Author Comment

by:shah36
ID: 41829579
Here is the picture Schemas However I think i missed a very important point.
in SSDT there is no connection reference / property. And it connects to the database through a publish profile that is when we publish it. Which means SSDT doesn't know any other schema apart from the dbo.
And the build fails when i try to use any other schema and publish wouldn't work.
You might come up with solution but i think this is the bug in SSDT.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829601
I never worked with SSDT but trying to understand on the database point of view but by the previous comments you said that you could login with SSDT in the database via SSMS and that you could create a table in the Staging schema, right?
0
 

Author Comment

by:shah36
ID: 41829613
No sorry my mistake perhaps or it was off from my head.
SSDT works independently of SSMS.
in SSDT we create a SQL Server Project and there is no connection manager in SSDT unlike SSIS or SSAS.
We can create tables, stored procedures, UDF etc. in SSDT. But the only time we connect with the database is when we create a publish profile. And the publish profile contains the connection string. We can have many publish profiles with different connection strings which will publish the same database project to the different environments (dev, QA, production, local) this is an advantage.
But the disadvantage seems to be as there is no connection manager so i think SSDT assumes that there is only one schema (dbo) and if we try create the table like Staging.TableName it doesn't recognize it. Because of this the project fails to build and hence we can't publish with any other schema apart from dbo.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829620
Oh, so it may not even be a bug but a limitation of the SSDT.
0
 

Author Comment

by:shah36
ID: 41829622
Yes you are right. So should i just close the question
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829631
Yes, maybe is better to close it unless you have some kind of hope that somebody else can help you with this.
1
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41829674
I was reading more about SSDT (sorry can't test it here) and found this article where has an section about the schemas:
Default Schema

If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).

This doesn't really says that you can't work with other schemas but you can work only with a default schema (it makes sense since in SQL Server a database user will have always a default schema). So to create a table in another schema you need to explicit the schema name, something like this:
CREATE TABLE [Staging].[AddisonLeeAirportLookUp] (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(50))

By your comments you also did it so my guess is that you need to add or enable something in your database project. Also read MSDN forum and I've found a reference to add the schema to your SSDT project by performing the following steps:
"Right-click on your database project node in Solution Explorer and select Add->New Item. From the templates menu, select Security in the categories pane and select the Schema object."
1
 

Author Comment

by:shah36
ID: 41829710
Please cancel my request to close it down as Vitor has come with the perfect solution.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829713
Question has been reopened.
0
 

Author Closing Comment

by:shah36
ID: 41829716
Thank you Vitor you are a star. This is the perfect solution.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41829718
Thank you for the opportunity to learn something more.
Cheers.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question