Thank you for your input. This is the script for the testing tables:
============================================================
CREATE TABLE [dbo].[Table1] (
[testingTable1ID] [int] IDENTITY (1, 1) NOT NULL ,
[TT1Data1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TT1Data2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Table2] Script Date: 4/30/2004 10:24:50 AM ******/
CREATE TABLE [dbo].[Table2] (
[testingTable2ID] [int] IDENTITY (1, 1) NOT NULL ,
[TT2Data1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TT2Data2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[testingTable1ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[testingTable2ID]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.TESTING Script Date: 4/30/2004 10:24:50 AM ******/
CREATE TRIGGER [TESTING] ON dbo.Table1
FOR INSERT, UPDATE
AS
DECLARE @Data1 char(10)
SET @Data1 = (SELECT TT1Data1 from inserted)
insert into Table2 (TT2Data1) values (@Data1)
GO
SET QUOTED_IDENTIFIER OFF
GO
============================================================
I'm not sure what you mean by 'IDENTITY_INSERT'. My NT login has the same permisions on all servers. I have all SQL admin permissions on the server, and on the client for all three SQL servers that I have tested. I have tested this on my client machine for all three servers, and on each of the servers themselves. Two of the servers are SQL 2000, one is SQL 7.0. The behavior is the same.
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.