From the references tab, I see I can create an instance reference, but I do
not see how I can instantiate that class with a constructor that has
arguments. Does it only do the default New() constructor?
I would like something like: m_MyClass = New MyClass(Version as Integer)I have not found a way to do this. I will perform further research on it
and update you once I have more information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi David,
In the Reference tab of report properties, if we specify a instance name we
can only use the default constructor. If you need something other than the
default constructor, you can instantiate the class in the <Code> property
of the report instead. You'll need to reference the assembly on the
References tab of the report properties, but you would't declare an
instance there.
Instead, you'd declare the instance and initialize it in the Code property:
For example,
Dim m_myClass As MyClass
Protected Overrides Sub OnInit()
m_myClass = new MyClass(1234)
End Sub
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label reference. Show all posts
Showing posts with label reference. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
CIRUCLAR REFERENCE Microsoft SQL Server 2000 Access ON DELETE UPDATE CASCADE
I need some Microsoft Access / SQL Server 2000 help. I have a SQL
Server 2000 database that is using Access as a front end (adp file).
Since SQL server does not allow cycle cascading or circular reference,
I am having problems.
For example, suppose I have tableA, tableB, tableC, and tableD. tableB
and tableC each have a foreign key (tableAID) which is tableA's primary
key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
tableB's primary key and one is tableC's primary Key. The problem is
that I can not have ON UPDATE/DELETE CASCADE because it is
circular/cyclic referential integrity. (I get an error when I try to
set both tableB and tableC to ON DELETE CASCADE... I can only set it
for one of them) What can I do to still have the behavior of ON
UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
but I don't know how to program a trigger. My objective is that when
I delete (or update) a row in tableA, the corresponding rows in tableB,
tableC, and tableD will be deleted/updated. Please help!The best way would be to not depend on cascading updates. Rather, you should
be
using stored procedures and *explicitly* delete referenced data as part of a
conscious design. However, I'd imagine you have your frontend linked directl
y to
the tables in the backend. This, among other reasons, is why this is a weak
design.
Thomas
<mneufeld@.gmail.com> wrote in message
news:1121874815.358446.298010@.f14g2000cwb.googlegroups.com...
>I need some Microsoft Access / SQL Server 2000 help. I have a SQL
> Server 2000 database that is using Access as a front end (adp file).
> Since SQL server does not allow cycle cascading or circular reference,
> I am having problems.
> For example, suppose I have tableA, tableB, tableC, and tableD. tableB
> and tableC each have a foreign key (tableAID) which is tableA's primary
> key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
> tableB's primary key and one is tableC's primary Key. The problem is
> that I can not have ON UPDATE/DELETE CASCADE because it is
> circular/cyclic referential integrity. (I get an error when I try to
> set both tableB and tableC to ON DELETE CASCADE... I can only set it
> for one of them) What can I do to still have the behavior of ON
> UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
> but I don't know how to program a trigger. My objective is that when
> I delete (or update) a row in tableA, the corresponding rows in tableB,
> tableC, and tableD will be deleted/updated. Please help!
>|||Why is this a weak design? Please Explain.
Is there anything wrong with depending on cascading deletes and
updates?
Also, how would I create a stored procedure or a trigger to
automatically delete the corresponding rows in tableB tableC and tableD
when I delete a row in tableA?|||>> Since SQL server does not allow cycle cascading or circular reference, I
am having problems. <<
Other SQLs do not have this problem, so you might want to switch to one
of them. Otherwise, you can use triggers (remember to comment it as
kludge) or write a stored procedure to do the job.
Don't do narratives; do DDL or at least psuedo-code. Is this what you
meant?
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Gamma
(gamma_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Delta
(delta_id INTEGER NOT NULL PRIMARY KEY,
beta_id INTEGER NOT NULL
REFERENCES Beta(beta_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
gamma_id INTEGER NOT NULL
REFERENCES Gamma(gamma_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
There is two paths from Alpha to Delta (A -> B -> D) and( A -> C -> D).
SQL Server sees this and chokes.
One ugly trick is to create a BetaGamma table with a compound key so
there is only one path (A ->. BC ->. D). Another trick is split Delta
so you have (A -> B -> D1) and( A -> C -> D2). But we'd need more
information to do one of these.
One of the advantages of DRI is that it gives information to the
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc.|||Thanks Celko for replying. It is actually funny because I was looking
at other threads that you replied to and I was thinking, "Wow, it looks
like Celko knows his stuff and I wish he would reply to my thread."
Then low and behold I checked my thread and you replied! I have a few
questions though.
I'm new to groups and I don't know what DDL is?
Yes
kludge) or write a stored procedure to do the job. <<
I have never written a trigger and I do not know how to. Can you please
explain.
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc. <<
I don't understand this entire paragraph. I'm new to groups and to
databases so can you please explain it to me.
Thanx.|||>> I'm new to groups and I don't know what DDL is? <<
SQL has three sub-languages: DDL (Data Declaration Language), DML (Data
Manipulation Language) and DCL (Data Control Language). DDL is the
CREATE, ALTER and DROP stuff.
For that, you need a book not a newsgroup. A trigger is a kind of
stored procedure that is attachd to a table and it gets "fired"
(invoked) when a database event occurs (update, delete, insert). Three
are some special things about triggers which involve the delted and
inserted rows in the T_SQL model and there are some limits as to what
you can do in a Trigger.
Oh Boy! This way outside a newsgroup! You tell SQL *what" you want
and the optimzer figures out *how* to find it. The optimizer uses
statistics, constraints, and DRI to pick the right access methods.
PK-FK can pass on some ifno. For example, can you look at this
predicate and tell me if it is TRUE or FALSE?
EXISTS
(SELECT *
FROM Delta
WHERE Delta.beta_id = Beta.beta_id).
Likewise if I do a JOIN on (Delta.beta_id = Beta.beta_id), I can pull
up stats to see the statistical distribution, number of disticnt
beta_id values in each table, etc.|||Celko,
Thanx for explaining heres the DDL:
CREATE TABLE tbServer
(lngServerID INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE tbDatabase
(lngDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID )
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplication
(lngApplicationID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplicationToDatabase
(lngApplicationToDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngApplicationID INTEGER NOT NULL
REFERENCES tbApplication(lngApplicationID)
ON DELETE CASCADE
ON UPDATE CASCADE,
lngDatabaseID INTEGER NOT NULL
REFERENCES tbDatabase(lngDatabaseID)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
First of all keep in mind that this is a sample DDL, not the real one.
OK so basically this is keeping track of the server's application and
databases and sometimes an Application connects to a DB for storage
etc.
I have database(s) that are running on server(s) that connect to
application(s) which may be running on different server(s). So when I
delete a server record I want all the applications and databases
running on this particular server deleted AND any connections deleted
(since the DB or App does not exist anymore). How can I do this. If the
only way is to do it with triggers then please show me the code for it.
Thanx a lot!|||
--CELKO-- wrote:
> Other SQLs do not have this problem, so you might want to switch to one
> of them.
Yep, even ACCESS can do this correctly! They've fixed it for SQL Server
2005, I understand.|||It is a weak design because it lacks abstraction and thereby limits your abi
lity
to make design changes. By linking directly to the source tables, you have n
o
ability to effective business rules in the backend without using something l
ike
triggers. Your example is quite illustrative of this design's short comings.
You
want to effect a business rule (delete X when I delete Y). This would be a s
nap
if you were doing all of your deletes through stored procedures which shield
the
UI to some degree from the backend structure. However, since there is no
abstraction between the UI and the backend database, you are left with a han
dful
of ugly choices (e.g. triggers, or UI logic).
A good indication of a weak design is one that prevents you from easily
effecting a simple change.
Thomas
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>|||> Is there anything wrong with depending on cascading deletes and
> updates?
YES!!!!! Cascading referential actions are problematic for a number of
reasons, but the main one involves locks, concurrency, and deadlocks.
Cascading updates and deletes complicate deadlock resolution because the
order in which locks are obtained for a cascading referential action is
undefined. From a programmer's perspective, all locks in the cascade chain
are obtained at the same time. While the problem is more prevalent with
cascading updates, the same principles apply to a cascading delete. The way
to minimize deadlocks is to always obtain locks in the same order in every
transaction. Cascading referential actions limit the control you as a
programmer have in specifying that order. I never use them. I use
surrogate keys so I don't have to worry about cascading updates, and I
explicitly code deletes either in a trigger, or in a stored procedure. That
way I have complete control over the order in which resources are locked,
and I only have to worry about deadlocks caused by a malformed execution
plan. In those very rare cases, I can manipulate the execution plan by
specifying optimizer hints.
CREATE TRIGGER tIOD_tableA ON tableA INSTEAD OF DELETE AS
BEGIN
DELETE tableD
FROM
(SELECT keyD FROM deleted JOIN tableB ON (tableB.keyA =
deleted.keyA)
UNION SELECT keyD FROM deleted JOIN tableC ON (tableC.keyA =
deleted.keyA))) allD
WHERE tableD.keyD = allD.keyD
DELETE tableB FROM deleted WHERE tableB.keyA = deleted.keyA
DELETE tableC FROM deleted WHERE tableC.keyA = deleted.keyA
DELETE tableA FROM deleted WHERE tableA.keyA = deleted.keyA
END
GO
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>
Server 2000 database that is using Access as a front end (adp file).
Since SQL server does not allow cycle cascading or circular reference,
I am having problems.
For example, suppose I have tableA, tableB, tableC, and tableD. tableB
and tableC each have a foreign key (tableAID) which is tableA's primary
key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
tableB's primary key and one is tableC's primary Key. The problem is
that I can not have ON UPDATE/DELETE CASCADE because it is
circular/cyclic referential integrity. (I get an error when I try to
set both tableB and tableC to ON DELETE CASCADE... I can only set it
for one of them) What can I do to still have the behavior of ON
UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
but I don't know how to program a trigger. My objective is that when
I delete (or update) a row in tableA, the corresponding rows in tableB,
tableC, and tableD will be deleted/updated. Please help!The best way would be to not depend on cascading updates. Rather, you should
be
using stored procedures and *explicitly* delete referenced data as part of a
conscious design. However, I'd imagine you have your frontend linked directl
y to
the tables in the backend. This, among other reasons, is why this is a weak
design.
Thomas
<mneufeld@.gmail.com> wrote in message
news:1121874815.358446.298010@.f14g2000cwb.googlegroups.com...
>I need some Microsoft Access / SQL Server 2000 help. I have a SQL
> Server 2000 database that is using Access as a front end (adp file).
> Since SQL server does not allow cycle cascading or circular reference,
> I am having problems.
> For example, suppose I have tableA, tableB, tableC, and tableD. tableB
> and tableC each have a foreign key (tableAID) which is tableA's primary
> key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
> tableB's primary key and one is tableC's primary Key. The problem is
> that I can not have ON UPDATE/DELETE CASCADE because it is
> circular/cyclic referential integrity. (I get an error when I try to
> set both tableB and tableC to ON DELETE CASCADE... I can only set it
> for one of them) What can I do to still have the behavior of ON
> UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
> but I don't know how to program a trigger. My objective is that when
> I delete (or update) a row in tableA, the corresponding rows in tableB,
> tableC, and tableD will be deleted/updated. Please help!
>|||Why is this a weak design? Please Explain.
Is there anything wrong with depending on cascading deletes and
updates?
Also, how would I create a stored procedure or a trigger to
automatically delete the corresponding rows in tableB tableC and tableD
when I delete a row in tableA?|||>> Since SQL server does not allow cycle cascading or circular reference, I
am having problems. <<
Other SQLs do not have this problem, so you might want to switch to one
of them. Otherwise, you can use triggers (remember to comment it as
kludge) or write a stored procedure to do the job.
Don't do narratives; do DDL or at least psuedo-code. Is this what you
meant?
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Gamma
(gamma_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Delta
(delta_id INTEGER NOT NULL PRIMARY KEY,
beta_id INTEGER NOT NULL
REFERENCES Beta(beta_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
gamma_id INTEGER NOT NULL
REFERENCES Gamma(gamma_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
There is two paths from Alpha to Delta (A -> B -> D) and( A -> C -> D).
SQL Server sees this and chokes.
One ugly trick is to create a BetaGamma table with a compound key so
there is only one path (A ->. BC ->. D). Another trick is split Delta
so you have (A -> B -> D1) and( A -> C -> D2). But we'd need more
information to do one of these.
One of the advantages of DRI is that it gives information to the
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc.|||Thanks Celko for replying. It is actually funny because I was looking
at other threads that you replied to and I was thinking, "Wow, it looks
like Celko knows his stuff and I wish he would reply to my thread."
Then low and behold I checked my thread and you replied! I have a few
questions though.
I'm new to groups and I don't know what DDL is?
Yes
kludge) or write a stored procedure to do the job. <<
I have never written a trigger and I do not know how to. Can you please
explain.
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc. <<
I don't understand this entire paragraph. I'm new to groups and to
databases so can you please explain it to me.
Thanx.|||>> I'm new to groups and I don't know what DDL is? <<
SQL has three sub-languages: DDL (Data Declaration Language), DML (Data
Manipulation Language) and DCL (Data Control Language). DDL is the
CREATE, ALTER and DROP stuff.
For that, you need a book not a newsgroup. A trigger is a kind of
stored procedure that is attachd to a table and it gets "fired"
(invoked) when a database event occurs (update, delete, insert). Three
are some special things about triggers which involve the delted and
inserted rows in the T_SQL model and there are some limits as to what
you can do in a Trigger.
Oh Boy! This way outside a newsgroup! You tell SQL *what" you want
and the optimzer figures out *how* to find it. The optimizer uses
statistics, constraints, and DRI to pick the right access methods.
PK-FK can pass on some ifno. For example, can you look at this
predicate and tell me if it is TRUE or FALSE?
EXISTS
(SELECT *
FROM Delta
WHERE Delta.beta_id = Beta.beta_id).
Likewise if I do a JOIN on (Delta.beta_id = Beta.beta_id), I can pull
up stats to see the statistical distribution, number of disticnt
beta_id values in each table, etc.|||Celko,
Thanx for explaining heres the DDL:
CREATE TABLE tbServer
(lngServerID INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE tbDatabase
(lngDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID )
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplication
(lngApplicationID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplicationToDatabase
(lngApplicationToDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngApplicationID INTEGER NOT NULL
REFERENCES tbApplication(lngApplicationID)
ON DELETE CASCADE
ON UPDATE CASCADE,
lngDatabaseID INTEGER NOT NULL
REFERENCES tbDatabase(lngDatabaseID)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
First of all keep in mind that this is a sample DDL, not the real one.
OK so basically this is keeping track of the server's application and
databases and sometimes an Application connects to a DB for storage
etc.
I have database(s) that are running on server(s) that connect to
application(s) which may be running on different server(s). So when I
delete a server record I want all the applications and databases
running on this particular server deleted AND any connections deleted
(since the DB or App does not exist anymore). How can I do this. If the
only way is to do it with triggers then please show me the code for it.
Thanx a lot!|||
--CELKO-- wrote:
> Other SQLs do not have this problem, so you might want to switch to one
> of them.
Yep, even ACCESS can do this correctly! They've fixed it for SQL Server
2005, I understand.|||It is a weak design because it lacks abstraction and thereby limits your abi
lity
to make design changes. By linking directly to the source tables, you have n
o
ability to effective business rules in the backend without using something l
ike
triggers. Your example is quite illustrative of this design's short comings.
You
want to effect a business rule (delete X when I delete Y). This would be a s
nap
if you were doing all of your deletes through stored procedures which shield
the
UI to some degree from the backend structure. However, since there is no
abstraction between the UI and the backend database, you are left with a han
dful
of ugly choices (e.g. triggers, or UI logic).
A good indication of a weak design is one that prevents you from easily
effecting a simple change.
Thomas
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>|||> Is there anything wrong with depending on cascading deletes and
> updates?
YES!!!!! Cascading referential actions are problematic for a number of
reasons, but the main one involves locks, concurrency, and deadlocks.
Cascading updates and deletes complicate deadlock resolution because the
order in which locks are obtained for a cascading referential action is
undefined. From a programmer's perspective, all locks in the cascade chain
are obtained at the same time. While the problem is more prevalent with
cascading updates, the same principles apply to a cascading delete. The way
to minimize deadlocks is to always obtain locks in the same order in every
transaction. Cascading referential actions limit the control you as a
programmer have in specifying that order. I never use them. I use
surrogate keys so I don't have to worry about cascading updates, and I
explicitly code deletes either in a trigger, or in a stored procedure. That
way I have complete control over the order in which resources are locked,
and I only have to worry about deadlocks caused by a malformed execution
plan. In those very rare cases, I can manipulate the execution plan by
specifying optimizer hints.
CREATE TRIGGER tIOD_tableA ON tableA INSTEAD OF DELETE AS
BEGIN
DELETE tableD
FROM
(SELECT keyD FROM deleted JOIN tableB ON (tableB.keyA =
deleted.keyA)
UNION SELECT keyD FROM deleted JOIN tableC ON (tableC.keyA =
deleted.keyA))) allD
WHERE tableD.keyD = allD.keyD
DELETE tableB FROM deleted WHERE tableB.keyA = deleted.keyA
DELETE tableC FROM deleted WHERE tableC.keyA = deleted.keyA
DELETE tableA FROM deleted WHERE tableA.keyA = deleted.keyA
END
GO
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>
Circular Reference?
I get this error when I ran the below statement what did I do wrong?
"The definition of MonthRange set contains a circular reference"
WITH SET [MonthRange] AS
{
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember
:
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember
}
SELECT {[Measures].[Closing Balance]} on 0,
{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1
FROM [ Bond Analytics OLAP]
WHERE ([MonthRange], [Industry].&[Subprime])
[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.
What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )
Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?
(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)
For example,
given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102
and
given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104
Try this version:
WITH
SET [MonthRange] AS
LastPeriods(3,
[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember
)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as
Aggregate([MonthRange])
SELECT {[Measures].[Closing Balance]} on 0,
{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1
FROM [ Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],
[Industry].&[Subprime])
|||Hi Deepark,
This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.
Is it because of some SCOPING issue in the calculation?
--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.
WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])
SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO
Calculations:
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-
/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;
SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
|||Hi Deepark,
Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!
Perhaps, it is a new feature in SQL Server 2005 SP2.
Maybe it has something to do with calculation order.
Thank you very much.
You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.
I think it's time you get a new title --> SUPER MVP 
Hi Deepark,
Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!
Perhaps, it is a new feature in SQL Server 2005 SP2.
Maybe it has something to do with calculation order.
Thank you very much.
You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.
I think it's time you get a new title --> SUPER MVP 
by the way, the code that works look like this:
--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.
--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?
WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS],
[Measures].[D30 OTS],
[Measures].[D60 OTS],
[Measures].[D90 OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO
--this one also works, so if I want another rolling period, I just replace the lag #
WITH SET [MonthRange] AS
{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}
MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),
SCOPE_ISOLATION = CUBE
select
{
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS],
[Measures].[D30 OTS],
[Measures].[D60 OTS],
[Measures].[D90 OTS]
} on 0,
([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1
from [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
Circular reference
I would like to know if there is a problem when I create a Work with several
steps and the last step call first step again?Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?
steps and the last step call first step again?Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?
Circular reference
I would like to know if there is a problem when I create a Work with several steps and the last step call first step again?
Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?
Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?
Tuesday, February 14, 2012
CHECKDB and Object ID 0
Can someone please tell me what "Object ID 0" is? It does not exist in
sysobjects and I can find a reference in BOL or in the groups/forums...
I am trying to anlyze the first line returned from DBCC CHECKDB
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
the page header = (0:120).
Thanks!Hi Dave
Error 8909 is documented in Books Online which advises to check for hardware
problems and then restore from a good back.
John
"Dave" wrote:
> Can someone please tell me what "Object ID 0" is? It does not exist in
> sysobjects and I can find a reference in BOL or in the groups/forums...
> I am trying to anlyze the first line returned from DBCC CHECKDB
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> the page header = (0:120).
> Thanks!
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:[vbcol=seagreen]
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardwa
re
> problems and then restore from a good back.
> John
> "Dave" wrote:
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:[vbcol=seagreen]
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardwa
re
> problems and then restore from a good back.
> John
> "Dave" wrote:
>|||Hi Dave
I believe it is corrupted data.
John
"Dave" wrote:
> Yes, I saw that. We had a hard drive fail in a Raid 5 array.
> I am planning on doing the restore today, but I would still like to
> know what object "ojbect id 0" is. Any ideas?
>
> John Bell wrote:
>
sysobjects and I can find a reference in BOL or in the groups/forums...
I am trying to anlyze the first line returned from DBCC CHECKDB
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
the page header = (0:120).
Thanks!Hi Dave
Error 8909 is documented in Books Online which advises to check for hardware
problems and then restore from a good back.
John
"Dave" wrote:
> Can someone please tell me what "Object ID 0" is? It does not exist in
> sysobjects and I can find a reference in BOL or in the groups/forums...
> I am trying to anlyze the first line returned from DBCC CHECKDB
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> the page header = (0:120).
> Thanks!
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:[vbcol=seagreen]
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardwa
re
> problems and then restore from a good back.
> John
> "Dave" wrote:
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:[vbcol=seagreen]
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardwa
re
> problems and then restore from a good back.
> John
> "Dave" wrote:
>|||Hi Dave
I believe it is corrupted data.
John
"Dave" wrote:
> Yes, I saw that. We had a hard drive fail in a Raid 5 array.
> I am planning on doing the restore today, but I would still like to
> know what object "ojbect id 0" is. Any ideas?
>
> John Bell wrote:
>
CHECKDB and Object ID 0
Can someone please tell me what "Object ID 0" is? It does not exist in
sysobjects and I can find a reference in BOL or in the groups/forums...
I am trying to anlyze the first line returned from DBCC CHECKDB
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
the page header = (0:120).
Thanks!Hi Dave
Error 8909 is documented in Books Online which advises to check for hardware
problems and then restore from a good back.
John
"Dave" wrote:
> Can someone please tell me what "Object ID 0" is? It does not exist in
> sysobjects and I can find a reference in BOL or in the groups/forums...
> I am trying to anlyze the first line returned from DBCC CHECKDB
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> the page header = (0:120).
> Thanks!
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Hi Dave
I believe it is corrupted data.
John
"Dave" wrote:
> Yes, I saw that. We had a hard drive fail in a Raid 5 array.
> I am planning on doing the restore today, but I would still like to
> know what object "ojbect id 0" is. Any ideas?
>
> John Bell wrote:
> > Hi Dave
> >
> > Error 8909 is documented in Books Online which advises to check for hardware
> > problems and then restore from a good back.
> >
> > John
> >
> > "Dave" wrote:
> >
> > > Can someone please tell me what "Object ID 0" is? It does not exist in
> > > sysobjects and I can find a reference in BOL or in the groups/forums...
> > >
> > > I am trying to anlyze the first line returned from DBCC CHECKDB
> > >
> > > Server: Msg 8909, Level 16, State 1, Line 1
> > > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > > the page header = (0:120).
> > >
> > > Thanks!
> > >
> > >
>
sysobjects and I can find a reference in BOL or in the groups/forums...
I am trying to anlyze the first line returned from DBCC CHECKDB
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
the page header = (0:120).
Thanks!Hi Dave
Error 8909 is documented in Books Online which advises to check for hardware
problems and then restore from a good back.
John
"Dave" wrote:
> Can someone please tell me what "Object ID 0" is? It does not exist in
> sysobjects and I can find a reference in BOL or in the groups/forums...
> I am trying to anlyze the first line returned from DBCC CHECKDB
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> the page header = (0:120).
> Thanks!
>|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Yes, I saw that. We had a hard drive fail in a Raid 5 array.
I am planning on doing the restore today, but I would still like to
know what object "ojbect id 0" is. Any ideas?
John Bell wrote:
> Hi Dave
> Error 8909 is documented in Books Online which advises to check for hardware
> problems and then restore from a good back.
> John
> "Dave" wrote:
> > Can someone please tell me what "Object ID 0" is? It does not exist in
> > sysobjects and I can find a reference in BOL or in the groups/forums...
> >
> > I am trying to anlyze the first line returned from DBCC CHECKDB
> >
> > Server: Msg 8909, Level 16, State 1, Line 1
> > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > the page header = (0:120).
> >
> > Thanks!
> >
> >|||Hi Dave
I believe it is corrupted data.
John
"Dave" wrote:
> Yes, I saw that. We had a hard drive fail in a Raid 5 array.
> I am planning on doing the restore today, but I would still like to
> know what object "ojbect id 0" is. Any ideas?
>
> John Bell wrote:
> > Hi Dave
> >
> > Error 8909 is documented in Books Online which advises to check for hardware
> > problems and then restore from a good back.
> >
> > John
> >
> > "Dave" wrote:
> >
> > > Can someone please tell me what "Object ID 0" is? It does not exist in
> > > sysobjects and I can find a reference in BOL or in the groups/forums...
> > >
> > > I am trying to anlyze the first line returned from DBCC CHECKDB
> > >
> > > Server: Msg 8909, Level 16, State 1, Line 1
> > > Table error: Object ID 0, index ID 0, page ID (1:23953). The PageId in
> > > the page header = (0:120).
> > >
> > > Thanks!
> > >
> > >
>
Subscribe to:
Posts (Atom)