bcp api and error handling

I have implemented a VB.NET application which uses the ODBC BCP api to bulk
insert data into a Microsoft SQL 2000 database. I have included error
handling as it is important to trap and log any errors when they occur. The
import works fine, and the error handling works fine for any ODBC errors
(server not found, login problems etc are all logged properly).
The problem is that ANY bcp error results in SQLGetDiagRec returning
SQL_NO_DATA. The same code works for the ODBC errors from the SQL* functions
,
but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in th
e
connection handle with SQL_HANDLE_DBC as the handle type like the docs say,
but I have never managed to get a BCP error from this function.
Sometimes I can run the input files (I am using data and format files for
simplicity) with the command line bcp utility, and then I will see error
messages I can use. Sometimes the bcp utility works however, so I need the
error handling to find out what is going wrong. (The application is running
under COM+ as a special domain user for security reasons).
How should I use VB.NET to get the error messages when the ODBC BCP
functions fail? Why would my error handling code work for some errors but
return SQL_NO_DATA for others?Hi
I have never used this but, from your description it is hard to tell what is
failing, or even if you are using the correct error handling mechanism.
Posting sample code that can re-create your problem will help.
You may want to look at the example
http://msdn.microsoft.com/library/d...>
mp_4pm8.asp, although this is in C++.
John
"Stephen Davies" wrote:

> I have implemented a VB.NET application which uses the ODBC BCP api to bul
k
> insert data into a Microsoft SQL 2000 database. I have included error
> handling as it is important to trap and log any errors when they occur. Th
e
> import works fine, and the error handling works fine for any ODBC errors
> (server not found, login problems etc are all logged properly).
> The problem is that ANY bcp error results in SQLGetDiagRec returning
> SQL_NO_DATA. The same code works for the ODBC errors from the SQL* functio
ns,
> but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in
the
> connection handle with SQL_HANDLE_DBC as the handle type like the docs say
,
> but I have never managed to get a BCP error from this function.
> Sometimes I can run the input files (I am using data and format files for
> simplicity) with the command line bcp utility, and then I will see error
> messages I can use. Sometimes the bcp utility works however, so I need the
> error handling to find out what is going wrong. (The application is runnin
g
> under COM+ as a special domain user for security reasons).
> How should I use VB.NET to get the error messages when the ODBC BCP
> functions fail? Why would my error handling code work for some errors but
> return SQL_NO_DATA for others?|||Hi,
Did you try the sample code included in SQL server setup CD? Please refer
to odbcerr.cpp for details.
http://msdn.microsoft.com/library/d...-us/odbcsql/od_
odbcsamp_4pm8.asp
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVAmXFPVsZNNvWPT5azzwfawDXxLg==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| Subject: bcp api and error handling
| Date: Wed, 13 Apr 2005 19:27:02 -0700
| Lines: 21
| Message-ID: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:85683
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have implemented a VB.NET application which uses the ODBC BCP api to
bulk
| insert data into a Microsoft SQL 2000 database. I have included error
| handling as it is important to trap and log any errors when they occur.
The
| import works fine, and the error handling works fine for any ODBC errors
| (server not found, login problems etc are all logged properly).
|
| The problem is that ANY bcp error results in SQLGetDiagRec returning
| SQL_NO_DATA. The same code works for the ODBC errors from the SQL*
functions,
| but if any bcp_* function fails I always get SQL_NO_DATA. I am passing in
the
| connection handle with SQL_HANDLE_DBC as the handle type like the docs
say,
| but I have never managed to get a BCP error from this function.
|
| Sometimes I can run the input files (I am using data and format files for
| simplicity) with the command line bcp utility, and then I will see error
| messages I can use. Sometimes the bcp utility works however, so I need
the
| error handling to find out what is going wrong. (The application is
running
| under COM+ as a special domain user for security reasons).
|
| How should I use VB.NET to get the error messages when the ODBC BCP
| functions fail? Why would my error handling code work for some errors but
| return SQL_NO_DATA for others?
||||Thanks John and Peter for your responses.
I have created a sample and in doing so found an interesting phenomenon:
The code works in a "normal" app, but when run as a COM+ component it fails
to return BCP error messages. Commenting out the "Inherits ServicedComponent
"
line causes the error messages to work. Leave it in and I get SQL_NO_DATA.
I can't see how to add attachments with Microsoft's web based newsgroup
reader, so I signed up to geocities:
http://www.geocities.com/stephenchalkydavies/VbBcp.zip
How can I get BCP error messages when running in a COM+ application?
Thanks again,
Stephen|||Hi
This may be interesting
http://support.microsoft.com/defaul...kb;en-us;319243 but
nothing to do with COM+
John|||It doesn't mention COM+, and is talking about other methods (SQLGetData)
returning SQL_NO_DATA. I am getting bcp failures and the SQLGetDiagRec call
returns SQL_NO_DATA - but only when running in COM+.
I tried installing the MDAC patch anyway just in case - but it wouldn't
install, I think because I already upgraded to MDAC 2.8 and it is for 2.7.
Any more ideas? I really need to get the error handling working.
Thanks,
Stephen
"John Bell" wrote:

> Hi
> This may be interesting
> http://support.microsoft.com/defaul...kb;en-us;319243 but
> nothing to do with COM+
> John|||Hello Stephen,
Did you try SQLGetDiagRecW? The issue might be related to Unicode?
Also, you may want to contact our Develop Support Services by telephone so
that a dedicated Support Professional can assist you on this issue.To
obtain the phone numbers for specific technology request please take a look
at the web site listed below:
http://support.microsoft.com/defaul...S;PHONENUMBERS.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVDsxEOzHtU0b+iSc6szI5qAdg/Kw==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| References: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
<EA1606BF-08E9-45B2-A71A-ED1FCD739434@.microsoft.com>
<A3B4D1EE-DD56-4290-919C-0ED9ECE806F8@.microsoft.com>
<1113548068.421350.42470@.o13g2000cwo.googlegroups.com>
| Subject: Re: bcp api and error handling
| Date: Sun, 17 Apr 2005 18:08:01 -0700
| Lines: 21
| Message-ID: <98CECA7A-7E8A-43AD-9426-D2AA20A1821B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:517122
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| It doesn't mention COM+, and is talking about other methods (SQLGetData)
| returning SQL_NO_DATA. I am getting bcp failures and the SQLGetDiagRec
call
| returns SQL_NO_DATA - but only when running in COM+.
|
| I tried installing the MDAC patch anyway just in case - but it wouldn't
| install, I think because I already upgraded to MDAC 2.8 and it is for 2.7.
|
| Any more ideas? I really need to get the error handling working.
|
| Thanks,
| Stephen
|
| "John Bell" wrote:
|
| > Hi
| >
| > This may be interesting
| > http://support.microsoft.com/defaul...kb;en-us;319243 but
| > nothing to do with COM+
| >
| > John
||||Hi Peter,
I just tried SQLGetDiagRecW, but when I enabled COM+ (by un-commenting the
"Inherits ServicedComponent" line) I got the same SQL_NO_DATA return code
that I got with SQLGetDiagRec.
What could COM+ be doing that causes bcp to lose its errors?
Yesterday I tried running an ODBC trace. Outside of COM+, I see a [DIAG]
message in the trace followed by a successful call to SQLGetDiagRec. When ru
n
in COM+ the only difference is that the [DIAG] message is not in the trace
and hence the call to SQLGetDiagRec returns SQL_NO_DATA.
Is the error message getting stored somewhere else?
Thanks,
Stephen
"Peter Yang [MSFT]" wrote:

> Hello Stephen,
> Did you try SQLGetDiagRecW? The issue might be related to Unicode?|||Hello Stephen,
I haven't found a known issue on this behavior. Please contact our Develop
Support Services by telephone on debugging/dump analysis to further
troubleshoot this issue.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: bcp api and error handling
| thread-index: AcVEdfIhZtnSq5dWQby7kIwLKqtnvA==
| X-WBNR-Posting-Host: 203.57.240.95
| From: "examnotes" <chalky@.newsgroup.nospam>
| References: <B608ADB0-3DFC-438D-89A4-45D4582D87C1@.microsoft.com>
<EA1606BF-08E9-45B2-A71A-ED1FCD739434@.microsoft.com>
<A3B4D1EE-DD56-4290-919C-0ED9ECE806F8@.microsoft.com>
<1113548068.421350.42470@.o13g2000cwo.googlegroups.com>
<98CECA7A-7E8A-43AD-9426-D2AA20A1821B@.microsoft.com>
<VLbWZOARFHA.2316@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: bcp api and error handling
| Date: Mon, 18 Apr 2005 17:23:01 -0700
| Lines: 24
| Message-ID: <90CF8356-0BCF-4740-91A6-F29889FC0A24@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:517422
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
|
| I just tried SQLGetDiagRecW, but when I enabled COM+ (by un-commenting
the
| "Inherits ServicedComponent" line) I got the same SQL_NO_DATA return code
| that I got with SQLGetDiagRec.
|
| What could COM+ be doing that causes bcp to lose its errors?
|
| Yesterday I tried running an ODBC trace. Outside of COM+, I see a [DIAG]
| message in the trace followed by a successful call to SQLGetDiagRec. When
run
| in COM+ the only difference is that the [DIAG] message is not in the
trace
| and hence the call to SQLGetDiagRec returns SQL_NO_DATA.
|
| Is the error message getting stored somewhere else?
|
| Thanks,
| Stephen
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Stephen,
| >
| > Did you try SQLGetDiagRecW? The issue might be related to Unicode?
|
|