Altering objects

This is a question regarding a general problem of scripting DB changes\alteration:

I'm writing script which contain few (lets say 2) DDL statements, which depend one on the other.

Each statement has it own "Go" command afterwards, for executing it.

I'm writing a script to alter a field and make it not null:

"Alter <table name> alter column <column name> int not null"

, so I can define it afterwards as a primary key, and the next statement add a primary key constraint on this filed:

Alter table <table name>

ADD CONSTRAINT <name of constraint> PRIMARY KEY (name of field).

It all goes fine if I run the statements separatelly !!, but when I try to run them in a script, there's an error message saying that a nullable field cannot be defined as primary key (I don't have the exact syntax here, I'm on a few days vacation).

It seems like the sql server dosen't recognize "quick enough" the first alter of the field, to become not null, so it can allow the field to be primary key.

Is it a problem of unupdated system tables ?

It seems like this is a general problems, because it happens to me also ina nother case, when I try to disable a table trigger, so I can delete one of it's records (the trigger dosen't allow deleting records).

Please support.

Guy

Is it a case of a missing [ GO ] between the ALTER statements, or missing parentheses around the column name?

This seems to work without error.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
MyData varchar(20),
MyCol2 char(1)
)
GO

ALTER TABLE MyTable
ALTER COLUMN MyCol2 int NOT NULL
GO

ALTER TABLE MyTable
ADD CONSTRAINT PK_MyCol2 PRIMARY KEY ( MyCol2 )
GO

|||

Guy, this is interesting - particularly as you mention that you are including GO between each statement.

Could you post your complete script to the forum for review?

Thanks

Chris

|||

Arnie,

Thanks for your reply.

The code you published is almost the same I have, except for the create table which dosen't exists in my code (in my case the table is laready exists), and also - in my script, there are few more statements. Still - dosen't work.

I'm actually looking for maybe a DBCC\system command, which can update the status of the relevant sys tables, maybe something like the DBCC UPDATEUSAGE is doing to the info of the sp_spaceused, to make the sysindexes synch with the actual data in the table. Any other solution will be helpfull.

|||

Chris,

Thank you for your reply.

I don't have the exact code here (I'm on a few days vacation), but what I generally do is (pseudo code)

Start transaction

statement 1

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

statment 2

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

errorhandle:

rollback transction

commit transaction

As I mentioned, the code run with no problems if I run it step by step, and if I fix the is null statement separatelly (run it before, seperatelly, to make it not null), then the all script run with no problems.

|||

Since the table exists, it would be helpful to post both the table DDL as well as the actual procedure code.

No, there is not a DBCC command that would do what you are asking.

The problem apparantly exists in the information we can't see.

|||

The problem appears to be due to the following:

The GO immediately after each of your IF @.@.ERROR checks. You're trying to jump to a label (errorhandle) which is declared outside of the current batch. Labels can only be referenced within the batch in which they are declared.