I am creating and maintaining my SQL Server 2008 warehouse database using a Visual Studio 2008 project.
When I try to deploy the project, I get an error as below
Creating DataWarehouseReports...
Company.HigherEducation.DataWarehouse.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 2 There is already an object named 'DataWarehouseReports' in the database.
Company.HigherEducation.DataWarehouse.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 2759, Level 16, State 0, Line 2 CREATE SCHEMA failed due to previous errors.
An error occurred while the batch was being executed.
Done executing task "SqlDeployTask" -- FAILED.
Done building target "DspDeploy" in project "Company.HigherEducation.DataWarehouse.dbproj" -- FAILED.
Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project "Company.HigherEducation.DataWarehouse.dbproj" -- FAILED.
Done building project "Company.HigherEducation.DataWarehouse.dbproj" -- FAILED.
What I found, is that the DataWarehouseReports reports schema exists in the master database (granted, by mistake), but I get the "duplicate" error when I try to create it in the OLAP database.
In development, my OLAP and OLTP databases are on the same server, and I encounter the same problem for schema names. For now, I have named schemas differently in the OLTP vs. the OLAP, but for consistency sakes, I would rather have the same name.
How do I debug this?
edit: PEBCAC Alert
I found out that I have a pre-deployment sql script that changes the database context to MASTER. My script was failing and therefore the context never was changed back.
So when the rest of my script executed, it was actually trying to create everything in Master.
Lesson learned: Read the output when the project Deploys.