Disk Backup stuck..NEED HELP

SQL 2000, Windows 2000 Server. I went to backup a
database last night to an existing file, choosing append.
7 hours later, the backup is still only 2 bars completed.
I then chose to "cancel" the backup procedure so that I
could create another file...I hit cancel and the backup
dialog still states it is running...Can I go into task
manager and kill sqlserver process? Will this cause
undesired effects?
People need to write to thsi database and I need to back
it up before they do.
TIA!!!!
~PJTo establish wheter the backup is still active you could use sp_who2 active.
I suggest running your backup in query analyser rather than EM, you can then
see the percentages complete. The following reports every 1% complete.
BACKUP DATABASE msdb TO DISK = 'C:\msdb.bak' WITH INIT , STATS = 1
--
HTH
Ryan Waight, MCDBA, MCSE
"PJ" <p.jones@.xsb.com> wrote in message
news:0e6301c39256$80eb8fb0$a401280a@.phx.gbl...
> SQL 2000, Windows 2000 Server. I went to backup a
> database last night to an existing file, choosing append.
> 7 hours later, the backup is still only 2 bars completed.
> I then chose to "cancel" the backup procedure so that I
> could create another file...I hit cancel and the backup
> dialog still states it is running...Can I go into task
> manager and kill sqlserver process? Will this cause
> undesired effects?
> People need to write to thsi database and I need to back
> it up before they do.
> TIA!!!!
> ~PJ|||I also wen into EM from another server and tried killing
the processes related to this problem, though I received
no error when "killing" the process, those processes did
not stop...
TIA!
~PJ
>--Original Message--
>SQL 2000, Windows 2000 Server. I went to backup a
>database last night to an existing file, choosing
append.
>7 hours later, the backup is still only 2 bars
completed.
>I then chose to "cancel" the backup procedure so that I
>could create another file...I hit cancel and the backup
>dialog still states it is running...Can I go into task
>manager and kill sqlserver process? Will this cause
>undesired effects?
>People need to write to thsi database and I need to back
>it up before they do.
>TIA!!!!
>~PJ
>.
>|||Thanks on the suggestion of doing it via query analyzer,
from now on I will
.
I opened EM and I look under process info and I see there
are 2 things SPID52 has. Under database it shows (first)
no database context - (second)CV-0803, then under
command, It shows killed/rollback for both. Should I
wait? Can I kill SQL Server ? Can I reboot server? What
should I do? I am really really stuck.
TIA!!!
~PJ
>--Original Message--
>To establish wheter the backup is still active you could
use sp_who2 active.
>I suggest running your backup in query analyser rather
than EM, you can then
>see the percentages complete. The following reports
every 1% complete.
>BACKUP DATABASE msdb TO DISK = 'C:\msdb.bak' WITH INIT ,
STATS = 1
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"PJ" <p.jones@.xsb.com> wrote in message
>news:0e6301c39256$80eb8fb0$a401280a@.phx.gbl...
>> SQL 2000, Windows 2000 Server. I went to backup a
>> database last night to an existing file, choosing
append.
>> 7 hours later, the backup is still only 2 bars
completed.
>> I then chose to "cancel" the backup procedure so that I
>> could create another file...I hit cancel and the backup
>> dialog still states it is running...Can I go into task
>> manager and kill sqlserver process? Will this cause
>> undesired effects?
>> People need to write to thsi database and I need to
back
>> it up before they do.
>> TIA!!!!
>> ~PJ
>
>.
>

Discovering SQL databases

