I cannot import records into a table that has a trigger. I get an ODBC error \"Connection is busy with results for another command\". Is there a way to make this work?
The following should reproduce my error in a very simplified scenario. I am using a Microsoft SQL 2005 Server on Windows 2000 and am using an ODBC connection to it.
1.) Use the script below to create two tables on a SQL server.
2.) Create a small text file with about 10 firstname,lastname records in it like:
Jim,Dandy
My,Name
Jolly,Jackson
etc...
3.) Create a transformation to import those records into the \"deleteme\" table. I really need to \"Add New and Update Old\" records but for the test the writer can be set to \"Add New Records.\"
When you run the import, you should get an error after the first record is imported.
Below is the script to create the test tables:
CREATE TABLE [dbo].[deleteme](
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL
);
GO
ALTER TABLE [dbo].[deleteme] ADD CONSTRAINT [PK_deleteme] PRIMARY KEY CLUSTERED
(
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
go
SET ANSI_PADDING OFF
CREATE TABLE [dbo].[deleteme2](
[TableName] [varchar](50) NOT NULL,
[FieldName] [varchar](20) NOT NULL,
[NewValue] [varchar](100) NOT NULL,
[ChangeDate] datetime NOT NULL
)
GO
create Trigger [dbo].[deleteme_AuditInsert] ON [dbo].[deleteme]
FOR INSERT
AS
Insert into deleteme2 (TableName, FieldName, NewValue, ChangeDate)
Select 'deleteme' , 'FirstName', i.FirstName, GetDate() from Inserted i
Insert into deleteme2 (TableName, FieldName, NewValue, ChangeDate)
Select 'deleteme' , 'LastName', i.LastName, GetDate() from Inserted i
Thanks,
Kelly
