KB275090 Alternative workaround

[techjosh]

Hi, I just had to deal with the issue described in KB275090. None of the
listed workarounds were satisfactory so I created my own. I feel this method
is preferable when you have the ability to add another trigger to the table.

Here is the code (add it to the bottom of "steps to reproduce"):

-- Figures out what the @@IDENTITY value should be and
-- artificially populates it using dynamic sql.
CREATE TRIGGER TableXX_Ident_Fix_Run_Last ON dbo.TableXX
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @Ident INT
DECLARE @Sql VARCHAR(8000)

SELECT @Ident = MAX(IDCol) FROM Inserted

SET @Sql = '
DECLARE @Table TABLE
(
PK INT IDENTITY (' + CAST(@Ident AS VARCHAR(12)) + ', 1),
test bit
)

INSERT INTO @Table (test) VALUES (1)
'

EXEC(@Sql)

SET NOCOUNT OFF
GO

 

[David Portas]

Hi, I just had to deal with the issue described in KB275090. None of the
listed workarounds were satisfactory so I created my own. I feel this
method
is preferable when you have the ability to add another trigger to the
table.

Here is the code (add it to the bottom of "steps to reproduce"):

-- Figures out what the @@IDENTITY value should be and
-- artificially populates it using dynamic sql.
CREATE TRIGGER TableXX_Ident_Fix_Run_Last ON dbo.TableXX
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @Ident INT
DECLARE @Sql VARCHAR(8000)

SELECT @Ident = MAX(IDCol) FROM Inserted

SET @Sql = '
DECLARE @Table TABLE
(
PK INT IDENTITY (' + CAST(@Ident AS VARCHAR(12)) + ', 1),
test bit
)

INSERT INTO @Table (test) VALUES (1)
'

EXEC(@Sql)

SET NOCOUNT OFF
GO

A much easier solution is to use the SCOPE_IDENTITY() function instead of
@@IDENTITY. SCOPE_IDENTITY() has local scope and won't be affected by
IDENTITY values generated in a trigger.

SCOPE_IDENTITY() is available in SQL Server 2000 and later versions.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[techjosh]

I agree, that is a much much simpler way to achieve the desired result. I
almost exclusively use the SCOPE_IDENTITY() function myself in my insert
sprocs. Unfortunately I didn't write Microsoft Access XP and can't alter
it's behavior. It is Access itself that performs the insert command and then
uses the @@IDENTITY value to retrieve the new record. I am, therefore,
forced into this trickery in order to make Access happy.

The other suggested workarounds were to manually acknowledge the generated
error message and manually (or automatically using the Form_AfterInsert
event) refresh the form. None of these methods fixes the actual problem
except mine which returns the expected result in the @@IDENTITY variable for
Access to use. In that case, I guess this should be in a separate "Solution"
category as it makes the problem go away.