MS SQL Server, Alter table alter column

Alter table alter column

Author	Message
Trac #1 / 8	Alter table alter column ALTER TABLE [dbo].[ecm] ALTER COLUMN [part_id] [varchar] (30) NOT NULL I am having a problem on the above statement in our production environment. This same statement worked correctly in our development and test environments, but failed in the production environment. The error message is Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'COLUMN'. Before attempting to run the command I dropped the indexes and constraints on the table. The owner is correct. Does anyone have any ideas? I was able to make the change through enterprise manager on the production box. Thanks!!
Wed, 24 Sep 2003 06:09:19 GMT

Brian Mora #2 / 8	Alter table alter column It would help if you provided an exact script of what you're doing, but... creating a fake table such as ... create table ecm (part_id varchar (10) not null) allows me to run your command ALTER TABLE [dbo].[ecm] ALTER COLUMN [part_id] [varchar] (30) NOT NULL is it possible that your production server is set to 6.5 compatability mode for some reason? << I was able to make the change through enterprise manager on the production box. If you watch SEM using SQL Profiler... you'll see that SEM actually drops and recreates the table... it's not really issuing the ALTER TABLE command... -- Brian Moran CrossTier SQL Server MVP SQL Server Magazine Columnist ALTER TABLE [dbo].[ecm] ALTER COLUMN [part_id] [varchar] (30) NOT NULL I am having a problem on the above statement in our production environment. This same statement worked correctly in our development and test environments, but failed in the production environment. The error message is Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'COLUMN'. Before attempting to run the command I dropped the indexes and constraints on the table. The owner is correct. Does anyone have any ideas? I was able to make the change through enterprise manager on the production box. Thanks!!
Wed, 24 Sep 2003 06:33:47 GMT

Kalen Delane #3 / 8	Alter table alter column Tracy Check the compatibility level of your database: exec sp_dbcmptlevel <database_name> will tell you. If you are in 6.5 level, ALTER COLUMN is not allowed. Change it to 70 or 80 (PLEASE make sure you state your version in future posts) with the following exec sp_dbcmptlevel <database_name>, 70 If by some chance you are actually using 6.5, the above procedure is not available and neither is the ALTER COLUMN syntax. Enterprise Manager is able to do it by completely dropping and rebuilding the table behind the scenes. HTH -- ------------------------------------- Kalen Delaney MCSE, SQL Server MCT, MVP www.InsideSQLServer.com Feed Someone for Free Today! www.thehungersite.com ALTER TABLE [dbo].[ecm] ALTER COLUMN [part_id] [varchar] (30) NOT NULL I am having a problem on the above statement in our production environment. This same statement worked correctly in our development and test environments, but failed in the production environment. The error message is Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'COLUMN'. Before attempting to run the command I dropped the indexes and constraints on the table. The owner is correct. Does anyone have any ideas? I was able to make the change through enterprise manager on the production box. Thanks!!
Wed, 24 Sep 2003 11:07:06 GMT

Tracy Hardma #4 / 8	Alter table alter column Thanks for the suggestions. We are running on 7.0 Our compatibility level shows as null on all 3 boxes. I will try setting it on the production box and see if it makes a difference. Thanks. * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Fri, 26 Sep 2003 20:12:46 GMT

Tibor Karasz #5 / 8	Alter table alter column Tracy, You set compatibility level per database, not per SQL Server. And I very much doubt that it can be NULL. Try below: exec sp_dbcmptlevel 'pubs' -- Tibor Karaszi, SQL Server MVP FAQ from Neil at: http://www.sqlserverfaq.com Please reply to the newsgroup only, not by email. Quote: > Thanks for the suggestions. We are running on 7.0 Our compatibility level shows as null on all 3 boxes. I will try setting it on the production box and see if it makes a difference. Thanks. Quote: > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Fri, 26 Sep 2003 20:25:31 GMT

Tracy Hardma #6 / 8	Alter table alter column When I execute exec sp_dbcmptlevel 'pubs' it is set to 70. When I execute this against my database it is 65. Tracy Hardman Harley-Davidson * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Fri, 26 Sep 2003 20:35:17 GMT

Tibor Karasz #7 / 8	Alter table alter column Then that is the reason why you cannot exploit some of the new features for SQL7. There are some 90+ pages in Books Online about compatibility, please check out that before you up your database to 70 mode (so you don't get into unpleasant surprises). -- Tibor Karaszi, SQL Server MVP FAQ from Neil at: http://www.sqlserverfaq.com Please reply to the newsgroup only, not by email. Quote: > When I execute exec sp_dbcmptlevel 'pubs' it is set to 70. When I execute this against my database it is 65. Quote: > Tracy Hardman > Harley-Davidson > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Fri, 26 Sep 2003 20:40:37 GMT

Tracy Hardma #8 / 8	Alter table alter column The compatibility level on our dev and test boxes is 70. The compatibility level on production is 65. We will change it and try again. Thanks for all of the help!! Tracy Hardman Harley-Davidson * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Fri, 26 Sep 2003 20:42:42 GMT

Page 1 of 1

[ 8 post ]