Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnaceHi
"stephanie" wrote:

> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John|||Yes. But I changed the user with default schema to another schema, e.g. 'db
o'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>|||Hi
"stephanie" wrote:

> Yes. But I changed the user with default schema to another schema, e.g. '
dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John|||a user schema called : guest
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema a
re
> you dropping?
> John|||Hi
"stephanie" wrote:
[vbcol=seagreen]
> a user schema called : guest
> "John Bell" wrote:
>
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in advnace

Hi Stephanie,

Could you please post the exact message that you get?

What is the schema name that you are trying to delete?

Check to see if it owns any objects:

Code Snippet

select count(*) from sys.objects where [schema_id]=schema_id('<schema_name>')

Jag

|||

The following query should show what objects exist under the schema foo

select name from sys.objects where schema_id = schema_id('foo')

union

select name from sys.types where schema_id = schema_id('foo')

union

select name from sys.xml_schema_collections where schema_id = schema_id('foo')

Some schemas are undroppable: sys, dbo, information_schema

hth,

-Steven Gott

S/DET

SQL Server

|||

my schema is called guest and I amended the script

there is no row returned.

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnace
Hi
"stephanie" wrote:

> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John
|||Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>
|||Hi
"stephanie" wrote:

> Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John
|||a user schema called : guest
"John Bell" wrote:

> Hi
> "stephanie" wrote:
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> you dropping?
> John
|||Hi
"stephanie" wrote:
[vbcol=seagreen]
> a user schema called : guest
> "John Bell" wrote:
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnaceHi
"stephanie" wrote:
> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John|||Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:
> Hi
> "stephanie" wrote:
> > I want to drop a schema but it shows Fail to drop schema - Error 15150.
> > I found that there is no object under the schema. Any idea? Thanks in
> > advnace
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>|||Hi
"stephanie" wrote:
> Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John|||a user schema called : guest
"John Bell" wrote:
> Hi
> "stephanie" wrote:
> > Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> > I can't drop that schema with the same error 15150. Thanks
> >
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> you dropping?
> John|||Hi
"stephanie" wrote:
> a user schema called : guest
> "John Bell" wrote:
> > Hi
> >
> > "stephanie" wrote:
> >
> > > Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> > > I can't drop that schema with the same error 15150. Thanks
> > >
> > Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> > you dropping?
> >
> > John
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

Fail to drop an instance.

Hi all,
Before I dropped an instance from SQL Server 2005, I tried to take it
offline or detach it. The SQL Server 2005 complaint that it is being
used or the database is locked.
I tried these 2 commands:
first command:
use master
go
drop database gem_prod_1_1
It complaint:
Msg 3702, Level 16, State 4, Line 2
Cannot drop database "gem_prod_1_1" because it is currently in use.
or this command:
alter database gem_prod_1_1
set single_user with rollback immediate
It complaint:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database
'gem_prod_1_1'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
idea?
Thanks.
HS Phuah
Use Activity Monitor or sys.sysprocesses to check if there are any active
connections to your database. It is also possible that some system process
is blocking your action.
Regards
Pawel Potasinski
Uzytkownik <hsphuah@.usa.com> napisal w wiadomosci
news:1184639710.453585.89490@.22g2000hsm.googlegrou ps.com...
> Hi all,
> Before I dropped an instance from SQL Server 2005, I tried to take it
> offline or detach it. The SQL Server 2005 complaint that it is being
> used or the database is locked.
> I tried these 2 commands:
> first command:
> use master
> go
> drop database gem_prod_1_1
> It complaint:
> Msg 3702, Level 16, State 4, Line 2
> Cannot drop database "gem_prod_1_1" because it is currently in use.
> or this command:
> alter database gem_prod_1_1
> set single_user with rollback immediate
> It complaint:
> Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database
> 'gem_prod_1_1'. Try again later.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
> idea?
> Thanks.
> HS Phuah
>

Fail to drop an instance.

Hi all,
Before I dropped an instance from SQL Server 2005, I tried to take it
offline or detach it. The SQL Server 2005 complaint that it is being
used or the database is locked.
I tried these 2 commands:
first command:
use master
go
drop database gem_prod_1_1
It complaint:
Msg 3702, Level 16, State 4, Line 2
Cannot drop database "gem_prod_1_1" because it is currently in use.
or this command:
alter database gem_prod_1_1
set single_user with rollback immediate
It complaint:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database
'gem_prod_1_1'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
idea?
Thanks.
HS PhuahUse Activity Monitor or sys.sysprocesses to check if there are any active
connections to your database. It is also possible that some system process
is blocking your action.
--
Regards
Pawel Potasinski
Uzytkownik <hsphuah@.usa.com> napisal w wiadomosci
news:1184639710.453585.89490@.22g2000hsm.googlegroups.com...
> Hi all,
> Before I dropped an instance from SQL Server 2005, I tried to take it
> offline or detach it. The SQL Server 2005 complaint that it is being
> used or the database is locked.
> I tried these 2 commands:
> first command:
> use master
> go
> drop database gem_prod_1_1
> It complaint:
> Msg 3702, Level 16, State 4, Line 2
> Cannot drop database "gem_prod_1_1" because it is currently in use.
> or this command:
> alter database gem_prod_1_1
> set single_user with rollback immediate
> It complaint:
> Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database
> 'gem_prod_1_1'. Try again later.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
> idea?
> Thanks.
> HS Phuah
>

Fail to drop an instance.

Hi all,
Before I dropped an instance from SQL Server 2005, I tried to take it
offline or detach it. The SQL Server 2005 complaint that it is being
used or the database is locked.
I tried these 2 commands:
first command:
use master
go
drop database gem_prod_1_1
It complaint:
Msg 3702, Level 16, State 4, Line 2
Cannot drop database "gem_prod_1_1" because it is currently in use.
or this command:
alter database gem_prod_1_1
set single_user with rollback immediate
It complaint:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database
'gem_prod_1_1'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
idea?
Thanks.
HS PhuahUse Activity Monitor or sys.sysprocesses to check if there are any active
connections to your database. It is also possible that some system process
is blocking your action.
Regards
Pawel Potasinski
Uzytkownik <hsphuah@.usa.com> napisal w wiadomosci
news:1184639710.453585.89490@.22g2000hsm.googlegroups.com...
> Hi all,
> Before I dropped an instance from SQL Server 2005, I tried to take it
> offline or detach it. The SQL Server 2005 complaint that it is being
> used or the database is locked.
> I tried these 2 commands:
> first command:
> use master
> go
> drop database gem_prod_1_1
> It complaint:
> Msg 3702, Level 16, State 4, Line 2
> Cannot drop database "gem_prod_1_1" because it is currently in use.
> or this command:
> alter database gem_prod_1_1
> set single_user with rollback immediate
> It complaint:
> Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database
> 'gem_prod_1_1'. Try again later.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I drop the gem_prod_1_1 instance from the SQL Server 2005? Any
> idea?
> Thanks.
> HS Phuah
>

Extremely slow runtime running SSRS on a cube

Hi everyone:

I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?

Barry

If you use the MDX query designer (the GUI) in SSRS, I believe it puts the NON EMPTY keyword on both axes. So as soon as you put calculated measures into the query, it now has to do a non-empty against the calculation. This is MUCH more expensive unless you've properly defined a NON_EMPTY_BEHAVIOR.

Option 1: Do a search for NON_EMPTY_BEHAVIOR and add that to all your calculated measures.

Option 2: Flip over to the advanced tab where you can manually control the MDX query and start using the NonEmpty function instead of the NON EMPTY keyword. If you use the NonEmpty function, then you can specify which (physical) measure it should check.

That make sense?

|||

Thanks so much! That worked like a charm.

I am new to MDX, been using Oracle and am pretty helpless without the query builder.

|||

Well, it ran pretty fast, but my calculated field disappears when I switch to design mode. Here's what the qury looks like:

WITH MEMBER [Measures].[Fill Price]

AS 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )'

SELECT NONEMPTY ( { [Measures].[Slippage Tick Open Price], [Measures].[Slippage Open Price], [Measures].[Slippage G1 Bank], [Measures].[Quantity], [Measures].[Slippage Contract Open Price], [Measures].[Fill Price], [Measures].[Slippage Contract TWAP], [Measures].[Slippage Tick TWAP], [Measures].[Slippage TWAP], [Measures].[Slippage G1 Model] } )

ON COLUMNS, NONEMPTY ( { ([Fill Date].[Full Date].[Full Date].ALLMEMBERS * [Dim Team].[Team].[Team].ALLMEMBERS * [Dim Trader].[Trader Name].[Trader Name].ALLMEMBERS * [Dim Trade Info].[Hold Type].[Hold Type].ALLMEMBERS * [Dim Model].[Model Name].[Model Name].ALLMEMBERS * [Dim Market].[Report Name].[Report Name].ALLMEMBERS * [Dim TradeId].[Direction].[Direction].ALLMEMBERS * [Dim TradeId].[Spot 3 Month].[Spot 3 Month].ALLMEMBERS * [Dim TradeId].[Price All In].[Price All In].ALLMEMBERS * [Dim TradeId].[Open Price].[Open Price].ALLMEMBERS * [Dim TradeId].[TWAP].[TWAP].ALLMEMBERS ) } )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Fill Date].[Full Date].&[2007-08-08T00:00:00] : [Fill Date].[Full Date].&[2007-08-09T00:00:00] ) ON COLUMNS FROM [Trade]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The part referenced by 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )' Is what I want to select. It disappeared, though.

Barry

Extremely slow runtime running SSRS on a cube

Hi everyone:

I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?

Barry

If you use the MDX query designer (the GUI) in SSRS, I believe it puts the NON EMPTY keyword on both axes. So as soon as you put calculated measures into the query, it now has to do a non-empty against the calculation. This is MUCH more expensive unless you've properly defined a NON_EMPTY_BEHAVIOR.

Option 1: Do a search for NON_EMPTY_BEHAVIOR and add that to all your calculated measures.

Option 2: Flip over to the advanced tab where you can manually control the MDX query and start using the NonEmpty function instead of the NON EMPTY keyword. If you use the NonEmpty function, then you can specify which (physical) measure it should check.

That make sense?

|||

Thanks so much! That worked like a charm.

I am new to MDX, been using Oracle and am pretty helpless without the query builder.

|||

Well, it ran pretty fast, but my calculated field disappears when I switch to design mode. Here's what the qury looks like:

WITH MEMBER [Measures].[Fill Price]

AS 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )'

SELECT NONEMPTY ( { [Measures].[Slippage Tick Open Price], [Measures].[Slippage Open Price], [Measures].[Slippage G1 Bank], [Measures].[Quantity], [Measures].[Slippage Contract Open Price], [Measures].[Fill Price], [Measures].[Slippage Contract TWAP], [Measures].[Slippage Tick TWAP], [Measures].[Slippage TWAP], [Measures].[Slippage G1 Model] } )

ON COLUMNS, NONEMPTY ( { ([Fill Date].[Full Date].[Full Date].ALLMEMBERS * [Dim Team].[Team].[Team].ALLMEMBERS * [Dim Trader].[Trader Name].[Trader Name].ALLMEMBERS * [Dim Trade Info].[Hold Type].[Hold Type].ALLMEMBERS * [Dim Model].[Model Name].[Model Name].ALLMEMBERS * [Dim Market].[Report Name].[Report Name].ALLMEMBERS * [Dim TradeId].[Direction].[Direction].ALLMEMBERS * [Dim TradeId].[Spot 3 Month].[Spot 3 Month].ALLMEMBERS * [Dim TradeId].[Price All In].[Price All In].ALLMEMBERS * [Dim TradeId].[Open Price].[Open Price].ALLMEMBERS * [Dim TradeId].[TWAP].[TWAP].ALLMEMBERS ) } )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Fill Date].[Full Date].&[2007-08-08T00:00:00] : [Fill Date].[Full Date].&[2007-08-09T00:00:00] ) ON COLUMNS FROM [Trade]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The part referenced by 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )' Is what I want to select. It disappeared, though.

Barry