Alter table alter column 
Author Message
 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
 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
 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
 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
 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
 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
 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
 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
 
 [ 8 post ] 

 Relevant Pages 

1. alter table alter column

2. ALTER TABLE ALTER COLUMN

3. ALTER TABLE ALTER COLUMN

4. Alter Table - Alter Column - Set Default ?'s

5. Help with Alter Table Alter Column

6. syntax for ALTER TABLE ALTER COLUMN DEFAULT

7. Alter Table Alter Column Syntax

8. Alter table alter column

9. Alter table Alter column problem.

10. Alter table alter column text

11. Alter table alter column syntax

12. Errors With ALTER TABLE/ALTER COLUMN


 
Powered by phpBB® Forum Software