[dba-SQLServer] Can't drop view

 > is it just DROP VIEW vwMyView

Yes.

I deleted the view manually and went on with my work.  The idea is to drop, then recreate the view. 
  If it can't be dropped, then the recreate fails as well.  The view was definitely bad, if I opened 
it manually it was trying to reference a table that simply did not exist.  I imagine that this 
process created the view at some point im time that the table DID exist and then ...

In any event, this is why I have to get error reporting back into the Access application.

As it happens I was running this (it was an order I am processing) directly from a SP that runs 
other SPs.  It also happens that this SP was the last SP in the line and I didn't have to have it 
working.  However in any case I just deleted the view, and ran it again.  The drop view is designed 
to print an error but may in fact be valid if (as in this case) there is no view to drop.

The bigger problem is that the entire SP failed to run because the missing table in the view being 
dropped triggered an error that "bubbled up" (the only way I can describe it) into the SP error 
reporting.  It was rather confusing at first because the name of the table wasn't ANYWHERE in my SP. 
  It was in fact down in the view about to be dropped.

Sigh.

John W. Colby
www.ColbyConsulting.com


Francisco Tapia wrote:
> What is the code you are using?
> 
> is it just DROP VIEW vwMyView
> 
> what is the error message, a lot of times you can use a Try Catch to move
> around the error messages to allow your sproc to continue running.  Post a
> snippet of the code and the error message I'll try to re-create the error
> here.
> 
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...
> 
> 
> On Fri, Jun 5, 2009 at 12:53 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:
> 
>> The view is used at a later step in a process and has varying fields.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Eric Barro wrote:
>>> Why are you rebuilding the view?
>>>
>>> -----Original Message-----
>>> From: dba-sqlserver-bounces at databaseadvisors.com
>>> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
>>> Sent: Friday, June 05, 2009 11:45 AM
>>> To: Dba-Sqlserver
>>> Subject: [dba-SQLServer] Can't drop view
>>>
>>> I have a stored procedure that drops and dynamically rebuilds a view.
>>  This
>>> stored procedure suddenly fails.  It turns out that the view that the SP
>> was
>>> trying to drop referenced a non-existent table.  I could not even
>> "compile"
>>> the sp.  BTW, what do you call the execute phase when you are editing a
>>> stored procedure - as opposed to actually running the stored procedure
>> using
>>> EXEC()?
>>>
>>> Anyway, when I tried to perform that step it gave me error messages.
>>> Apparently the SP tries to open the view and if the view is bad it barfs.
>>> >From my perspective, I don't care if the view is bad because I am going
>> to
>>> drop the view anyway and rebuild it from scratch.  Unfortunately if the
>> SP
>>> won't run then I can't drop the bad view.  Not good.
>>>
>>> So I went in and manually deleted the view and the stored procedure runs
>> as
>>> I intended.
>>>
>>> Is there any way to programmatically drop a view that references a
>>> non-existent table, or has any other error that would prevent the view
>> from
>>> running?
>>>
>>> --
>>> John W. Colby
>>> www.ColbyConsulting.com
>>> _______________________________________________
>>> dba-SQLServer mailing list
>>> dba-SQLServer at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>> http://www.databaseadvisors.com
>>>
>>> _______________________________________________
>>> dba-SQLServer mailing list
>>> dba-SQLServer at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>> http://www.databaseadvisors.com
>>>
>>>
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>