At 03:33 PM 12/08/2005 -0400, you wrote:

>Hey all,
>
>I have just attempted to add a computed field to one of our tables.
>Consistantly, it sends the server process to 100% cpu utilization. All
>current connections stop working (They don't drop, they just "freeze"),
>and the server no longer can accept connections (It doesn't outright
>refuse them, it just never connects). On windows, the server is so
>unresponsive that it doesn't cooperate with the Services manager. It is
>signaled to stop, but just sits there, tearing up the cpu, constantly
>with the status message "stopping".
>
>Here is my SQL, though it won't be of perticular use by itself (I don't
>think):

Well, it is quite useful to see it, since it has syntax errors. So -
besides other problems with doing this kind of thing, you have some runtime
ill logic in this statement that probably won't raise any exceptions: the
engine will just keep trying forever to do the impossible thing that you
ask it to. I would want to begin by testing your Alter Table statement
rigorously in a reliable DB Admin tool and making sure you get that right
first.

>Alter Table NAInvoices Add MaintInvoiceTotal Computed By (Case When
>Coalesce(CustDiv, "") = "" Then (Select MaintInvoiceTotal From
>GetInvoiceTotalsByCompany(NAInvoices.InvNo, NAInvoices.CustID)) Else
>(Select MaintInvoiceTotal From
>GetInvoiceTotalsByCompanyDiv(NAInvoices.InvNo, NAInvoices.CustID,
>NAInvoices.CustDiv)) End)

Hint: the purpose of double quotes is to enclose otherwise "illegal"
identifiers. Look at your COALESCE() expression. The string delimiter in
SQL is the single quote; to get a derived value of "empty string" you need
two single quotes. And a double-quote is *not* two single quotes.

Another hint: check the syntax allowed when referring to "self" columns in
a COMPUTED BY clause.

>Now, up until forever ago, I would loop through the NAInvoices table,
>and execute the procedure
>GetInvoiceTotalsByCompany/GetInvoiceTotalsByCompanyDiv for each row.
>This took seconds. Nothing is complex, and the amount of data is small.

Hint: If you try to SELECT from a procedure that is written as an
executable procedure, you will get nothing back in the return arguments.

>I can accept that maybe I have bad indexes or too much data, or
>something along those lines... but here is the kicker. Even when I am
>logged in as a user with absolutely no privledges to NAInvoices, it
>STILL locks up as explained above.
>
>Is this a bug?

Errrm...

>Or is there an obvious no-no that I am touching on?

Understatement. In short, what you are trying to do here is a can o' worms.

Logic errors in your expressions aside, it is not cool to be attempting
metadata changes while the database is in use (even when the metadata
changes are simple, which yours is not, by any standard!). This statement
involves a whole bunch of rules, privileges and dependencies (visible and
invisible, since we have no idea what other dependencies you have in the
SP). There is no way the engine is going to be able to carry this request
through while other transactions are interested in that table or any
dependent tables (even aside from the logic problem in the statement
itself, dependencies, privileges problems..)

Bear in mind also that the question of whether a user has privileges to an
object does not determine whether that user is allowed to make metadata
changes to that object. Only the object owner and SYSDBA may change an
object. So there's the first showstopper.

Next, your call to (a suitably constructed) SP will require that, when a
run-time user has to access that table, that user must (by some means) have
EXECUTE privileges on that SP. The SP, in turn, must have the required
privileges to the tables that it is accessing...and REFERENCES privs to any
tables referred to by any foreign keys in the picture...et al., ad infinitum...

Some advice:

1. All statements in application code should be tested hard externally
before they are used in applications. This is never more true than when
writing applications that change metadata.

2. Don't assume that it is ever a good idea to change metadata in an
active database -- even though *some* changes might work *sometimes*. If
you need to distribute metadata changes into a production system, make it a
one-off thing that is done under clinical conditions.

3. But, ABOVE ALL, when defining computed columns, avoid dependencies on
other objects. It is very easy for computed columns with dependencies on
other objects (tables, views and HORRORS! compiled code such as SPs and
UDFs) to get broken horribly in backup, making your backups unrestorable.

4. If a COMPUTED BY definition is so complex that you need a PhD in SQL to
make it work, then deciding to define it was the wrong choice. Provide the
reusable bits via views and SPs that you can reach via DSQL.

./heLen