bcp_init and SQL Native Client

Hi all,
I am having trouble to get the bulk copy operations working in
collaboration with SQL Native Client.
My test program crashed with an access violation in the call to
bcp_init.
I know that the error is probably mine, but I cannot find any
mistakes.
I have include the C++ source below, and I hope that someone can help
me:
#include <windows.h>
#include <oledb.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#define _SQLNCLI_ODBC_
#include <sqlncli.h>
#include <cassert>
#include <iostream>
namespace
{
HENV createEnvironment()
{
HENV environment;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&environment);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLSetEnvAttr failed");
}
return environment;
}
SQLHDBC createConnection(HENV environment)
{
SQLHDBC connection;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
// Need to set this prior to connection.
result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
SQL_BCP_ON,
SQL_IS_INTEGER);
result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
<SQLCHAR*> (
const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
(
const_cast <char*> ("password")), SQL_NTS);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLConnect failed");
}
return connection;
}
int realMain(int argc, char *argv[])
{
HENV environment = createEnvironment();
SQLHDBC connection = createConnection(environment);
RETCODE result;
result = bcp_init(connection, "testdata", 0, 0, DB_IN);
assert(result != FAIL);
return 0;
}
} // anonymous namespace
int main(int argc, char* argv[])
{
try
{
return realMain(argc, argv);
}
catch (const std::exception& ex)
{
std::cerr << "exception: " << ex.what() << std::endl;
}
return 1;
}There is no error in my side. To enable trobule-shooting, you may add the
following code segment just after bcp_init:
result = bcp_init(connection, "myTable", 0, 0, DB_IN);
char SQLState[6] = "";
char Msg[256] = "";
SQLINTEGER iNativeError = 0;
SQLSMALLINT iMsgLen = 0;
int iRc = SQLGetDiagRec(SQL_HANDLE_DBC, connection, 1,
(SQLCHAR*)SQLState, &iNativeError, (SQLCHAR*)Msg, 256, &iMsgLen);
if (iRc != SQL_NO_DATA) {
printf("SQLState=%s, NativeError=%d, Msg=%s\n", SQLState,
iNativeError, Msg);
}
assert(result != FAIL);
Ming.
MDAC Team, Microsoft.
"Peter" wrote:

> Hi all,
> I am having trouble to get the bulk copy operations working in
> collaboration with SQL Native Client.
> My test program crashed with an access violation in the call to
> bcp_init.
> I know that the error is probably mine, but I cannot find any
> mistakes.
> I have include the C++ source below, and I hope that someone can help
> me:
> #include <windows.h>
> #include <oledb.h>
> #include <sql.h>
> #include <sqlext.h>
> #include <sqltypes.h>
> #define _SQLNCLI_ODBC_
> #include <sqlncli.h>
> #include <cassert>
> #include <iostream>
> namespace
> {
> HENV createEnvironment()
> {
> HENV environment;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
> &environment);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
> reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLSetEnvAttr failed");
> }
> return environment;
> }
> SQLHDBC createConnection(HENV environment)
> {
> SQLHDBC connection;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> // Need to set this prior to connection.
> result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
> SQL_BCP_ON,
> SQL_IS_INTEGER);
> result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
> const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
> <SQLCHAR*> (
> const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
> (
> const_cast <char*> ("password")), SQL_NTS);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLConnect failed");
> }
> return connection;
> }
> int realMain(int argc, char *argv[])
> {
> HENV environment = createEnvironment();
> SQLHDBC connection = createConnection(environment);
> RETCODE result;
> result = bcp_init(connection, "testdata", 0, 0, DB_IN);
> assert(result != FAIL);
> return 0;
> }
> } // anonymous namespace
> int main(int argc, char* argv[])
> {
> try
> {
> return realMain(argc, argv);
> }
> catch (const std::exception& ex)
> {
> std::cerr << "exception: " << ex.what() << std::endl;
> }
> return 1;
> }
>sql

bcp_init and SQL Native Client

Hi all,
I am having trouble to get the bulk copy operations working in
collaboration with SQL Native Client.
My test program crashed with an access violation in the call to
bcp_init.
I know that the error is probably mine, but I cannot find any
mistakes.
I have include the C++ source below, and I hope that someone can help
me:
#include <windows.h>
#include <oledb.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#define _SQLNCLI_ODBC_
#include <sqlncli.h>
#include <cassert>
#include <iostream>
namespace
{
HENV createEnvironment()
{
HENV environment;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&environment);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLSetEnvAttr failed");
}
return environment;
}
SQLHDBC createConnection(HENV environment)
{
SQLHDBC connection;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
// Need to set this prior to connection.
result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
SQL_BCP_ON,
SQL_IS_INTEGER);
result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
<SQLCHAR*> (
const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
(
const_cast <char*> ("password")), SQL_NTS);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLConnect failed");
}
return connection;
}
int realMain(int argc, char *argv[])
{
HENV environment = createEnvironment();
SQLHDBC connection = createConnection(environment);
RETCODE result;
result = bcp_init(connection, "testdata", 0, 0, DB_IN);
assert(result != FAIL);
return 0;
}
} // anonymous namespace
int main(int argc, char* argv[])
{
try
{
return realMain(argc, argv);
}
catch (const std::exception& ex)
{
std::cerr << "exception: " << ex.what() << std::endl;
}
return 1;
}
There is no error in my side. To enable trobule-shooting, you may add the
following code segment just after bcp_init:
result = bcp_init(connection, "myTable", 0, 0, DB_IN);
char SQLState[6] = "";
char Msg[256] = "";
SQLINTEGER iNativeError = 0;
SQLSMALLINT iMsgLen = 0;
int iRc = SQLGetDiagRec(SQL_HANDLE_DBC, connection, 1,
(SQLCHAR*)SQLState, &iNativeError, (SQLCHAR*)Msg, 256, &iMsgLen);
if (iRc != SQL_NO_DATA) {
printf("SQLState=%s, NativeError=%d, Msg=%s\n", SQLState,
iNativeError, Msg);
}
assert(result != FAIL);
Ming.
MDAC Team, Microsoft.
"Peter" wrote:

> Hi all,
> I am having trouble to get the bulk copy operations working in
> collaboration with SQL Native Client.
> My test program crashed with an access violation in the call to
> bcp_init.
> I know that the error is probably mine, but I cannot find any
> mistakes.
> I have include the C++ source below, and I hope that someone can help
> me:
> #include <windows.h>
> #include <oledb.h>
> #include <sql.h>
> #include <sqlext.h>
> #include <sqltypes.h>
> #define _SQLNCLI_ODBC_
> #include <sqlncli.h>
> #include <cassert>
> #include <iostream>
> namespace
> {
> HENV createEnvironment()
> {
> HENV environment;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
> &environment);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
> reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLSetEnvAttr failed");
> }
> return environment;
> }
> SQLHDBC createConnection(HENV environment)
> {
> SQLHDBC connection;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> // Need to set this prior to connection.
> result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
> SQL_BCP_ON,
> SQL_IS_INTEGER);
> result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
> const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
> <SQLCHAR*> (
> const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
> (
> const_cast <char*> ("password")), SQL_NTS);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLConnect failed");
> }
> return connection;
> }
> int realMain(int argc, char *argv[])
> {
> HENV environment = createEnvironment();
> SQLHDBC connection = createConnection(environment);
> RETCODE result;
> result = bcp_init(connection, "testdata", 0, 0, DB_IN);
> assert(result != FAIL);
> return 0;
> }
> } // anonymous namespace
> int main(int argc, char* argv[])
> {
> try
> {
> return realMain(argc, argv);
> }
> catch (const std::exception& ex)
> {
> std::cerr << "exception: " << ex.what() << std::endl;
> }
> return 1;
> }
>

bcp_init and SQL Native Client

Hi all,
I am having trouble to get the bulk copy operations working in
collaboration with SQL Native Client.
My test program crashed with an access violation in the call to
bcp_init.
I know that the error is probably mine, but I cannot find any
mistakes.
I have include the C++ source below, and I hope that someone can help
me:
#include <windows.h>
#include <oledb.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#define _SQLNCLI_ODBC_
#include <sqlncli.h>
#include <cassert>
#include <iostream>
namespace
{
HENV createEnvironment()
{
HENV environment;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&environment);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLSetEnvAttr failed");
}
return environment;
}
SQLHDBC createConnection(HENV environment)
{
SQLHDBC connection;
SQLRETURN result;
result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLAllocHandle failed");
}
// Need to set this prior to connection.
result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
SQL_BCP_ON,
SQL_IS_INTEGER);
result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
<SQLCHAR*> (
const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
(
const_cast <char*> ("password")), SQL_NTS);
if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
{
throw std::runtime_error("SQLConnect failed");
}
return connection;
}
int realMain(int argc, char *argv[])
{
HENV environment = createEnvironment();
SQLHDBC connection = createConnection(environment);
RETCODE result;
result = bcp_init(connection, "testdata", 0, 0, DB_IN);
assert(result != FAIL);
return 0;
}
} // anonymous namespace
int main(int argc, char* argv[])
{
try
{
return realMain(argc, argv);
}
catch (const std::exception& ex)
{
std::cerr << "exception: " << ex.what() << std::endl;
}
return 1;
}There is no error in my side. To enable trobule-shooting, you may add the
following code segment just after bcp_init:
result = bcp_init(connection, "myTable", 0, 0, DB_IN);
char SQLState[6] = "";
char Msg[256] = "";
SQLINTEGER iNativeError = 0;
SQLSMALLINT iMsgLen = 0;
int iRc = SQLGetDiagRec(SQL_HANDLE_DBC, connection, 1,
(SQLCHAR*)SQLState, &iNativeError, (SQLCHAR*)Msg, 256, &iMsgLen);
if (iRc != SQL_NO_DATA) {
printf("SQLState=%s, NativeError=%d, Msg=%s\n", SQLState,
iNativeError, Msg);
}
assert(result != FAIL);
Ming.
MDAC Team, Microsoft.
"Peter" wrote:
> Hi all,
> I am having trouble to get the bulk copy operations working in
> collaboration with SQL Native Client.
> My test program crashed with an access violation in the call to
> bcp_init.
> I know that the error is probably mine, but I cannot find any
> mistakes.
> I have include the C++ source below, and I hope that someone can help
> me:
> #include <windows.h>
> #include <oledb.h>
> #include <sql.h>
> #include <sqlext.h>
> #include <sqltypes.h>
> #define _SQLNCLI_ODBC_
> #include <sqlncli.h>
> #include <cassert>
> #include <iostream>
> namespace
> {
> HENV createEnvironment()
> {
> HENV environment;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
> &environment);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> result = SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION,
> reinterpret_cast <SQLPOINTER> (SQL_OV_ODBC3), SQL_IS_INTEGER);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLSetEnvAttr failed");
> }
> return environment;
> }
> SQLHDBC createConnection(HENV environment)
> {
> SQLHDBC connection;
> SQLRETURN result;
> result = SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLAllocHandle failed");
> }
> // Need to set this prior to connection.
> result = SQLSetConnectAttr(connection, SQL_COPT_SS_BCP, (void*)
> SQL_BCP_ON,
> SQL_IS_INTEGER);
> result = SQLConnect(connection, reinterpret_cast <SQLCHAR*> (
> const_cast <char*> ("database")), SQL_NTS, reinterpret_cast
> <SQLCHAR*> (
> const_cast <char*> ("user")), SQL_NTS, reinterpret_cast <SQLCHAR*>
> (
> const_cast <char*> ("password")), SQL_NTS);
> if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
> {
> throw std::runtime_error("SQLConnect failed");
> }
> return connection;
> }
> int realMain(int argc, char *argv[])
> {
> HENV environment = createEnvironment();
> SQLHDBC connection = createConnection(environment);
> RETCODE result;
> result = bcp_init(connection, "testdata", 0, 0, DB_IN);
> assert(result != FAIL);
> return 0;
> }
> } // anonymous namespace
> int main(int argc, char* argv[])
> {
> try
> {
> return realMain(argc, argv);
> }
> catch (const std::exception& ex)
> {
> std::cerr << "exception: " << ex.what() << std::endl;
> }
> return 1;
> }
>

BCP with ODBC Native driver

We are using Visual Studio 2003 to develop a VC++ application to support SQL Server 2000 and 2005 through ODBC driver. I was able to successfully test the application with ODBC driver for 2000. But when it comes to the SQL Server 2005 using native ODBC driver it fails with "ODBCBCP.dll is not compatible with 'SQL Native Client' driver. Please configure 'SQL Server' driver for the DSN or connection string.

Then I tried to use the sqlncli.h inplace of odbcss.h and replaced the odbcbcp.lib with sqlncli.lib pointing to the C:\Program Files\Microsoft SQL Server\90\SDK\Lib\x86 folder.

Code Snippet

#include <sql.h>

#include <sqlext.h>

#include <sqltypes.h>

#define _SQLNCLI_ODBC_

#include <sqlncli.h>

Now I can not compile the program. The errors I am getting are as follows:

c:\Program Files\Microsoft SQL Server\90\SDK\Include\sqlncli.h(2894): error C2061: syntax error : identifier 'DB_UPARAMS'

c:\Program Files\Microsoft SQL Server\90\SDK\Include\sqlncli.h(2901): error C2061: syntax error : identifier 'DB_UPARAMS'
etc

c:\Program Files\Microsoft SQL Server\90\SDK\Include\sqlncli.h(2938): error C2061: syntax error : identifier 'HCHAPTER'
c:\Program Files\Microsoft SQL Server\90\SDK\Include\sqlncli.h(2944): error C2061: syntax error : identifier 'HCHAPTER'
etc

The same code just works fine in the Visual Studio 2005, but we need the dll in Visual studio 2003.

I think I am missing pretty obvious. Your help is appreciated.

Can you try not defining _SQLNCLI_ODBC_, I believe that would work.

Thanks

Waseem

sql

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm
"ChrisR" wrote:
> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> > One of my upcoming projects is going to be to move some data & its
> > related heavy processing off an production server off to a newer
> > server, do the processing, and then move the data back. There will be
> > large amounts of data going out & going in. My question deals with
> > efficiency of this data movement. I read in BOL that for exporting,
> > bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> > this data was coming out of SQL Server to a flat text file or bcp'ing
> > over to another SQL instance. So I'm not sure if I should bcp out to
> > text, then bcp in to my other server...or just bcp directly to the
> > other server. Also, I suppose I should not even consider DTS, since
> > bcp is just as fast, and there won't be any significant transformations
> > on the data? Opinions?
> >
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?
BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>
|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?
|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegr oups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>
|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_c...timization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
>
>
sql

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.
Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:

> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode format
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>
|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...[vbcol=seagreen]
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
|||I would still try -n
John
"Jeje" wrote:
[vbcol=seagreen]
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their native
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> I would still try -n
> John
> "Jeje" wrote:
|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...[vbcol=seagreen]
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character when
importing the data with BCP.
HTH
John
"Jeje" wrote:
[vbcol=seagreen]
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds when
I use BCP.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This issue could be cause be either a row or field terminator being present
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not be
> imported. The easiest way to correct the former is to generate the data file
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character when
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
[vbcol=seagreen]
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds when
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:
> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode format
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
>> Hi,
>> I'm using BCP to copy some data between 2 servers.
>> I'm using the -N option to export the tables into the Native Unicode
>> format
>> this works fine
>> except for few tables where I receive the truncation error:
>> SQLState = 22001, NativeError = 0
>> Error = [Microsoft][SQL Native Client]String data, right truncation
>> this process has been used for few month without any issue and now I
>> start
>> to see this error.
>> any idea?
>> thanks for your quick guides!
>> jerome.
>>|||I would still try -n
John
"Jeje" wrote:
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their native
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > Hi
> >
> > -N is keep non-text native and -n is native so you may want to try that
> > instead.
> >
> > John
> >
> > "Jeje" wrote:
> >
> >> Hi,
> >>
> >> I'm using BCP to copy some data between 2 servers.
> >>
> >> I'm using the -N option to export the tables into the Native Unicode
> >> format
> >> this works fine
> >> except for few tables where I receive the truncation error:
> >> SQLState = 22001, NativeError = 0
> >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >>
> >> this process has been used for few month without any issue and now I
> >> start
> >> to see this error.
> >>
> >> any idea?
> >> thanks for your quick guides!
> >>
> >> jerome.
> >>
> >>|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
> I would still try -n
> John
> "Jeje" wrote:
> > the BOL says:
> > -N is native unicode format:
> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >
> > the varchar and char are exported in Unicode and others are in their native
> > format.
> >
> > we have solve the issue for few tables by using the -w instead of -N
> > but some tables continues to suffer the same issue!
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > Hi
> > >
> > > -N is keep non-text native and -n is native so you may want to try that
> > > instead.
> > >
> > > John
> > >
> > > "Jeje" wrote:
> > >
> > >> Hi,
> > >>
> > >> I'm using BCP to copy some data between 2 servers.
> > >>
> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> format
> > >> this works fine
> > >> except for few tables where I receive the truncation error:
> > >> SQLState = 22001, NativeError = 0
> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >>
> > >> this process has been used for few month without any issue and now I
> > >> start
> > >> to see this error.
> > >>
> > >> any idea?
> > >> thanks for your quick guides!
> > >>
> > >> jerome.
> > >>
> > >>|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
>> I would still try -n
>> John
>> "Jeje" wrote:
>> > the BOL says:
>> > -N is native unicode format:
>> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
>> >
>> > the varchar and char are exported in Unicode and others are in their
>> > native
>> > format.
>> >
>> > we have solve the issue for few tables by using the -w instead of -N
>> > but some tables continues to suffer the same issue!
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
>> > > Hi
>> > >
>> > > -N is keep non-text native and -n is native so you may want to try
>> > > that
>> > > instead.
>> > >
>> > > John
>> > >
>> > > "Jeje" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> I'm using BCP to copy some data between 2 servers.
>> > >>
>> > >> I'm using the -N option to export the tables into the Native Unicode
>> > >> format
>> > >> this works fine
>> > >> except for few tables where I receive the truncation error:
>> > >> SQLState = 22001, NativeError = 0
>> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
>> > >>
>> > >> this process has been used for few month without any issue and now I
>> > >> start
>> > >> to see this error.
>> > >>
>> > >> any idea?
>> > >> thanks for your quick guides!
>> > >>
>> > >> jerome.
>> > >>
>> > >>|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character when
importing the data with BCP.
HTH
John
"Jeje" wrote:
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > I am having a similar problem.
> > When I use -N, I get this error:
> > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]String data, right truncation
> >
> > When I use -n I get this error:
> > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]Unexpected EOF encountered in BCP data-file
> >
> > Any help will be appreciated.
> >
> > Thanks
> >
> >
> > "John Bell" wrote:
> >
> >> I would still try -n
> >>
> >> John
> >>
> >> "Jeje" wrote:
> >>
> >> > the BOL says:
> >> > -N is native unicode format:
> >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >> >
> >> > the varchar and char are exported in Unicode and others are in their
> >> > native
> >> > format.
> >> >
> >> > we have solve the issue for few tables by using the -w instead of -N
> >> > but some tables continues to suffer the same issue!
> >> >
> >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> >> > > Hi
> >> > >
> >> > > -N is keep non-text native and -n is native so you may want to try
> >> > > that
> >> > > instead.
> >> > >
> >> > > John
> >> > >
> >> > > "Jeje" wrote:
> >> > >
> >> > >> Hi,
> >> > >>
> >> > >> I'm using BCP to copy some data between 2 servers.
> >> > >>
> >> > >> I'm using the -N option to export the tables into the Native Unicode
> >> > >> format
> >> > >> this works fine
> >> > >> except for few tables where I receive the truncation error:
> >> > >> SQLState = 22001, NativeError = 0
> >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >> > >>
> >> > >> this process has been used for few month without any issue and now I
> >> > >> start
> >> > >> to see this error.
> >> > >>
> >> > >> any idea?
> >> > >> thanks for your quick guides!
> >> > >>
> >> > >> jerome.
> >> > >>
> >> > >>|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds when
I use BCP.
Thanks
"John Bell" wrote:
> Hi
> This issue could be cause be either a row or field terminator being present
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not be
> imported. The easiest way to correct the former is to generate the data file
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character when
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
> > yes, same error here.
> >
> >
> > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > I am having a similar problem.
> > > When I use -N, I get this error:
> > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]String data, right truncation
> > >
> > > When I use -n I get this error:
> > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]Unexpected EOF encountered in BCP data-file
> > >
> > > Any help will be appreciated.
> > >
> > > Thanks
> > >
> > >
> > > "John Bell" wrote:
> > >
> > >> I would still try -n
> > >>
> > >> John
> > >>
> > >> "Jeje" wrote:
> > >>
> > >> > the BOL says:
> > >> > -N is native unicode format:
> > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > >> >
> > >> > the varchar and char are exported in Unicode and others are in their
> > >> > native
> > >> > format.
> > >> >
> > >> > we have solve the issue for few tables by using the -w instead of -N
> > >> > but some tables continues to suffer the same issue!
> > >> >
> > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > >> > > Hi
> > >> > >
> > >> > > -N is keep non-text native and -n is native so you may want to try
> > >> > > that
> > >> > > instead.
> > >> > >
> > >> > > John
> > >> > >
> > >> > > "Jeje" wrote:
> > >> > >
> > >> > >> Hi,
> > >> > >>
> > >> > >> I'm using BCP to copy some data between 2 servers.
> > >> > >>
> > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> > >> format
> > >> > >> this works fine
> > >> > >> except for few tables where I receive the truncation error:
> > >> > >> SQLState = 22001, NativeError = 0
> > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >> > >>
> > >> > >> this process has been used for few month without any issue and now I
> > >> > >> start
> > >> > >> to see this error.
> > >> > >>
> > >> > >> any idea?
> > >> > >> thanks for your quick guides!
> > >> > >>
> > >> > >> jerome.
> > >> > >>
> > >> > >>|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds when
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:
> > Hi
> >
> > This issue could be cause be either a row or field terminator being present
> > withing your data or possibly a missing row terminator at the end of the
> > file. The latter should be easy to identify as only the last row may not be
> > imported. The easiest way to correct the former is to generate the data file
> > with specify terminators that you will know are not going to occur in the
> > data, then use the -r and -t option to specify the appropriate character when
> > importing the data with BCP.
> >
> > HTH
> >
> > John
> >
> >
> > "Jeje" wrote:
> >
> > > yes, same error here.
> > >
> > >
> > > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > > I am having a similar problem.
> > > > When I use -N, I get this error:
> > > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]String data, right truncation
> > > >
> > > > When I use -n I get this error:
> > > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]Unexpected EOF encountered in BCP data-file
> > > >
> > > > Any help will be appreciated.
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > >> I would still try -n
> > > >>
> > > >> John
> > > >>
> > > >> "Jeje" wrote:
> > > >>
> > > >> > the BOL says:
> > > >> > -N is native unicode format:
> > > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > > >> >
> > > >> > the varchar and char are exported in Unicode and others are in their
> > > >> > native
> > > >> > format.
> > > >> >
> > > >> > we have solve the issue for few tables by using the -w instead of -N
> > > >> > but some tables continues to suffer the same issue!
> > > >> >
> > > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > >> > > Hi
> > > >> > >
> > > >> > > -N is keep non-text native and -n is native so you may want to try
> > > >> > > that
> > > >> > > instead.
> > > >> > >
> > > >> > > John
> > > >> > >
> > > >> > > "Jeje" wrote:
> > > >> > >
> > > >> > >> Hi,
> > > >> > >>
> > > >> > >> I'm using BCP to copy some data between 2 servers.
> > > >> > >>
> > > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > > >> > >> format
> > > >> > >> this works fine
> > > >> > >> except for few tables where I receive the truncation error:
> > > >> > >> SQLState = 22001, NativeError = 0
> > > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > > >> > >>
> > > >> > >> this process has been used for few month without any issue and now I
> > > >> > >> start
> > > >> > >> to see this error.
> > > >> > >>
> > > >> > >> any idea?
> > > >> > >> thanks for your quick guides!
> > > >> > >>
> > > >> > >> jerome.
> > > >> > >>
> > > >> > >>sql

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:

> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode forma
t
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncatio
n
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...[vbcol=seagreen]
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
>|||I would still try -n
John
"Jeje" wrote:
[vbcol=seagreen]
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their nativ
e
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> I would still try -n
> John
> "Jeje" wrote:
>|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...[vbcol=seagreen]
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
>|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character whe
n
importing the data with BCP.
HTH
John
"Jeje" wrote:
[vbcol=seagreen]
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds whe
n
I use BCP.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This issue could be cause be either a row or field terminator being presen
t
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not b
e
> imported. The easiest way to correct the former is to generate the data fi
le
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character w
hen
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
>|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
[vbcol=seagreen]
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds w
hen
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:
>