Problem with Script

Hey all,
Can anyone explain me why this doesnt work:
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
*****declare show_defrag cursor for
exec ('select [name] from ' + @.dbname
+ '..sysobjects where type =''U''') for read only *** how
come i cannot use the variable @.dbname in the second
cursor without getting an error? Any workarround?
Please help,
Thanxs> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
Do the whole lot inside dynamic SQL:
SET @.sql = 'DECLARE show_defrag cursor for SELECT "name" FROM ' + @.dbname +
'..sysobjects WHERE ...'
EXEC(@.sql)
However, you don't have to loop inside the database for DBCC SHOWCONTIG.
Just use DBCC SHOWCONTIG at the database level using the WITH TABLERESULTS
option.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> Hey all,
> Can anyone explain me why this doesnt work:
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
> Thanxs|||It dont seem to work... the process just
stays "pending"... Here is the whole script.. any help
would be greatful:
set nocount on
CREATE TABLE #aux (
[ObjectName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Pages] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MaximumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageRecordSize] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[FowardedRecords] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Extents] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentSwitches] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageFreeBytes] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AveragePageDensity] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDensity] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BestCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
declare @.sql varchar (300)
SET @.sql = 'DECLARE show_defrag cursor for
SELECT [name] FROM ' + @.dbname
+ '..sysobjects WHERE type = ''U'' for read only'
declare @.objectname varchar(200)
declare @.msg varchar(200)
EXEC(@.sql)
--declare show_defrag cursor for
--select [name] from ..sysobjects where type = 'U'
for read only
open show_defrag
fetch next from show_defrag into @.objectname
while @.@.fetch_status = 0
Begin
INSERT INTO #aux
EXEC ('DBCC SHOWCONTIG (' + @.objectname
+ ') WITH TABLERESULTS')
if exists (select Objectname from #aux where
Objectname = @.objectname and
ExtentSwitches > Extents or
ScanDensity < cast (95.0 as Decimal) or
LogicalFragmentation > cast (10.0 as Decimal) or
ExtentFragmentation > cast (10.0 as Decimal))
Begin
set @.msg = 'The table ' + @.objectname + ' in
database' + @.dbname + ' is fragmented'
Print @.msg
--exec master..xp_logevent 51515, @.msg,
informational
End
fetch next from db_defrag into @.dbname
fetch next from show_defrag into @.objectname
End
deallocate db_defrag
deallocate show_defrag
drop table #aux
set nocount off
>--Original Message--
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>Do the whole lot inside dynamic SQL:
>SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
FROM ' + @.dbname +
>'..sysobjects WHERE ...'
>EXEC(@.sql)
>However, you don't have to loop inside the database for
DBCC SHOWCONTIG.
>Just use DBCC SHOWCONTIG at the database level using the
WITH TABLERESULTS
>option.
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
>> Hey all,
>> Can anyone explain me why this doesnt work:
>> declare db_defrag cursor for
>> select [name] from master..sysdatabases where dbid
>> NOT IN (1,2,3,4,5,6) for read only
>> declare @.dbname varchar(50)
>> open db_defrag
>> fetch next from db_defrag into @.dbname
>> while @.@.fetch_status = 0
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>> Thanxs
>
>.
>|||One thing I see that that inside your WHILE, you need BEGIN and END:
WHILE ...
BEGIN
...
...
FETCH NEXT FROM ...
END
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:0b9501c3adb6$987ae5b0$a101280a@.phx.gbl...
> It dont seem to work... the process just
> stays "pending"... Here is the whole script.. any help
> would be greatful:
>
>
> set nocount on
> CREATE TABLE #aux (
> [ObjectName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ObjectId] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexId] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Pages] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Rows] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MinimumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaximumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageRecordSize] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [FowardedRecords] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extents] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentSwitches] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageFreeBytes] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AveragePageDensity] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ScanDensity] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [BestCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActualCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogicalFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> declare @.sql varchar (300)
> SET @.sql = 'DECLARE show_defrag cursor for
> SELECT [name] FROM ' + @.dbname
> + '..sysobjects WHERE type = ''U'' for read only'
> declare @.objectname varchar(200)
> declare @.msg varchar(200)
> EXEC(@.sql)
>
> --declare show_defrag cursor for
> --select [name] from ..sysobjects where type = 'U'
> for read only
>
> open show_defrag
> fetch next from show_defrag into @.objectname
> while @.@.fetch_status = 0
> Begin
> INSERT INTO #aux
> EXEC ('DBCC SHOWCONTIG (' + @.objectname
> + ') WITH TABLERESULTS')
>
> if exists (select Objectname from #aux where
> Objectname = @.objectname and
> ExtentSwitches > Extents or
> ScanDensity < cast (95.0 as Decimal) or
> LogicalFragmentation > cast (10.0 as Decimal) or
> ExtentFragmentation > cast (10.0 as Decimal))
> Begin
> set @.msg = 'The table ' + @.objectname + ' in
> database' + @.dbname + ' is fragmented'
> Print @.msg
> --exec master..xp_logevent 51515, @.msg,
> informational
> End
> fetch next from db_defrag into @.dbname
> fetch next from show_defrag into @.objectname
> End
> deallocate db_defrag
> deallocate show_defrag
> drop table #aux
>
> set nocount off
>
>
>
> >--Original Message--
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >
> >Do the whole lot inside dynamic SQL:
> >
> >SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
> FROM ' + @.dbname +
> >'..sysobjects WHERE ...'
> >EXEC(@.sql)
> >
> >However, you don't have to loop inside the database for
> DBCC SHOWCONTIG.
> >Just use DBCC SHOWCONTIG at the database level using the
> WITH TABLERESULTS
> >option.
> >
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> >> Hey all,
> >>
> >> Can anyone explain me why this doesnt work:
> >>
> >> declare db_defrag cursor for
> >> select [name] from master..sysdatabases where dbid
> >> NOT IN (1,2,3,4,5,6) for read only
> >>
> >> declare @.dbname varchar(50)
> >>
> >> open db_defrag
> >> fetch next from db_defrag into @.dbname
> >>
> >> while @.@.fetch_status = 0
> >>
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >>
> >> Thanxs
> >
> >
> >.
> >

Problem with reference dimension & snowflaked tables

OK, this is a bit complicated, so I hope I can explain it clearly. I'll try not to get bogged down in the details of the problem domain, but bear with me.

We have a cube with a regular dimension and a reference dimension. The reference dimension is connected through the fact table through the regular dimension (there are many other dimensions as well, but I'll simplify the situation here). Both the regular dimension and the reference dimension have hierarchies that that include as their top level a level called "System". For both of the dimensions, the "System" level is snowflaked in from the same table. So the regular dimension table contains a column called System_key, and a corresponding attribute. Similarly, the reference dimension table also contains a column called System_key, and a corresponding attribute. Both keys point in to the same table. Naturally, the regular dimension table also has a column that points to the reference dimension table, as you would expect with a reference dimension.

OK, I hope that's clear (if a little pathological). Essentially, the "System" attribute is present on both dimensions, and both dimensions get that value by snowflaking in to the same table.

So the problem comes when looking at measures broken down by System. The regular dimension has a "System" user hierarchy which contains a single level, the System level. When I drag that hierarchy on to a pivot chart, I get the wrong results.

From what I can tell, when processing the dimension it is populating the system attribute for the regular dimension based on the key value stored in the reference dimension table, not the regular dimension table. I have no idea why it would do that. But I look at the SQL statement generated during processing of the key attribute for the regular dimension, and it includes in the where statement a join between the reference dimension System_key column and the System table, rather than the regular dimension System_key column as I would expect. The result is totally wrong values.

Does this make any kind of sense? Any idea what could be causing this, and what might fix it (note that totally redoing all the incestuous table relationships at this point is probably not an option).What if, in the Data Source View, you create another instance of the "System" attribute table, using a simple Named Query like: "select * from System"? Then you snowflake one instance with the regular dimension table, and the other instance with the reference dimension table. My guess (maybe someone can confirm this?) is that, unless you explicitly use role-playing dimensions (like Date in Adventure Works), a single snowflaked table in the DSV is interpreted as a single instance, even if it's incorporated into the schema of multiple dimensions. And in your case, I'm assuming that breaking the "System" attribute out into its own role-playing dimension is not an option?|||Deekpak,
Thanks for the suggestion. That does seem to solve the problem.

However, it does seem like a bit of a hack. It would be nice to know if this is a bug in SSAS that may be fixed in the future, or if there's something fundamental that I don't understand here. We snowflake in this table in multiple other dimensions as well (though none others with reference dimensions currently), so it would be good to know if we're setting ourselves for more trouble later.

Any 'Softies out there care to comment?