How To Drop _WA Statistics On SQL2K5

It appears that SQL2K5 has removed the ability to drop _WA statistics. It
keeps giving me the error â'Cannot drop the statistics
'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist or
you do not have permission.â' When trying to run â'drop statistics
[tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]â'. In SQL2K this used to work
fineâ?¦.
We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
then now to SQL2K5 and each table originally was not indexed well at all so
we have TONS of these _WA statistics pied up on every table. I want to be
able to remove all _WA stats and allow the SQL Server to regenerate these as
needed now that we are running the new SQL2K5 engine.
Anyone find a way of doing this now? Or will SQL2K5 remove these on its own
if it does not need them any longer? Does not appear it removes them by
itself based on the quantity of these hanging around.
Thx!
Ross NornesHow are you getting the list of statistics and are you sure the account does
have the proper permissions to drop them?
--
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
> It appears that SQL2K5 has removed the ability to drop _WA statistics. It
> keeps giving me the error "Cannot drop the statistics
> 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist
> or
> you do not have permission." When trying to run "drop statistics
> [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this used to
> work
> fine..
> We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
> then now to SQL2K5 and each table originally was not indexed well at all
> so
> we have TONS of these _WA statistics pied up on every table. I want to be
> able to remove all _WA stats and allow the SQL Server to regenerate these
> as
> needed now that we are running the new SQL2K5 engine.
> Anyone find a way of doing this now? Or will SQL2K5 remove these on its
> own
> if it does not need them any longer? Does not appear it removes them by
> itself based on the quantity of these hanging around.
> Thx!
> Ross Nornes
>|||We just use a simple query to generate the DROP scripts that we used to use
for SQL2K. I have included it below for review.
And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
not be an issue.
select
'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%'
order by i.name
Thx!
Ross Nornes
"Andrew J. Kelly" wrote:
> How are you getting the list of statistics and are you sure the account does
> have the proper permissions to drop them?
> --
> Andrew J. Kelly SQL MVP
> "Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
> news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
> > It appears that SQL2K5 has removed the ability to drop _WA statistics. It
> > keeps giving me the error "Cannot drop the statistics
> > 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist
> > or
> > you do not have permission." When trying to run "drop statistics
> > [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this used to
> > work
> > fine..
> >
> > We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
> > then now to SQL2K5 and each table originally was not indexed well at all
> > so
> > we have TONS of these _WA statistics pied up on every table. I want to be
> > able to remove all _WA stats and allow the SQL Server to regenerate these
> > as
> > needed now that we are running the new SQL2K5 engine.
> >
> > Anyone find a way of doing this now? Or will SQL2K5 remove these on its
> > own
> > if it does not need them any longer? Does not appear it removes them by
> > itself based on the quantity of these hanging around.
> >
> > Thx!
> >
> > Ross Nornes
> >
>
>|||Ross,
I have seen that when the drop scrip was run in the context of master and
not the user db. But in any case you may want to use the new sys.stats view
instead.
SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] +
']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE
'_WA%'
ORDER BY i.name
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:8F4B7751-D825-47F6-BB7B-A0B0C35D6105@.microsoft.com...
> We just use a simple query to generate the DROP scripts that we used to
> use
> for SQL2K. I have included it below for review.
> And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
> not be an issue.
> select
> 'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
> from sysindexes i join
> sysobjects o on i.id = o.id
> where
> i.name like '_wa%'
> order by i.name
> Thx!
> Ross Nornes
> "Andrew J. Kelly" wrote:
>> How are you getting the list of statistics and are you sure the account
>> does
>> have the proper permissions to drop them?
>> --
>> Andrew J. Kelly SQL MVP
>> "Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
>> news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
>> > It appears that SQL2K5 has removed the ability to drop _WA statistics.
>> > It
>> > keeps giving me the error "Cannot drop the statistics
>> > 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not
>> > exist
>> > or
>> > you do not have permission." When trying to run "drop statistics
>> > [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this used
>> > to
>> > work
>> > fine..
>> >
>> > We have a large legacy DB that we upgraded originally from SQL 7 to
>> > SQL2K,
>> > then now to SQL2K5 and each table originally was not indexed well at
>> > all
>> > so
>> > we have TONS of these _WA statistics pied up on every table. I want to
>> > be
>> > able to remove all _WA stats and allow the SQL Server to regenerate
>> > these
>> > as
>> > needed now that we are running the new SQL2K5 engine.
>> >
>> > Anyone find a way of doing this now? Or will SQL2K5 remove these on its
>> > own
>> > if it does not need them any longer? Does not appear it removes them
>> > by
>> > itself based on the quantity of these hanging around.
>> >
>> > Thx!
>> >
>> > Ross Nornes
>> >
>>

How To Drop _WA Statistics On SQL2K5

It appears that SQL2K5 has removed the ability to drop _WA statistics. It
keeps giving me the error “Cannot drop the statistics
'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist or
you do not have permission.” When trying to run “drop statistics
[tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]”. In SQL2K this used to work
fine….
We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
then now to SQL2K5 and each table originally was not indexed well at all so
we have TONS of these _WA statistics pied up on every table. I want to be
able to remove all _WA stats and allow the SQL Server to regenerate these as
needed now that we are running the new SQL2K5 engine.
Anyone find a way of doing this now? Or will SQL2K5 remove these on its own
if it does not need them any longer? Does not appear it removes them by
itself based on the quantity of these hanging around.
Thx!
Ross Nornes
How are you getting the list of statistics and are you sure the account does
have the proper permissions to drop them?
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
> It appears that SQL2K5 has removed the ability to drop _WA statistics. It
> keeps giving me the error "Cannot drop the statistics
> 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist
> or
> you do not have permission." When trying to run "drop statistics
> [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this used to
> work
> fine..
> We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
> then now to SQL2K5 and each table originally was not indexed well at all
> so
> we have TONS of these _WA statistics pied up on every table. I want to be
> able to remove all _WA stats and allow the SQL Server to regenerate these
> as
> needed now that we are running the new SQL2K5 engine.
> Anyone find a way of doing this now? Or will SQL2K5 remove these on its
> own
> if it does not need them any longer? Does not appear it removes them by
> itself based on the quantity of these hanging around.
> Thx!
> Ross Nornes
>
|||We just use a simple query to generate the DROP scripts that we used to use
for SQL2K. I have included it below for review.
And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
not be an issue.
select
'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%'
order by i.name
Thx!
Ross Nornes
"Andrew J. Kelly" wrote:

> How are you getting the list of statistics and are you sure the account does
> have the proper permissions to drop them?
> --
> Andrew J. Kelly SQL MVP
> "Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
> news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
>
>
|||Ross,
I have seen that when the drop scrip was run in the context of master and
not the user db. But in any case you may want to use the new sys.stats view
instead.
SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] +
']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE
'_WA%'
ORDER BY i.name
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:8F4B7751-D825-47F6-BB7B-A0B0C35D6105@.microsoft.com...[vbcol=seagreen]
> We just use a simple query to generate the DROP scripts that we used to
> use
> for SQL2K. I have included it below for review.
> And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
> not be an issue.
> select
> 'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
> from sysindexes i join
> sysobjects o on i.id = o.id
> where
> i.name like '_wa%'
> order by i.name
> Thx!
> Ross Nornes
> "Andrew J. Kelly" wrote:

How To Drop _WA Statistics On SQL2K5

It appears that SQL2K5 has removed the ability to drop _WA statistics. It
keeps giving me the error “Cannot drop the statistics
'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist or
you do not have permission.” When trying to run “drop statistics
[tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]”. In SQL2K this u
sed to work
fine….
We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
then now to SQL2K5 and each table originally was not indexed well at all so
we have TONS of these _WA statistics pied up on every table. I want to be
able to remove all _WA stats and allow the SQL Server to regenerate these as
needed now that we are running the new SQL2K5 engine.
Anyone find a way of doing this now? Or will SQL2K5 remove these on its own
if it does not need them any longer? Does not appear it removes them by
itself based on the quantity of these hanging around.
Thx!
Ross NornesHow are you getting the list of statistics and are you sure the account does
have the proper permissions to drop them?
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
> It appears that SQL2K5 has removed the ability to drop _WA statistics. It
> keeps giving me the error "Cannot drop the statistics
> 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist
> or
> you do not have permission." When trying to run "drop statistics
> [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this u
sed to
> work
> fine..
> We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
> then now to SQL2K5 and each table originally was not indexed well at all
> so
> we have TONS of these _WA statistics pied up on every table. I want to be
> able to remove all _WA stats and allow the SQL Server to regenerate these
> as
> needed now that we are running the new SQL2K5 engine.
> Anyone find a way of doing this now? Or will SQL2K5 remove these on its
> own
> if it does not need them any longer? Does not appear it removes them by
> itself based on the quantity of these hanging around.
> Thx!
> Ross Nornes
>|||We just use a simple query to generate the DROP scripts that we used to use
for SQL2K. I have included it below for review.
And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
not be an issue.
select
'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%'
order by i.name
Thx!
Ross Nornes
"Andrew J. Kelly" wrote:

> How are you getting the list of statistics and are you sure the account do
es
> have the proper permissions to drop them?
> --
> Andrew J. Kelly SQL MVP
> "Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
> news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
>
>|||Ross,
I have seen that when the drop scrip was run in the context of master and
not the user db. But in any case you may want to use the new sys.stats view
instead.
SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+
i.[name] +
']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] L
IKE
'_WA%'
ORDER BY i.name
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:8F4B7751-D825-47F6-BB7B-A0B0C35D6105@.microsoft.com...[vbcol=seagreen]
> We just use a simple query to generate the DROP scripts that we used to
> use
> for SQL2K. I have included it below for review.
> And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
> not be an issue.
> select
> 'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
> from sysindexes i join
> sysobjects o on i.id = o.id
> where
> i.name like '_wa%'
> order by i.name
> Thx!
> Ross Nornes
> "Andrew J. Kelly" wrote:
>

How to do this...in mdx query?

In a MDX query how to create a new member within the same dimension. The following is my MDX query:

OLAP cube: AP Statistics by Cancer Centre

Dimension: DIM_Fiscal_Year

Attribute: Fiscal Year

Attribute: Fiscal Year Full

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[FY] AS '[DIM_Fiscal_Year].[Fiscal Year Full].CURRENTMEMBER.MEMBER_CAPTION'

SELECT {[Measures].[FY], [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {ORDER({FILTER([DIM_Fiscal_Year].[Fiscal Year].MEMBERS,[Measures].[ParameterLevel]=1)},([Measures].[ParameterCaption]), DESC)} ON ROWS FROM [AP Statistics by Cancer Centre]

New member is in Red, It returns the value "All" for the entire "FY" column.

2008 All 2008 [DIM_Fiscal_Year].[Fiscal Year].&[2008] 1

2007 All 2007 [DIM_Fiscal_Year].[Fiscal Year].&[2007] 1

2006 All 2006 [DIM_Fiscal_Year].[Fiscal Year].&[2006] 1

Thanks

Could you explain the structure of [DIM_Fiscal_Year] with examples - and is any relationship defined between the [Fiscal Year] and [Fiscal Year Full] attributes? From the results above, it looks like [Fiscal Year Full] is not related to [Fiscal Year].

|||

The reason I have the "fiscal_year" and "fiscal_year_full" is because 'fiscal_year" is a 4 digit year of the fiscal year and was used to create the "Time" Dimension and fiscal_year_full is the full description of the fiscal year. The following is an example:

fiscal year.........2007

fiscal year full....2006-07

I use the fiscal year full to display in the report.

|||

In that case, if you relate "fiscal year full" to "fiscal year" via an attribute relationship (while removing "fiscal year full" from its existing attribute relationship), then the appropriate "fiscal year full" member should be selected when you select a "fiscal year" member. One way to do this would be to drag "fiscal year full" under "fiscal year", as described below:

SQL Server 2005 Books Online

Defining and Configuring an Attribute Relationship

...

You can create an attribute relationship between any two attributes in a dimension. With the Attributes pane of Dimension Designer set to tree view, drag the attribute that you want to relate to another attribute onto the <new attribute relationship> field under the attribute.

...