DROP VIEW vs DROP TABLE

I looked through this newsgroup for Error 3705, which states that I cannot
use DROP VIEW on a particular view because it "thinks" it's a table so i am
supposed to use DROP TABLE instead. The suggestions I found do not apply in
this matter, so here goes...
I have a snapshot replication publication where I have some tables, stored
procedures, and views. The publication includes all the tables required by
the sp's and views and there is no sharing of object names. So how come the
agent thinks my view is a table? All I need replicated is the schema of the
view and not the data. Is there a way to speicfy this or does it matter
seeing that the table that the view uses is already replicated?
Roger.
If you happen to publish an indexed view as an 'indexed view logbased"
article, replication will expect that the indexed view to be instantiated as
a table on the subscriber. Of course, there may be more to it in your case.
-Raymond
"Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
news:8F24D000-5382-4FC5-B889-3B3AA662DB07@.microsoft.com...
>I looked through this newsgroup for Error 3705, which states that I cannot
> use DROP VIEW on a particular view because it "thinks" it's a table so i
> am
> supposed to use DROP TABLE instead. The suggestions I found do not apply
> in
> this matter, so here goes...
> I have a snapshot replication publication where I have some tables, stored
> procedures, and views. The publication includes all the tables required
> by
> the sp's and views and there is no sharing of object names. So how come
> the
> agent thinks my view is a table? All I need replicated is the schema of
> the
> view and not the data. Is there a way to speicfy this or does it matter
> seeing that the table that the view uses is already replicated?
> --
> Roger.

Drop all objects in database?

I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:

1. If I run this command

EXEC sp_MScheck_uid_owns_anything 5

I get this weird error message:

"The user owns objects in the database and cannot be dropped."

Not sure why that is since I'm just trying to list the objects, not drop them.

2. I tried running a simple query to get the objects from the system table instead:

SELECT * from [dbo].[sysobjects] where uid = 5

This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:

The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.

Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!

-CliffUse the TYPE column of sysobjects to filter out foreign keys, which should drop automatically when the tables are dropped.|||Thanks for responding. Your approach requires that the tables be dropped in a particular order so that there won't be any constraint violations, right? I was hoping to avoid that by deleting all the foreign keys first and then dropping all the tables...|||select 'alter table ' + object_name (fkeyid) + ' drop constraint ' + object_name (constid)
from sysreferences|||Thanks, that's getting me closer! I'll let you know how it goes ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1

-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1


-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)

hmmmmmmmm cursorssssssssss, im loving it!|||Bah! The performance problems will be lost in the wash of "Hey, where'd my table go?" complaints.

Hopefully, you have everything owned by dbo. If not, you will have to go back, and add in the user_name(uid) function.

Drillthrough Query

I have the following Drillthroug query. i require it to return only those documents that fall within a given date range for a particular customer and worktype.

But all the records for a customer and worktype are returned irrespective of the date

DRILLTHROUGH maxrows 10000

SELECT [Measures].[TAT] ON 0 ,

([Hierarchy].[CustomerName].[ABC], [Hierarchy].[Work Type Name].[EEG] ) on 1

FROM ( SELECT ([Date].[Short Date].[2004-03-15] : [Date].[Short Date].[2006-04-10]) ON COLUMNS FROM [DW]) Return[$Customer].[CustomerName] As [CustomerName],[$Measures].[TAT] AS [TAT],[$Document].[Document ID] AS [Document ID]

Hello Reena,

First of all bad news: AS 2005 doesn't allow DrillThrough to be performed on multiple cells, so unfortuantly you'd need to generate multiple drillthrough statements to retrive details for all those dates.

Secondary, why this query doesn't return an error, but doesn't take subselect into consideration. The issue is that drillthrough is performed on the current coordinate, but subselect doesn't affect the current coordinate and the current coordinate for the time dimension in this case is a default member of time - ALL. So, drillthrough is performed on the cell corresponding to the ALL and not to the range.

Hope this helps,

Irina

drill down spid for high CPU

http://www.support.microsoft.com/?id=117559
I have read something equivalent to the article above but for SQL 2K that
drills down to a particular spid while troubleshooting for high CPU usage.
I cant seem to recollect where I read it. Does anybody know ?
ThanksCheck out DBCC SQLPERF (THREADS)
Bart Simpson
On Tue, 29 Jul 2003 13:04:22 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>http://www.support.microsoft.com/?id=117559
>I have read something equivalent to the article above but for SQL 2K that
>drills down to a particular spid while troubleshooting for high CPU usage.
>I cant seem to recollect where I read it. Does anybody know ?
>Thanks
>|||Well not too sure if that was it.. but more of an article in the same line
as the link I sent
"Bart Simpson" <bartsimpson000@.yahoo.com> wrote in message
news:oipdivs7otcna04mc5ct9lifk8d8huaris@.4ax.com...
> Check out DBCC SQLPERF (THREADS)
> Bart Simpson
> On Tue, 29 Jul 2003 13:04:22 -0700, "Hassan" <fatima_ja@.hotmail.com>
> wrote:
> >http://www.support.microsoft.com/?id=117559
> >
> >I have read something equivalent to the article above but for SQL 2K that
> >drills down to a particular spid while troubleshooting for high CPU
usage.
> >
> >I cant seem to recollect where I read it. Does anybody know ?
> >
> >Thanks
> >
>