Error while creating a new view

I get the message "There is already an object named 'xxx' in the
database. I was creating a new view and had an error on it. I fixed the
error and resubmitted the create view and I received this error
message. I tried to find the view but it was not there. It must have
created an object with that name somewhere in the data base. How do I
find the object and remove it?I forgot to mention that I am using SQL Server 2000.|||I forgot to mention that I am using SQL Server 2000.|||Add this at the top before your Create View statement:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'thenameofyourview')
DROP VIEW thenameofyourview
GO
"moonflower" <Jack_S_Owens@.hotmail.com> wrote in message
news:1132166124.934501.74210@.f14g2000cwb.googlegroups.com...
>I get the message "There is already an object named 'xxx' in the
> database. I was creating a new view and had an error on it. I fixed the
> error and resubmitted the create view and I received this error
> message. I tried to find the view but it was not there. It must have
> created an object with that name somewhere in the data base. How do I
> find the object and remove it?
>|||When I put the IF statement before the Create View statement in the SQL
QueryAnalyzer program I get an error message " CREATE VIEW must be the
first statement in a query batch". Where can I run these statements and
not get the new error message?|||"moonflower" <Jack_S_Owens@.hotmail.com> wrote in message
news:1132166124.934501.74210@.f14g2000cwb.googlegroups.com...
>I get the message "There is already an object named 'xxx' in the
> database. I was creating a new view and had an error on it. I fixed the
> error and resubmitted the create view and I received this error
> message. I tried to find the view but it was not there. It must have
> created an object with that name somewhere in the data base. How do I
> find the object and remove it?
>
IF object_ID('xxx') is not null
exec ( 'drop view xxx' )
go
create view xxx
as
select 1234
GO
How are you looking for the view ?
Have you hit REFRESH ?
S.|||Use the IF before the DROP VIEW command. Then execute CREATE VIEW in its own
batch (after GO).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"moonflower" <Jack_S_Owens@.hotmail.com> wrote in message
news:1132179017.603657.268500@.g49g2000cwa.googlegroups.com...
> When I put the IF statement before the Create View statement in the SQL
> QueryAnalyzer program I get an error message " CREATE VIEW must be the
> first statement in a query batch". Where can I run these statements and
> not get the new error message?
>