cascading parameters

In cascading parameters if we change the value of parent parameter then the
child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
automatically as default.
--
asharmaUse query and dont hard code then a post back happens everytime.
Amarnath,MCTS
"asharma2004" wrote:
> In cascading parameters if we change the value of parent parameter then the
> child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
> automatically as default.
> --
> asharma|||Hi! Actually it is a 3-level dependency. Here it is..
Param_head => Country = Select country from country [single valued param]
Param1=> Region =Select region from region where country in (@.country) [Multi]
Param2=> City= Select city from cities where region in (@.region)
Let's say I selected USA for Country. For this case, Region should display
all the states in USA and City should display all the Cities in USA in
default. However, when I select just one State (i.e California), default
values on City are correct and all selected, but when I try to add another
state, i.e Utah, City displays all the cities in Utah and California but only
California cities are selected.
I could not get around this. Is this a bug or something?
Thanks!
--jarm
"Wei Lu [MSFT]" wrote:
> Hello Jarm,
> Could you please let me know the query you use for those 2 parameters?
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Jarm,
Yes, I reproduce this issue.
This is because after you change the first parameter, the report will not
refresh so that the default value for the second parameter will not refresh.
The only time that dependent parameters are updated is when an upstream
parameter change causes dependent parameter selections to be invalid. This
satisfies the vast majority of cases where users do not an upstream
parameter change to blow away their other settings (consider a report with
many parameters, all of which might need to be reset if the user decides to
make a change to one of the upstream values).
We'll consider doing this in a future release.
Thank you for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community 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.

Cascade set to null on delete.

Hi all,

I am using SQL Server 2000 and am trying to perform a basic delete query on a table called ClientType. The only child table of this is called Client. The relationship between the tables has a cascade action of cascade update and when I try to perform the delete operation, I get the error "DELETE statement conflicted with COLUMN REFERENCE constraint". The foreign key field accepts nulls and has a defauilt value of null. Now, am I being completely dense or shouldn't the cascade update set the foreign key values to null?

Regards,

Stephen.

Could you please send over the DDL for the table and the constraints, thanks.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Here you go:
...

CREATE TABLE [dbo].[ClientType] (
[ClientTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[Type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Client] (
[ClientId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[ClientTypeId] [int] NULL ,
[ReferenceNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[DateRegistered] [datetime] NULL ,
[Address1] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address3] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone3] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Position] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InternalOverview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CV] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HoldingCompanyName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] WITH NOCHECK ADD
CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
(
[ClientTypeId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] ADD
CONSTRAINT [DF_ClientType_ObjectVersion] DEFAULT (0) FOR [ObjectVersion]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF_Client_ObjectVersion] DEFAULT (0) FOR [ObjectVersion],
CONSTRAINT [DF_Client_ClientTypeId] DEFAULT (null) FOR [ClientTypeId]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [FK_Client_ClientType] FOREIGN KEY
(
[ClientTypeId]
) REFERENCES [dbo].[ClientType] (
[ClientTypeId]
) ON UPDATE CASCADE
GO

...

Regards,

Stephen.

|||OK, you just defined the Cascade on the update, if you delete a row and the child table contains rows for that parent row and you did not define a cascade delete on the parent table, this error message will come up.

You wil have to add: ON DELETE CASCADE

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the response. I do understand that adding cascade delete would get rid of the error but will that not result in the child rows being deleted? I only want the foreign keys to be set to null, not have the entire related record dropped from the table.

Regards,

Stephen.

|||OK, then you will have to use the ON DELETE SET NULL.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||OK. I gave that a go, but I just get an error "incorrect syntax near the keyword 'SET'".|||

Sorry, I re-read your first post and saw that you are using SQL Server 2k. SET NULL is a new feature for SQL 2k5. In SQL Server 2000 you probably would use no constraint in that case and do the work with triggers, in that case an update trigger.

Sorry for confusing you :-)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

OK. I'll look into that.

Thanks for your help. Its much appreciated.

Regards,

Stephen.

Cascade delete relationship slower?

I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:

> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.

> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.

> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>

Cascade Delete Question

I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.

tblUsers
-----
ID_PK
Fname
Lname
...

tblUser_Phone
------
ID_FK
Phone
...

tbUser_Email
------
ID_FK
Email
...

If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?

Thanks in advance.I believe cascade delete only deletes children records, so the parent table would be safe.|||I believe you are correct. Cascading only travels down the hierarchy, not up.

Cascade Delete

Hi,
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer