bcp_init fails on Windows XP, works fine on Windows 2000

I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?
I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:

> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>

bcp_init fails on Windows XP, works fine on Windows 2000

I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:

> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>

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_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCH
I have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:

> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>
sql

bcp_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCHI have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:
> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>

BCP unility and SQL Server Management Studio Express

I ahve a utility which uses the bulk copy untility (or program) bcp.exe
which doesn't seem to be part of SQL Server Management Studio Express. I
don't need a database server on that machine but I do want the bcp.exe
program. How can I get this?
Many thanks for any help in advance!
Peter (peteATkapiti.co.nz) writes:
> I ahve a utility which uses the bulk copy untility (or program) bcp.exe
> which doesn't seem to be part of SQL Server Management Studio Express. I
> don't need a database server on that machine but I do want the bcp.exe
> program. How can I get this?
BCP comes with SQL Express. I don't remember off-hand if SQL Express
offers a client-only install, but you could try running the install and
see if you get that offer. Just make sure press all Advanced buttons
you see.
I guess you also could cull it from a an existing installation. You
would have to install SQL Native Client (which is available as a separate
install), but if you are lucky that maybe all. (Or have I seen a
BCP.rll somewhere?)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I had to install the SQL Express Toolkit to get bcp.exe.
But I now have a really strange situation I wonder if anyone can answer...
Just installed on Vista Ultimate.
At first I couldn't find BCP in what I thought should be an expected
directory (A bin directory in the SQL Express folder tree).
Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
supposed to be in
...\Program Files\Microsoft SQL Server\90\Tools
But, when I browsed to that location, there are no files, just folders.
Re-launched Explorer to run "As Administrator" (which itself is an irritant)
in case files were hidden, but not so.
Started manually opening up folders and I found it in the same folder as
bcp.rll
...\Program Files\Microsoft SQL Server\90\Tools\Binn
Why the weirdness? This is the first time I've run a Microsoft OS Search for
over 15 years on anything which returned bad results like this.
Tony Su
www.su-networking.com
ISA
SBS
Enterprise Mobile Solutions Architect
"Erland Sommarskog" wrote:

> Peter (peteATkapiti.co.nz) writes:
> BCP comes with SQL Express. I don't remember off-hand if SQL Express
> offers a client-only install, but you could try running the install and
> see if you get that offer. Just make sure press all Advanced buttons
> you see.
> I guess you also could cull it from a an existing installation. You
> would have to install SQL Native Client (which is available as a separate
> install), but if you are lucky that maybe all. (Or have I seen a
> BCP.rll somewhere?)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Tony Su (TonySu@.discussions.microsoft.com) writes:
> I had to install the SQL Express Toolkit to get bcp.exe.
> But I now have a really strange situation I wonder if anyone can answer...
> Just installed on Vista Ultimate.
> At first I couldn't find BCP in what I thought should be an expected
> directory (A bin directory in the SQL Express folder tree).
> Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
> supposed to be in
> ..\Program Files\Microsoft SQL Server\90\Tools
> But, when I browsed to that location, there are no files, just folders.
> Re-launched Explorer to run "As Administrator" (which itself is an
> irritant) in case files were hidden, but not so.
> Started manually opening up folders and I found it in the same folder as
> bcp.rll
> ..\Program Files\Microsoft SQL Server\90\Tools\Binn
> Why the weirdness? This is the first time I've run a Microsoft OS Search
> for over 15 years on anything which returned bad results like this.
It appears that your question is more about the search in Vista, and
I'm afraid I can't help with that. The search in Vista is very mysterious,
and nothing use unless I'm in desperation.
Tools\Binn is indeed the place..
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

BCP path problem

