2

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.

2

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.

  • 1
    I was going to suggest that you query sys.objects in the target database to look for a stray object with the same name. Glad you found your solution. – Jonathan Fite Feb 14 '17 at 14:09
-2

Add a drop statement in front of your create.

Take note that this is dangerous.

  • 1
    The reason this is a dangerous suggestion is that the OP didn't know what the other object was. Since it turned out to be a table, DROP VIEW wouldn't have worked anyway. While the suggestion is generally a good one, in this case the user would want to confirm that the object being deleted (if it had been a view) was not in use, serving some other purpose. – RDFozz Feb 14 '17 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.