Solved

Schema Not Showing in SSDT Database Project

Posted on 2016-09-14
26
48 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
Comment Utility
Does the login you're using have permissions for the Staging schema?
0
 

Author Comment

by:shah36
Comment Utility
Yes i can use this Schema in SSMS. I have logged in with same credentials in SSDT as of SSMS
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
change authorization and then try.
0
 

Author Comment

by:shah36
Comment Utility
Hi Pawan,
Where do i need to change authorization?

regards
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Sample..

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;    
GO
0
 

Author Comment

by:shah36
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Close the SSMS and try again.
0
 

Author Comment

by:shah36
Comment Utility
same error again. i tried running it on Master database too
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Try to use sp_changedbowner stored procedure.
0
 

Author Comment

by:shah36
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:shah36
Comment Utility
Yes you are right. So should i just close the question
0
 
LVL 45

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
Please cancel my request to close it down as Vitor has come with the perfect solution.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Question has been reopened.
0
 

Author Closing Comment

by:shah36
Comment Utility
Thank you Vitor you are a star. This is the perfect solution.
0
 
LVL 45

Expert Comment

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now