2012年3月29日星期四
BCP/DTS/cmdshell problem
Using 2000
I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million
rows each). I need your help and advice on this
1)is dts faster than BCP converting to flatfiles and again copying to
destination tables.
2) if we write cmdshell and use BCP in that instead of directly using in
.cmd file, will performance be slower. I am new to .cmd so controlling and
error handling will be easier if I write in a sp which uses cmd shell to
extract bcp
3. any resource on writing .cmd files using osql and bcp( templates etc). I
googled but no use.
--
Thanks
DevaHi deva
I think you will get a better answer if you try the DTS discussion forum
"DEva" wrote:
> Hi
> Using 2000
> I am writing a .cmd file for Bulk copying data(about 25 tables of 1 millio
n
> rows each). I need your help and advice on this
> 1)is dts faster than BCP converting to flatfiles and again copying to
> destination tables.
> 2) if we write cmdshell and use BCP in that instead of directly using in
> .cmd file, will performance be slower. I am new to .cmd so controlling and
> error handling will be easier if I write in a sp which uses cmd shell to
> extract bcp
> 3. any resource on writing .cmd files using osql and bcp( templates etc).
I
> googled but no use.
> --
> Thanks
> Deva
2012年3月27日星期二
bcp without sqlserver client
sqlserver client on that system.does copying any dll's and executables help
thanks a lot for help.
iYou just need to copy the Bulk Copy Program to the client that needs to run it...
2012年3月25日星期日
BCP using ODBC - problem with unique identifier
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version:)
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
> I'm having a nasty problem with bulk copying into a table that has
> unique identifier column. I'm coding on C++, using ODBC driver.
> I'm coping from a file containing UID description like this:
> {43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
> (I also tried a non-bracket version)
> and in the DB table I get:
> 4233347B-4235-4433-452D-353238302D34
> which seems random at first sight, but it is:
> [B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
> and my question is: what the hell?
> my code look like this:
> if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
> ret = -1;
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
> SQLUNIQUEID, colNo) == FAIL){
> ret = -1;
> }
> (I also tried a VARLEN version:)
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
> (LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
> ret = -1;
> }
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
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|||> First you need to decide in which format is the UID? It if is in text,
> you should specify SQLVARCHAR for the data type. Only if you have the
> UID as binary, you should specify SQLUNIQUEID.
> Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
> It depends on what's in delimiter, but since a GUIO is always 36
> characters (without braces), you could just as well specify 36 for the
> length.
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
> 1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
> SQL Server Driver]Invalid character value for cast specification". What
> do you meas format of the UID? You mean in the input file? in the input
> file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
> 2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
> result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.
--
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 using ODBC - problem with unique identifier
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read bi
nary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,p
Value,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
> I'm having a nasty problem with bulk copying into a table that has
> unique identifier column. I'm coding on C++, using ODBC driver.
> I'm coping from a file containing UID description like this:
> {43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
> (I also tried a non-bracket version)
> and in the DB table I get:
> 4233347B-4235-4433-452D-353238302D34
> which seems random at first sight, but it is:
> [B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read
binary as hex.
> and my question is: what the hell?
> my code look like this:
> if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
> ret = -1;
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
> SQLUNIQUEID, colNo) == FAIL){
> ret = -1;
> }
> (I also tried a VARLEN version
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
> (LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
> ret = -1;
> }
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
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|||
> First you need to decide in which format is the UID? It if is in text,
> you should specify SQLVARCHAR for the data type. Only if you have the
> UID as binary, you should specify SQLUNIQUEID.
> Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
> It depends on what's in delimiter, but since a GUIO is always 36
> characters (without braces), you could just as well specify 36 for the
> length.
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][O
DBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
> 1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][
;ODBC
> SQL Server Driver]Invalid character value for cast specification". What
> do you meas format of the UID? You mean in the input file? in the input
> file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
> 2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
> result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to 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 using ODBC - problem with unique identifier
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version:)
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
Quote:
Originally Posted by
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
>
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
>
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
>
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
>
and my question is: what the hell?
>
my code look like this:
>
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
>
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
>
(I also tried a VARLEN version:)
>
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
--
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|||
Quote:
Originally Posted by
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
>
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
>
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
Quote:
Originally Posted by
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
Quote:
Originally Posted by
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to 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|||Erland Sommarskog wrote:
Quote:
Originally Posted by
tha_mihau (mpietrzyk@.autograf.pl) writes:
Quote:
Originally Posted by
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
>
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
>
Quote:
Originally Posted by
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
>
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.
Thx, Erland!
Running
bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 36, (LPCBYTE)NULL, 0,
SQLVARCHAR, colNo)
on a file with GUIDs with no braces did the trick.
Works like a charm.
Thanks again, M.sql
2012年3月20日星期二
BCP output with header and trailer
add a header and trailer.
Is this possible? If so, please help me with the steps.
Also, is it possible to append to a text file while doing the bcp? If
so, how?"sbh" <stephanie.herbert@.tdh.state.tx.us> wrote in message
news:1d68006.0403040850.23a28ccb@.posting.google.co m...
> I'm copying data out to a file with pipe delimiters. I would like to
> add a header and trailer.
> Is this possible? If so, please help me with the steps.
> Also, is it possible to append to a text file while doing the bcp? If
> so, how?
Not directly, but you might be able to use -queryout or osql.exe -o to
execute a query or stored procedure which returns your header and trailer
information as well as the data. Since you don't say what you want in your
header/trailer, it's hard to be more precise. As for appending, I believe
that both bcp and osql overwrite any existing file, but a simple script in
Perl, VBScript etc. would be a more flexible solution.
Simonsql
2012年3月6日星期二
BCP Error handling
declare
@.FILENAME_C nvarchar(255),
@.sqlstmt2 nvarchar(255),
@.outputcatch varchar(1000)
SET @.FILENAME_C= 'c:\it.txt'
SET @.SQLSTMT2='BCP "SELECT claimnumber from demo.dbo.wcclm1" queryout '+@.FILENAME_C+' /c /t, /r \n /U demo /P demo /S '+@.@.servername
EXEC @.outputcatch=MASTER.DBO.XP_CMDSHELL @.SQLSTMT2, no_output --@.outputcatch=OUTPUT
select @.outputcatch
Note that this sample has a simple select statement. If I change claimnumber to clamnumber @.outputcatch is 1 due to failure. Put it back to claimnumber and it returns 0 which indicates success.
Just keep looking at @.outputcatch and if you get a 1 RETURN.
2012年2月25日星期六
bcp copying,but no file
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query
I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL
and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.
Could somebody please help meWhere are you looking for the file?