Error with a fieldname that does not exist anymore.

I get an :
[Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
'oldfieldname_eid'
While inserting in the Enterprise Manager. The 'oldfieldname_eid' does not
exist in any table of the target database. The error only occurs if an
insert trigger is defined. The insert trigger does not contain a string
resembling the 'oldfieldname_eid'. The name has been used in the past for a
field.
The error does reproduce, after a reset of the client, after setting the
database offline and online again.
The error does disappear when the trigger is 'removed' but reappears
again when the trigger is recreated. The error does not appear when
a 'dummy' trigger is used, only containing "print 'hello'"
Does anybody have a suggestion ?
ben brugmanIt would have been quite helpful if you had posted the text of the trigger,
because that seems to be where the problem is, now we have to make a guess.
One thing I can think of is if you have a view on the table that contained
the 'oldfieldname_eid' column, and that view contains SELECT *. The
definition of a view is not updated automatically when the underlying table
changes, so the column might still exist in the view definition.
Try SELECT table_name FROM information_schema.columns
WHERE column_name = 'oldfieldname_eid'
to find a view or table that includes a column with that name.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:O$T6PNioDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I get an :
> [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> 'oldfieldname_eid'
> While inserting in the Enterprise Manager. The 'oldfieldname_eid' does not
> exist in any table of the target database. The error only occurs if an
> insert trigger is defined. The insert trigger does not contain a string
> resembling the 'oldfieldname_eid'. The name has been used in the past for
a
> field.
> The error does reproduce, after a reset of the client, after setting the
> database offline and online again.
> The error does disappear when the trigger is 'removed' but reappears
> again when the trigger is recreated. The error does not appear when
> a 'dummy' trigger is used, only containing "print 'hello'"
> Does anybody have a suggestion ?
> ben brugman
>
>|||> Try SELECT table_name FROM information_schema.columns
> WHERE column_name = 'oldfieldname_eid'
The field 'oldfieldname_eid' does not appear in any View/table or trigger.
It does not appear anywhere anymore in the complete database.
There does not exist a field 'oldfieldname_eid'.
If we change the fieldname into 'AAAAAA' we get the following error
that the field 'newfieldname_eid' does not exist
> > [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> > 'newfieldname_eid'
> It would have been quite helpful if you had posted the text of the
trigger,
> because that seems to be where the problem is, now we have to make a
guess.
For the trigger see the end of the message.
Adding a new column to the table with 'newfieldname_eid' next to 'AAAAAA'
and
then deleting the 'AAAAAA' field did solve the problem. We expect that the
count
of columns did have a mismatch in the internal meta data and that this was
solved
by adding and deletion of columns. (But this is a guess).
(The table appears to be exactly as when the error occured but the error is
'gone').
(probably a bug).
We still would like to know what happened !
ben brugman
The trigger.
The trigger :
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [TR_I_Tabel2] ON dbo.Tabel1
FOR INSERT
NOT FOR REPLICATION
AS
INSERT INTO
Tabel2 (Field1, Field2, Field3, Field4, Field5, Field6)
SELECT
Field1, Field2, Field3, current_Field4, current_Field5, current_Field6
FROM inserted
UPDATE
Tabel1
SET
Field7 =NULL
FROM
Tabel1
INNER JOIN
inserted
ON
Tabel1.Field3 = inserted.Field3
INNER JOIN
Table3
ON
Table3.Field4 = inserted.current_Field4
WHERE
Table3.status_nbr >= 50
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:O$T6PNioDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I get an :
> >
> > [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> > 'oldfieldname_eid'
> >
> > While inserting in the Enterprise Manager. The 'oldfieldname_eid' does
not
> > exist in any table of the target database. The error only occurs if an
> > insert trigger is defined. The insert trigger does not contain a string
> > resembling the 'oldfieldname_eid'. The name has been used in the past
for
> a
> > field.
> > The error does reproduce, after a reset of the client, after setting the
> > database offline and online again.
> >
> > The error does disappear when the trigger is 'removed' but reappears
> > again when the trigger is recreated. The error does not appear when
> > a 'dummy' trigger is used, only containing "print 'hello'"
> >
> > Does anybody have a suggestion ?
> >
> > ben brugman
> >
> >
> >
>