Sql Basic Security: servers

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

2012年3月29日星期四

BcpBatchSize problem in replication

BCPBatchSize and Replication hanging

I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
fiddling with the bcpbatchsize is not always a good idea. What this does is
determines how many rows are processed before they are committed. Setting
this to a high value means your tlog on your subscriber will grow and the
bcp process (performed by odbcbcp) will chug along longer before sending
messages back to the SQL Server agent. This will cause the "waiting for
response from the backend".
using a small value for bcpbatchsize will take longer, but is more chatty
and you won't get this message.
Think of it like the way your girlfriend blows your check. Give her a large
amount of money and you won't hear from her for weeks. On the other hand
give her a small amount of money, and she will be back in a few hours. Give
her to little money and she will never come back. So you have to balance the
amount of money you give her with how frequently you want to see her. Same
with bcpbatchsize - but I digress.
If your subscriber is a multiproc machine think about using UseInprocLoader
switch.
But, I'm a little confused - are you regenerating your snapshot? Are the
tables whose column names you have changed large?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E29DD9C85B5WBT39312565@.207.46.248.16...
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The servers
> are running windows 2003. Everything is enterprise edition and the sql
> servers are using about 14 gig memory.
> We restore the database to the second server and then enable publication
> to that server turning of the generation of SPs but enabling the use of
> column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just fine.
> (before we started using column names).
> Up until this past weekend we did not need the column name option in the
> replication distribution and everything ran fine. Some table structures
> changed but not that big of changes but we do not have about 60 ident
> columns that we are dealing with.
> Starting this Monday we needed to use the column name option because we
> are replicating to some large tables that have identity columns on them.
> We have enabled the not for replication property on the columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it a
> couple of times and each time the distribution job ran fine for a while
> and then would just hang. Once it would hang there was no getting past
> that point.
> I have traced the replication process and it just stops sending data. I
> have also enabled the output of the distribution job to a file and it
> shows more data being sent than I see in the sql trace. The trace is
> not missing data because it always stops at the same spot (I tried it
> several times) and the output file always stops also but shows alot more
> data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the way
> down to 1 or 2 but the trace still shows many more commands per batch
> than 1 or 2. I have enabled the tsql batch start and end to trace the
> batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables with
> large rows I am thinking that the 10,000 commands per batch is too much
> now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
|||Thanks for the response.
I was changing the bcpbatchsize because of the problem I am having were
the replication just stopped. I let it sit for over 24 hrs and saw no
activity, no error message, it just said it "waiting for response from
the backend co". It never timed out. I was using the default profile
at that time. The destination server was not showing much activity and
the trace showed no command flowing between the servers.
The other problem I then saw using the trace was that even when I set
the bcpbatchsize very small and I was able to keep replication running
(after doing complete resetup) I would see more commands sent per batch
than I had specified. I set it to two and during the trace saw more
than two commands between eatch batch start and end in the trace. My
understanding of the bcpbatchsize is as you defined it. I just did not
see it actually doing that in the trace. That is why I am wondering
just what it actually does?
Even with the bcpbatchsize at 2 the replication eventually froze again
on the same message, "waiting for a response from the backend co". The
problem was that it was still sending the big batches. I wanted to make
them smaller as a test and was having no luck.
We don't use the snapshot because it takes way too long for our 70 gig
databases. We can backup and then restore the database in about an
hour.
Some of the tables are quite large (lots of columns and some the columns
get pretty big) and some of the column names get a little wordy.
We have gone back to removing the idents (which takes about an hour
right now it took about 1/2 hr 6 months ago on slower hardware) and are
no longer setting the "use column names" in the replication and it has
been running fine for over a week. I don't want to keep removing the
idents because the time it takes is getting longer quickly and the
window to setup replication is shrinking. I can get about 6 hours now
before users start to complain. Our next upgrade is coming up soon and
I will be redoing the replication setup again because several tables
will have structure changes.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:

> fiddling with the bcpbatchsize is not always a good idea. What this
> does is determines how many rows are processed before they are
> committed. Setting this to a high value means your tlog on your
> subscriber will grow and the bcp process (performed by odbcbcp) will
> chug along longer before sending messages back to the SQL Server
> agent. This will cause the "waiting for response from the backend".
> using a small value for bcpbatchsize will take longer, but is more
> chatty and you won't get this message.
> Think of it like the way your girlfriend blows your check. Give her a
> large amount of money and you won't hear from her for weeks. On the
> other hand give her a small amount of money, and she will be back in a
> few hours. Give her to little money and she will never come back. So
> you have to balance the amount of money you give her with how
> frequently you want to see her. Same with bcpbatchsize - but I
> digress.
> If your subscriber is a multiproc machine think about using
> UseInprocLoader switch.
> But, I'm a little confused - are you regenerating your snapshot? Are
> the tables whose column names you have changed large?
>
|||There is a condition where the buffers are exhausted/depleted this might
account for what you are seeing. Normally you get this when the agent
starts.
There is another condition, which again you might be running into. When you
push a tonne of data over the wire it takes some time for the data to be
committed. This time is a function of how much data and bcpbatchsize and
your log size. I have on occasion waited overnight for a snapshot to be
applied.
I would advise you to look into another strategy for deploying these large
snapshots. For instance you might want to break your publication into
pieces related to DRI. Put the large tables into separate publications, i.e.
one publication for each large table and tables related to it by DRI.
Then do a nosync subscription with these tables and use another method for
deploying the snapshot. Like bcp, or copying the snapshot to a cd,
tapedrive, network share and then applying it to the subscriber from this
location.
I prefer to use bcp as I have a window into what is being applied and I can
control it in a highly granular fashion. It will require more work, but the
problem is with large snapshot you have to start off at square one with each
hiccup.
SQL 2005 will restart the snapshot application where it fails so it should
fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E37DDC6CCD5WBT39312565@.207.46.248.16...
> Thanks for the response.
> I was changing the bcpbatchsize because of the problem I am having were
> the replication just stopped. I let it sit for over 24 hrs and saw no
> activity, no error message, it just said it "waiting for response from
> the backend co". It never timed out. I was using the default profile
> at that time. The destination server was not showing much activity and
> the trace showed no command flowing between the servers.
> The other problem I then saw using the trace was that even when I set
> the bcpbatchsize very small and I was able to keep replication running
> (after doing complete resetup) I would see more commands sent per batch
> than I had specified. I set it to two and during the trace saw more
> than two commands between eatch batch start and end in the trace. My
> understanding of the bcpbatchsize is as you defined it. I just did not
> see it actually doing that in the trace. That is why I am wondering
> just what it actually does?
> Even with the bcpbatchsize at 2 the replication eventually froze again
> on the same message, "waiting for a response from the backend co". The
> problem was that it was still sending the big batches. I wanted to make
> them smaller as a test and was having no luck.
> We don't use the snapshot because it takes way too long for our 70 gig
> databases. We can backup and then restore the database in about an
> hour.
> Some of the tables are quite large (lots of columns and some the columns
> get pretty big) and some of the column names get a little wordy.
> We have gone back to removing the idents (which takes about an hour
> right now it took about 1/2 hr 6 months ago on slower hardware) and are
> no longer setting the "use column names" in the replication and it has
> been running fine for over a week. I don't want to keep removing the
> idents because the time it takes is getting longer quickly and the
> window to setup replication is shrinking. I can get about 6 hours now
> before users start to complain. Our next upgrade is coming up soon and
> I will be redoing the replication setup again because several tables
> will have structure changes.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
> news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
>
|||Thanks for the info.
My problem is not with deploying the snapshots, we have figured out how
to do the replication setup without using snapshots.
The problem is that we want to start using column names in the
replication.
I have done more testing and tracing and have narrowed down my problem.
We are using 1 way transactional replication. When the system updates
records, replication is issuing deletes and then inserts. The deletes
are the part that are taking a long time (over an hour). The inserts
takes seconds. It is the same number of rows deleted and inserted.
If I delete the rows outside of the update statement on the publisher
and then just insert the data on the publisher the process takes seconds
for the deletes and inserts to replicate. It is when I do update
statments to the publisher that the distribution takes a very long time.
The log reader gets the data in seconds in either case. The odd part is
the during the trace I can see that the distribution is just doing
deletes and then inserts. I have even taken and recreated the deletes
that replication appears to be using and they run very quickly against
the publisher and through the distribution process.
The table has the same structure on both the publisher and subscriber.
There are no idents in this table and all foreign keys have been removed
on the subscriber. The delete statement is using the clustered primary
to select the records and the deletes are being done one row at a time.
I have done the deletes one row at time outside of the update statement
and it took about 2 min to execute and about 2 min to replicate.
Why do the delete statments created by replication for the updates at
the publisher take so much longer to execute when using column names in
replication? Updating the same records without column names takes
seconds.
Thanks,
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:usZrKu#$EHA.1564@.TK2MSFTNGP09.phx.gbl:

> There is a condition where the buffers are exhausted/depleted this
> might account for what you are seeing. Normally you get this when the
> agent starts.
> There is another condition, which again you might be running into.
> When you push a tonne of data over the wire it takes some time for the
> data to be committed. This time is a function of how much data and
> bcpbatchsize and your log size. I have on occasion waited overnight
> for a snapshot to be applied.
> I would advise you to look into another strategy for deploying these
> large snapshots. For instance you might want to break your
> publication into pieces related to DRI. Put the large tables into
> separate publications, i.e. one publication for each large table and
> tables related to it by DRI.
> Then do a nosync subscription with these tables and use another method
> for deploying the snapshot. Like bcp, or copying the snapshot to a cd,
> tapedrive, network share and then applying it to the subscriber from
> this location.
> I prefer to use bcp as I have a window into what is being applied and
> I can control it in a highly granular fashion. It will require more
> work, but the problem is with large snapshot you have to start off at
> square one with each hiccup.
> SQL 2005 will restart the snapshot application where it fails so it
> should fix this.
>
sql

2012年3月25日星期日

BCP truncation error on few tables?

Hi,
I'm using BCP to copy some data between 2 servers.
I'm using the -N option to export the tables into the Native Unicode format
this works fine
except for few tables where I receive the truncation error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
this process has been used for few month without any issue and now I start
to see this error.
any idea?
thanks for your quick guides!
jerome.Hi
-N is keep non-text native and -n is native so you may want to try that
instead.
John
"Jeje" wrote:
> Hi,
> I'm using BCP to copy some data between 2 servers.
> I'm using the -N option to export the tables into the Native Unicode format
> this works fine
> except for few tables where I receive the truncation error:
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> this process has been used for few month without any issue and now I start
> to see this error.
> any idea?
> thanks for your quick guides!
> jerome.
>|||the BOL says:
-N is native unicode format:
http://msdn2.microsoft.com/en-us/library/ms189941.aspx
the varchar and char are exported in Unicode and others are in their native
format.
we have solve the issue for few tables by using the -w instead of -N
but some tables continues to suffer the same issue!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> Hi
> -N is keep non-text native and -n is native so you may want to try that
> instead.
> John
> "Jeje" wrote:
>> Hi,
>> I'm using BCP to copy some data between 2 servers.
>> I'm using the -N option to export the tables into the Native Unicode
>> format
>> this works fine
>> except for few tables where I receive the truncation error:
>> SQLState = 22001, NativeError = 0
>> Error = [Microsoft][SQL Native Client]String data, right truncation
>> this process has been used for few month without any issue and now I
>> start
>> to see this error.
>> any idea?
>> thanks for your quick guides!
>> jerome.
>>|||I would still try -n
John
"Jeje" wrote:
> the BOL says:
> -N is native unicode format:
> http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> the varchar and char are exported in Unicode and others are in their native
> format.
> we have solve the issue for few tables by using the -w instead of -N
> but some tables continues to suffer the same issue!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > Hi
> >
> > -N is keep non-text native and -n is native so you may want to try that
> > instead.
> >
> > John
> >
> > "Jeje" wrote:
> >
> >> Hi,
> >>
> >> I'm using BCP to copy some data between 2 servers.
> >>
> >> I'm using the -N option to export the tables into the Native Unicode
> >> format
> >> this works fine
> >> except for few tables where I receive the truncation error:
> >> SQLState = 22001, NativeError = 0
> >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >>
> >> this process has been used for few month without any issue and now I
> >> start
> >> to see this error.
> >>
> >> any idea?
> >> thanks for your quick guides!
> >>
> >> jerome.
> >>
> >>|||I am having a similar problem.
When I use -N, I get this error:
SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
Client]String data, right truncation
When I use -n I get this error:
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
Client]Unexpected EOF encountered in BCP data-file
Any help will be appreciated.
Thanks
"John Bell" wrote:
> I would still try -n
> John
> "Jeje" wrote:
> > the BOL says:
> > -N is native unicode format:
> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >
> > the varchar and char are exported in Unicode and others are in their native
> > format.
> >
> > we have solve the issue for few tables by using the -w instead of -N
> > but some tables continues to suffer the same issue!
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > Hi
> > >
> > > -N is keep non-text native and -n is native so you may want to try that
> > > instead.
> > >
> > > John
> > >
> > > "Jeje" wrote:
> > >
> > >> Hi,
> > >>
> > >> I'm using BCP to copy some data between 2 servers.
> > >>
> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> format
> > >> this works fine
> > >> except for few tables where I receive the truncation error:
> > >> SQLState = 22001, NativeError = 0
> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >>
> > >> this process has been used for few month without any issue and now I
> > >> start
> > >> to see this error.
> > >>
> > >> any idea?
> > >> thanks for your quick guides!
> > >>
> > >> jerome.
> > >>
> > >>|||yes, same error here.
"Agho" <Agho@.discussions.microsoft.com> wrote in message
news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> I am having a similar problem.
> When I use -N, I get this error:
> SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> Client]String data, right truncation
> When I use -n I get this error:
> SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> Client]Unexpected EOF encountered in BCP data-file
> Any help will be appreciated.
> Thanks
>
> "John Bell" wrote:
>> I would still try -n
>> John
>> "Jeje" wrote:
>> > the BOL says:
>> > -N is native unicode format:
>> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
>> >
>> > the varchar and char are exported in Unicode and others are in their
>> > native
>> > format.
>> >
>> > we have solve the issue for few tables by using the -w instead of -N
>> > but some tables continues to suffer the same issue!
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
>> > > Hi
>> > >
>> > > -N is keep non-text native and -n is native so you may want to try
>> > > that
>> > > instead.
>> > >
>> > > John
>> > >
>> > > "Jeje" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> I'm using BCP to copy some data between 2 servers.
>> > >>
>> > >> I'm using the -N option to export the tables into the Native Unicode
>> > >> format
>> > >> this works fine
>> > >> except for few tables where I receive the truncation error:
>> > >> SQLState = 22001, NativeError = 0
>> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
>> > >>
>> > >> this process has been used for few month without any issue and now I
>> > >> start
>> > >> to see this error.
>> > >>
>> > >> any idea?
>> > >> thanks for your quick guides!
>> > >>
>> > >> jerome.
>> > >>
>> > >>|||Hi
This issue could be cause be either a row or field terminator being present
withing your data or possibly a missing row terminator at the end of the
file. The latter should be easy to identify as only the last row may not be
imported. The easiest way to correct the former is to generate the data file
with specify terminators that you will know are not going to occur in the
data, then use the -r and -t option to specify the appropriate character when
importing the data with BCP.
HTH
John
"Jeje" wrote:
> yes, same error here.
>
> "Agho" <Agho@.discussions.microsoft.com> wrote in message
> news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > I am having a similar problem.
> > When I use -N, I get this error:
> > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]String data, right truncation
> >
> > When I use -n I get this error:
> > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > Client]Unexpected EOF encountered in BCP data-file
> >
> > Any help will be appreciated.
> >
> > Thanks
> >
> >
> > "John Bell" wrote:
> >
> >> I would still try -n
> >>
> >> John
> >>
> >> "Jeje" wrote:
> >>
> >> > the BOL says:
> >> > -N is native unicode format:
> >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> >> >
> >> > the varchar and char are exported in Unicode and others are in their
> >> > native
> >> > format.
> >> >
> >> > we have solve the issue for few tables by using the -w instead of -N
> >> > but some tables continues to suffer the same issue!
> >> >
> >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> >> > > Hi
> >> > >
> >> > > -N is keep non-text native and -n is native so you may want to try
> >> > > that
> >> > > instead.
> >> > >
> >> > > John
> >> > >
> >> > > "Jeje" wrote:
> >> > >
> >> > >> Hi,
> >> > >>
> >> > >> I'm using BCP to copy some data between 2 servers.
> >> > >>
> >> > >> I'm using the -N option to export the tables into the Native Unicode
> >> > >> format
> >> > >> this works fine
> >> > >> except for few tables where I receive the truncation error:
> >> > >> SQLState = 22001, NativeError = 0
> >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> >> > >>
> >> > >> this process has been used for few month without any issue and now I
> >> > >> start
> >> > >> to see this error.
> >> > >>
> >> > >> any idea?
> >> > >> thanks for your quick guides!
> >> > >>
> >> > >> jerome.
> >> > >>
> >> > >>|||Can you please give an example of whay you want done? I don't quite
understand it. For now, I am using OPENDATASOURCE to insert the data in to
the desired table. This is taking about 3 minutes compared to 15 seconds when
I use BCP.
Thanks
"John Bell" wrote:
> Hi
> This issue could be cause be either a row or field terminator being present
> withing your data or possibly a missing row terminator at the end of the
> file. The latter should be easy to identify as only the last row may not be
> imported. The easiest way to correct the former is to generate the data file
> with specify terminators that you will know are not going to occur in the
> data, then use the -r and -t option to specify the appropriate character when
> importing the data with BCP.
> HTH
> John
>
> "Jeje" wrote:
> > yes, same error here.
> >
> >
> > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > I am having a similar problem.
> > > When I use -N, I get this error:
> > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]String data, right truncation
> > >
> > > When I use -n I get this error:
> > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > Client]Unexpected EOF encountered in BCP data-file
> > >
> > > Any help will be appreciated.
> > >
> > > Thanks
> > >
> > >
> > > "John Bell" wrote:
> > >
> > >> I would still try -n
> > >>
> > >> John
> > >>
> > >> "Jeje" wrote:
> > >>
> > >> > the BOL says:
> > >> > -N is native unicode format:
> > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > >> >
> > >> > the varchar and char are exported in Unicode and others are in their
> > >> > native
> > >> > format.
> > >> >
> > >> > we have solve the issue for few tables by using the -w instead of -N
> > >> > but some tables continues to suffer the same issue!
> > >> >
> > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > >> > > Hi
> > >> > >
> > >> > > -N is keep non-text native and -n is native so you may want to try
> > >> > > that
> > >> > > instead.
> > >> > >
> > >> > > John
> > >> > >
> > >> > > "Jeje" wrote:
> > >> > >
> > >> > >> Hi,
> > >> > >>
> > >> > >> I'm using BCP to copy some data between 2 servers.
> > >> > >>
> > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > >> > >> format
> > >> > >> this works fine
> > >> > >> except for few tables where I receive the truncation error:
> > >> > >> SQLState = 22001, NativeError = 0
> > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > >> > >>
> > >> > >> this process has been used for few month without any issue and now I
> > >> > >> start
> > >> > >> to see this error.
> > >> > >>
> > >> > >> any idea?
> > >> > >> thanks for your quick guides!
> > >> > >>
> > >> > >> jerome.
> > >> > >>
> > >> > >>|||Hi
Can you post DDL and (a small set of) example data that will fail.
John
"Agho" wrote:
> Can you please give an example of whay you want done? I don't quite
> understand it. For now, I am using OPENDATASOURCE to insert the data in to
> the desired table. This is taking about 3 minutes compared to 15 seconds when
> I use BCP.
>
> Thanks
>
> "John Bell" wrote:
> > Hi
> >
> > This issue could be cause be either a row or field terminator being present
> > withing your data or possibly a missing row terminator at the end of the
> > file. The latter should be easy to identify as only the last row may not be
> > imported. The easiest way to correct the former is to generate the data file
> > with specify terminators that you will know are not going to occur in the
> > data, then use the -r and -t option to specify the appropriate character when
> > importing the data with BCP.
> >
> > HTH
> >
> > John
> >
> >
> > "Jeje" wrote:
> >
> > > yes, same error here.
> > >
> > >
> > > "Agho" <Agho@.discussions.microsoft.com> wrote in message
> > > news:A81B5189-A84A-4C3E-B1D8-DED1DB3190C0@.microsoft.com...
> > > > I am having a similar problem.
> > > > When I use -N, I get this error:
> > > > SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]String data, right truncation
> > > >
> > > > When I use -n I get this error:
> > > > SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native
> > > > Client]Unexpected EOF encountered in BCP data-file
> > > >
> > > > Any help will be appreciated.
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > >> I would still try -n
> > > >>
> > > >> John
> > > >>
> > > >> "Jeje" wrote:
> > > >>
> > > >> > the BOL says:
> > > >> > -N is native unicode format:
> > > >> > http://msdn2.microsoft.com/en-us/library/ms189941.aspx
> > > >> >
> > > >> > the varchar and char are exported in Unicode and others are in their
> > > >> > native
> > > >> > format.
> > > >> >
> > > >> > we have solve the issue for few tables by using the -w instead of -N
> > > >> > but some tables continues to suffer the same issue!
> > > >> >
> > > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > >> > news:451208EF-8061-4BCB-9F2F-D12800122EC5@.microsoft.com...
> > > >> > > Hi
> > > >> > >
> > > >> > > -N is keep non-text native and -n is native so you may want to try
> > > >> > > that
> > > >> > > instead.
> > > >> > >
> > > >> > > John
> > > >> > >
> > > >> > > "Jeje" wrote:
> > > >> > >
> > > >> > >> Hi,
> > > >> > >>
> > > >> > >> I'm using BCP to copy some data between 2 servers.
> > > >> > >>
> > > >> > >> I'm using the -N option to export the tables into the Native Unicode
> > > >> > >> format
> > > >> > >> this works fine
> > > >> > >> except for few tables where I receive the truncation error:
> > > >> > >> SQLState = 22001, NativeError = 0
> > > >> > >> Error = [Microsoft][SQL Native Client]String data, right truncation
> > > >> > >>
> > > >> > >> this process has been used for few month without any issue and now I
> > > >> > >> start
> > > >> > >> to see this error.
> > > >> > >>
> > > >> > >> any idea?
> > > >> > >> thanks for your quick guides!
> > > >> > >>
> > > >> > >> jerome.
> > > >> > >>
> > > >> > >>sql

BCP truncation error on few tables?

2012年3月22日星期四

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
Im using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastin|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=
ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1nsql

2012年3月20日星期二

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
--
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
I´m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastián|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1n

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

2012年3月6日星期二

bcp error handling

Hello
I am using bcp.exe to transfer data between remote servers and need a way to
do the error handling. What I need is simple - just to know whether the copy
was successful or not, because it needs to be in transaction with other
operations. There are situations when the return value of the exe is always
0 and nothing gets written into the error log ( for example copying from
file to table that violates a constraint ). All that came to me was to parse
the log file ( option -o ) for strings like 'error' and 'failed' but this
doesn't look like a great idea. And yes, I am using bcp in both directions,
so bulk insert will not do.
Any ideas of how to find whether the copy was successful?
(sql server 2000 sp3, in case it matters)
Thanks
Plamen
"Plamen Doykov" <p_doykov@.code.bg> schrieb im Newsbeitrag
news:uoZ1ear1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am using bcp.exe to transfer data between remote servers and need a
way to
> do the error handling. What I need is simple - just to know whether the
copy
> was successful or not, because it needs to be in transaction with other
> operations. There are situations when the return value of the exe is
always
> 0 and nothing gets written into the error log ( for example copying from
> file to table that violates a constraint ). All that came to me was to
parse
> the log file ( option -o ) for strings like 'error' and 'failed' but
this
> doesn't look like a great idea. And yes, I am using bcp in both
directions,
> so bulk insert will not do.
> Any ideas of how to find whether the copy was successful?
> (sql server 2000 sp3, in case it matters)
We settled with the output parsing also. That's what we use in Java:
private static final Pattern ERROR_PATTERN =
Pattern.compile(
"^\\s*(?:SQLState\\s*=\\s*([^,]+?)\\s*,\\s*NativeError\\s*=\\s*([+-]?\\d+)
\\s*|" +
"Error\\s+=\\s*+(.*?))$", Pattern.MULTILINE );
/**
* Extract error BCP messages from the given process output.
*
* @.param bcpOutput the output to extract the messages from.
* @.return a List of {@.link String} with the messages.
*/
protected static List extractErrorMessages( String bcpOutput ) {
List messages = new LinkedList();
Matcher m = ERROR_PATTERN.matcher( bcpOutput );
SqlServerError err = null;
while ( m.find() ) {
String message = m.group( 3 );
if ( message == null ) {
/*
* no message => first line:
* SQLState = 23000, NativeError = 3604
*/
err = new SqlServerError();
err.setSqlState( m.group( 1 ) );
err.setNativeError( StringParsing.string2int( m.group(
2 ) ) );
}
else {
/*
* second line:
* Error = [Microsoft][ODBC SQL Server Driver][SQL
Server]Doppelter Schl?ssel wurde ignoriert.
*/
err.setMessage( message );
messages.add( err );
err = null;
}
}
return messages;
}
Kind regards
robert

2012年2月25日星期六

BCP between two different servers

How would you go about bcp two tables from 2000 on server a and bcping it to
6.5 on server b?
New SQL Server DBA
BCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA

BCP between two different servers

How would you go about bcp two tables from 2000 on server a and bcping it to
6.5 on server b?
--
New SQL Server DBABCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA

BCP between two different servers

2012年2月23日星期四

BCP and Bulk Insert to Linked Servers

Hi guys!

Heres my set up:
1) Im using Win2003 with MS SQL 2000
2) I have a linked server in SQL Server pointing to an MS Access DB.

Why MS Access? Gee, I dont know. The guy who owns it refused to update his
VB app and point it to SQL Server.

Anyway, I have 190,000 records in SQL Server that I wanted to dump and
insert it to MS Access.
I tried to use OPENQUERY but OLE DB provider choked and wont be able to
handle that much records. Sucks!
Moreover, DTS packages wont do the job. I tried it and it have the same
problem.
Now, I got one last option to go to. I EXPORTED SQL Server data to a text
file using BCP but my problem is how to IMPORT those data from the TEXT
file to my Linked Server that points to an MS Access.

This is what Im trying to do:

SQL Server Data --> Text file --> Linked Server (MS Access)

bcp LinkedServerName..MSAccess_TableName in Shares1_tmp.txt -c -T -t ',' -r
'\n'

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()).

Thank you and you guys have a nice day.

--
Message posted via http://www.sqlmonster.comErvs Sevilla via SQLMonster.com (forum@.SQLMonster.com) writes:
> Heres my set up:
> 1) Im using Win2003 with MS SQL 2000
> 2) I have a linked server in SQL Server pointing to an MS Access DB.
> Why MS Access? Gee, I dont know. The guy who owns it refused to update his
> VB app and point it to SQL Server.
> Anyway, I have 190,000 records in SQL Server that I wanted to dump and
> insert it to MS Access.
> I tried to use OPENQUERY but OLE DB provider choked and wont be able to
> handle that much records. Sucks!
> Moreover, DTS packages wont do the job. I tried it and it have the same
> problem.
> Now, I got one last option to go to. I EXPORTED SQL Server data to a text
> file using BCP but my problem is how to IMPORT those data from the TEXT
> file to my Linked Server that points to an MS Access.
> This is what Im trying to do:
> SQL Server Data --> Text file --> Linked Server (MS Access)

This sounds like a dead end to me. Bulk insert to linked server is not
supported, as I recall. And last time I looked at it, at least the
other server was another SQL Server.

I would suggest that you inquire in an Access newsgroup for how to import
that data into Access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yah thats what I thought too because those option fields from BCP dont have
something for Linked Servers.

