Cross table copy working in Query Analyser, but not from code

I'm trying to copy a row from one table to another for audit purposes
using a 'INSERT INTO x SELECT y FROM z' statement. This works
absolutely fine in query analyser, however, when running the exact same
statement from code (.NET via oledb), it fails with the error:
An explicit value for the identity column in table 'x' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
or when specifying all columns:
Cannot insert explicit value for identity column in table 'x' when
IDENTITY_INSERT is set to OFF
Table 'x' has no identity columns, table 'y' has one (integer ID)
identity column.
Anyone have any ideas why this statement would work in the query
analyser, and not in code?
(SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)I would guess you are connected to different databases/instances and the
table schema are different. If needed, you can run a Profiler trace to
verify this.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137432432.481007.178730@.f14g2000cwb.googlegroups.com...
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
> (SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)
>|||That's definately not the issue. I'm fairly experienced with database
applications, despite using .net. I've just never come across an
instance whereby the query analyser gave different results to the oledb
components.
Does either the Query Analyser or oledb .net component do anything
unusual behind the scenes? Or is it a possibility that this is
happening because in my program it is taking place inside a
transaction? (it's the first thing in the transaction, and the
exception is thrown immediately on adding the command to the
transaction)
Thanks for the reply Dan|||A Profiler trace should show all that is going on. I can't think of
anything that would cause different behavior for a single statement like
this. The error clearly indicates the target table has an identity column.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137437890.787728.312550@.o13g2000cwo.googlegroups.com...
> That's definately not the issue. I'm fairly experienced with database
> applications, despite using .net. I've just never come across an
> instance whereby the query analyser gave different results to the oledb
> components.
> Does either the Query Analyser or oledb .net component do anything
> unusual behind the scenes? Or is it a possibility that this is
> happening because in my program it is taking place inside a
> transaction? (it's the first thing in the transaction, and the
> exception is thrown immediately on adding the command to the
> transaction)
> Thanks for the reply Dan
>|||Rory (rory.smith@.gmail.com) writes:
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
One thing to check is that there are not two table x in the database.
Own owned by dbo, which I assume that you run as from Query Analyzer,
and one owned by the user that you connect with from the application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland - you were right - I'm unsure of where the 'phantom' table came
from - I created the database from a script which only has one instance
of the table. Anyway, all sorted now.
Many thanks to you both