drop not null

hi

i'm using mssql server 2000

i want to remove a not null column constraint from the column
answertext in table answertext.

i tried the following line
ALTER TABLE AnswerText ALTER COLUMN AnswerText DROP NOT NULL;

it didn't work and this error message apeared (sorry about the german)
Server: Nachr.-Nr. 156, Schweregrad 15, Status 1, Zeile 6
Falsche Syntax in der Nhe des NOT-Schlsselwortes.

is there a way to drop not null column constraint or do i have to save
the data, drop the table and recreate it?

i created the table with the following ddl code
CREATE TABLE AnswerText(
...
,AnswerText VARCHAR(512) NOT NULL
...
)

tanks for your helpcreate table foo (blah varchar(10) not null)

alter table foo alter column blah varchar(10)null

I found the best way to learn stuff like this is either find it in BOL
or run prfiler on myself and do it in enterprise manager and then look
at the syntax.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Drop Column with Constraint

Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais

ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?

Seems your code is OK:

IF OBJECT_ID('T1') IS NOT NULL
drop table T1;

IF OBJECT_ID('T2') IS NOT NULL
drop table T2;

Create Table T1 (
num int primary key,
[Name] sysname
)

Create Table T2 (
Id int primary key,
num int,
Constraint T2_FK Foreign Key(num) References T1(num)
)

BEGIN TRANSACTION
ALTER TABLE T2 DROP CONSTRAINT T2_FK
ALTER TABLE T2 DROP COLUMN num;
COMMIT

The reason it require drop constraint first, is because T2_FK depends on column num,
Just like you can't drop a table if the table reference it not droped.

Thanks,

zuomin

|||

It should be work because the ALTER TABLE has an implicit transactions. More , I tested your situations and the things worked good. I thing your error come to the other part.

|||Yes my mistake. The error was coming from default constraint.. nevermind it! Tks for your help!

Drop a linked server

HI Freinds,
SQ 2000
I had a transaction replication before with a linked server.
I stoped the replication completely and now I am trying to drop linked
server.
But it doesn't le me to do so, Complains about the server still in
replication error 20583
How can I drop te linked server ?
Thanks,
PatAre you using that server as a publisher or subscriber?
Ed
"Patrick" wrote:

> HI Freinds,
> SQ 2000
> I had a transaction replication before with a linked server.
> I stoped the replication completely and now I am trying to drop linked
> server.
> But it doesn't le me to do so, Complains about the server still in
> replication error 20583
> How can I drop te linked server ?
> Thanks,
> Pat
>
>|||look at sp_dropserver in bol
Ed
"Patrick" wrote:

> HI Freinds,
> SQ 2000
> I had a transaction replication before with a linked server.
> I stoped the replication completely and now I am trying to drop linked
> server.
> But it doesn't le me to do so, Complains about the server still in
> replication error 20583
> How can I drop te linked server ?
> Thanks,
> Pat
>
>