Hi, I'm in the process of migrating / decommissioning a series of SQL
servers.
Is there / are there any tools to sniff out any existing eroneous SQL
databases which people may have created by themselves?
TIAThere are several ways to accomplish the task. Here are a couple ideas.
User SQLPing. Read about its usage here:
http://www.sqlteam.com/item.asp?ItemID=5403
A simple bat file idea:
http://www.databasejournal.com/feat...cle.php/3397981
And of course, any decent network scanning tool.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <lbwer@.blah.com> wrote in message
news:uzQExf$rGHA.2464@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm in the process of migrating / decommissioning a series of SQL
> servers.
> Is there / are there any tools to sniff out any existing eroneous SQL
> databases which people may have created by themselves?
> TIA
>|||I suspect you mean sniff out instances of SQL Server, not databases.
I use SQL Recon: http://www.specialopssecurity.com/labs/sqlrecon/
Great utility to find any instance running on the network.
"Stu" <lbwer@.blah.com> wrote in message
news:uzQExf$rGHA.2464@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm in the process of migrating / decommissioning a series of SQL
> servers.
> Is there / are there any tools to sniff out any existing eroneous SQL
> databases which people may have created by themselves?
> TIA
>sql

Discover relationships between existing tables of a database

Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.|||I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.

Disarming IDENTITY generation

Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can’t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using

SET IDENTITY_INSERT biblio.dbo.authors OFF

Is there an equivalent in SQL Ev?

My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?

Bill,

no, you cannot disable the generation of identity values as you insert records into a table with an identity column, but you can alter the seed and increment values on the column using ALTER TABLE ALTER COLUMN standard syntax. For example, if the rows you are putting into the empty table begin with identity values starting at 1000, just create the table with this identity columns seed set to 1000.

if you want to know the last value of an identity column after one or more inserts, select the @.@.IDENTITY function against the table.

if there is any chance this table will be in an RDA or merge replication synchronization relationship with a server, I would strongly suggest you use uniqueidentifiers instead of identity columns on your table. this will avoid a lot of admin and possible conflicts if you are in a multi-user environment.

as I follow your posts, it sure sounds to me like you are in the process of creating a starter SQL Everywhere database based on a server-side database. Have you considered just setting up a quick merge replication and using the option to have the SQL Everywhere database created dynamically from the server upon initial replication (AddSubscription(AddOption.CreateDatabase))? I have posted a lot of info on this and done a few MSDN webcasts on the topic, so search this forum for 'starter database' for more details. this would solve your issues with identity column values and also with FK creation.

Darren

|||

Actually, I'm trying to write an EBook to supplement my new Hitchhiker's Guide to Visual Studio and SQL Server book. So far, all I've found is a million issues...

The current problem is that the ID values in the rows on the source table are joined logically to other tables. That is, the Author_ID is in the Title_Author table so it can't be arbitrarily changed without changing the other table(s) that are linked to the Author_ID. The same goes for the other tables. This makes it tough to load Identity values at all into a SQLCE database--I guess I'll have to give up on that approach. However, I don't see that using uniqueidentifiers will help unless I can go back and rebuild the PK/FK relationships on the source tables using them (a PIA).

Yes, I can see that I need to go back two squares and try to build a replication scenario.

My inital tests with SSIS did not work so that's a non-starter. I found that the host database uses datatypes not supported by SQL Ev including VarChar and Timestamp which chokes SSIS.

I expect I'll have to create a custom server-side database that's specifically designed to deal with all of these issues.

I'll check out your content. I would like to check out the SQL CF MVP pages (I'm also an MVP) can you drop a private note pointing to where you hang out?

Bill

|||

sure Bill - feel free to shoot me an email - I'm googlable and in the MVP directory as well.

-ds

|||

My two cents... With SQL CE 2.0 you could actually overwite the identity values through the OLE DB provider (a bit like in Access but this engine goes a bit further and even updates the seed). Microsoft dropped this behavior with SQL Mobile and this posed a big challenge for my database copy / sync tools. What now? Turns out, the only solution is to ALTER TABLE and set the seed to the next value to be inserted for every row. You will also have to reset the identity properties to what they are supposed to be at the end of the export procedure. To make all of this work, you also have to make sure you close the base table cursor (if any) before altering the identity value. This means more work for a SqlCeResultSet, but not for an INSERT command.

