1

When using the Schema Compare tool with SSDT in Visual Studio 2012 to update a database project with changes made to schema objects on a local database, the schema is 'lost' from the object declaration. I am connected to my local sql server instance using a Windows account and this account has default schema of dbo on the database being compared.

For example, a local stored procedure definition may be:

CREATE PROCEDURE [dbo].[SetPriceBounds]

But when this is added to the database project using schema compare, it becomes:

CREATE PROCEDURE SetPriceBounds

When this is subsequently deployed to another SQL Server instance, it fails with the error:

The default schema does not exist.

I need to get the dbo. prefix preserved when the schema comparison is executed, any ideas are welcome.

Jason
  • 3,599
  • 10
  • 37
  • 52
  • I think you've found an interesting bug. They probably never considered there not being a default schema. – David Nov 25 '13 at 15:55
  • If you look at the properties of the database project, there's a field for "default schema". Try changing it t a nonsense value (i.e. a schema that doesn't exist) and see how the compare behaves. – Ben Thul Nov 25 '13 at 19:48
  • Thanks for the suggestion, Ben; it didn't make any difference, though. – Jason Nov 26 '13 at 08:41

1 Answers1

0

This has been solved by setting the default schema on the deployment target database to dbo for the user that was performing the deployment. I'm not convinced this is the right way to fix the problem, but it works.

Jason
  • 3,599
  • 10
  • 37
  • 52