Why can't I include the path to the bcp utility when I use xp_cmdshell?
This works fine in the command window:
"C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
"ABData.dbo.tCMD_OutputToFile" out "c:\temp\abdata.txt" -c -U"chagus" -P"c"
but this don't work from within SQL Query Analyzer (or a stored procedure):
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
"c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
Thanks in advance
/KarinIs the command window on your client PC or the SQL Server ...?
Default SQL Server install should add the path for BCP.EXE to the server
%PATH%. You shouldn't need to include it eg:-
EXEc master..xp_cmdshell "bcp ..." should suffice
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> Why can't I include the path to the bcp utility when I use xp_cmdshell?
> This works fine in the command window:
> "C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
> "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"
> but this don't work from within SQL Query Analyzer (or a stored
> procedure):
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
>
> Thanks in advance
> /Karin|||"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> Why can't I include the path to the bcp utility when I use xp_cmdshell?
> This works fine in the command window:
> "C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
> "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"
> but this don't work from within SQL Query Analyzer (or a stored
> procedure):
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
>
> Thanks in advance
> /Karin
What error are you getting?
When you xp_cmdshell, you are executing in the security context of either
the MSSQLServer service account (if you are an sa in the db), or in the
security account associated with the SQLExecutiveCmdExec if you are a
non-sa. Ensure that whichever account has the rights needed to execute in
that directory.
Rick Sawtell
MCT, MCSD, MCDBA|||I have both the client and server on my developer-PC so I run the usual
command window (Run cmd). The problem is that when I specify the path in
xp_cmdshell no file will be created and no error message appears anywhere,
I've looked in SQL Server's Log and Event Log. This is the only thing I get:
The command(s) completed successfully.
I know that I don't need to specify the path, but I have a customer which
probably have some other bcp installed (perhaps sybase I don't know) beause
she gets the following message:
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
I read that It should works even if you specify the path, so why doesn't it?
"Ryan" wrote:

> Is the command window on your client PC or the SQL Server ...?
> Default SQL Server install should add the path for BCP.EXE to the server
> %PATH%. You shouldn't need to include it eg:-
> EXEc master..xp_cmdshell "bcp ..." should suffice
> --
> HTH. Ryan
> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
>
>|||I don't get any error message, only: The command(s) completed successfully.
But no file creates in C:\temp
I am sa and administrator and SQL Server runs under local system account.
"Rick Sawtell" wrote:

> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> What error are you getting?
> When you xp_cmdshell, you are executing in the security context of either
> the MSSQLServer service account (if you are an sa in the db), or in the
> security account associated with the SQLExecutiveCmdExec if you are a
> non-sa. Ensure that whichever account has the rights needed to execute i
n
> that directory.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Having read the error message and looked at your BCP cmd line i can't see
the -Sservername switch.
eg :- bcp ABData.dbo.tCMD_OutputToFile out
c:\temp\abdata.txt -c -Uchagus -Pc -Sservername
But if that were the case it wouldn't work on your dev machine either..
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>I have both the client and server on my developer-PC so I run the usual
> command window (Run cmd). The problem is that when I specify the path in
> xp_cmdshell no file will be created and no error message appears anywhere,
> I've looked in SQL Server's Log and Event Log. This is the only thing I
> get:
> The command(s) completed successfully.
> I know that I don't need to specify the path, but I have a customer which
> probably have some other bcp installed (perhaps sybase I don't know)
> beause
> she gets the following message:
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> I read that It should works even if you specify the path, so why doesn't
> it?
> "Ryan" wrote:
>|||No, the problem is that no file will be created when I include the path to
bcp.exe in xp_cmdshell command-string... All works fine until I include the
path.
"Ryan" wrote:

> Having read the error message and looked at your BCP cmd line i can't see
> the -Sservername switch.
> eg :- bcp ABData.dbo.tCMD_OutputToFile out
> c:\temp\abdata.txt -c -Uchagus -Pc -Sservername
> But if that were the case it wouldn't work on your dev machine either..
> --
> HTH. Ryan
> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>
>|||I found the solution in SQL Server Books Online:
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have troubl
e
with embedded spaces, consider using FAT 8.3 file names as a workaround.
This doesn't work:
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
"c:\temp\authors.txt" -c -U"chagus" -P"c"', no_output
but this will:
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" ABData.dbo.tCMD_OutputToFile out
c:\temp\authors.txt -c -Uchagus -Pc', no_output
:o)
So that means that the output file cannot be created in a path with spaces
in it.
:o(
/Karin
"Karin" wrote:
> No, the problem is that no file will be created when I include the path to
> bcp.exe in xp_cmdshell command-string... All works fine until I include th
e
> path.
> "Ryan" wrote:
>|||I'll make a note of that for future..
Thanks for posting your solution
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:C0474E2C-9FAF-4E32-BB83-CF5DF46AF391@.microsoft.com...
>I found the solution in SQL Server Books Online:
> command_string cannot contain more than one set of double quotation marks.
> A
> single pair of quotation marks is necessary if any spaces are present in
> the
> file paths or program names referenced by command_string. If you have
> trouble
> with embedded spaces, consider using FAT 8.3 file names as a workaround.
> This doesn't work:
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\authors.txt" -c -U"chagus" -P"c"', no_output
> but this will:
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" ABData.dbo.tCMD_OutputToFile out
> c:\temp\authors.txt -c -Uchagus -Pc', no_output
> :o)
> So that means that the output file cannot be created in a path with spaces
> in it.
> :o(
> /Karin
>
> "Karin" wrote:
>|||Karin (Karin@.discussions.microsoft.com) writes:
> I know that I don't need to specify the path, but I have a customer
> which probably have some other bcp installed (perhaps sybase I don't
> know) beause she gets the following message:
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
Yes, CTLIB is Sybase.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP output - putting double quotes around text

Folks,
How can I program BCP to output text items in double quotes (")?
Here is an example (please try it) that trys to output some columns from a
table to csv file. However, due to the existence of commas within the
fields, the comma separation gets messed up.
USE [MASTER]
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1')
DROP TABLE mcg1
go
CREATE TABLE mcg1
(pk INT IDENTITY(1,1)
,Address_1 VARCHAR(100)
,City VARCHAR(100))
go
INSERT INTO mcg1 (Address_1, City) VALUES ('100 Road1, Suburb1' , 'BigCity1'
)
INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity2'
)
SELECT * FROM mcg1
Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
The output I get is below. You can see how the use of commas in the text
makes the comma separate list all
100 Road1, Suburb1,BigCity1
200 Road2, Suburb2,BigCity2
Thus what I want is
"100 Road1, Suburb1","BigCity1"
"200 Road2, Suburb2","BigCity2"
You can do this OK in DTS by specifying the text identifier to be
double-quotes.
I do NOT want to use DTS and want to be able to do via a T-SQL procedure.
Note that the real table I will export from has numeric datatypes and I woul
d
prefer NOT to wrap them in double-quotes too.
Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each
text field in double quotes. I may have to use a format file in which case
please provide the format file too.
Thanks in advance
Mgale1Exec Master..xp_Cmdshell 'bcp "SELECT '"' + Address_1 + '"', '"' + City + '"
'
FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
What you see above is a single quote, followed by a double-quote, followed
by another single quote. Instead of trying to get bcp to do the formatting,
have the query do it.
"mgale1" wrote:

> Folks,
> How can I program BCP to output text items in double quotes (")?
> Here is an example (please try it) that trys to output some columns from a
> table to csv file. However, due to the existence of commas within the
> fields, the comma separation gets messed up.
> --
> USE [MASTER]
> IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1')
> DROP TABLE mcg1
> go
> CREATE TABLE mcg1
> (pk INT IDENTITY(1,1)
> ,Address_1 VARCHAR(100)
> ,City VARCHAR(100))
> go
> INSERT INTO mcg1 (Address_1, City) VALUES ('100 Road1, Suburb1' , 'BigCity
1')
> INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity
2')
> SELECT * FROM mcg1
> Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout
> "C:\mcg1.csv" -c -t,"'
> --
> The output I get is below. You can see how the use of commas in the text
> makes the comma separate list all
> 100 Road1, Suburb1,BigCity1
> 200 Road2, Suburb2,BigCity2
> Thus what I want is
> "100 Road1, Suburb1","BigCity1"
> "200 Road2, Suburb2","BigCity2"
> You can do this OK in DTS by specifying the text identifier to be
> double-quotes.
> I do NOT want to use DTS and want to be able to do via a T-SQL procedure.
> Note that the real table I will export from has numeric datatypes and I wo
uld
> prefer NOT to wrap them in double-quotes too.
> Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each
> text field in double quotes. I may have to use a format file in which cas
e
> please provide the format file too.
> --
> Thanks in advance
> Mgale1|||Sorry, didn't read through your entire post.
Exec Master..xp_Cmdshell 'bcp "SELECT CASE WHEN ISNUMERIC(Address_1) = 1
THEN Address_1 ELSE ''"'' + Address_1 + ''"'' END, CASE WHEN ISNUMERIC(City)
= 1 THEN City ELSE ''"'' + City + ''"'' END
FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
Couple of things about the above:
-ISNUMERIC has been known to evaluate to 1 for things that aren't really
numeric. See http://www.aspfaq.com/show.asp?id=2390.
-Whatever datatype Address_1 and City are, if they are not numeric, must be
implicitly convertible to a character data type. If it isn't, you could use
CAST or CONVERT to force it.
"Mark Williams" wrote:
> Exec Master..xp_Cmdshell 'bcp "SELECT '"' + Address_1 + '"', '"' + City +
'"'
> FROM mcg1" queryout
> "C:\mcg1.csv" -c -t,"'
> What you see above is a single quote, followed by a double-quote, followed
> by another single quote. Instead of trying to get bcp to do the formatting
,
> have the query do it.
> --
>
> "mgale1" wrote:
>|||mgale1 (mgale1@.discussions.microsoft.com) writes:
> How can I program BCP to output text items in double quotes (")?
You could use a format file:
8.0
4
1 SQLCHAR 0 0 "\"" 0 ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\r\n" 3 col3 ""
This format file defines an output for three fields on the form
"data","more data","even, more, data"
There are four fields in the format file, because there are to be an
empty field to get the first " in place. The 0 on that row, means that
there is no database-column mapping here.
I will need to add that I've only tried this for input, not for output.
But it should work...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Folks,
Thanks for your replies - I am grateful for your help.
Unfortunately, I dont think your suggestions are going to work for me
Mark Williams - I cant get your syntax to work at all. Query Analyser gets
over all the quotes and simply wont run the BCP command. Instead i
t
returns the standard BCP error msg like 'BCP commands should be in the form
of..." etc
Erland - I have be having trouble getting your example to work. My command
is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1"
queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get "Host-file
columns may be skipped only when copying into the Server" as an error.
Thanks for your help - another colleague has found a way around this problem
for me by using DTSRUN on a command line. Thus please dont put too much
effort into working on this any further unless it is your wish
Thanks again, much appreciated
Mgale1|||mgale1 (mgale1@.discussions.microsoft.com) writes:
> Erland - I have be having trouble getting your example to work. My
> command is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM
> mcg1" queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get
> "Host-file columns may be skipped only when copying into the Server" as
> an error.
Drat, it didn't work out. Hm, shat if you change the SELECT to
SELECT '', Address_1, City FROM mcgl
and update the format file to read 1 2 3 and 0 1 2 in the database-
column column?
(Sorry for not testing myself, but it's about bed-time for me.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP in using bcp_sendrow fails, not enough columns bound

Hello, first posting:
I am using bcp_sendrow to INSERT data from program variables into the
database.
I am sending less columns than the database contains, because new
columns need to be added to the tables before the program is changed.
However, despite the fact that the unspecified columns have default
values associated with them, I still get the error message:
"Not enough columns bound"
every time I attempt to insert a line. It does not matter if the
column is NULLable or not, I have determined that it will fail if the
unspecified columns are at the end of the table. Unspecified columns
in the middle of the table automatically get populated with the default
value or NULL.
Has anyone else come across this problem? Is there some sort of switch
I can use to make this work? The only solution I have come up with is
to insert new columns in the middle of the table, which I shouldn't
have to do (and it's slow at that).
Thanks in advance,
RobBind all columns and set the columns to ignore as NULL like this:
r = bcp_bind(hdbc, /* Database Handle */
(const LPCBYTE)"", /* Empty String */
0, /* 0 prefix len */
SQL_NULL_DATA, /* length of -1 = set column to NULL */
(const LPCBYTE)"", /* Terminator Char */
1, /* Terminator Length = 1 for \0x00 */
SQLCHARACTER, /* Column type */
Col_Num /* Column Number */
);
"Robert B" <rob@.automatedfinancial.com> wrote in message
news:1148604213.015728.148440@.38g2000cwa.googlegroups.com...
> Hello, first posting:
> I am using bcp_sendrow to INSERT data from program variables into the
> database.
> I am sending less columns than the database contains, because new
> columns need to be added to the tables before the program is changed.
> However, despite the fact that the unspecified columns have default
> values associated with them, I still get the error message:
> "Not enough columns bound"
> every time I attempt to insert a line. It does not matter if the
> column is NULLable or not, I have determined that it will fail if the
> unspecified columns are at the end of the table. Unspecified columns
> in the middle of the table automatically get populated with the default
> value or NULL.
> Has anyone else come across this problem? Is there some sort of switch
> I can use to make this work? The only solution I have come up with is
> to insert new columns in the middle of the table, which I shouldn't
> have to do (and it's slow at that).
>
> Thanks in advance,
> Rob
>|||Thanks, Mike,
I have no problem binding to a column and inserting NULL data. My
problem is that my program will fail with "not enough columns bound" as
soon as a new column is added to the table, even if the new column is
set to NULL, default NULL. I need to maintain some sort of
backwards-compatibility where the program will continue to work even
though I have not specified all the columns.
Rob|||I'm working on a similar problem myself right now. The fix I'm looking at
is to grab the column information from the table ahead of time and bind the
columns dynamically using that information. The info is available in the
INFORMATION_SCHEMA views, and I'm also testing a couple of other methods
myself. It's definitely more work, but definitely necessary in my case.
"Robert B" <rob@.automatedfinancial.com> wrote in message
news:1148659354.289141.188110@.j55g2000cwa.googlegroups.com...
> Thanks, Mike,
> I have no problem binding to a column and inserting NULL data. My
> problem is that my program will fail with "not enough columns bound" as
> soon as a new column is added to the table, even if the new column is
> set to NULL, default NULL. I need to maintain some sort of
> backwards-compatibility where the program will continue to work even
> though I have not specified all the columns.
> Rob
>

BCP DB Library functions

Hi,
We are using the DB-Library functions in a C++ program to do a BCP out of
SQL Server and are having a problem getting it to output character data.
When using the batch BCP utility there is the -c option which outputs the
information as character data. Is it possible to set this option with the
available DB-Library functions, or some other way? bcp_init does not seem
to have the full set of options that are available in the batch utility.
Thanks in advance for any help.
Wayne AntinoreWayne Antinore (wantinore@.veramark.com) writes:
> We are using the DB-Library functions in a C++ program to do a BCP out of
> SQL Server and are having a problem getting it to output character data.
> When using the batch BCP utility there is the -c option which outputs the
> information as character data. Is it possible to set this option with the
> available DB-Library functions, or some other way? bcp_init does not seem
> to have the full set of options that are available in the batch utility.
You have access to all features that the command-line interface provides,
they are just more difficult to use. When using the API, you need to think
in terms of format files, no matter you if specify data format as such,
or if you define columns programmatically with bcp_colfmt. -c and -n are
just shortcuts for special cases format files.
The bcp functions are described in Books Online, but the documentation
is a bit obscure in places, not the least the one bcp_colfmt. I know,
I had a hard time to understand it myself. I have a Perl module for DB-Lib
which include the DB-Library routines, and even if you use C++, you may find
my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
dblib.html#bcp_routines.
By the way, why DB-Library? In my opinion, DB-Library is a very good
client library, but alas Microsoft does not agree with me and has
deprecated it, and has not added support for the new datatypes in SQL7
and later. You should probably use ODBC or OLE DB instead. (Which I'm
told have a very similar bulk-copy interface to DB-Library.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks Erland,
Since we were only putting out one column we formatted for the column and
all works well. We were still using DB-Library because this was an addition
to a component that already was using it so we were sticking with what we
knew (or thought we did!) .
Thanks, again.
Wayne
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AA27345CCYazorman@.127.0.0.1...
> Wayne Antinore (wantinore@.veramark.com) writes:
> > We are using the DB-Library functions in a C++ program to do a BCP out
of
> > SQL Server and are having a problem getting it to output character data.
> > When using the batch BCP utility there is the -c option which outputs
the
> > information as character data. Is it possible to set this option with
the
> > available DB-Library functions, or some other way? bcp_init does not
seem
> > to have the full set of options that are available in the batch utility.
> You have access to all features that the command-line interface provides,
> they are just more difficult to use. When using the API, you need to think
> in terms of format files, no matter you if specify data format as such,
> or if you define columns programmatically with bcp_colfmt. -c and -n are
> just shortcuts for special cases format files.
> The bcp functions are described in Books Online, but the documentation
> is a bit obscure in places, not the least the one bcp_colfmt. I know,
> I had a hard time to understand it myself. I have a Perl module for DB-Lib
> which include the DB-Library routines, and even if you use C++, you may
find
> my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
> dblib.html#bcp_routines.
> By the way, why DB-Library? In my opinion, DB-Library is a very good
> client library, but alas Microsoft does not agree with me and has
> deprecated it, and has not added support for the new datatypes in SQL7
> and later. You should probably use ODBC or OLE DB instead. (Which I'm
> told have a very similar bulk-copy interface to DB-Library.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

BCP DB Library functions

Hi,
We are using the DB-Library functions in a C++ program to do a BCP out of
SQL Server and are having a problem getting it to output character data.
When using the batch BCP utility there is the -c option which outputs the
information as character data. Is it possible to set this option with the
available DB-Library functions, or some other way? bcp_init does not seem
to have the full set of options that are available in the batch utility.
Thanks in advance for any help.
Wayne AntinoreWayne Antinore (wantinore@.veramark.com) writes:
> We are using the DB-Library functions in a C++ program to do a BCP out of
> SQL Server and are having a problem getting it to output character data.
> When using the batch BCP utility there is the -c option which outputs the
> information as character data. Is it possible to set this option with the
> available DB-Library functions, or some other way? bcp_init does not seem
> to have the full set of options that are available in the batch utility.
You have access to all features that the command-line interface provides,
they are just more difficult to use. When using the API, you need to think
in terms of format files, no matter you if specify data format as such,
or if you define columns programmatically with bcp_colfmt. -c and -n are
just shortcuts for special cases format files.
The bcp functions are described in Books Online, but the documentation
is a bit obscure in places, not the least the one bcp_colfmt. I know,
I had a hard time to understand it myself. I have a PERL module for DB-Lib
which include the DB-Library routines, and even if you use C++, you may find
my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
dblib.html#bcp_routines.
By the way, why DB-Library? In my opinion, DB-Library is a very good
client library, but alas Microsoft does not agree with me and has
deprecated it, and has not added support for the new datatypes in SQL7
and later. You should probably use ODBC or OLE DB instead. (Which I'm
told have a very similar bulk-copy interface to DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Since we were only putting out one column we formatted for the column and
all works well. We were still using DB-Library because this was an addition
to a component that already was using it so we were sticking with what we
knew (or thought we did!) .
Thanks, again.
Wayne
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AA27345CCYazorman@.127.0.0.1...
> Wayne Antinore (wantinore@.veramark.com) writes:
of
the
the
seem
> You have access to all features that the command-line interface provides,
> they are just more difficult to use. When using the API, you need to think
> in terms of format files, no matter you if specify data format as such,
> or if you define columns programmatically with bcp_colfmt. -c and -n are
> just shortcuts for special cases format files.
> The bcp functions are described in Books Online, but the documentation
> is a bit obscure in places, not the least the one bcp_colfmt. I know,
> I had a hard time to understand it myself. I have a PERL module for DB-Lib
> which include the DB-Library routines, and even if you use C++, you may
find
> my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
> dblib.html#bcp_routines.
> By the way, why DB-Library? In my opinion, DB-Library is a very good
> client library, but alas Microsoft does not agree with me and has
> deprecated it, and has not added support for the new datatypes in SQL7
> and later. You should probably use ODBC or OLE DB instead. (Which I'm
> told have a very similar bulk-copy interface to DB-Library.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

BCP API & max_errors

I'm using the BCP APIs from a C++ program. Is there a way to set the
maximum number of errors allowed as one does with the BCP utility or
bulk_insert?
Thanks.
If you are doing the ODBC or DB-Library bulk copy functions, you should be
able to set the BCPMAXERRORS property using the bcp_control function:
http://msdn.microsoft.com/library/?u...asp?frame=true
I don't know how you set it if you're using the SQLOLEDB IRowsetFastLoad
interface.
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

BCP API & max_errors

I'm using the BCP APIs from a C++ program. Is there a way to set the
maximum number of errors allowed as one does with the BCP utility or
bulk_insert?
Thanks.If you are doing the ODBC or DB-Library bulk copy functions, you should be
able to set the BCPMAXERRORS property using the bcp_control function:
http://msdn.microsoft.com/library/?.../>
?frame=true
I don't know how you set it if you're using the SQLOLEDB IRowsetFastLoad
interface.
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

BCP API & max_errors

I'm using the BCP APIs from a C++ program. Is there a way to set the
maximum number of errors allowed as one does with the BCP utility or
bulk_insert?
Thanks.If you are doing the ODBC or DB-Library bulk copy functions, you should be
able to set the BCPMAXERRORS property using the bcp_control function:
http://msdn.microsoft.com/library/?url=/library/en-us/odbcsql/od_odbc_bcp_51rg.asp?frame=true
I don't know how you set it if you're using the SQLOLEDB IRowsetFastLoad
interface.
--
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
Avi
Hi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>
|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have troubl
e
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp
"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name> )" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:
> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
--
Thanks
Yogish

bcp and order of rows in table

I am loading data from external program using bcp into a temp table.
I assumed that the rows would be loaded sequentially (same order as
file). Is there a option or some other method to load the data in the
same order as the records in the file. Here is what I'm trying to do:
SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
e:\kaleida\smsadtimp.fmt -U bla -P blabla'
EXEC @.R = master.dbo.xp_cmdshell @.C
DECLARE c_adtrecs CURSOR
for
select * from ##tkaladt
process records in the order they are in the file.
A table is, per definition, not ordered. How about adding an identity column, letting SQL Server generate the
identity values as BCP inserts the rows and use that column in your SELECT statement's ORDER BY?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joe R" <jralabate@.kaleidahealth.org> wrote in message news:eQE13hGLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> I am loading data from external program using bcp into a temp table.
> I assumed that the rows would be loaded sequentially (same order as
> file). Is there a option or some other method to load the data in the
> same order as the records in the file. Here is what I'm trying to do:
> SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
> e:\kaleida\smsadtimp.fmt -U bla -P blabla'
>
> EXEC @.R = master.dbo.xp_cmdshell @.C
> DECLARE c_adtrecs CURSOR
> for
> select * from ##tkaladt
> process records in the order they are in the file.
>