Re: ALTER COLUMN
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/26/05
- Next message: Barce5: "Convert text to number"
- Previous message: Jacco Schalkwijk: "Re: Syntax checker"
- In reply to: Hugo Kornelis: "Re: ALTER COLUMN"
- Next in thread: Steve Kass: "Re: ALTER COLUMN"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 Jan 2005 09:43:19 -0600
If you want to make sure that both alters work, or neither, you can use a
transaction:
drop table test
go
create table test
(
column1 varchar(10) not null,
column2 varchar(10) not null
)
go
insert into test
values (1,10)
insert into test
values (1,'FRED')
go
select * from test
go
/*
returns
column1 column2
---------- ----------
1 10
1 FRED
*/
go
begin transaction
alter table test
alter column column1 int not null
alter table test
alter column column2 int
commit transaction
go
/* error returned:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'FRED' to a column of data type
int.
The statement has been terminated.
*/
go
select * from test --doesn't change
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:1mdfv0ll9tnrvh32qt4oejgrvpobsg3ah7@4ax.com... > On Wed, 26 Jan 2005 10:20:15 -0500, Vik wrote: > >>What is correct syntax to alter a few columns in one query like this: >> >>ALTER TABLE dbo.Inventory >>ALTER COLUMN >> Fld1 varchar(50) NULL, >> Fld2 int NULL >> >>I get "Incorrect syntax near ',' " error message on this query. > > Hi Vik, > > You can alter only one caolumn at a time. > > ALTER TABLE dbo.Inventory > ALTER COLUMN > Fld1 varchar(50) NULL > ALTER TABLE dbo.Inventory > ALTER COLUMN > Fld2 int NULL > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Barce5: "Convert text to number"
- Previous message: Jacco Schalkwijk: "Re: Syntax checker"
- In reply to: Hugo Kornelis: "Re: ALTER COLUMN"
- Next in thread: Steve Kass: "Re: ALTER COLUMN"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
