bcp right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
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" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
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" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary).
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.
My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>
sql

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary)
.
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary).
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
Thanks
How big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>
|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>
|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>
|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
ThanksHow big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

Batch Insert into a Table

I have a large volume of records I need to insert into a table, but am
running into issues with my transaction log filling up. I want to
create a commit point at every 50-100k records, but don't know how to
do that in SQL Server. I was thinking it has something to do with the
@.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
use them.
Does anyone have any code or suggestions on an approach?
Many thanks,
DanYou're transaction log will fill up when doing large inserts, regardless of
whether you do it in small batches or all at once.
You could use ALTER DATABASE to put your db into bulked-logged recovery
mode, then do the insert
ALTER DATABASE [YourDb] SET RECOVERY BULK_LOGGED
--do large insert, then set recovery model back to full
ALTER DATABASE [YourDb] SET RECOVERY FULL
"Dan" wrote:

> I have a large volume of records I need to insert into a table, but am
> running into issues with my transaction log filling up. I want to
> create a commit point at every 50-100k records, but don't know how to
> do that in SQL Server. I was thinking it has something to do with the
> @.@.TRANCOUNT or @.@.ROWCOUNT system variables, but again, not sure how to
> use them.
> Does anyone have any code or suggestions on an approach?
> Many thanks,
> Dan
>|||I should also note that the data is already in the database and I'm
trying to perform an insert from a staging/temp table into another
table:
insert into final_table (field1, field2,...fieldN)
select field1, field2,...fieldN
from staging_table

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?
Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>