I've got an upsized SQL Server 2000 app (A2K MDB front end, using
ODBC) that's been operating fine for over a year now, and I am
informed today that something has stopped working that worked
before.

I use a view to alias some fields for the tables, and have just
discovered from SQL Profiler that when I run INSERT SQL that used to
work, it's trying to set Identity_Insert ON for the *view*, not for
the underlying table, and this is failing.

It worked before.

In fact, I explicity run a passthrough query to set it on for the
underlying table:

SET IDENTITY_INSERT dbo.tblDonor ON

But when I run the INSERT in Access (that is inserting into the view
based on tblDonor), I see this right before the SQL trace for the
INSERT:

SET IDENTITY_INSERT dbo.vblDonor ON

"vblDonor" is the view for tblDonor, with certain fields aliased
(for backward compatibility with old app, and for a couple of other
reasons).

If I create an ODBC link directly to the raw table, and insert into
it, everything works just fine.

Why would this have been working for nearly a year (and it *was*
working, because I have the records of the rewriting of this module
to prove it), and now suddenly break (I don't know precisely when it
broke, as the client is rather phlegmatic in telling me about
show-stopping bugs).

Any ideas?

I could move it all server-side and be done with it, but the whole
point of upsizing an MDB is ease of use, and that means I shouldn't
have to touch the existing code any more than necessary.

For now, I'm just going to have both the view and the raw table
linked, and live with it like that, since I just want to get the
client up and running, but it's an inelegant solution, and it
bothers me a lot that it worked for nearly a year and has somehow
just broken. The SQL Server 2000 version is 8.00.2039 (SP4), and I
think that's the most recent? Is there a quick way to find out?

I worried that the sysadmin had reverted the server to an earlier
version, but that doesn't seem to have happened (he knows zilch
about SQL Server and has basically let me manage it myself, so it
would be unlikely that he would touch anything other than the backup
systems).

Anyway, I'm both puzzled and annoyed.

(though I must admit I'm most annoyed at the client for not
bothering to report this for however many weeks it's been an issue)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/