May 1, 2002 at 3:33 pm
Setting:
Sql 7:
No Sql SP (it conflicts with our PowerBuilder app).
Table: benefits
Column: vac_available.
When I try to delete this column using Enterprise Manager or t-sql:
"alter table benefits drop column vac_available"
I get:
"Server: Msg 170, Level 15, State 1
Line 1 Incorrect syntax near vac_available".
I used "dbcc checktable ('benefits')" but no error messages printed out?!
Any idea what to do next?
TIA,
Bill
May 1, 2002 at 3:59 pm
quote:
"Server: Msg 170, Level 15, State 1Line 1 Incorrect syntax near vac_available".
?You get this same error in EM? If so do you have any kind of replication going on or constraint on the column?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 1, 2002 at 6:15 pm
I spoke a little too loosely. Under EM I get the error:
-Unable to modify table:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Line 2: Incorrect syntax near 'vac_available'
Nothing special about that field except it's
numeric, 5(6,2), allow nulls, default = 0.
Bill
May 1, 2002 at 6:55 pm
2 things , 1 can you post the table DDL so I can see if there is something key maybe about the another field or the table itself. 2 run profiler to watch what EM is doing when you run this as this may provide some key to the problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 5:19 pm
Antares686,
The developers wanted my to change the (field) names to protect the innocent but here's the Sql script. I haven't had a chance to run profiler yet but I will do that next.
Bill
CREATE TABLE [dbo].[benefits] (
[l1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[l2] [datetime] NOT NULL ,
[l3] [datetime] NOT NULL ,
[l4] [datetime] NOT NULL ,
[l5] [numeric](6, 2) NOT NULL ,
[l6] [numeric](6, 2) NOT NULL ,
[l7] [numeric](6, 2) NOT NULL ,
[l8] [numeric](6, 2) NOT NULL ,
[l9] [numeric](6, 2) NOT NULL ,
[l10] [numeric](6, 2) NOT NULL ,
[l11] [numeric](6, 2) NOT NULL ,
[l12] [numeric](6, 2) NOT NULL ,
[l13] [numeric](6, 2) NOT NULL ,
[l14] [numeric](6, 2) NOT NULL ,
[l15] [numeric](6, 2) NOT NULL ,
[l16] [numeric](6, 2) NOT NULL ,
[l17] [numeric](6, 2) NOT NULL ,
[l18] [numeric](6, 2) NOT NULL ,
[l19] [numeric](6, 2) NOT NULL ,
[l20] [numeric](6, 2) NOT NULL ,
[l21] [numeric](6, 2) NOT NULL ,
[l22] [numeric](6, 2) NOT NULL ,
[vac_available] [numeric](6, 2) NULL ,
[l23] [numeric](6, 2) NOT NULL ,
[l24] [numeric](6, 2) NOT NULL ,
[l25] [numeric](6, 2) NOT NULL ,
[l26] [numeric](6, 2) NOT NULL ,
[l27] [int] NOT NULL ,
[l28] [int] NOT NULL ,
[l29] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[l30] [datetime] NULL ,
[l31] [datetime] NULL ,
[l32] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[benefits] WITH NOCHECK ADD
CONSTRAINT [PK_benefits] PRIMARY KEY CLUSTERED
(
[l1]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[benefits] WITH NOCHECK ADD
CONSTRAINT [DF_benefits_l5] DEFAULT (0) FOR [l5],
CONSTRAINT [DF_benefits_l6] DEFAULT (0) FOR [l6],
CONSTRAINT [DF_benefits_l7] DEFAULT (0) FOR [l7],
CONSTRAINT [DF_benefits_l8] DEFAULT (0) FOR [l8],
CONSTRAINT [DF_benefits_l9] DEFAULT (0) FOR [l9],
CONSTRAINT [DF_benefits_l10] DEFAULT (0) FOR [l10],
CONSTRAINT [DF_benefits_l11] DEFAULT (0) FOR [l11],
CONSTRAINT [DF_benefits_l12] DEFAULT (0) FOR [l12],
CONSTRAINT [DF_benefits_l13] DEFAULT (0) FOR [l13],
CONSTRAINT [DF_benefits_l14] DEFAULT (0) FOR [l14],
CONSTRAINT [DF_benefits_l15] DEFAULT (0) FOR [l15],
CONSTRAINT [DF_benefits_l16] DEFAULT (0) FOR [l16],
CONSTRAINT [DF_benefits_l17] DEFAULT (0) FOR [l17],
CONSTRAINT [DF_benefits_l18] DEFAULT (0) FOR [l18],
CONSTRAINT [DF_benefits_l19] DEFAULT (0) FOR [l19],
CONSTRAINT [DF_benefits_l20] DEFAULT (0) FOR [l20],
CONSTRAINT [DF_benefits_vac_adjustment] DEFAULT (0) FOR [l21],
CONSTRAINT [DF_benefits_l22] DEFAULT (0) FOR [l22],
CONSTRAINT [DF_benefits_vac_available] DEFAULT (0) FOR [vac_available],
CONSTRAINT [DF_benefits_l23] DEFAULT (0) FOR [l23],
CONSTRAINT [DF_benefits_ph_adjustment] DEFAULT (0) FOR [l24],
CONSTRAINT [DF_benefits_l25] DEFAULT (0) FOR [l25],
CONSTRAINT [DF_benefits_l26] DEFAULT (0) FOR [l26],
CONSTRAINT [DF_benefits_l27] DEFAULT (0) FOR [l27],
CONSTRAINT [DF_benefits_l28] DEFAULT (0) FOR [l28]
GO
May 2, 2002 at 6:02 pm
I used profiler and found that it errored out on:
ALTER TABLE dbo.benefits DROP CONSTRAINT DF_benefits_vac_available
ALTER TABLE dbo.benefits DROP COLUMN vac_available
I didn't see anything wrong so I looked up the Alter command in BOL. It stated that these commands can't be used if dbcompatibility mode is set to 65 -- which it was!
Now this is a Sql 7 DB. Why would compatibilty mode be set to 65? (does this indicate that it was originally a Sql 65 DB that was converted to Sql 7? Maybe conversion problems prevented it from being upgraded to Sql 7 level?)
Bill
May 2, 2002 at 6:16 pm
Have no idea, but it is great to see that you found the issue since I would have never thought of that. Gotta love what you can find out with profiler, my most favorite troubleshooting tool.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply