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 and SQL DATETIME type

Hi all,
I'm working on a simple ODBC API bulk load application and I've run
into a problem. I am binding variables in memory and sending the rows
as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
into issues when I try to bind SQL DATETIME data types. I can't seem
to find examples of how this should be done anywhere... Should my
variable that I'm binding to be a C-Style string or a
SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
Anyone know where I can find a sample that bcp_binds to a DATETIME
column in a table?
TIANever mind, issue resolved.
"Michael C#" wrote:

> Hi all,
> I'm working on a simple ODBC API bulk load application and I've run
> into a problem. I am binding variables in memory and sending the rows
> as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
> works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
> into issues when I try to bind SQL DATETIME data types. I can't seem
> to find examples of how this should be done anywhere... Should my
> variable that I'm binding to be a C-Style string or a
> SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
> Anyone know where I can find a sample that bcp_binds to a DATETIME
> column in a table?
> TIA
>|||Hello Michael,
Can you please show me an example that "works fine for SQL VARCHAR"? I just
can't send text using the bcp...
TIA

quote:

---

Originally posted by Michael C
Never mind, issue resolved.
"Michael C#" wrote:

> Hi all,
> I'm working on a simple ODBC API bulk load application and I've run
> into a problem. I am binding variables in memory and sending the rows
> as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
> works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
> into issues when I try to bind SQL DATETIME data types. I can't seem
> to find examples of how this should be done anywhere... Should my
> variable that I'm binding to be a C-Style string or a
> SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
> Anyone know where I can find a sample that bcp_binds to a DATETIME
> column in a table?
> TIA
>

---

|||Hi Michael, i am facing similar issue i.e. failing of bcp_binf for datetime
datatpye. Can You please share the solution that you have for this issue?
Thanks in advance.
From http://developmentnow.com/g/111_200...
ype.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

BCP to export an SP which uses Temporary Tables not working - SQL Server 2005

Hi

I am trying to export the data from a stored procedure via bcp export. The SP uses temporary tables although the actual data in the temp tables is not the data being exported they are just used to help get the final data.

When running the BCP Export I get an error message that the Object does not exist however if I change the SP to use real tables as opposed to temporary then it runs fine.

I have read that there is no problem exporting from temporary tables with BCP but I am not exporting the data in the temporary tables is this what is causing the problem, it seems a but strange that you can only use them if the data contained is what is being exported.

Can anyone explain?

Thanks

Paul

Maybe you can post some code.

I'm somewhat confused. You say it's throwing an error about the temporary tables, but you say you're exporting from the temporary tables.

|||

Paul:

If the temp tables are created inside the stored procedures then what you are doing is not going to be possible because the temp tables will go out of scope when you exit the stored procedure. This is also logically consistent with the fact that if you use permanent tables instead of temporary tables that the BCP works.

It seems to me that the most likely scenario for you to get your stored procedure operate on a temp table and then have BCP also operate on the same temp table is if (1) your temp table is a global temp table -- that is, it starts with ## instead of # -- that is created before the the stored procedure is call by the connection that also calls the stored procedure and (2) the connection is maintained and the global temp table is not dropped. Under these circumstances you should be able to use BCP on the global temp table.

It would be simpler if you could convert your stored procedure to a function or a view.

|||

Hi Kent

Thanks for the reply I have tried using global temp tables and i get the same error but with ## before the object name so it would appear that it is not going to work with temp tables at all. This may not be a problem as I can always create them then drop them so in effect they are temporary.

Problem with converting to anything else is the whole routine is someone elses that they have been working on for many months I was just trying to help with the BCP aspect, it is a large amount of code and apart from the temp tables it does work we were just trying to understand why it wouldnt work so it can be documented or if possible we could have fixed it.

I can see the problem with the local temp tables thanks to your help but dont see why the global ones wouldnt work I even tried without dropping them at the end of the SP. Oh wait a moment are you saying that the global temp tables would need to be created before the SP is executed and therefore my order of events would be

1. Create Global Temp Tables

2. Execute SP in the BCP export Command

3. Once all is done and happy with the results send in another SQL statement to Drop the Global Tables

If so that should be a good enough reason for us to create real tables and drop them instead.

Thanks for your help

Paul

bcp syntax error help?

I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
Thanks

I have realized how to go around this thanks to all

BCP problem.Pls solve anyone immediately

Hi all,

I tried with that but i am able to inserting abtable(this has 4 cols and 12000 records) with bcp.that is working very good.but i'm getting problem with other table bibtable(this has 52 cols and 73000 records) with bcp but it is inserting all rows with dts.i want to insert both of the tables either of one bcp or dts to insert data into sqlserver 7.0

my bcp commands are:
bcp master..BIBDATA in c:\BIB20031006.TXT -fc:\MSSQL7\Binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is working properly with abtable but not with bibtable

bcp master..bibdata in c:\medsite\idocfile\new\bib20031006.txt -c -F2 -t\t -r\n -e c:\mssql7\binn\bib52.fmt -SJAVADEV2-PC-NJ -Usa -P
this is not working with anyone

in dts:

.delimited
filetype:ANSI SKIP ROWS:0
ROW DELIMITER:LF FIRST ROW HAS COLNAMES CHECKED
TEXT QUALIFIER:''

this is inserting bibtable perfectly but not abtable(inserting few records only)Try removing the space after the -e in the string -e c:\mssql7\binn\bib52.fmt so it looks like -ec:\mssql7\binn\bib52.fmt and put spaces in this string -SJAVADEV2-PC-NJ so it looks like -SJAVADEV2 -PC -NJ|||Try removing the -F2, this is telling the bcp utility to only copy the first two rows.

BCP import - remove quotes from CSV File

My bcp process is now working. FINALLY.
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:

> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:

> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
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 data importing error when using xml format file

Hello, I have a question.

I'm performing some early test for bulk import/export using the bcp(9.0) utility, but there is a problem when working with an xml FormatFile. I execute the following command:

>bcp TestDB.dbo.myNewTable1 in myDataFile.dat -fmyXMLFormatFile.xml -SmySever

-UmyAccount -PmyPassword

And get the following error:

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.

Please help me understand and fix this problem.

Thanks a lot! Best regards,
Diego Valdez

For XML format you suppose to use -x switch...

Please check BOL for more info.

-x

Used with the format and -f format_file options, generates an XML-based format file instead of the default non-XML format file. The -x does not work when importing or exporting data. It generates an error if used without both format and -f format_file.

|||I don't think you understood my post...
My problem is not in the generated xml format file. This file was created succesfully. The issue arises when I try to import data with my specified bcp command and seems that there is something wrong with the Data File, as the message I get says "Unexpected EOF encountered in BCP data-file"
Hope someone can help me get around this.|||There maybe a problem with the data file but you need to get BCP to read your format file first as it could be a spurious error thats getting thrown, right now your commandline is not valid.

Bcp and Bulk Insert not working

Hello,

I have been trying to load a delimited data file to SQL Server. I
have tried both of the options that are available: each time, I get
different errors. This is on an eval version of SQL Server 2K, with
SP 3a on a Windows XP box.

First, I tried to load the data with Bulk Insert. This didn't go
through as it requires sysadmin/bulkadmin privileges. I am the only
person using the SQL Server, and I wanted to grant myself those
privileges. But I cannot find them using the Enterprise Manager. All
I see is privileges like datareader, datawriter, etc.

Then I tried to use bcp. This doesn't seem to work either as it gives
the following error:

ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does
not exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
child process exited abnormally

Anybody with a solution to make this work? For reference, I am using
the following bcp command. I can login to the database using the
server/user/password combination with no problem:

C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exe
testUser.products
IN
"C:/Documents and Settings/testUser/products.txt"
-f "C:/Documents and Settings/testUser/prodformat.txt"
-t "-" -r "\r\n"
-S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406150753.42e65c9b@.posting.google.c om...
> Hello,
> I have been trying to load a delimited data file to SQL Server. I
> have tried both of the options that are available: each time, I get
> different errors. This is on an eval version of SQL Server 2K, with
> SP 3a on a Windows XP box.
> First, I tried to load the data with Bulk Insert. This didn't go
> through as it requires sysadmin/bulkadmin privileges. I am the only
> person using the SQL Server, and I wanted to grant myself those
> privileges. But I cannot find them using the Enterprise Manager. All
> I see is privileges like datareader, datawriter, etc.
> Then I tried to use bcp. This doesn't seem to work either as it gives
> the following error:
> ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does
> not exist or access denied.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> child process exited abnormally
>
> Anybody with a solution to make this work? For reference, I am using
> the following bcp command. I can login to the database using the
> server/user/password combination with no problem:
> C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exe
> testUser.products
> IN
> "C:/Documents and Settings/testUser/products.txt"
> -f "C:/Documents and Settings/testUser/prodformat.txt"
> -t "-" -r "\r\n"
> -S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK

Regarding the bulkadmin role, it sounds like you may be looking at database
roles, not server roles - bulkadmin is in EM under Security, Server Roles.
If you can't access it there, then you're not connected as a sysadmin, so
you should connect as a sysadmin and add testUser to that role.

As for the connection issue, there are a few possible reasons - see "Client-
or Application-Related Causes" in this article:

http://support.microsoft.com/defaul...KB;EN-US;328306

