July 15, 2009 at 1:06 am
How can disabled and enable constraints in sql Server 2000
July 15, 2009 at 4:11 am
If the future pleas post questions regarding to SQL Server 2000 in a SQL Server 2000 forum and not in a SQL Server 2005 forum.
You can do it with alter table statement . If you’ll look up alter table statement in BOL (the help file that is installed when you install SQL Server), you’ll get an explanation and an example that enable/disable constraint.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2009 at 10:01 am
When I load a test database using dts in ss 2000, I run the following first:
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
After the dts is done, I run:
sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
go
The use of the word CHECK twice in the re-enabling was something that I had to track down so that my Sql Compare would display correctly.
There should be a word of caution about disabling contraints. Since this is run on a test database and is run after hours, I feel comfortable using it.
Steve
July 17, 2009 at 6:08 am
Hi
If i use sp_msforeachtable commands within DTS as one of the step, its not unchecking the constraints. If i execute it in query analyser its working.
why?
July 17, 2009 at 6:41 am
I'm not sure about doing it within Dts, but if you create a Sql Server Agent job with 3 steps it works. The first step is disabling constraints, the second step runs the Dts, and the third step re-enables the constraints.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply