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

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

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

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

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

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

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

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

BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam Caffrey
You could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:
> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

Batch processing of Stored Procedure

Hi,
I am trying to batch processing stored procedures with SQL 2000, MDAC
2.8.
All settings are default. SQLSetStmtAttr is called with
SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
ODBC CALL escape sequence.
I noticed from SQL Profiler trace that the stored procedure is
executed as many
times as the elements in the array (e.g., in separate batches).
If I change the stored procedure call to a vanilla INSERT then all of
the INSERTs are performed in one batch.
Is there any extra settings to be done to execute the procedure in a
true batch execution mode rather than a simulated batch processing.
In the past someone complained about the same issue but the only
suggestion was to concatenate all stored procedure call strings into
one big string and then execute it in one batch. But then there is no
way to return the array of results or errors.
Any hints will be appreciated. Thanks.
KongThis is by design. Each stored procedure is executed within its own scope fo
r
each call.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403112016.372b4871@.posting.google.com...
> Hi,
> I am trying to batch processing stored procedures with SQL 2000, MDAC
> 2.8.
> All settings are default. SQLSetStmtAttr is called with
> SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
> ODBC CALL escape sequence.
> I noticed from SQL Profiler trace that the stored procedure is
> executed as many
> times as the elements in the array (e.g., in separate batches).
> If I change the stored procedure call to a vanilla INSERT then all of
> the INSERTs are performed in one batch.
> Is there any extra settings to be done to execute the procedure in a
> true batch execution mode rather than a simulated batch processing.
> In the past someone complained about the same issue but the only
> suggestion was to concatenate all stored procedure call strings into
> one big string and then execute it in one batch. But then there is no
> way to return the array of results or errors.
> Any hints will be appreciated. Thanks.
> Kong|||Hi, oj,
Thanks for the reply. But according to MSDN
http://msdn.microsoft.com/library/d...br />
2msz.asp
It is possible to batch stored procedure calls.
I understand Microsoft manual page is not always correct :-) Just
want to make sure that it is indeed "by design" before I resort to
other methods.
Thanks again.
Kong
"oj" <nospam_ojngo@.home.com> wrote in message news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>..
.
> This is by design. Each stored procedure is executed within its own scope
for
> each call.
> --
> -oj
> http://www.rac4sql.net
>
> "Kong Li" <likong@.email.com> wrote in message
> news:9e121089.0403112016.372b4871@.posting.google.com...|||Kong,
This statement implies that you do not have any control.
"The Microsoft SQL ServerT ODBC driver automatically batches stored
procedure calls to the server when appropriate."
I'll check to see if there is any and post back when I have an answer.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403121052.4137690b@.posting.google.com...
> Hi, oj,
> Thanks for the reply. But according to MSDN
>
http://msdn.microsoft.com/library/d..._6_035_2msz.asp[
color=darkred]
> It is possible to batch stored procedure calls.
> I understand Microsoft manual page is not always correct :-) Just
> want to make sure that it is indeed "by design" before I resort to
> other methods.
> Thanks again.
> Kong
>
> "oj" <nospam_ojngo@.home.com> wrote in message[/color]
news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>...
scope for

Batch Processing

/**
l'm trying to improve my batch processing routines by
creating a generic method which l can apply on most of
my jobs.Reason Being l'm working with large Data Sets
This is what l'm trying to achieve
1.Get The Rowcount of the Source
2.Split It Into manageable batches
3.Loop through the batches Until the last one is Done
**/
How do l intergrate these two so it can automatically pick up the
batchsizes and process until the last batch is done.
--Outer Loop
Declare @.RowCount Int
Declare @.LoopIteration Int
Declare @.BatchSize Int
--Get The RowCount Of the Table
Select @.RowCount = (Select Count(*) From tbTest)
--
Select @.LoopIteration = @.RowCount/2000000
Select @.BatchSize = @.RowCount/@.LoopIteration
--Select Distinct Client,Convert(Varchar(15),'') As CltNo
-- ,Convert(Int,0) As Status Into tbtest
from tbAddress
--Inner Loop --Works when run Manually
SET ROWCOUNT @.BatchSize--100000 --54 Secs
WHILE 1 = 1
BEGIN
Begin Transaction
Update tbTest
Set CltNo = Client + 1
,Status = 1 Where Status = 0
Commit
if @.@.rowcount = 0
break
END
SET ROWCOUNT 0
--Select * from tbTest Where Status = 1
Message posted via http://www.webservertalk.comWhy do you care what the table size is? You should pick a batch size that
can be accomplished in a short enough time so that it does not interfere
with other users and can have minimal overhead. It usually doesn't matter
if the table is 10M rows or 100K rows. Your batch size should be the same.
What that size is can only be found with proper testing and adjustment.
Usually 10K is a good place to start as it is small enough to finish quickly
and large enough to nibble at the work load. IF you check the @.@.ROWCOUNT
and break when it is 0 then you don't have to worry about how many loops
there will be. Another comment is that there is no need to wrap the Update
in a Begin - Commit. Each update is ATOMIC all by itself. If you update
10K rows in a single update statement all 10K will either work or they wont.
There will not be any middle ground. By adding it in there you now have to
put error checking and handle possible open trans.
Andrew J. Kelly SQL MVP
"Raymond M via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:a911f5b29ab44d0b837051f840966d55@.SQ
webservertalk.com...
> /**
> l'm trying to improve my batch processing routines by
> creating a generic method which l can apply on most of
> my jobs.Reason Being l'm working with large Data Sets
> This is what l'm trying to achieve
> 1.Get The Rowcount of the Source
> 2.Split It Into manageable batches
> 3.Loop through the batches Until the last one is Done
> **/
> How do l intergrate these two so it can automatically pick up the
> batchsizes and process until the last batch is done.
> --Outer Loop
> Declare @.RowCount Int
> Declare @.LoopIteration Int
> Declare @.BatchSize Int
>
> --Get The RowCount Of the Table
> Select @.RowCount = (Select Count(*) From tbTest)
> --
> Select @.LoopIteration = @.RowCount/2000000
> Select @.BatchSize = @.RowCount/@.LoopIteration
>
>
> --Select Distinct Client,Convert(Varchar(15),'') As CltNo
> -- ,Convert(Int,0) As Status Into tbtest
> from tbAddress
> --Inner Loop --Works when run Manually
> SET ROWCOUNT @.BatchSize--100000 --54 Secs
> WHILE 1 = 1
> BEGIN
> Begin Transaction
> Update tbTest
> Set CltNo = Client + 1
> ,Status = 1 Where Status = 0
> Commit
> if @.@.rowcount = 0
> break
> END
> SET ROWCOUNT 0
> --Select * from tbTest Where Status = 1
> --
> Message posted via http://www.webservertalk.com