Re: Stored procs and DB-aware components possible at all?
From: Arthur Hoornweg (antispam.hoornweg_at_casema.nl.net)
Date: 03/08/05
- Next message: Kovács Ferenc: "Re: Union queries and batchoptimistic lock"
- Previous message: Arthur Hoornweg: "Re: Stored procs and DB-aware components possible at all?"
- In reply to: Vitali Kalinin: "Re: Stored procs and DB-aware components possible at all?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 08 Mar 2005 09:44:47 +0100
Vitali Kalinin schreef:
> What do you mean by this? If you have defined yours views with INSTEAD OF
> triggers then just make sure that views are created WITH VIEW_METADATA. For
> details look at BOL.
I've already been through all that two years ago, but I
encountered some really hair-pulling issues that made me
give up on updateable views.
Problem 1 has to do with "WITH VIEW_METADATA" : it makes
ADO property "update criteria=adcriteriakey" stop working.
Ado insists on locating the records that it updates by
comparing all fields with their previous values, not just
the primary key field. In my specific case, that is very
undesirable because it is a multi-user database and different
users need to be able to edit different fields in the same
records simultaneously.
I tried to circumvent this by creating an updateable view with
a primary key but it just wouldn't work. So I had to give
up on "with view_metadata".
Problem 2: I tried to let the view call a UDF (user-defined-
function, that's a special kind of stored procedure) that
implements the row-based security, but unfortunately MSSQL
failed to optimize that call, the function was executed for
every row in the resultset of the query that defined the
view. Performance became dreadfully slow if the database
size exceeded a few 100,000 records. That was unacceptable.
So I had to give up on views that call UDF's/stored procs.
Problem 3: I also tried to realize the updateable view using an
INNER JOIN instead of a UDF. That initially worked very well,
but it turned out to be impossible to delete records (this is
documented in BOL, BTW). So I had to give up on INNER JOIN.
Problem 4:
I tried using "WHERE...IN" instead of INNER JOIN. This was
extremely promising at first, but it turns out that MS SQL
Server 2000 has a nasty bug here: you simply can't use
GUID's in the "WHERE" clause. All my primary keys are
GUIDS, so I had to give up on this.
After losing a week of precious time on this issue I decided
that updateable views don't deliver what they promise and
completely gave up on them.
Then somebody in this newsgroup (I believe it was Brian) mentioned
"application roles" to me, and that solved my problem, at the
expense of having to lock out all other software applications
from my database.
-- Arthur Hoornweg (In order to reply per e-mail, please just remove the ".net" from my e-mail address. Leave the rest of the address intact including the "antispam" part. I had to take this measure to counteract unsollicited mail.)
- Next message: Kovács Ferenc: "Re: Union queries and batchoptimistic lock"
- Previous message: Arthur Hoornweg: "Re: Stored procs and DB-aware components possible at all?"
- In reply to: Vitali Kalinin: "Re: Stored procs and DB-aware components possible at all?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