Do you have any other suggestions to copy and insert those 190,000 records
to MS Access?

Thank you for the reply.
I appreciate it.

--
Message posted via http://www.sqlmonster.com|||Ervs Sevilla via SQLMonster.com (forum@.SQLMonster.com) writes:
> Do you have any other suggestions to copy and insert those 190,000 records
> to MS Access?

To repeat myself: ask in a newsgroup devoted to Access. Maybe there
are some people here who knows Access, but I am certainly not one of
them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Ervs Sevilla via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:41fb40a4015744f5bee5bfdc03052973@.SQLMonster.c om...
> Yah thats what I thought too because those option fields from BCP dont
> have
> something for Linked Servers.
> Do you have any other suggestions to copy and insert those 190,000 records
> to MS Access?
> Thank you for the reply.
> I appreciate it.
> --
> Message posted via http://www.sqlmonster.com

One thing to try would be to experiment with the batch size option for a DTS
Transform Data task. The OLE DB provider might not like handling 190,000
rows in a single insert, but if you do it in batches of 10,000 rows (or
whatever), it might work. However, that's pure speculation, and as Erland
says, you'll probably get better information on importing into Access in an
Access group.

Simon|||Thank you guys...
Ill post my prob in MS Access forum.

By the way, I did tried to insert 1,000 records at a time but again OLEDB
Jet 4.0 for MS Access choked.

I forgot to mentioned that the destination table in Access have 106 columns
thats why using OPENQUERY choked as well. The table is flat like a pan cake.
Moreover, theres another table that only have 54 columns/fields and I was
able to insert a total of 230,000 records.

--
Message posted via http://www.sqlmonster.com

订阅：博文 (Atom)

2012年3月29日星期四

BcpBatchSize problem in replication

BcpBatchSize problem in replication

BcpBatchSize problem in replication

BCPBatchSize and Replication hanging

2012年3月25日星期日

BCP truncation error on few tables?

BCP truncation error on few tables?

BCP truncation error on few tables?

2012年3月22日星期四

BCP problem on different servers

2012年3月20日星期二

BCP problem on different servers

2012年3月11日星期日

BCP IN trouble

2012年3月6日星期二

bcp error handling

2012年2月25日星期六

BCP between two different servers

BCP between two different servers

BCP between two different servers

2012年2月23日星期四

BCP and Bulk Insert to Linked Servers

Sql Basic Security

Blog Archive

About Me