It's a lot of development work but the code is not slower than in the SQL CE 2.0 case, so it's quite workable and it is the solution I actually adopted.

|||

Interesting solution. I guess it would only be more convoluted if it involved live chickens.

I'm not sure I'll recommend this in my book other than mention it as an alternative...

Thanks

|||

I don't think that was going to work because the SQL Server 2005 Mobile Edition does not support Alter table as a SQL Statement .At leat I could not make it work.Right now I'm stucked on that problem and I would like to read other opinions

Nelson

|||

I would like to know how can I make it work using the alter table statement since I tried and it did not work.I tried using the SQL Server Editor in Visual Studio and at runtime .None of those options seems to work.

Thanks in advance

Disarming IDENTITY generation

Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can’t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using

SET IDENTITY_INSERT biblio.dbo.authors OFF

Is there an equivalent in SQL Ev?

My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?

Bill,

no, you cannot disable the generation of identity values as you insert records into a table with an identity column, but you can alter the seed and increment values on the column using ALTER TABLE ALTER COLUMN standard syntax. For example, if the rows you are putting into the empty table begin with identity values starting at 1000, just create the table with this identity columns seed set to 1000.

if you want to know the last value of an identity column after one or more inserts, select the @.@.IDENTITY function against the table.

if there is any chance this table will be in an RDA or merge replication synchronization relationship with a server, I would strongly suggest you use uniqueidentifiers instead of identity columns on your table. this will avoid a lot of admin and possible conflicts if you are in a multi-user environment.

as I follow your posts, it sure sounds to me like you are in the process of creating a starter SQL Everywhere database based on a server-side database. Have you considered just setting up a quick merge replication and using the option to have the SQL Everywhere database created dynamically from the server upon initial replication (AddSubscription(AddOption.CreateDatabase))? I have posted a lot of info on this and done a few MSDN webcasts on the topic, so search this forum for 'starter database' for more details. this would solve your issues with identity column values and also with FK creation.

Darren

|||

Actually, I'm trying to write an EBook to supplement my new Hitchhiker's Guide to Visual Studio and SQL Server book. So far, all I've found is a million issues...

The current problem is that the ID values in the rows on the source table are joined logically to other tables. That is, the Author_ID is in the Title_Author table so it can't be arbitrarily changed without changing the other table(s) that are linked to the Author_ID. The same goes for the other tables. This makes it tough to load Identity values at all into a SQLCE database--I guess I'll have to give up on that approach. However, I don't see that using uniqueidentifiers will help unless I can go back and rebuild the PK/FK relationships on the source tables using them (a PIA).

Yes, I can see that I need to go back two squares and try to build a replication scenario.

My inital tests with SSIS did not work so that's a non-starter. I found that the host database uses datatypes not supported by SQL Ev including VarChar and Timestamp which chokes SSIS.

I expect I'll have to create a custom server-side database that's specifically designed to deal with all of these issues.

I'll check out your content. I would like to check out the SQL CF MVP pages (I'm also an MVP) can you drop a private note pointing to where you hang out?

Bill

|||

sure Bill - feel free to shoot me an email - I'm googlable and in the MVP directory as well.

-ds

|||

My two cents... With SQL CE 2.0 you could actually overwite the identity values through the OLE DB provider (a bit like in Access but this engine goes a bit further and even updates the seed). Microsoft dropped this behavior with SQL Mobile and this posed a big challenge for my database copy / sync tools. What now? Turns out, the only solution is to ALTER TABLE and set the seed to the next value to be inserted for every row. You will also have to reset the identity properties to what they are supposed to be at the end of the export procedure. To make all of this work, you also have to make sure you close the base table cursor (if any) before altering the identity value. This means more work for a SqlCeResultSet, but not for an INSERT command.

