SQL Server 2014. I have a database on which I created a view called 'Sales-Analysis'. I also have a second database with the same schema as the first and I want to add the view to that. So in SSMS I performed 'Script view as CREATE to new Query Editor window'. I changed the first line of the script to reflect the name of the second database and ensured it was selected in the SSMS databases dropdown too. However when I execute I get:
"There is already an object named 'sales-analysis' in the database."
There isn't other than the one in the original database. No temporary tables involved. VS 2015 is closed.
It also doesn't work even if I take the original db offline, or if I save the script as an .SQL file and execute it with SQLCMD.
Same server. Refreshed views. Can successfully create a view with a '2' appended into the target database. It's just when the view name is the same as the original database. use [database] is already at the beginning of the script -'Script view' adds it. I changed it to the target database name. The view 100% does not exist in the target db. I checked via querying sys.views.
Best Answer
Turns out the target db had a TABLE (not view) with the same name. I didn't create it, I suspect what has happened is that an Entity Framework project which I have created it when I changed the app.config connection string to point at the second database and ran the project.
Naughty Entity Framework.