Cellset.open hangs, query works fine in Management Studio?

Hi All,

We are experiencing a very strange Cellset behaviour.

Trying to open a cellset in VBA (same in VB6) in Excel 2003, using ADOMD.Catalog, accessing a AS2005SP2, the system 'sometimes' hangs, depending on the 'dynamic' MDX query string. While the query works fine in the SQL Management Studio.

For example, this MDX works fine:

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200502] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

This one causes Excel (and VB6) to freeze, but nicely returns a result in SQL Management Studio!!!?

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200501] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

There is nothing wrong with the data, it's more connection related (I expect...)?

Does somebody have any advice?

PS: we have msxml6,asoledb9 installed

It is a firewall issue. See also: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1558402&SiteID=1

Cheers,

Tom

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
AlexThe NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com