Sql Basic Security: trouble

显示标签为“trouble”的博文。显示所有博文

2012年3月29日星期四

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/BULK INSERT and blank lines

Does anyone know of a way to make BULK INSERT or bcp ignore blank lines in the file? I am having trouble with a bunch of data files coming back with 1 or 2 blank lines at the end, and it causes the entire bcp to fail.

I suppose I could write a utility to trim the files but that seems a bit overkill. Any thoughts?

You will need to trim the data 'cuz bcp/bulk insert is just a _dumb_ data loader.|||One option is to use the -L parameter of BCP to specify the last row. This will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT.|||ah, -L! Thanks for the correction. I've never used that flag. It seems much simpler to just trim the blank lines...

bcp won't run

2012年3月27日星期二

bcp won't run

I'm having trouble running bcp from a command line. It fails without an error
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik JohnsonAre you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window,,,
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an error
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I get
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
>>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
>>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
>>from there.
>>I don't think I've ever heard of a command line app closing a command window,,,
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
>>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>>
>>I'm having trouble running bcp from a command line. It fails without an error
>>message and closes the DOS window.
>>Does anyone know what I might be missing here?
>>-Nik Johnson
>>
>>|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:
> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
> >C>A little more experimentation shows that if I RUN the whole path
> >(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
> >get the following:
> >
> >Parameter format not correct
> >Specified COMMAND search directory bad
> >Too many parameters
> >Too many parameters
> >Microsoft(R) Windows DOS
> >(C)Copyright Microsoft Corp 1990-1999.
> >
> >If I move everything to a directory immediately under the root (c:bcp) I get
> >a message: "Unable to load BCP resource DLL. BCP cannot continue."
> >
> >-Nik
> >
> >
> >
> >
> >"Tibor Karaszi" wrote:
> >
> >
> >
> >>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> >>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> >>from there.
> >>
> >>I don't think I've ever heard of a command line app closing a command window,,,
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> >>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> >>
> >>
> >>I'm having trouble running bcp from a command line. It fails without an error
> >>message and closes the DOS window.
> >>
> >>Does anyone know what I might be missing here?
> >>
> >>-Nik Johnson
> >>
> >>
> >>
> >>
> >>
>

2012年3月11日星期日

BCP IN trouble

I try to transfert data table from one server to another one by using BCP.
The 2 servers are running SQLS2k sp3.
The outpout work fine, but the input (option: -m2 -N) in destination DB return a message:

Starting copy...
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'.
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'.

i guess that i have the keyword "order" in my .bcp file so that disturb the input command.
How can i manage this ?

PS: i tried already the -w option for out-in command

Many thanks.Can you put in your bcp statement here? -T&R, Suresh.|||here after my BCP statement:

bcp.exe "Production.dbo.[Backup_Ordersdetail]" In "C:\...\bcpfiles\Backup_Ordersdetail.bcp" -m2 -N -Sxxxxxxx -Uxx -Pxxxxxxx

bcp import help

I am trying to import data thru a bcp call to pull data from an access database. I am having trouble accessing the access database. Below is the bcp I tried, along with an openrowset attempt. Neither of them are working. Any help would be greatly appreciated.

bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX

select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!|||I can add it as a linked server, but then I cant actually query against any of the access tables.

sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\\ctisvr\stats\2007\11\D1107.MDB'

SELECT *
FROM D1107...Line Report

when I run the select statment I then get:
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'|||Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?

As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||Did you try the DTS Wizard?

I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.

BCP Help - Going Crazy!

I have successfully used BCP before. But, this time, I have an entirely
new import csv file and it's causing me so much trouble. I am working
with a CSV file with more fields than the SQL table but I am following
what BOL tells me to do. PLEASE HELP!!!
ERROR: Incorrect host-column number found in BCP format-file
Here are the specs:
SQL2000
tablename: lbar_mlsdata_temp_resi
fields:
ML_NUMBER varchar 255
Property_Type_Desc varchar 255
bcp fmt file:
8.0
3
1 SQLCHAR 0 255 "," 1 ML_Number
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "," 0 Listing Agent ID
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\r\n" 0 Type/Style Desc
SQL_Latin1_General_CP1_CI_AS
CSV FILE:
"ML_Number","Listing Agent ID","Listing Agent Name"
"204980","33404","Cindy W Crutcher"
"300614","16901","Linda C Wilson"
"406804","21109","Glynn A Gregory"
"312289","17314","Linda L Friess"
command line:
bcp databasename..lbar_mlsdata_temp_resi in ""c:\bcptest\resi2.csv"" -f
""c:\bcptest\bcp.fmt"" -F 2 -o ""c:\bcptest\VendorOut.txt"" -U x -P x -S
x
*** Sent via Developersdex http://www.examnotes.net ***The answer to your problem doesn't immediately occur to me. Although it
may occur to someone else here.
Were I you, I would create a table with just the three columns that
match your BCP format spec...and try that. If that works, then run
subsequently a stored procedure to Insert the data into the final
destination table.
FYI it's my understand also that CSV files have no text qualifiers
like: "" and just commas seperating values. I call the file you have a:
quote-comma delimted file. But that probably isn't too relative to your
issue.|||Ok, so I figured out at least part of the ISSUE. The field names in the
BCP fmt file has spaces in them. That was the issue. I should have known
this, but, lesson learned.
I am going to post the new problem to another message.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***

2012年2月11日星期六

Basic: Starting SQL Server

Hello,

I am having trouble just starting SQL Server 2005. When I try to use Management Studio, I do not see any server name. I just need to be able to start it up!

Thanks,

pagates

Hi pagates,

One of the easiest ways to start SQL Server 2005 is go to Start -> Control Panel -> Administrative Tools -> Services. Look up for a service by the name SQL Server or SQL Server (your instance name), right click -> Start. If you do not see this service listed, it is likely that you have installed only the client tools for SQL Server 2005 and not the server.

Hope that helps,

Amol.

2012年2月9日星期四

Basic SQL Connection

Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.

When it gets to the third line the page crashes with an error.

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True");

SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection);

SqlDataReader reader = cmd.ExecuteReader();

is there somewhere i need to put in my username or password? or is this code just wrong

Many thanks burnside.

-- Edited by longhorn2005

What is your error message?|||

Try the below connection string

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;Integrated Security=True");

And if your database require Username and password check the below link

Building Connection String C#

|||

Thanks for the replys,

The page seems to crash when i uncomment out the third line of sql code "// SqlDataReader rdr = cmd.ExecuteReader();"
"System.Data.SqlClient" %> and i get this slightly unhelpful error message @.http://s152182516.websitehome.co.uk/test/default2.aspx

@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

scriptrunat="server">protectedvoid Page_Load(object sender,EventArgs e)

{

// Define database connectionSqlConnection conn =newSqlConnection("uid=xxxxx;password=xxxxx;Server=mssql07.oneandone.co.uk;Database=db198704784;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);SqlDataReader rdr = cmd.ExecuteReader();

}

script>

htmlxmlns="http://www.w3.org/1999/xhtml">

headrunat="server"><title>Untitled Page</title>

head>

body><formid="form1"runat="server"><div><asp:TextBoxID="TextBox1"runat="server"OnTextChanged="TextBox1_TextChanged"Height="176px"Width="338px"></asp:TextBox><asp:LabelID="Label1"runat="server"Text="Test Of SQL"></asp:Label></div></form>

body>

html>|||

Try to open the connection before you call ExecuteReader() example below

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();

Mark this post as ANSWERED if it helped you

|||

I've tried the Open command but still no joy. I think i might have to hire someone to go into my MS hosting and check it and the SQL is setup, and make a little Logon aspx with the sql database so i can see the code to access it. If anyone is interested please email me.

|||

Hello,

These are some tutorial for you:

http://www.codeproject.com/aspnet/SQLConnect.asp

http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx

HTH

|||

I tried to access the page you provided but unfortunately its unavailable. What do you mean by the page crashes? Can you post the complete error you are getting? Make sure the database exists and the table you trying to query exists in thedatabase.

Note: Never give out the username/password in the posts. replace them with XXXXX

Thanks

Basic Replication trouble.

I'm trying to run a simple update script on this database which is
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.

UPDATE
CUSTOMER
SET
[TIMESTAMP]='20060920090453'
[IVBTYPE]='M '
WHERE
[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'

yields:

Server: Msg 208, Level 16, State 1, Procedure
upd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14
Invalid object name 'dbo.sysmergearticles'.

Do I have to do something to the database before trying to update it?P.S. I notice I'm missing a comma in there after the first set, missed
it while I was copying it over and didn't notice until I posted.

订阅：博文 (Atom)

2012年3月29日星期四

bcp_init and SQL Native Client

bcp_init and SQL Native Client

bcp_init and SQL Native Client

bcp/BULK INSERT and blank lines

bcp won't run

2012年3月27日星期二

bcp won't run

bcp won't run

2012年3月11日星期日

BCP IN trouble

bcp import help

BCP Help - Going Crazy!

2012年2月11日星期六

Basic: Starting SQL Server

2012年2月9日星期四

Basic SQL Connection

Basic Replication trouble.

Sql Basic Security

Blog Archive

About Me