It's a lot of development work but the code is not slower than in the SQL CE 2.0 case, so it's quite workable and it is the solution I actually adopted.

|||

Interesting solution. I guess it would only be more convoluted if it involved live chickens.

I'm not sure I'll recommend this in my book other than mention it as an alternative...

Thanks

|||

I don't think that was going to work because the SQL Server 2005 Mobile Edition does not support Alter table as a SQL Statement .At leat I could not make it work.Right now I'm stucked on that problem and I would like to read other opinions

Nelson

|||

I would like to know how can I make it work using the alter table statement since I tried and it did not work.I tried using the SQL Server Editor in Visual Studio and at runtime .None of those options seems to work.

Thanks in advance

Disabling replication on existing databases

Hi,
we are adding new verisons of databases to out SQL Instance. we want to
disable existing databases replication, with out effecting production db
replication process.
i think if u use GUI, SQL Server will disable all the prublications? i am
not sure this is correct or wrong.
please advise me, the scripts which should execute, perform and go smooth.
good luck for endeavors.
Thanks,
John.
It will drop publications and subscriptions.
If I were you, I would script out the publications and subscriptions, (right
click on your publication and select generate sql script to do this).
upgrade your database, and then reapply the scripts.
There is something bothering me about your post though - it sounds like you
are about to do this in a production environment. I would advise you to do
this in a test environment first to see what will happen.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.discussions.microsoft.com> wrote in message
news:29AC881F-3837-4BE8-BB9F-AFA8DF6CAB13@.microsoft.com...
> Hi,
> we are adding new verisons of databases to out SQL Instance. we want to
> disable existing databases replication, with out effecting production db
> replication process.
> i think if u use GUI, SQL Server will disable all the prublications? i am
> not sure this is correct or wrong.
> please advise me, the scripts which should execute, perform and go smooth.
> good luck for endeavors.
> Thanks,
> John.

disable foreign keys

Is there a way to disable foreign keys on tables without dropping them?
Unchecking "check existing data on creation" wont cut it here as the FK
already exists, I just want to delete from the table.
sql2k
TIA, ChrisR-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
From: Roman Rehak:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||ALTER DATABASE tblname
NOCHECK CONSTRAINT ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them? Unchecking "check existing
> data on creation" wont cut it here as the FK already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||With nocheck doesnt enable me to delete data on a table that is a parent to
another table.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||Hmmh, I thought i understood you ;-) You want to ignore inserting data, even
if there is no parent row in the other entity, but you want a Cascading
delete on the parent table ?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
> With nocheck doesnt enable me to delete data on a table that is a parent
> to another table.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>|||No. I just want to delete a parent, leaving the child untouched.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
> even if there is no parent row in the other entity, but you want a
> Cascading delete on the parent table ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent
>> to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>>
>|||Ok, thats kinda weird, but ok ;-)
Do this with a trigger, as the follwoing example:
CREATE TRIGGER TRG_DEL_ChildTabel ON ChildTable
FOR DELETE
AS
BEGIN
Delete from parenttable where ReferencedIdColumn IN
(Select ReferencedIdColumn From Deleted)
END
That should work,
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
>> even if there is no parent row in the other entity, but you want a
>> Cascading delete on the parent table ?
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
>> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent
>> to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>>
>>
>|||Disabling a foreign key constraint allow you to do just that:
USE tempdb
GO
CREATE TABLE t1(c1 int primary key)
CREATE TABLE t2(c1 int primary key)
GO
alter table t2
add constraint cnstr foreign key (c1) references t1(c1)
GO
alter table t2 nocheck constraint cnstr
go
insert into t1 values(1)
insert into t2 values(1)
delete from t1 where c1 = 1
select * from t1
select * from t2
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>> Hmmh, I thought i understood you ;-) You want to ignore inserting data, even if there is no
>> parent row in the other entity, but you want a Cascading delete on the parent table ?
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them? Unchecking "check
>> existing data on creation" wont cut it here as the FK already exists, I just want to delete
>> from the table.
>> sql2k
>> TIA, ChrisR
>>
>>
>

disable foreign keys

Is there a way to disable foreign keys on tables without dropping them?
Unchecking "check existing data on creation" wont cut it here as the FK
already exists, I just want to delete from the table.
sql2k
TIA, ChrisR-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
From: Roman Rehak:
http://sqljunkies.com/WebLog/roman/...01/30/7037.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||ALTER DATABASE tblname
NOCHECK CONSTRAINT ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...[
vbcol=seagreen]
> Is there a way to disable foreign keys on tables without dropping them? Un
checking "check existing
> data on creation" wont cut it here as the FK already exists, I just want t
o delete from the table.
> sql2k
> TIA, ChrisR
>[/vbcol]|||With nocheck doesnt enable me to delete data on a table that is a parent to
another table.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||Hmmh, I thought i understood you ;-) You want to ignore inserting data, even
if there is no parent row in the other entity, but you want a Cascading
delete on the parent table ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
> With nocheck doesnt enable me to delete data on a table that is a parent
> to another table.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||No. I just want to delete a parent, leaving the child untouched.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
> even if there is no parent row in the other entity, but you want a
> Cascading delete on the parent table ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>|||Ok, thats kinda weird, but ok ;-)
Do this with a trigger, as the follwoing example:
CREATE TRIGGER TRG_DEL_ChildTabel ON ChildTable
FOR DELETE
AS
BEGIN
Delete from parenttable where ReferencedIdColumn IN
(Select ReferencedIdColumn From Deleted)
END
That should work,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>|||Disabling a foreign key constraint allow you to do just that:
USE tempdb
GO
CREATE TABLE t1(c1 int primary key)
CREATE TABLE t2(c1 int primary key)
GO
alter table t2
add constraint cnstr foreign key (c1) references t1(c1)
GO
alter table t2 nocheck constraint cnstr
go
insert into t1 values(1)
insert into t2 values(1)
delete from t1 where c1 = 1
select * from t1
select * from t2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote i
n message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

disable foreign keys

Is there a way to disable foreign keys on tables without dropping them?
Unchecking "check existing data on creation" wont cut it here as the FK
already exists, I just want to delete from the table.
sql2k
TIA, ChrisR
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
From: Roman Rehak:
http://sqljunkies.com/WebLog/roman/a...1/30/7037.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>
|||ALTER DATABASE tblname
NOCHECK CONSTRAINT ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them? Unchecking "check existing
> data on creation" wont cut it here as the FK already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>
|||With nocheck doesnt enable me to delete data on a table that is a parent to
another table.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>
|||Hmmh, I thought i understood you ;-) You want to ignore inserting data, even
if there is no parent row in the other entity, but you want a Cascading
delete on the parent table ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
> With nocheck doesnt enable me to delete data on a table that is a parent
> to another table.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
|||No. I just want to delete a parent, leaving the child untouched.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
> even if there is no parent row in the other entity, but you want a
> Cascading delete on the parent table ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>
|||Ok, thats kinda weird, but ok ;-)
Do this with a trigger, as the follwoing example:
CREATE TRIGGER TRG_DEL_ChildTabel ON ChildTable
FOR DELETE
AS
BEGIN
Delete from parenttable where ReferencedIdColumn IN
(Select ReferencedIdColumn From Deleted)
END
That should work,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>
|||Disabling a foreign key constraint allow you to do just that:
USE tempdb
GO
CREATE TABLE t1(c1 int primary key)
CREATE TABLE t2(c1 int primary key)
GO
alter table t2
add constraint cnstr foreign key (c1) references t1(c1)
GO
alter table t2 nocheck constraint cnstr
go
insert into t1 values(1)
insert into t2 values(1)
delete from t1 where c1 = 1
select * from t1
select * from t2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>