If the article doesn't help to resolve your issue, I suggest you post again
with some more information, in particular if your XP box is on a network or
not, which protocols you configured the server to listen on (see Server
Network Utility), and which tools you have successfully connected to the
server with as testUser (eg. Query Analyzer, osql.exe).

Simon|||another thing to try is to create a DTS package to insert the data for
you.
1.) connect to the server with a login, pw
2.) select file(source)
3.) highlight both, left click onthe server icon, and pick "transform
data".
4.) double click on teh blue line
5.) verify tab one is pointing to the text file. hit preview to verify
that it will insert as desired. if not, go to properties of text file
icon.
6.) create table by "create table from source"
7.) make sure each column is represented and has an arrow pointing to
each other.
click on go.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

BCP - help with import/export using collation Chinese_PRC_CI_AS

Hi,

I've been trying to export/import a table from a DB with
Chinese_PRC_CI_AS collation, and I can't seem to get it working at all
- the export seems rather simple, it is the import that is not working
at all. I am using MSSQL 2000:

The table (under a Chinese_PRC_CI_AS collation DB) is as follows:

CREATE TABLE [dbo].[test_table] (
[id] [int] NOT NULL ,
[first_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:

BCP "select * from testdb.dbo.test_table" queryout test_table.txt -c -
t -S"10.10.10.10" -U"sa" -P"mypassword"

(This is how I must to run the export - I can not change the way of
the statement is run - the reason is because I am using someone elses
exports)

No I try import the data back into the same table, test_table, but
first I take the following steps:

from iSQL: "DELETE FROM testdb.dbo.test_table"

Then I create a format file, test_table.fmt, which looks like this:

8.0
2
1SQLINT012""1id Chinese_PRC_CI_AS
2SQLNCHAR0100"\r\n"2first_name Chinese_PRC_CI_AS

So here is the problem:

When I import the data using the command

BCP "testdb.dbo.test_table" in "test_table.txt" -f"test_table.fmt" -
S"10.10.10.10" -U"sa" -P"mypassword"

The rows get imported - but they are nothing like the data that I
imported:

test_table.txt contents:

1
pete
2
rob
3
sam
4
carl

Imported results:

12800爀漀戀
13056猀愀洀
13312挀愀爀氀
3276543瀀攀琀攀

I've tried changing the format file line's separator to be as
follows: "\n", "\n\0", "\r\0\n\0" - but the results did not differ
much from each other.

Any help is greatly appreciated.

Thank you,

Jim.InvestorTrade (shija03@.gmail.com) writes:

Quote:

Originally Posted by

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:
>
BCP "select * from testdb.dbo.test_table" queryout test_table.txt -c -
t -S"10.10.10.10" -U"sa" -P"mypassword"
>...
Then I create a format file, test_table.fmt, which looks like this:
>
8.0
2
1 SQLINT 0 12 "" 1 id Chinese_PRC_CI_AS
2 SQLNCHAR 0 100 "\r\n" 2 first_name Chinese_PRC_CI_AS

The format file does not match the command that generates the file.
The export command is for a tab-delimited text file, your format file
is for a binary file. A format file for a text file uses only
SQLCHAR for the data type (or SQLNCHAR if it's a Unicode file).

Thus the correct format file would be:

8.0
2
1 SQLCHAR 0 0 "\r\n" 1 id Chinese_PRC_CI_AS
2 SQLCHAR 0 0 "\r\n" 2 first_name Chinese_PRC_CI_AS

Although I suspect that you could simply use -c to import the file.

--
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 - between 2 SQL Server Tables

Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!are the 2 tables in the same database, or the same server or on the same network? you may be able to do this with a simple

INSERT INTO MyTable(MyField)
SELECT

using fully qualified names and maybe a linked server.|||if you want to use bcp, you have to do it in 2 steps:

1. use bcp with "out" or "queryout" keywords to go from source table to flat file
2. use bcp with "in" keyword to go from flat file to dest table.

if you have many millions of rows, bcp is faster than using INSERT...SELECT.

read about bcp syntax here:

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Another option, since you are using .net, is to use the sqlbulkcopy class, which has the advantage of not needing to write the intermediate file. It's quite fast. Read up here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx. I recommend you to use the overload of WriteToServer that takes IDataReader, not the DataTable overloads. much more memory efficient.|||Thanks..maybe i forgot that one..workin on it now..hope it will be much faster|||thats what im thinking..i have to do two steps..good if im transferring it to other database or web.will it still be much faster than 'Insert...Select' statement in two steps?...|||it depends on how many rows you are moving. if it's millions, bcp/bulk insert/sqlbulkcopy will be faster than INSERT...SELECT.

If you have lots of rows, I would try sqlbulkcopy. it's only one step, and uses the same underlying method as bcp, namely, bulk insert.