Strange problem with bulk insert

[Wayne]

Platform is SQL Server 2005. I have a table of information that gets
information bulk inserted into it as well as processes that read the
information in that table for reporting purposes. The inserting and
reporting can be going on at any time so while an insert is happening someone
could be running a report to request information. In the queries that read
the data we added a nolock hint as part of an effort to generally improve
performance. Now that we have done this we get an error in the BCP process.
The error that comes back thorugh the profiler says "Insert bulk failed due
to a schema change of the target table." But there have been no schema
changes. Even with the nolock hint I'm betting we are still putting on some
type of schema stability locks could this be causing this? Is there any way
in profiler to monitor the DDL changes other than looking for TSQL
statements? In case it helps, reduced for the sake of brevity, and some
names changed here is the ddl of the table. Thanks in advance for any help.

CREATE TABLE ManyRecords
(
idManyRecords integer IDENTITY,
idFKtoOtherTable integer NOT NULL,
nDuration integer NOT NULL,
nCost decimal(16,4) NOT NULL DEFAULT 0,
nAdjustedCost decimal(16,4) NOT NULL,
szLocation varchar(24) NOT NULL,
szDigits varchar(24) NOT NULL
)

ALTER TABLE ManyRecords ADD
CONSTRAINT PK_ManyRecords
PRIMARY KEY CLUSTERED (idManyRecords)
go

[Erland Sommarskog]

Wayne (Wa...@discussions.microsoft.com) writes:
> Platform is SQL Server 2005. I have a table of information that gets
> information bulk inserted into it as well as processes that read the
> information in that table for reporting purposes. The inserting and
> reporting can be going on at any time so while an insert is happening
> someone could be running a report to request information. In the
> queries that read the data we added a nolock hint as part of an effort
> to generally improve performance. Now that we have done this we get an
> error in the BCP process. The error that comes back thorugh the
> profiler says "Insert bulk failed due to a schema change of the target
> table." But there have been no schema changes. Even with the nolock
> hint I'm betting we are still putting on some type of schema stability
> locks could this be causing this?

Indeed, a NOLOCK query does take out Sch-S locks. I don't know if that
could be the reason for this failure. In such case the error message is
misleading.

Rather than using NOLOCK, have you considered using snapshot isolation
or read committed snapshot?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx