Hi,

We have just migrated from MSSQL 2000 to MSSQL 2005. Everything seemed to be
working for about a week, when we realised that... no actual data in the DB
is updated. The problem is quite difficult to find, because the failure
happens in triggers.

I will try to simplify the actual case to illustrate the problem.

There is a table, say "tTable" with two fields, id_tb - an identity, and
data_tb - a string.

The table has the 'instead of insert' trigger created. The trigger contains
the following statement:
    select top 0 id_tb, data_tb into #inserted from inserted
This is just a dummy select statement creating a temporary table #inserted.
The temporary table is used in the rest of the code of the trigger.

When you try to add records to tTable:
    insert into tTable (data_tb) select SomeStringField from SomeOtherTable

You get the following error:
"Severe error occurred on the current command. the result, if any, should be
discarded."

By commenting out parts of the code of the trigger, we found out that the
failing line is the one mentioned above, i.e.
    select top 0 id_tb, data_tb into #inserted from inserted

It seems that the problem lies in the identity field, because:
    select * into #inserted from inserted
also FAILS and
    select top 0 0 as id_tb, data_tb into #inserted from inserted
DOES NOT FAIL

Has anybody come across such a problem and - hopefully - found a workaround?
It is of course possible to change the failing statement to "create table",
the problem is, however, the fact, that our DB system contains a lot of
databases with many table each... this just means many thousands of code to
be checked. And this is a legacy code written by programmers who do not work
for us anymore :-/. And everything should be up-and-running just now, of
course ;-).

Any help will be greatly appreciated.

DW

I forgot to write that we have the compatibility level set to MSSQL 2000.

Below you will an example of actual code thaf fails. Id does fail no matter
if the database is a result of migration from MSSQL 2000 or it is freshly
created in MSSQL 2005.

Please test it yourself. Any comments will be greatly appreciated.

-------------------------------------------------------------------


CREATE TABLE [dbo].[test_tb](
            [id_tb] [int] IDENTITY(1,1) NOT NULL,
            [txt] [varchar](50) COLLATE SQL_Polish_CP1250_CI_AS NULL,
)

-------------------------------------------------------------------

CREATE TRIGGER test_tb_on_insert
ON dbo.test_tb
INSTEAD OF INSERT
AS
SELECT id_tb ,txt INTO #tmp FROM inserted

-----------------------------------------------------------------

insert into test_tb (txt) values ('test')

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should
be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should
be discarded.

Just my humble opinion - but it looks to me like the trigger functions
different because of the handling of nulls.  SQL 2000 returns an id_tb
of 0 into the temporary table, where SQL2005 is saying the value is
null (because the record was not yet inserted due to the trigger) which
isn't allowed on the identity.

Interesting issue, and I look forward to the SQL gurus to respond.

Thanks for your answer.

We added the line
    select * from inserted
to the trigger code and saw the output - the identity column contained
zeroes, not NULLs :-/. I am rather puzzled about it.


Me too, indeed :-)

DW

On Mon, 22 May 2006 10:23:27 +0200, master wrote:

Hi master,

I could reproduce this on SQL Server 2005 version 9.00.2047.00. I have
no explanation for this behaviour - I guess it's a bug.

You can file this bug on http://lab.msdn.microsoft.com/ProductFeedback/.
If you post a URL to the bug report, I'll add a confirmation.

Meanwhile, here's a workaround:

CREATE TRIGGER test_tb_on_insert
ON dbo.test_tb
INSTEAD OF INSERT
AS
SELECT COALESCE(id_tb, 0) AS id_tb ,txt INTO #tmp FROM inserted

(Or even

CREATE TRIGGER test_tb_on_insert
ON dbo.test_tb
INSTEAD OF INSERT
AS
SELECT id_tb + 0 AS id_tb ,txt INTO #tmp FROM inserted

!!)

Hi Hugo,

Thanks very much for your answer.


Sorry for this ridiculous nickname ;-). This is a legacy account created by
some technician for all people in the company who want to use NNTP ;-).
My name is Dobek, actually, and I submitted the error under this name.


Here is the URL:
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK50288

Have you got any idea how long could it take to get the bug fixed? For the
time being we migrated crucial databases back to MSSQL 2000, but we would
like to start using the new technology.


Thanks for the suggestions, but this means changes of the legacy code which
we would like to avoid, as I indicated in the first post. Anyway... we will
use the workaround if this is the only option.

Yours sincerely,
D. Wroblewski.

Assuming this is a bug, Microsoft would either include it on the next
service pack, or if enough people complain it would go out as a hotfix.
Either way you are probably looking at a month to a couple months.  You
are better off going back to 2000, but you could (and probably will) be
there awhile.

The other possibility is that the original functionality in SQL 2000
was incorrect.  In effect, if you aren't inserting a row there
shouldn't be anything assigned to the identity until you do so (you
could rollback the transaction and then invalidate the identity).

On Tue, 23 May 2006 10:19:45 +0200, master wrote:

Hi Dobek,

No problem. :-)

I'm in a minority here, but I still prefer to start my messages with a
greeting. I always look for names in the message first, and use the
nickname only if that fails.

Thanks for posting the URL. I have just validated and voted for the bug,
and I have also posted a workaround.

Unfortunately, no. That's up to Microosoft to decide.

Just check the URL from time to time. MS will post updates when they
decide how to handle the issue.

If getting this fixed is critical, you might try opening a support
incident with MS. That would especiallly be helpful if MS decide to fix
this in a hotfix but decides against publishing this hotfix for all
customers to download.

Hi Hugo,


A very nice manner, I can only support it :-).


Thanks, I deliberately chose not to add your workaround. I thought you
should do it if you choose.


I see, thanks again.

DW

The current status is:
    Closed by Microsoft on 2006-05-25 at 14:28:10
        Thanks for reporting this. We are aware of this issue and the fix
will be in SP2.
        Thanks much

Has anybody any idea when can we expect the release of the SP2?

DW

On Mon, 29 May 2006 11:03:13 +0200, master wrote:

Hi Dobek,

There are no official announcements that I know of.

<speculation>
Since SP1 has just been released, I wouldn't hold my breath for SP2.
</speculation>

sp question re: dynamic sql
Crazy Row Numbering Poblem
can't delete a row from primary table - is there an SP for this?
Stored Proc SQL 2000 and Dates
Problem with convert