Deleting a column DEFAULT....

[Giacomo]

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO
it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.
Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

[Erland Sommarskog]

Giacomo (no*****@grazie .it) writes:

P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I don't
have the time to post the exact code. You will need to read the system
tables to get the name of the default and then use dynamic SQL to run
ALTER TABLE DROP CONSTRAINT. Best practice is to always name your defaults,
so that you don't run into this problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

Giacomo,

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettag lio.OreStraordi nario', '__old', 'COLUMN'

ALTER TABLE PlanningDettagl io
ADD OreStraordinari o DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettagl io
SET OreStraordinari o = __old

ALTER TABLE PlanningDettagl io DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan
Giacomo wrote:

>
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

[Erland Sommarskog]

Erland Sommarskog (es****@sommars kog.se) writes:

Giacomo (no*****@grazie .it) writes:

>P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I
don't have the time to post the exact code. You will need to read the
system tables to get the name of the default and then use dynamic SQL to
run ALTER TABLE DROP CONSTRAINT. Best practice is to always name your
defaults, so that you don't run into this problem.

So this is how you do it:

DECLARE @default sysname
SELECT @default = object_name(cde fault)
FROM syscolumns
WHERE id = object_id('your table')
AND name = 'yourcolumn'
EXEC ('ALTER TABLE yourtable DROP CONSTRAINT ' + @default)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Giacomo]

Thank you very much,
it works perfectly! :)

Giacomo

[sonu]

Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>, table
tablename,colum n <columnname>

Kindly give solution of this problem.
sonika.
Gert-Jan Strik wrote:

Giacomo,

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettag lio.OreStraordi nario', '__old', 'COLUMN'

ALTER TABLE PlanningDettagl io
ADD OreStraordinari o DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettagl io
SET OreStraordinari o = __old

ALTER TABLE PlanningDettagl io DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan
Giacomo wrote:

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

[Erland Sommarskog]

sonu (so********@gma il.com) writes:

Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>, table
tablename,colum n <columnname>

Kindly give solution of this problem.

I'm sorry, but you will have to try again. To start with, your problem
appears to be completely different from what Giacomo had, so please start
a new thread.

As for your problem, you run a DELETE statement and a constraint fires.
I assume that this is a foreign key constraint. Usually foreign keys
are a good thing, as it prevents from you from doing bad things like
deleting a product which are listed on open order items. Then you talk
about diagrams, but it's impossible to understand what you are talking
about. Are you talking about database diagrams in an SQL tool, or is one
of your tables called Diagrams? Please post again, and make an effor to
explain your problem clearly. It helps if you includ the definition of
the involved tables, and the SQL you are having problem with.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx