Skip to Content.
Sympa Menu

freetds - [freetds] bcp with NTWDBLIB and SQL Server 2008 R2

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: TDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] bcp with NTWDBLIB and SQL Server 2008 R2
  • Date: Sat, 15 Jan 2011 14:18:47 -0500

It appears that Microsoft made a change with SQL Server 2008 R2 that
cripples DB-Library BCP as implemented in NTWDBLIB.DLL. The change
doesn't affect FreeTDS except in a good way: it's one more reason to use
FreeTDS. But the behavior is subtle and misleading, and might otherwise
cause you to waste time if you don't know about it.

I have been unable to find any report of it anywhere on the Web. I would
appreciate independent confirmation or clarification if you are in a
position to provide it. I have not even explored the issue with Microsoft;
it was expedient for us simply to migrate to FreeTDS for Win32, especially
because we'll soon move the same applications to Win64 on Windows 7,
leaving 32-bit DLLs behind forever.

The change also might pre-date 2008; I encountered it when migrating from
2000 to 2008r2.

Summary: Applications linked to NTWDBLIB.DLL are unable to use the BCP
API (bcp_init, bcp_bind, etc.) to bulk load data. When attempted, the
server returns this message:

Server error: Error 4834 (severity 16)
message: You do not have permission to use the bulk load statement.
Error: 10007 (severity 5):
message: General SQL Server error: Check messages from the SQL
Server.

It is *not* due to a administrative permissions mistake. The error occurs
even if the user is in db_owner role in the database and the table is
owned by dbo. Without changing anything on the server, BCP.EXE will
successfully load the same data into the same table.

There is a workaround: if the user is in the server-wide BULKADMIN role,
the bulk load succeeeds.

Although NTWDBLIB still uses TDS 4.2, it is obviously not a technical TDS
problem because it is resolved using the BULKADMIN role.

The only conclusion I can form is that Microsoft decided to break TDS 4.2
BCP. Even if that was necessary or advisable -- which I doubt -- they
decided to return a misleading error: a permission-denied rather than a
protocol-unsupported message. In the short run, this will lead to many
people looking for an administrative resolution, wasted time about which
Microsoft is evidently unconcerned. In the long run, it will lead to
disuse of NTWDBLIB, which of course Microsoft has been counselling for
many years.

FreeTDS users are unaffected. FreeTDS never supported BCP with TDS 4.2
because we discovered Microsoft and Sybase implement the protocol slightly
differently.

The next time you read that Microsoft "doesn't support" DB-Library,
remember what it means: it means they dropped support for their library,
and they are free to change the server in ways that cause previously
working programs to fail. Remember too that so-called ODBC applications
(e.g. BCP.EXE) continue to work, and that FreeTDS continues to work no
matter which API is used. The server doesn't know what library or API the
client is using; it knows only the TDS version and login packet options.
That is why FreeTDS continues to be compatible even as Microsoft's own
libraries suffer from planned obsolescence.

--jkl



  • [freetds] bcp with NTWDBLIB and SQL Server 2008 R2, James K. Lowden, 01/15/2011

Archive powered by MHonArc 2.6.24.

Top of Page