delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Default Values does not include all the values (Cascading Parameters)

A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.

One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).

If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.

What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.

Anyone?|||

I am having the same issue and am unable to fix. My parent parameter is also a multi select. The only way the child values that are selected by default change is when the parent parameter used before the changed is no longer selected. I still haven't discovered a solution.

|||

I am running RS2005, SP2. I have the same problem with slightly different scenario. I have two multi-value parameters, parameter B depends on parameter A. A contains value x, y z. When value x is selected in parameter A, parameter B is refreshed with a matching list where all values selected. However, when I add y to the selection for parameter A, parameter B refreshed with a matching list for both x and y, but only those values associated with x are selected.

Is this a bug? If not how can I do to fix the problem?

Thanks.

SouBee

|||I now have a scenario same as SouBee's with the same result. Can someone tell me if there is a way to fix this?|||

I just found out that this was reported in January 07 and supposedly fixed in SP2. I am running SQL Server 2005 Standard edition SP2. I still have this issue. Was this fixed in Enterprise edition only? When will this be fixed in standard edition?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249637

|||I have enterprise and developer. Same issues. Doens't seem like it fixed it.|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032|||Perhaps you can send them that feedback

Default Values does not include all the values (Cascading Parameters)

A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.

One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).

If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.

What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.

Anyone?|||

I am having the same issue and am unable to fix. My parent parameter is also a multi select. The only way the child values that are selected by default change is when the parent parameter used before the changed is no longer selected. I still haven't discovered a solution.

|||

I am running RS2005, SP2. I have the same problem with slightly different scenario. I have two multi-value parameters, parameter B depends on parameter A. A contains value x, y z. When value x is selected in parameter A, parameter B is refreshed with a matching list where all values selected. However, when I add y to the selection for parameter A, parameter B refreshed with a matching list for both x and y, but only those values associated with x are selected.

Is this a bug? If not how can I do to fix the problem?

Thanks.

SouBee

|||I now have a scenario same as SouBee's with the same result. Can someone tell me if there is a way to fix this?|||

I just found out that this was reported in January 07 and supposedly fixed in SP2. I am running SQL Server 2005 Standard edition SP2. I still have this issue. Was this fixed in Enterprise edition only? When will this be fixed in standard edition?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249637

|||I have enterprise and developer. Same issues. Doens't seem like it fixed it.|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032|||Perhaps you can send them that feedback

Default Values does not include all the values (Cascading Parameters)

A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.

One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).

If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.

What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.

Anyone?|||

I am having the same issue and am unable to fix. My parent parameter is also a multi select. The only way the child values that are selected by default change is when the parent parameter used before the changed is no longer selected. I still haven't discovered a solution.

|||

I am running RS2005, SP2. I have the same problem with slightly different scenario. I have two multi-value parameters, parameter B depends on parameter A. A contains value x, y z. When value x is selected in parameter A, parameter B is refreshed with a matching list where all values selected. However, when I add y to the selection for parameter A, parameter B refreshed with a matching list for both x and y, but only those values associated with x are selected.

Is this a bug? If not how can I do to fix the problem?

Thanks.

SouBee

|||I now have a scenario same as SouBee's with the same result. Can someone tell me if there is a way to fix this?|||

I just found out that this was reported in January 07 and supposedly fixed in SP2. I am running SQL Server 2005 Standard edition SP2. I still have this issue. Was this fixed in Enterprise edition only? When will this be fixed in standard edition?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249637

|||I have enterprise and developer. Same issues. Doens't seem like it fixed it.|||https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=268032|||Perhaps you can send them that feedback