BCP Handling

Hi,

I am executing script like this. How to check for the errors if "master..xp_cmdshell @.bcpCommand" fails. Is there any way to verify that BCP is completed successfully

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P'

EXEC master..xp_cmdshell @.bcpCommand

Thanks in Advance,declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

|||Is is possible...........|||

In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P -ee:\myBCPerror.txt'

declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

CREATE TABLE #bcperr(input varchar(255) null)
INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
RAISERROR('There was an error with the BCP command.', 16, 1)
DROP TABLE #bcperr

You can also just do a SELECT * FROM #bcperr to get the actual error rows.

|||

thanks a lot..

any idea how to trap the number of rows that were transffered during the bcp out process...

|||

If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

declare @.rc int
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

output
NULL
- C:\MYBCPOUTPUT.TXT
23 rows copied.
NULL

(4 row(s) affected)

=;o)

/Kenneth

|||Hey how do I capture this in a table... betn thanks !!!|||

Here's an example.

create table #bcpResult
( result varchar(50) null )
go

declare @.rc int

insert #bcpResult
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
go
select * from #bcpResult where result like '%rows copied%'
go
drop table #bcpResult
go

=;o)
/Kenneth

bcp error handling

Hello
I am using bcp.exe to transfer data between remote servers and need a way to
do the error handling. What I need is simple - just to know whether the copy
was successful or not, because it needs to be in transaction with other
operations. There are situations when the return value of the exe is always
0 and nothing gets written into the error log ( for example copying from
file to table that violates a constraint ). All that came to me was to parse
the log file ( option -o ) for strings like 'error' and 'failed' but this
doesn't look like a great idea. And yes, I am using bcp in both directions,
so bulk insert will not do.
Any ideas of how to find whether the copy was successful?
(sql server 2000 sp3, in case it matters)
Thanks
Plamen
"Plamen Doykov" <p_doykov@.code.bg> schrieb im Newsbeitrag
news:uoZ1ear1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am using bcp.exe to transfer data between remote servers and need a
way to
> do the error handling. What I need is simple - just to know whether the
copy
> was successful or not, because it needs to be in transaction with other
> operations. There are situations when the return value of the exe is
always
> 0 and nothing gets written into the error log ( for example copying from
> file to table that violates a constraint ). All that came to me was to
parse
> the log file ( option -o ) for strings like 'error' and 'failed' but
this
> doesn't look like a great idea. And yes, I am using bcp in both
directions,
> so bulk insert will not do.
> Any ideas of how to find whether the copy was successful?
> (sql server 2000 sp3, in case it matters)
We settled with the output parsing also. That's what we use in Java:
private static final Pattern ERROR_PATTERN =
Pattern.compile(
"^\\s*(?:SQLState\\s*=\\s*([^,]+?)\\s*,\\s*NativeError\\s*=\\s*([+-]?\\d+)
\\s*|" +
"Error\\s+=\\s*+(.*?))$", Pattern.MULTILINE );
/**
* Extract error BCP messages from the given process output.
*
* @.param bcpOutput the output to extract the messages from.
* @.return a List of {@.link String} with the messages.
*/
protected static List extractErrorMessages( String bcpOutput ) {
List messages = new LinkedList();
Matcher m = ERROR_PATTERN.matcher( bcpOutput );
SqlServerError err = null;
while ( m.find() ) {
String message = m.group( 3 );
if ( message == null ) {
/*
* no message => first line:
* SQLState = 23000, NativeError = 3604
*/
err = new SqlServerError();
err.setSqlState( m.group( 1 ) );
err.setNativeError( StringParsing.string2int( m.group(
2 ) ) );
}
else {
/*
* second line:
* Error = [Microsoft][ODBC SQL Server Driver][SQL
Server]Doppelter Schl?ssel wurde ignoriert.
*/
err.setMessage( message );
messages.add( err );
err = null;
}
}
return messages;
}
Kind regards
robert

BCP Error handling

I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.Are your BCP statements in a stored procedure?|||Here is something I hope will help:

declare
@.FILENAME_C nvarchar(255),
@.sqlstmt2 nvarchar(255),
@.outputcatch varchar(1000)

SET @.FILENAME_C= 'c:\it.txt'
SET @.SQLSTMT2='BCP "SELECT claimnumber from demo.dbo.wcclm1" queryout '+@.FILENAME_C+' /c /t, /r \n /U demo /P demo /S '+@.@.servername
EXEC @.outputcatch=MASTER.DBO.XP_CMDSHELL @.SQLSTMT2, no_output --@.outputcatch=OUTPUT

select @.outputcatch

Note that this sample has a simple select statement. If I change claimnumber to clamnumber @.outputcatch is 1 due to failure. Put it back to claimnumber and it returns 0 which indicates success.

Just keep looking at @.outputcatch and if you get a 1 RETURN.

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?
|
|