I have a contacts table that has around 30 tables related to it (which
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
Showing posts with label dependent. Show all posts
Showing posts with label dependent. Show all posts
Thursday, March 22, 2012
deleting dependent rows in SQL 2000
I have a contacts table that has around 30 tables related to it (which
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
Sunday, March 11, 2012
Deletes From Multiple Tables.
Hello everybody,
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.
Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)
Absolute amateurs.
What advice would you give them?|||What advice would you give them?
Well, it realy sounds like a request for homework...but
DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''
SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')
SELECT @.sql
--EXEC(@.sql)|||Thanks dear friends.
Brett, it's not a homework at all.
In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.
Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]
I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.
It seems without a recursive loop , we cannot achieve this.
Can you give me any idea if we can achieve this using a simple loop.
I want to delete all the dependent records to the Nth level.
Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".
I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.
Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.
I would like to thank everyone for chipping in with helpful suggestions and valuable advice.
thanks once again.
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.
Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)
Absolute amateurs.
What advice would you give them?|||What advice would you give them?
Well, it realy sounds like a request for homework...but
DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''
SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')
SELECT @.sql
--EXEC(@.sql)|||Thanks dear friends.
Brett, it's not a homework at all.
In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.
Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]
I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.
reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.
Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.
It seems without a recursive loop , we cannot achieve this.
Can you give me any idea if we can achieve this using a simple loop.
I want to delete all the dependent records to the Nth level.
Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".
I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.
Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.
I would like to thank everyone for chipping in with helpful suggestions and valuable advice.
thanks once again.
Friday, March 9, 2012
Deleted SQL Resouce
i was deleting a disk resource from our cluster, the sql resource depended on that disk, when i was asked about deleting the dependent resources i clicked the delete option, so now i dont have any sql resourse. now i can′t see my SQL anymore even whe i
try to create a sql resource manually.
What can i do?
Thanks
I think you should
- copy the mdf and ldf file off to a safe location
- uninstall sql server from the cluster
- reinstall sql server to the cluster
- attach back the copied mdf and ldf files.
I don't think you can create the sql server resource without going through
the setup, but I could be wrong.
Vikram
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i cant
see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
|||Hi...
create SQL resource using below scripts.
It will create 3 sql resources and set proper private property values.
before you execute scripts, you should change what i comment
It will help you. and really works!!!
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server" /Create /Group:"SQLVS1 Group" /Type:"SQL
Server" <= Change group name
cluster . Resource "SQL Server" /AddDependency:"SQL Network Name(SQLVS1)" <=
Change network name
cluster . Resource "SQL Server" /AddDependency:"Physical Disk(Disk S
" <=
Change Disk name
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Rep lication"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\SQL serverAgent"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Clu ster"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\MSS QLSERVER"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\PRO VIDERS"
cluster . Resource "SQL Server" /Priv VirtualServerName="SQLVS1" <= Change
SQL Server VS name
cluster . Resource "SQL Server" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Agent" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Agent" /Create /Group:"SQLVS1 Group"
/Type:"SQL Server Agent" <= Change group name
cluster . Resource "SQL Server Agent" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Agent" /Priv VirtualServerName="SQLVS1" <=
Change SQL Server VS name
cluster . Resource "SQL Server Agent" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server Agent" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Fulltext" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Fulltext" /Create /Group:"SQLVS1 Group"
/Type:"Microsoft Search Service Instance" <= Change group name
cluster . Resource "SQL Server Fulltext" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationName="SQLServer"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationPath="S:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA" <= Change Drive and folder name
where SQL Database installs
Cheolwon Choi
Senior Consultant / Consulting Biz Unit / Feelanet Co., Ltd.
Microsoft MCSE/MCSA/MCT, HP MasterASE/ACI
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i
can′t see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
try to create a sql resource manually.
What can i do?
Thanks
I think you should
- copy the mdf and ldf file off to a safe location
- uninstall sql server from the cluster
- reinstall sql server to the cluster
- attach back the copied mdf and ldf files.
I don't think you can create the sql server resource without going through
the setup, but I could be wrong.
Vikram
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i cant
see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
|||Hi...
create SQL resource using below scripts.
It will create 3 sql resources and set proper private property values.
before you execute scripts, you should change what i comment
It will help you. and really works!!!
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server" /Create /Group:"SQLVS1 Group" /Type:"SQL
Server" <= Change group name
cluster . Resource "SQL Server" /AddDependency:"SQL Network Name(SQLVS1)" <=
Change network name
cluster . Resource "SQL Server" /AddDependency:"Physical Disk(Disk S
" <=Change Disk name
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Rep lication"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\SQL serverAgent"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Clu ster"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\MSS QLSERVER"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\PRO VIDERS"
cluster . Resource "SQL Server" /Priv VirtualServerName="SQLVS1" <= Change
SQL Server VS name
cluster . Resource "SQL Server" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Agent" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Agent" /Create /Group:"SQLVS1 Group"
/Type:"SQL Server Agent" <= Change group name
cluster . Resource "SQL Server Agent" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Agent" /Priv VirtualServerName="SQLVS1" <=
Change SQL Server VS name
cluster . Resource "SQL Server Agent" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server Agent" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Fulltext" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Fulltext" /Create /Group:"SQLVS1 Group"
/Type:"Microsoft Search Service Instance" <= Change group name
cluster . Resource "SQL Server Fulltext" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationName="SQLServer"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationPath="S:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA" <= Change Drive and folder name
where SQL Database installs
Cheolwon Choi
Senior Consultant / Consulting Biz Unit / Feelanet Co., Ltd.
Microsoft MCSE/MCSA/MCT, HP MasterASE/ACI
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i
can′t see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
Subscribe to:
Posts (Atom)