disable constraints

Hi,
I disabled all constraints on a database before using DTS by running the
following command:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
When comparing the source tables and the destination tables after the DTS I
found that some DEFAULT constraints were not disabled. The result was that
source columns populated with NULLs got populated on the destinatin columns
with the dafult values as defined in the constraints.
Thanks,
YanivYou cannot disable DEFAULT, UNIQUE or PRIMARY KEY constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl
..
> Hi,
> I disabled all constraints on a database before using DTS by running the f
ollowing command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
I found that some
> DEFAULT constraints were not disabled. The result was that source columns
populated with NULLs got
> populated on the destinatin columns with the dafult values as defined in t
he constraints.
>
> Thanks,
> Yaniv
>|||Hi, Yaniv
I think you should have first to drop contstraint (DEFAULT in your case) ,
insert the data and then re-create contraints
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I disabled all constraints on a database before using DTS by running the
> following command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
> I found that some DEFAULT constraints were not disabled. The result was
> that source columns populated with NULLs got populated on the destinatin
> columns with the dafult values as defined in the constraints.
>
> Thanks,
> Yaniv
>|||Thank you all,
This is what I did; I droped and recreated the defaults but I wanted to
find out why a default constraint was not diabled.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OjQBmIp7FHA.3752@.tk2msftngp13.phx.gbl...
> Hi, Yaniv
> I think you should have first to drop contstraint (DEFAULT in your case)
> , insert the data and then re-create contraints
>
>
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
>|||See my earlier reply. You cannot disable default constraints. I.e., default,
pk and uq constraints
are not disabled when you disable "ALL" constraints for a table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:OPF1Tfp7FHA.3388@.TK2MSFTNGP11.phx.gbl
..
> Thank you all,
> This is what I did; I droped and recreated the defaults but I wanted to f
ind out why a default
> constraint was not diabled.
>
> --
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:OjQBmIp7FHA.3752@.tk2
msftngp13.phx.gbl...
>|||hi guys try the attached file to dynamically drop a default constraint on a
column in a table.