Errors while browsing the cube - "#Value"

While browsing the cube, I'm getting a "#Value" error, which I believe is due to the non existence of certain attribute members.

For example, working on Adventure Works database. Assuming there is no Product Category = 5 in the SSAS database but we want to add this category into the calculation for future purposes, so the calculation would be as follows

(Measures.[Internet Sales Amount], [Product].[Category].&[5])

This calculation gives me a "#Value" error while browsing, to handle this kind of situation, I added an exception handling, check whether the member exist, if not return 0.

IIF(

ISEMPTY(EXISTS([Product].[Category].[Category].MEMBERS, [Product].[Category].&[5]).ITEM(0)),

0,

Measures.[Internet Sales Amount], [Product].[Category].&[5]

)

This seems to work fine in the SQL Managemen Studio, but when added to the Cube in the Calculations and processes, still I get the same "#Value" error.

Is the apporach to handle this is right? Is there a different way to handle this?

-

Vivek

Doing the whole "IIF( ISEMPTY( EXISTS(...)))" is going to wreck your performance. I'm surprised that it appears to work in SSMS as the EXISTS function will attempt to resolve both the sets in order to evaluate them and should throw an exception trying to resolve [Product[.[Category].&[5] and IsEmpty is not checking if the set is empty, but rather it will be doing a value comparison against the default measure which could lead to misleading results.

There are better ways to code a check like this, but they are still not a good idea. If you know you are going to have a Category 5, add that row to the dimension now and re-process the dimension, even though you don't yet have any facts for it. This way this script will be able to resolve the member reference and everything will work.

Adding references in the script to non-existant members and trying to trap errors is generally a bad thing.

NOTE: in SSMS you can hover over or double click on the #Value to see the text of the error message.

|||

Thanks for the reply.

I guess you are right, I further did a search on this and go the same inputs.

Books Online suggested to use "IS" instead of "ISEMPTY()" to check for emptiness of dimension/attribute members. Even this works in SSMS (MDX query) and when ported to BIDS and browse fails.

You did say there are ways to handle this kind of exception, can you share them? I know it is performance intensive, but I have to catch such kind of exception.

-

Vivek

|||

Just to be entirely clear - you ONLY ever need this sort of check if you are taking user input for something like a parameter in SSRS or some other reporting engine. You should NEVER use the following type of code in your MDX Script.

The pattern in something like an SSRS report would look like the following, passing in the unique name as a string and using the StrToMember() function and testing with the IS operator.

eg.

IIF( StrToMember( @.parameter ) IS NULL, ...

I know I am repeating myself, but I need to make sure this is clear - if you know that you are going to need a product category 5, then create it in the dimension member now. Never put references in your MDX script to members that do not yet exist and then try to wrap these references with code to handle the errors.

If you have a calc that works in SSMS, but not in BIDS, it will be because of the way the browser control in BIDS is structuring the query. Start up a profiler session and capture the queries that BIDS generates and test them in SSMS. There will probably be multiple commands fired off as BIDS quite often uses things like session scoped sets in it's queries.

|||

Darren,

Thanks. Surely will not use this in BIDS, as it clearly doesn't work.

--

Vivek

Errors while browsing the cube - "#Value"

While browsing the cube, I'm getting a "#Value" error, which I believe is due to the non existence of certain attribute members.

For example, working on Adventure Works database. Assuming there is no Product Category = 5 in the SSAS database but we want to add this category into the calculation for future purposes, so the calculation would be as follows

(Measures.[Internet Sales Amount], [Product].[Category].&[5])

This calculation gives me a "#Value" error while browsing, to handle this kind of situation, I added an exception handling, check whether the member exist, if not return 0.

IIF(

ISEMPTY(EXISTS([Product].[Category].[Category].MEMBERS, [Product].[Category].&[5]).ITEM(0)),

0,

Measures.[Internet Sales Amount], [Product].[Category].&[5]

)

This seems to work fine in the SQL Managemen Studio, but when added to the Cube in the Calculations and processes, still I get the same "#Value" error.

Is the apporach to handle this is right? Is there a different way to handle this?

-

Vivek

Doing the whole "IIF( ISEMPTY( EXISTS(...)))" is going to wreck your performance. I'm surprised that it appears to work in SSMS as the EXISTS function will attempt to resolve both the sets in order to evaluate them and should throw an exception trying to resolve [Product[.[Category].&[5] and IsEmpty is not checking if the set is empty, but rather it will be doing a value comparison against the default measure which could lead to misleading results.

There are better ways to code a check like this, but they are still not a good idea. If you know you are going to have a Category 5, add that row to the dimension now and re-process the dimension, even though you don't yet have any facts for it. This way this script will be able to resolve the member reference and everything will work.

Adding references in the script to non-existant members and trying to trap errors is generally a bad thing.

NOTE: in SSMS you can hover over or double click on the #Value to see the text of the error message.

|||

Thanks for the reply.

I guess you are right, I further did a search on this and go the same inputs.

Books Online suggested to use "IS" instead of "ISEMPTY()" to check for emptiness of dimension/attribute members. Even this works in SSMS (MDX query) and when ported to BIDS and browse fails.

You did say there are ways to handle this kind of exception, can you share them? I know it is performance intensive, but I have to catch such kind of exception.

-

Vivek

|||

Just to be entirely clear - you ONLY ever need this sort of check if you are taking user input for something like a parameter in SSRS or some other reporting engine. You should NEVER use the following type of code in your MDX Script.

The pattern in something like an SSRS report would look like the following, passing in the unique name as a string and using the StrToMember() function and testing with the IS operator.

eg.

IIF( StrToMember( @.parameter ) IS NULL, ...

I know I am repeating myself, but I need to make sure this is clear - if you know that you are going to need a product category 5, then create it in the dimension member now. Never put references in your MDX script to members that do not yet exist and then try to wrap these references with code to handle the errors.

If you have a calc that works in SSMS, but not in BIDS, it will be because of the way the browser control in BIDS is structuring the query. Start up a profiler session and capture the queries that BIDS generates and test them in SSMS. There will probably be multiple commands fired off as BIDS quite often uses things like session scoped sets in it's queries.

|||

Darren,

Thanks. Surely will not use this in BIDS, as it clearly doesn't work.

--

Vivek

sql

error:8646,severity:21,state:1

hi to all
Does any one here help me please in this error.By the way i've found this
error when i check the error log due to error in my replication monitor,all
subscriber cannot replicate due to this error.Im using sql 7.
complete error:
the index entry for row ID was not found in index Id 5,of table 362536425,
in Database 'Database Name'
Error:8646,Severity:21,State:1
Thank you in advance
Pheter R. Flores
This article applies to SQL2000, but I'd check to see if it apples to SQL7
anyway:
http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
I'd also run DBB CHECKDB (even though the article says DBCC will not report
any errors described by the article), especially if the KB article doesn't
apply.
For a non-clustered index, often dropping and re-creating it may fix the
problem, though not the root cause.
Linchi
"MICS" wrote:

> hi to all
> Does any one here help me please in this error.By the way i've found this
> error when i check the error log due to error in my replication monitor,all
> subscriber cannot replicate due to this error.Im using sql 7.
> complete error:
> the index entry for row ID was not found in index Id 5,of table 362536425,
> in Database 'Database Name'
> Error:8646,Severity:21,State:1
> Thank you in advance
> Pheter R. Flores
|||I had already read that article before and it seems that the fix is only
applied on sql 2000.Is there any fix aside from the hotfix on that article?
Thank you so much
"Linchi Shea" wrote:
[vbcol=seagreen]
> This article applies to SQL2000, but I'd check to see if it apples to SQL7
> anyway:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
> I'd also run DBB CHECKDB (even though the article says DBCC will not report
> any errors described by the article), especially if the KB article doesn't
> apply.
> For a non-clustered index, often dropping and re-creating it may fix the
> problem, though not the root cause.
> Linchi
> "MICS" wrote:
|||maybe your right,but the problem is i don't know what particular table should
i rebuild the index,because as you've notice the error message display only
the number of the table and not the name.Do you know hoe to locate the table
name given only the number?thanks
"Tibor Karaszi" wrote:

> Seems like a corruption problem. Possibly dropping that index (and then re-creating it) will clean
> it up. Also see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
>
|||i''ve found it on the sysobjects system table,and by the way i already solve
the problem by running dbcc.tnank you so much
"Tibor Karaszi" wrote:

> Check out the OBJECT_NAME() function. Or look it up in the sysobjects system table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:46BA4D55-694E-4428-9A1D-F23813F4FC7A@.microsoft.com...
>

error:8646,severity:21,state:1

hi to all
Does any one here help me please in this error.By the way i've found this
error when i check the error log due to error in my replication monitor,all
subscriber cannot replicate due to this error.Im using sql 7.
complete error:
the index entry for row ID was not found in index Id 5,of table 362536425,
in Database 'Database Name'
Error:8646,Severity:21,State:1
Thank you in advance
Pheter R. FloresThis article applies to SQL2000, but I'd check to see if it apples to SQL7
anyway:
http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
I'd also run DBB CHECKDB (even though the article says DBCC will not report
any errors described by the article), especially if the KB article doesn't
apply.
For a non-clustered index, often dropping and re-creating it may fix the
problem, though not the root cause.
Linchi
"MICS" wrote:
> hi to all
> Does any one here help me please in this error.By the way i've found this
> error when i check the error log due to error in my replication monitor,all
> subscriber cannot replicate due to this error.Im using sql 7.
> complete error:
> the index entry for row ID was not found in index Id 5,of table 362536425,
> in Database 'Database Name'
> Error:8646,Severity:21,State:1
> Thank you in advance
> Pheter R. Flores|||I had already read that article before and it seems that the fix is only
applied on sql 2000.Is there any fix aside from the hotfix on that article?
Thank you so much
"Linchi Shea" wrote:
> This article applies to SQL2000, but I'd check to see if it apples to SQL7
> anyway:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;822747
> I'd also run DBB CHECKDB (even though the article says DBCC will not report
> any errors described by the article), especially if the KB article doesn't
> apply.
> For a non-clustered index, often dropping and re-creating it may fix the
> problem, though not the root cause.
> Linchi
> "MICS" wrote:
> > hi to all
> > Does any one here help me please in this error.By the way i've found this
> > error when i check the error log due to error in my replication monitor,all
> > subscriber cannot replicate due to this error.Im using sql 7.
> >
> > complete error:
> >
> > the index entry for row ID was not found in index Id 5,of table 362536425,
> > in Database 'Database Name'
> >
> > Error:8646,Severity:21,State:1
> >
> > Thank you in advance
> >
> > Pheter R. Flores|||Seems like a corruption problem. Possibly dropping that index (and then re-creating it) will clean
it up. Also see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
> hi to all
> Does any one here help me please in this error.By the way i've found this
> error when i check the error log due to error in my replication monitor,all
> subscriber cannot replicate due to this error.Im using sql 7.
> complete error:
> the index entry for row ID was not found in index Id 5,of table 362536425,
> in Database 'Database Name'
> Error:8646,Severity:21,State:1
> Thank you in advance
> Pheter R. Flores|||maybe your right,but the problem is i don't know what particular table should
i rebuild the index,because as you've notice the error message display only
the number of the table and not the name.Do you know hoe to locate the table
name given only the number?thanks
"Tibor Karaszi" wrote:
> Seems like a corruption problem. Possibly dropping that index (and then re-creating it) will clean
> it up. Also see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
> > hi to all
> > Does any one here help me please in this error.By the way i've found this
> > error when i check the error log due to error in my replication monitor,all
> > subscriber cannot replicate due to this error.Im using sql 7.
> >
> > complete error:
> >
> > the index entry for row ID was not found in index Id 5,of table 362536425,
> > in Database 'Database Name'
> >
> > Error:8646,Severity:21,State:1
> >
> > Thank you in advance
> >
> > Pheter R. Flores
>|||Check out the OBJECT_NAME() function. Or look it up in the sysobjects system table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:46BA4D55-694E-4428-9A1D-F23813F4FC7A@.microsoft.com...
> maybe your right,but the problem is i don't know what particular table should
> i rebuild the index,because as you've notice the error message display only
> the number of the table and not the name.Do you know hoe to locate the table
> name given only the number?thanks
> "Tibor Karaszi" wrote:
>> Seems like a corruption problem. Possibly dropping that index (and then re-creating it) will
>> clean
>> it up. Also see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "MICS" <MICS@.discussions.microsoft.com> wrote in message
>> news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
>> > hi to all
>> > Does any one here help me please in this error.By the way i've found this
>> > error when i check the error log due to error in my replication monitor,all
>> > subscriber cannot replicate due to this error.Im using sql 7.
>> >
>> > complete error:
>> >
>> > the index entry for row ID was not found in index Id 5,of table 362536425,
>> > in Database 'Database Name'
>> >
>> > Error:8646,Severity:21,State:1
>> >
>> > Thank you in advance
>> >
>> > Pheter R. Flores|||i''ve found it on the sysobjects system table,and by the way i already solve
the problem by running dbcc.tnank you so much
"Tibor Karaszi" wrote:
> Check out the OBJECT_NAME() function. Or look it up in the sysobjects system table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:46BA4D55-694E-4428-9A1D-F23813F4FC7A@.microsoft.com...
> > maybe your right,but the problem is i don't know what particular table should
> > i rebuild the index,because as you've notice the error message display only
> > the number of the table and not the name.Do you know hoe to locate the table
> > name given only the number?thanks
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems like a corruption problem. Possibly dropping that index (and then re-creating it) will
> >> clean
> >> it up. Also see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> >> news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
> >> > hi to all
> >> > Does any one here help me please in this error.By the way i've found this
> >> > error when i check the error log due to error in my replication monitor,all
> >> > subscriber cannot replicate due to this error.Im using sql 7.
> >> >
> >> > complete error:
> >> >
> >> > the index entry for row ID was not found in index Id 5,of table 362536425,
> >> > in Database 'Database Name'
> >> >
> >> > Error:8646,Severity:21,State:1
> >> >
> >> > Thank you in advance
> >> >
> >> > Pheter R. Flores
> >>
>

error:8646,severity:21,state:1

hi to all
Does any one here help me please in this error.By the way i've found this
error when i check the error log due to error in my replication monitor,all
subscriber cannot replicate due to this error.Im using sql 7.
complete error:
the index entry for row ID was not found in index Id 5,of table 362536425,
in Database 'Database Name'
Error:8646,Severity:21,State:1
Thank you in advance
Pheter R. FloresThis article applies to SQL2000, but I'd check to see if it apples to SQL7
anyway:
http://support.microsoft.com/defaul...kb;en-us;822747
I'd also run DBB CHECKDB (even though the article says DBCC will not report
any errors described by the article), especially if the KB article doesn't
apply.
For a non-clustered index, often dropping and re-creating it may fix the
problem, though not the root cause.
Linchi
"MICS" wrote:

> hi to all
> Does any one here help me please in this error.By the way i've found this
> error when i check the error log due to error in my replication monitor,al
l
> subscriber cannot replicate due to this error.Im using sql 7.
> complete error:
> the index entry for row ID was not found in index Id 5,of table 362536425,
> in Database 'Database Name'
> Error:8646,Severity:21,State:1
> Thank you in advance
> Pheter R. Flores|||I had already read that article before and it seems that the fix is only
applied on sql 2000.Is there any fix aside from the hotfix on that article?
Thank you so much
"Linchi Shea" wrote:
[vbcol=seagreen]
> This article applies to SQL2000, but I'd check to see if it apples to SQL7
> anyway:
> http://support.microsoft.com/defaul...kb;en-us;822747
> I'd also run DBB CHECKDB (even though the article says DBCC will not repor
t
> any errors described by the article), especially if the KB article doesn't
> apply.
> For a non-clustered index, often dropping and re-creating it may fix the
> problem, though not the root cause.
> Linchi
> "MICS" wrote:
>|||Seems like a corruption problem. Possibly dropping that index (and then re-c
reating it) will clean
it up. Also see http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
> hi to all
> Does any one here help me please in this error.By the way i've found this
> error when i check the error log due to error in my replication monitor,al
l
> subscriber cannot replicate due to this error.Im using sql 7.
> complete error:
> the index entry for row ID was not found in index Id 5,of table 362536425,
> in Database 'Database Name'
> Error:8646,Severity:21,State:1
> Thank you in advance
> Pheter R. Flores|||maybe your right,but the problem is i don't know what particular table shoul
d
i rebuild the index,because as you've notice the error message display only
the number of the table and not the name.Do you know hoe to locate the table
name given only the number?thanks
"Tibor Karaszi" wrote:

> Seems like a corruption problem. Possibly dropping that index (and then re
-creating it) will clean
> it up. Also see http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:EA0291E2-D243-4896-A0F8-CCCD06923FF9@.microsoft.com...
>|||Check out the OBJECT_NAME() function. Or look it up in the sysobjects system
table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:46BA4D55-694E-4428-9A1D-F23813F4FC7A@.microsoft.com...[vbcol=seagreen]
> maybe your right,but the problem is i don't know what particular table sho
uld
> i rebuild the index,because as you've notice the error message display onl
y
> the number of the table and not the name.Do you know hoe to locate the tab
le
> name given only the number?thanks
> "Tibor Karaszi" wrote:
>|||i''ve found it on the sysobjects system table,and by the way i already solve
the problem by running dbcc.tnank you so much
"Tibor Karaszi" wrote:

> Check out the OBJECT_NAME() function. Or look it up in the sysobjects syst
em table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MICS" <MICS@.discussions.microsoft.com> wrote in message
> news:46BA4D55-694E-4428-9A1D-F23813F4FC7A@.microsoft.com...
>

Error:2147749904

When trying to install SQL Server 2005 I encountered this error:

The SQL Server Configuration Checker cannont be executed due to WMI configuration on the machine -- Error:2147749904 (0x80041010).

Could you please help.

Thanks

Here is a good thread outlining how to repair your WMI configuration:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1066137&SiteID=1

Thanks,
Sam Lester (MSFT)

Error:2147749904

When trying to install SQL Server 2005 I encountered this error:

The SQL Server Configuration Checker cannont be executed due to WMI configuration on the machine -- Error:2147749904 (0x80041010).

Could you please help.

Thanks

Here is a good thread outlining how to repair your WMI configuration:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1066137&SiteID=1

Thanks,
Sam Lester (MSFT)