Can't save edited view in SSMS - 'There is already an object named xxx' error

Question

While in SSMS 2014, I right clicked on one of my views, which I will simply refer to here as 'xxxxxx'. I then selected Design from the pop up menu. After making a small edit, I tried to save my modified view and got the following error, 

• There is already an object named 'xxxxxx' in the database.

Needless to say, I didn't find that message very useful. How could I possibly be editing an object that DIDN'T already exist? I have not had this problem when editing any of my other views, many of which (like this one) depend on sub-queries.

My SQL Server skills are a bit limited and Googling this problem has not yet found a relevant answer. I would therefore be very grateful if someone would shed some light on this obscure error.

David

Answer 1

When you use the design feature on a view, it's creating TSQL behind the scenes and running it. Sounds like the DROP VIEW failed in your case, subsiquently causing the following CREATE VIEW to fail because the object already exists.

With your design tab still open, issue a DROP VIEW <DATABASE>.<SCHEMA>.xxxxx in another tab.

Then, save your view.

Answer 2

SSMS objected to me putting the database name into your DROP VIEW command so I tried,

      DROP VIEW dbo.[The name of my view]

However, SSMS has been showing the status of 'Executing query...' for a very long time (much, much longer than it took for my attempt to save the edited view to generate an error). I have a growing feeling that this process will never end...

The underlying table only contains 11,364 records, so there is not a massive amount of date to crunch.

David

 

Answer 3

Sounds like there are locks (or even deadlocks!) in play here. Do you have access to run sp_who2 or use activity monitor on the database?

Answer 4

That DROP VIEW command is still executing, so something certainly appears to be locking the process.

I've never heard of either of those tools. Are they likely to be available for an SQL Server database hosted by an ISP, as mine is?

David

Answer 5

I've fixed it!

I frequently have both Visual Studio and SSMS open at the same time and this does not normally cause any conflicts. Sometimes I also have SQL Server Data Tools for VS 2013 open as well, without any problem.

I have just tried closing down all of these except SSMS and was now able to save my edited view in the normal manner.

David

Answer 6

Activity moniter is a feature of SQL Server Management Stuido, and sp_who2 is a system stored procedure. It depends on your level of access to the server whether or not they are available to you. Either one would have revealed the SPID of the transaction blocking yours, which I'm assuming belong to one of the tools you shut down (killing it's transaction, and releasing the DROP).

Answer 7

When I try to use Activity Monitor in SSMS I get a message telling me that I do not have permission to use it for my database. Also, my list of available stored procs does not include sp_who2.

However, now that I know the cause of this problem, I will hopefully remember what to do next time it occurs.

Many thanks for your help, which definitely nudged me towards the solution. 

David

Answer 8

try running sp_who2 (it lives in the system stored procs) and see what it gets you. Most times it's available.

Answer 9

I don't know where to see any other stored procs. The list I can see includes several that begin with 'sp_' but not sp_who2.

David

Answer 10

Hi David,

You can directly run sp_who2 in SQL Server with the following scripts:

USE master;

GO

EXEC sp_who2;

GO

To see all executing sessions on the instance of SQL Server, a user requires VIEW SERVER STATE permission on the server. Otherwise, the user sees only the current session.

Besides, to view the Activity Monitor in SQL Server, a user must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE. To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.

There are two related articles for your reference.
Using sp_who2 to help with SQL Server troubleshooting
Open Activity Monitor (SQL Server Management Studio)


Thanks,
Lydia Zhang

If you have any feedback on our support, please click here.

---

Lydia Zhang
TechNet Community Support

Answer 11

Hi Lydia,
I am pleased to say that your script worked for me. I can now run sp_who2 via this method.

However, I would still like to know where I can see a list of all system procedures. When I view Server Explorer in VS 2013 I am offered a choice of Tables, Views and Procedures but the Procedures list only contains my own SPs, a lot of procs associated with the ASP.NET Membership system and a small number of procs with a 'sp_' prefix that relate to diagrams. Where are all the other SPs with an 'sp_' prefix such as sp_who2?

As I mentioned earlier, I am unable to use Activity Monitor. I just get a pop-up error message saying that "The Activity Monitor is unable to execute queries against server xxxxxx ............. The user does not have permission to perform this action. (Microsoft SQL Error, Error: 297)".

David

Answer 12

Hi Lydia,
I am pleased to say that your script worked for me. I can now run sp_who2 via this method.

However, I would still like to know where I can see a list of all system procedures. When I view Server Explorer in VS 2013 I am offered a choice of Tables, Views and Procedures but the Procedures list only contains my own SPs, a lot of procs associated with the ASP.NET Membership system and a small number of procs with a 'sp_' prefix that relate to diagrams. Where are all the other SPs with an 'sp_' prefix such as sp_who2?

As I mentioned earlier, I am unable to use Activity Monitor. I just get a pop-up error message saying that "The Activity Monitor is unable to execute queries against server xxxxxx ............. The user does not have permission to perform this action. (Microsoft SQL Error, Error: 297)".

David

Hi David,

sp_who2 is located in the “master” database, you can view sp_who2 and other system procedures in SQL Server Object Explorer/ Databases/System Databases/master/Programmability/stored procedures/System Stored Procedures. Below is an example for you.

 

Additionally, please give permission to your login using one of  the following two methods. In this way, you will be able to use Activity Monitor, for more details, please my first reply.

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. SQL Server Management Studio GUI: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

Thanks,
Lydia Zhang

---

Lydia Zhang
TechNet Community Support

Answer 13

Lydia,
Thanks for that. I can now see all those system SPs. 

I have also been able to grant myself VIEW SERVER STATE permission but all this seems to let me do in Activity Monitor is to look at a row of 4 graphs. Selecting any of the other options, i.e. Processes, Resource Waits, Data File I/O and Recent Expensive Queries, just triggers the error message I mentioned earlier.

An attempt to grant myself the VIEW ANY DEFINITION permission was just ignored.

David

CORRECTION: I must have misread the Effective tab in the Properties box for my database in SSMS after trying to grant myself VIEW SERVER STATE permission. I am actually only listed as having CONNECT SQL and VIEW ANY DATABASE permissions. It appears that all my requests to grant myself permissions are being ignored.