Formulas (that are correct) don't validate in EM

I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David WalkerEM is not as sophisticated. That's one of the reasons why you should use
Query Analyzer and DDL statements for altering your table structures. You
will also see similar problems with view definitions in EM.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DW" <None> wrote in message news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl...
I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David Walker|||So it's a bug in EM, or maybe a lack of interest on the developers' part
in making it work correctly? That's good to know, so I won't rely on EM
or believe what it says. It might throw some newbies off, though.
Doesn't EM just send the definition through as Alter Table statements?
Those shouldn't fail. Strange...
Thanks.
David Walker
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in
news:Oh1KLxTDEHA.3584@.TK2MSFTNGP11.phx.gbl:

> EM is not as sophisticated. That's one of the reasons why you should
> use Query Analyzer and DDL statements for altering your table
> structures. You will also see similar problems with view definitions
> in EM. --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "DW" <None> wrote in message
> news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl... I have the following
> table in SQL server 2000 SP3:
> CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
> [EOMDate] [smalldatetime] NOT NULL ,
> [BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1))) ,
> [BOM3MonthsAgo] AS
> (dateadd(month,(-3),(convert(smalldatetime,[EOMDate]) + 1))) ,
> [EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1)) - 1)
> ) ON [PRIMARY]
> The idea here is simple; we enter the end-of-month date for the most
> recent month where we have all the data, (when that happens is a
> little unpredictable). The other dates are commonly used
> beginning-of-month and the ending date of the previous month, etc.
> (Those convert functions appeared when I scripted the table -- I
> didn't enter them.)
> This create statement works fine, and I can fill in the EOMDate, and
> everything else works perfectly.
> UNLESS I edit any of the table definition in Enterprise Manager. Then
> I get these messages from EM:
> 'Dates' table
> - Error validating the formula for column 'BOMDate'.
> - Error validating the formula for column 'EOMPrev'.
> - Error validating the formula for column 'BOM3MonthsAgo'.
> Why does EM not like these formulas? In EM, the BOMDate formula looks
> like this:
> (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
> Aside from the abundance of parentheses, what is EM complaining about?
> Since it *works*, I think EM is confused.
> Thanks for any insight on this.
> David Walker
>
>|||Hi David,
Yes, when you press YES when the prompt titled ' Validation Warnings', the
Enterprise Manager will send the Alter statement to change the definition
of the table. However, before that there is some pre-validation process for
the table with formulas. It have to check all the constraints and use the
formulas to re-acount the related columns. This process is complecated and
this is during this process that the warning message prompt. It is just a
warning and would not prevent you from alter the table. As Vyas said, you
could use the Query Analyzer to alter the table without get any warning
messages so as your client application.
Hope this helps.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.