I've tried SQLGetDiagRec, which tells me that there was an invalid date
format on a column, but there's no indication of *which* column, and my
table has several date columns in it.

I then spotted some references to SQLGetDiagField with
SQL_DIAG_COLUMN_NUMBER, but I don't get any records back from that
call-- should I be able to get something, or is there some other way or
just *no* way to get the column number that failed?

It looks like no one knows the answer to this.  I'm using VS 2005 & SQL
Server 2005, and after my bcp_sendrow error, have tried all the
SQL_DIAG fields with SQLGetDiagField, and have so far found that
SQL_DIAG_DYNAMIC_FUNCTION returns a null string on records 0 & 1,
SQL_DIAG_NUMBER returns 1, SQL_DIAG_RETURN_CODE returns -1,
SQL_DIAG_DYNAMIC_FUNCTION_CODE returns 0 and ignores record number, and
none of the rest return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.  And
SQL_DIAG_SS_MSGSTATE and SQL_DIAG_SS_SEVERITY both return SQL_ERROR.
SQLGetDiagField appears completely useless here.  And SQLGetDiagRec
doesn't include the column info.

On the other hand, bcp.exe used on the same data produces an error
message that includes column number.  The question of the day is, how
does it do it?   I turned on ODBC trace, and let bcp.exe do its thing.
In the trace log, I found a bunch of calls to SQLColAttributes, one for
each column, then a bunch of calls to SQLGetInfoW,  then a call to
SQLSetConnectAttr with what appears to be a bogus attribute (-28236)
which then returns the first error-- it then calls SQLErrorW and gets
the message I'm able to get (invalid character for cast specification)
and then it does an SQLGetConnectionOption with 112 (packet_size) and
then disconnect and frees.  I can't see where it's getting the
row/column info from that it writes to the error output.

Then, I tried turning ODBC trace on and running my program that uses
bcp_sendrow.   It does a *single* call to SQLBindCol (though I'm
calling bcp_bind about 35 times), then a bunch of SQLGetInfoW's like
the bcp.exe version, then the same SQLSetConnectAttrW with the odd
attribute value (-28236) that returns the first error,  and then my
SQLGetDiagRec/SQLGetDiagField tries that never get me the column
information.

One thing, is bcp_sendrow does not operate with a statement handle, but
a connection handle.  bcp.exe is calling SQLColAttributes with a
statement handle, which it is using for all the SQLColAttributes calls,
after having issued a "select * from <table> where ?\ 0" which looks
like it could be a method for determining the column type of all the
columns, in which case the sanity checking of the data and perhaps even
the conversion may be done in bcp.exe itself, different from
bcp_sendrow which handles the conversion somewhere in the API.  If
that's the case it may be that bcp_sendrow doesn't make column
information available on an error, which is really annoying.  I'm
trying to produce a relatively generalized bulk import capability that
needs to flag what's wrong when a user tries to import bogus data.  I'm
using bcp_sendrow because there is a lot of associated data tweaking on
the way in and I want to minimize the overhead-- eliminating writing an
intermediate form to disk and then calling bcp.exe to do the import.  I
presume it is that kind of thing the bulk-copy API is for.

At least I know the row that is affected, as bcp_sendrow operates a row
at a time.  I suppose I could use bcp_sendrow until I get an error,
then throw that row out to a file and run bcp.exe on it and let IT
detect the specific column information, but that's pretty darn kludgy.

SQLSetConnectAttrW with an attribute code of -28236 seems to be doing
something special-- perhaps even initiating the row import, as it
appears to be *that* call in both cases that is throwing the error I'm
trying to get the column number for.  Haven't been able to find a
define for the value in the includes, and don't know how it would
appear anyway, possibly in hex as 0x91b4 or -0x6e4c or some kind of
ORing together multiple values...

SUCCESS!

I'm talking to myself here but perhaps it will benefit others.  I found
out how to get the row/column information on a bcp_sendrow error.

First, you have to specify an error file in the bcp_init call.  I did
that and ended up getting a null error file, initially.  Searched the
newsgroups and found several people had that problem.   But then I
remembered that Windows is not like Unix in that file data sometimes
doesn't get written to disk if a close isn't done when the program
exits.  I've seen that before.  Open a file, fprint some stuff to it,
then exit.  File exists, but is null.  So, I figured I probably have to
call bcp_done to get the error file closed.  Sure enough, now I'm
getting the error info.

One issue is, what if I don't want to "commit" the partial batch to the
database on an error?  I figured by not doing a bcp_done I would
achieve a rollback.  Haven't verified that though, and it looks like I
can't do it that way anyway because I need the errors.  There may be
another way to do a rollback, which I'll look into and is a subject for
another day...

If you don't have a blog up yet, maybe this is a good opportunity to start.


ML

---
http://milambda.blogspot.com/

(kd***@hotmail.com) writes:
Show quoteHide quoteThanks for posting this! I don't relly have anything to add. But as I
have a module for Perl users that exposes bcp_sendrow et al, this is
useful information. My module uses DB-Library, so I need to port it to
ODBC one day...

Your welcome.  Thanks for the non-snide remarks.  Also, WRT "commits,"
it appears that the most suggested way to handle imports is to import
everything into a "staging" table, then use SQL to move from the
staging table into the "live" table.   Then, the "better" constraint
checking & transaction handling features are available.  I found this a
pretty unsatisfactory solution, as the whole point of my project is to
reduce the overhead of large imports, as the app I'm working on is a
database analysis program that will be constantly doing large imports
of a good-sized schema's worth of tables exported from another server.
Seems to be mostly working now however, though I had to make use of
indicators on the bind variables so that I could pass in NULL flags
where necessary...

generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot
What's a good SQL server naming convention
Optimizing an IN clause