we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
>> we are frequently running the maintenance tasks on production during that
>> databases are not accessible.
>> to avoid this we want to switch over to the standby server during
>> maintenace and after maintenance has been completed
>> switch back to primary server.During maintenance all data changes need to
>> be replicated back to production server.
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>> To accomplish this I am planning to setup database replication from
>> producation database server to Standby server.
>> Please advise best solutions for this like microsoft trnsaction
>> replication,Goldengate replication, wansync replication.
>> I think microsoft trnsaction replication,Goldengate replication doesn't
>> support complex shema changes to get replicated.
>> Requirement :
>> 1. 10 databases need to get replicated (with 30GB - 70GB each)
>> 2. these 10 databases will have schema changes monthly.
>> 3. data changes need to moved from standby to production server after
>> maintenance has been completed.
>> Please advise
>> Thanks
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label switch. Show all posts
Showing posts with label switch. Show all posts
Tuesday, March 27, 2012
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
Monday, March 19, 2012
Best practice method for shrinking the log file in dev environments
Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Best practice method for shrinking the log file in dev environments
Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Best practice method for shrinking the log file in dev environments
Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon
Wednesday, March 7, 2012
Best Performance Query
Hi:
I have the following query, can somebody help me?
SELECT
s.Id, s.Name
FROM
Switch s
INNER JOIN
SwitchTelephoneRange r ON s.Id = r.Id
WHERE
'1526858' BETWEEN FromTelephone AND ToTelephone
Where the '1526858' is a phone number.
My problem is, I want to run the above query for each record in :
select Telephone from PhoneDirectory
So, each telephone number in the second phone, would macth the ' ' in the first query.
How can I do so? Do I need a loop? a cursor? Can you help please?
Thanks
Sounds like you need a correlated sub-query. Seehere andhere for more help.|||Maybe something like this?
SELECT
s.Id, s.Name
FROM
Switch s
INNER JOIN
SwitchTelephoneRange r ON s.Id = r.Id
INNER JOIN
PhoneDirectory pd ON pd.Telephone BETWEEN FromTelephone AND ToTelephone
Thanks for all.
Darek, your way is the best.
How can I join on a table without specifying that a key = key ?
You just joined on a table to get the phone number from and said
"inner join ... ON telephon between"
Can you explain that to me please?
That was a great tip.
Thanks a lot.
|||Thanks.
Everything afterON inJOIN clause is nothing more like simply conditions.
You can write something like that:
FROM a
inner join b.SomeVarChar LIKE a.SomeOtherVarChar
Previous query in ther way:
SELECT
s.Id, s.Name
FROM
Switch s
INNER JOIN
SwitchTelephoneRange r
INNER JOIN
PhoneDirectory pd
WHERE
s.Id = r.Id
AND pd.Telephone BETWEEN FromTelephone AND ToTelephone
Friday, February 10, 2012
BCP's /F switch - forposting.txt [1/1]
begin 755 forposting.txt
M2"!^,C`P-#`U,C(Q-CHR.3HU.7Y"3$%(0DQ!2'Y865H-"D\@.?C`P,7XR,#`T
M,#0P.'Y:42`P,#$P,#`Q?DI/12!30TA-3T4@.0T]-4$%.62`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?B!^1$)!?B!^,#`P,#!^-#,@.0D%,($A)
M3$P@.4D0N("`@.("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("!^0TA%4E)9($A)3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*?B`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?C$T,#$P("`@.("!^3GXR,#`V,#,P-'XR,#`W,#,P,7X@.
M("`@.("`@.("`@.?C$Y,#`P,3`Q?B`@.("`@.("`@.("XP,'Y44GY#3TU004Y9($Y!
M344@.("`@.("`@.("`@.("`@.("`@.("`@.('Y03R!"3U@.@..3DY("`@.("`@.("`@.("`@.
M("`@.("`@.('XQ-R!-04E.(%-4("`@.("!^4TE,5D52(%-04DE.1R`@.("`@.("`@.
M("`@.("`@.("`@.("!^341^,C$Q,3$@.("`@.('Y,:6-E;G-E($AE<F4@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('X@.("`@.("`V.#`P,'Y/0T-^("`@.("`@.("`@.
M(#!^("`@.?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.,'XD?D]#0WX@.("`@.("`@.
M("`@.,'X@.("`@.("`@.,'XD?B`@.('X@.("`@.("`@.("`@.,'X@.("`@.("`@.,'XD?D]#
M0WX@.("`@.("`Q.#DN,#!^('XP,#`P,'XQ.2!"3%5%($A)3$P@.4D0N("`@.("`@.
M("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^0TA%4E)9($A)
M3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*
M?C`X-3(S("`@.("!^("`@.("`@.-C@.P,#!^("`S-3DN-S@.X-'Y9?DY!?C$@.?C`P
M,#`P?CDY($),544@.059%+B`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^34%$25-/3B`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('Y.2GXP.#4T,"`@.("`@.?EI:60T*3R!^,#`Q?C(P,#(P-#`X?DU.
M(#`P-#`P,#)^3D57($Q)1D4@.1$E35%))0E543U)3("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^('Y$0D%^('XP,#`P,'XY,3`@.4TE-4$Q%($%612!!
M4%0@.,D(@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y02514
M4T)54D=(("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^("`@.("`@.("`@.("`@.("`@.
M("`@.("!^,#`Y.3$@.("`@.('Y.?C(P,#8P,S`Q?C(P,#<P,S`Q?B`@.("`@.("`@.
M("!^,3DP,#`Q,#%^("`@.("`@.("`@.+C`P?E12?D-/35!!3ED@.3D%-12`@.("`@.
M("`@.("`@.("`@.("`@.("`@.?E!/($)/6"`Y.3D@.("`@.("`@.("`@.("`@.("`@.("`@.
M?C$W($U!24X@.4U0@.("`@.('Y324Q615(@.4U!224Y'("`@.("`@.("`@.("`@.("`@.
M("`@.('Y-1'XR,3$Q,2`@.("`@.?DQI8V5N<V4@.2&5R92`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?B`@.("`@.(#<X.3`P?D]#0WX@.("`@.("`@.("`@.,'X@.("!^
M("`@.("`@.("`@.(#!^("`@.?B`@.("`@.("`P?B1^3T-#?B`@.("`@.("`@.("`P?B`@.
M("`@.("`P?B1^("`@.?B`@.("`@.("`@.("`P?B`@.("`@.("`P?B1^3T-#?B`@.("`@.
M(#(P,2XP,'X@.?C`P,#`P?CDY.2!32$]25"!!5D4@.05!4(#)"("`@.("`@.?B`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y-041)4T].("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#@.Y.3D@.
M("`@.('X@.("`@.("`W.#DP,'X@.(#,Y,BXU,S<S?EE^3D%^,2!^,#`P,#!^-34P
M(%-(3U)4($A)3$P@.059%($%05"`R0B`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.('Y$3U=30E521T@.@.("`@.("`@.("`@.("`@.("`@.("`@.
M?DY*?C`X.#8U("`@.("!^6E!1#0I/('XP,#%^,C`P,3`T,#A^3U$@.,#`V,#`P
M,WY304T@.34%#3D%-05)!("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.?D1"07X@.?C`P,#`P?E!/($)/6"`Q,C0@.("`@.("`@.("`@.("`@.
M("!^("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DU!1$E33TX@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GX@.("`@.("`@.("`@.("`@.("`@.("`@.('XP
M-S$P-"`@.("`@.?DY^,C`P-C`S,#%^,C`P-S`S,#%^("`@.("`@.("`@.('XQ.3`P
M,#$P,7X@.("`@.("`@.("`N,#!^5%)^0T]-4$%.62!.04U%("`@.("`@.("`@.("`@.
M("`@.("`@.("!^4$\@.0D]8(#DY.2`@.("`@.("`@.("`@.("`@.("`@.("!^,3<@.34%)
M3B!35"`@.("`@.?E-)3%9%4B!34%))3D<@.("`@.("`@.("`@.("`@.("`@.("`@.?DU$
M?C(Q,3$Q("`@.("!^3&EC96YS92!(97)E("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("!^("`@.("`@.(#<T,#!^3T-#?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.
M("`@.,'X@.("!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.("`@.(#!^("`@.("`@.(#!^
M)'X@.("!^("`@.("`@.("`@.(#!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.(#,Q+C`P
M?B!^,#`P,#!^4$\@.0D]8(#DQ.2`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DI%5TE45"`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GXP.3DQ,"`@.("`@.?B`@.
M("`@.("`W-#`P?B`@.,C,X+C<P.3=^67Y.07XQ('XP,#`P,'XY,"!(04U05$].
M(%)$+B`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.?DI!4U!%4B`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#<T
-,C$@.("`@.('Y24%$-"@.
`
endRick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> begin 755 forposting.txt
> H ~2004052216:29:59~BLAHBLAH~XYZ
> O ~001~20040408~ZQ 0010001~JOE SCHMOE COMPANY
>...
I'm awfully sorry, but the hour is late, so I need to be quick.
But it appears to me that you should be able to bulk-load this file
without splitting.
First you define a format file for the header, four columns, and
use /L1 to load on the first record according to that defintion.
For the other table, you need to defined a first field in the
table that has "O ~" as terminator. For this field you specify 0
in column number - that is you don't import this field. This field
will always yield the empty string - except in the very first record.
You should not use /F2 when you import the second table.
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|||Thanks, I'd be very happy to do this without splitting. I'm not sure I
understand when you say I should define a first field in the table that
has "O ~" as terminator. That first field in the table is called
transaction_type and is VARCHAR(2), and it does need to be populated
with all the "O" values.
Am I creating a format file for all the "O" rows, and I specify 0 as the
column number in the format file? But I do need to import that field.
Sorry -- I'm not clear on what you're saying.
In article <Xns97CD7786BAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> I'm awfully sorry, but the hour is late, so I need to be quick.
> But it appears to me that you should be able to bulk-load this file
> without splitting.
> First you define a format file for the header, four columns, and
> use /L1 to load on the first record according to that defintion.
> For the other table, you need to defined a first field in the
> table that has "O ~" as terminator. For this field you specify 0
> in column number - that is you don't import this field. This field
> will always yield the empty string - except in the very first record.
> You should not use /F2 when you import the second table.
>
>|||Rick C. (blue--nospam--.heron3@.verizon.net) writes:
> Thanks, I'd be very happy to do this without splitting. I'm not sure I
> understand when you say I should define a first field in the table that
> has "O ~" as terminator. That first field in the table is called
> transaction_type and is VARCHAR(2), and it does need to be populated
> with all the "O" values.
> Am I creating a format file for all the "O" rows, and I specify 0 as the
> column number in the format file? But I do need to import that field.
> Sorry -- I'm not clear on what you're saying.
I think you got the message. Yes, the idea is that you would have to
sacrifice that field. It the value is always "O ", then you can always
arrange that with a default value.
If there are records further down in the file that start with a different
letter, then my suggestion cannot work.
I got some weird idea last night before I went to bed, but I will have to
test that first, because it may be a completely dead end. That will not
happen until tomorrow, so for the time being the answer appears to be
"cannot be done without splitting".
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|||In article <Xns97CD611DF49FAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> If there are records further down in the file that start with a different
> letter, then my suggestion cannot work.
The first record is a Header that starts with "H", then there are 2000
Original records that start with "O", and the file ends with one Footer
record that starts with "F". There are three tables, each to receive
one of these three types of records.
> I got some weird idea last night before I went to bed, but I will have to
> test that first, because it may be a completely dead end. That will not
> happen until tomorrow, so for the time being the answer appears to be
> "cannot be done without splitting".
Thanks very much for this help, and I look forward to your new thought.
If that doesn't work out, I'd be very happy to devise a way to simply
split off the Header and Footer records to two new files of one record
each ***while keeping all 2000 of the Original records *in the original-
named file****. (But that's not really a SQL Server issue!) In any
case, everyone's help here has been much appreciated.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The first record is a Header that starts with "H", then there are 2000
> Original records that start with "O", and the file ends with one Footer
> record that starts with "F". There are three tables, each to receive
> one of these three types of records.
Only 2000 records? And a footer too? Hey, what's wrong with writing
a program in C#, VBScript, Perl or whatever to just run INSERT statements?
2000 records is no big deal. For 200000 records I would not recommend this,
though!
Nevertheless, I tried my crazy idea, and I am completely perplexed,
it works!
Here are sample tables:
CREATE TABLE header (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE footer (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE middler (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL,
e varchar(12) NOT NULL,
f varchar(10) NULL,
g varchar(10) NULL)
Here is a sample file:
H~Goddag~~yxskaft!
M~Nu~tndas~tusen~juleljus~~
M~Vi~g~ver~daggstnkta~berg~
M~Vi~ro~musikanter~utifrn~~Skaraborg
M~Elva~elaka~elefanter~ervrade~Enkping
~
F~Hello~~goodbye!
The first table is trivial to load:
bcp torsten..header in slask.bcp -T -c -t ~ -F1 -L1
The essential is -L1, this constraints the load to the first line.
The footer table is almost equally simple:
bcp torsten..footer in slask.bcp -T -c -t ~ -F6 -L6
The catch is that you need to know the actual number, 6 in this case.
So you need to use a program that counts the line in file and compose
the SQL command with help of this.
The BCP command for the middler table:
bcp torsten..middler in slask.bcp -T -f slask.fmt -L4
And this is the format file:
8.0
8
1 SQLCHAR 0 0 "\n" 0 "" ""
2 SQLCHAR 0 0 "~" 1 a Finnish_Swedish_CS_AS
3 SQLCHAR 0 0 "~" 2 b Finnish_Swedish_CS_AS
4 SQLCHAR 0 0 "~" 3 c Finnish_Swedish_CS_AS
5 SQLCHAR 0 0 "~" 4 d Finnish_Swedish_CS_AS
6 SQLCHAR 0 0 "~" 5 f Finnish_Swedish_CS_AS
7 SQLCHAR 0 0 "~" 6 g Finnish_Swedish_CS_AS
8 SQLCHAR 0 0 "\r" 7 h Finnish_Swedish_CS_AS
The crazy idea I got that the line terminator Windows is two characters,
CR-LF. (Pray tell that your file does not come from Unix and only has
\n as separtor!) Normally you specify them together as the terminator for
the last field, but here I've split them. The net result of this is that
the first field for the first record in the file is the entire first
line. As you see, the DB-column number for this field is 0, which means
that we do not import it. For remaining records that first field is
just the empty string.
Again, I use the -L option to constrain how many records that are
imported. Note that the number is 4 - not 5. The -L option is not
required here, but without it you would get the message "Unexpected EOF
encountered in BCP data file". The row still gets imported though.
(With BCP. With BULK INSERT you need to set BATCHSIZE=1.)
What baffles me is that if I delete the footer row, I don't get this
message even if I leave out the -L option. After all, in this case,
there is an empty first field for an incomplete record in the file.
But that is good news! Maybe not for you, but for everyone else who
has a header, but not a footer. They don't have to count lines in the
file just to escape the message. (You have to anyway, because of the
footer.)
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|||Thanks VERY much for this crazy (and perplexing) idea, Erland -- I'll
try it out... Crazy ideas are always the most enjoyable.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!|||ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
The amazing is that it worked the first time I tried it. And yes, I'm
sure it's that specially-modified format file that does it with the
separation of \n and \r as delimiters. Thank so SO MUCH. Much
appreciated. This is great.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Only 2000 records? And a footer too? Hey, what's wrong with writing
> a program in C#, VBScript, Perl or whatever to just run INSERT statements?
> 2000 records is no big deal. For 200000 records I would not recommend this
,
> though!
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!
> Here are sample tables:|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
> YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
> The amazing is that it worked the first time I tried it. And yes, I'm
> sure it's that specially-modified format file that does it with the
> separation of \n and \r as delimiters. Thank so SO MUCH. Much
> appreciated. This is great.
I'm glad to hear that it worked out!
And I can tell you that I too was amazed when it worked in my first try.
BCP is really a tool that fosters your creative thinking. After all these
years, I'm still finding new angles with it.
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
M2"!^,C`P-#`U,C(Q-CHR.3HU.7Y"3$%(0DQ!2'Y865H-"D\@.?C`P,7XR,#`T
M,#0P.'Y:42`P,#$P,#`Q?DI/12!30TA-3T4@.0T]-4$%.62`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?B!^1$)!?B!^,#`P,#!^-#,@.0D%,($A)
M3$P@.4D0N("`@.("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("!^0TA%4E)9($A)3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*?B`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?C$T,#$P("`@.("!^3GXR,#`V,#,P-'XR,#`W,#,P,7X@.
M("`@.("`@.("`@.?C$Y,#`P,3`Q?B`@.("`@.("`@.("XP,'Y44GY#3TU004Y9($Y!
M344@.("`@.("`@.("`@.("`@.("`@.("`@.('Y03R!"3U@.@..3DY("`@.("`@.("`@.("`@.
M("`@.("`@.('XQ-R!-04E.(%-4("`@.("!^4TE,5D52(%-04DE.1R`@.("`@.("`@.
M("`@.("`@.("`@.("!^341^,C$Q,3$@.("`@.('Y,:6-E;G-E($AE<F4@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('X@.("`@.("`V.#`P,'Y/0T-^("`@.("`@.("`@.
M(#!^("`@.?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.,'XD?D]#0WX@.("`@.("`@.
M("`@.,'X@.("`@.("`@.,'XD?B`@.('X@.("`@.("`@.("`@.,'X@.("`@.("`@.,'XD?D]#
M0WX@.("`@.("`Q.#DN,#!^('XP,#`P,'XQ.2!"3%5%($A)3$P@.4D0N("`@.("`@.
M("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^0TA%4E)9($A)
M3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*
M?C`X-3(S("`@.("!^("`@.("`@.-C@.P,#!^("`S-3DN-S@.X-'Y9?DY!?C$@.?C`P
M,#`P?CDY($),544@.059%+B`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^34%$25-/3B`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('Y.2GXP.#4T,"`@.("`@.?EI:60T*3R!^,#`Q?C(P,#(P-#`X?DU.
M(#`P-#`P,#)^3D57($Q)1D4@.1$E35%))0E543U)3("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^('Y$0D%^('XP,#`P,'XY,3`@.4TE-4$Q%($%612!!
M4%0@.,D(@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y02514
M4T)54D=(("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^("`@.("`@.("`@.("`@.("`@.
M("`@.("!^,#`Y.3$@.("`@.('Y.?C(P,#8P,S`Q?C(P,#<P,S`Q?B`@.("`@.("`@.
M("!^,3DP,#`Q,#%^("`@.("`@.("`@.+C`P?E12?D-/35!!3ED@.3D%-12`@.("`@.
M("`@.("`@.("`@.("`@.("`@.?E!/($)/6"`Y.3D@.("`@.("`@.("`@.("`@.("`@.("`@.
M?C$W($U!24X@.4U0@.("`@.('Y324Q615(@.4U!224Y'("`@.("`@.("`@.("`@.("`@.
M("`@.('Y-1'XR,3$Q,2`@.("`@.?DQI8V5N<V4@.2&5R92`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?B`@.("`@.(#<X.3`P?D]#0WX@.("`@.("`@.("`@.,'X@.("!^
M("`@.("`@.("`@.(#!^("`@.?B`@.("`@.("`P?B1^3T-#?B`@.("`@.("`@.("`P?B`@.
M("`@.("`P?B1^("`@.?B`@.("`@.("`@.("`P?B`@.("`@.("`P?B1^3T-#?B`@.("`@.
M(#(P,2XP,'X@.?C`P,#`P?CDY.2!32$]25"!!5D4@.05!4(#)"("`@.("`@.?B`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y-041)4T].("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#@.Y.3D@.
M("`@.('X@.("`@.("`W.#DP,'X@.(#,Y,BXU,S<S?EE^3D%^,2!^,#`P,#!^-34P
M(%-(3U)4($A)3$P@.059%($%05"`R0B`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.('Y$3U=30E521T@.@.("`@.("`@.("`@.("`@.("`@.("`@.
M?DY*?C`X.#8U("`@.("!^6E!1#0I/('XP,#%^,C`P,3`T,#A^3U$@.,#`V,#`P
M,WY304T@.34%#3D%-05)!("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.?D1"07X@.?C`P,#`P?E!/($)/6"`Q,C0@.("`@.("`@.("`@.("`@.
M("!^("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DU!1$E33TX@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GX@.("`@.("`@.("`@.("`@.("`@.("`@.('XP
M-S$P-"`@.("`@.?DY^,C`P-C`S,#%^,C`P-S`S,#%^("`@.("`@.("`@.('XQ.3`P
M,#$P,7X@.("`@.("`@.("`N,#!^5%)^0T]-4$%.62!.04U%("`@.("`@.("`@.("`@.
M("`@.("`@.("!^4$\@.0D]8(#DY.2`@.("`@.("`@.("`@.("`@.("`@.("!^,3<@.34%)
M3B!35"`@.("`@.?E-)3%9%4B!34%))3D<@.("`@.("`@.("`@.("`@.("`@.("`@.?DU$
M?C(Q,3$Q("`@.("!^3&EC96YS92!(97)E("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("!^("`@.("`@.(#<T,#!^3T-#?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.
M("`@.,'X@.("!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.("`@.(#!^("`@.("`@.(#!^
M)'X@.("!^("`@.("`@.("`@.(#!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.(#,Q+C`P
M?B!^,#`P,#!^4$\@.0D]8(#DQ.2`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DI%5TE45"`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GXP.3DQ,"`@.("`@.?B`@.
M("`@.("`W-#`P?B`@.,C,X+C<P.3=^67Y.07XQ('XP,#`P,'XY,"!(04U05$].
M(%)$+B`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.?DI!4U!%4B`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#<T
-,C$@.("`@.('Y24%$-"@.
`
endRick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> begin 755 forposting.txt
> H ~2004052216:29:59~BLAHBLAH~XYZ
> O ~001~20040408~ZQ 0010001~JOE SCHMOE COMPANY
>...
I'm awfully sorry, but the hour is late, so I need to be quick.
But it appears to me that you should be able to bulk-load this file
without splitting.
First you define a format file for the header, four columns, and
use /L1 to load on the first record according to that defintion.
For the other table, you need to defined a first field in the
table that has "O ~" as terminator. For this field you specify 0
in column number - that is you don't import this field. This field
will always yield the empty string - except in the very first record.
You should not use /F2 when you import the second table.
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|||Thanks, I'd be very happy to do this without splitting. I'm not sure I
understand when you say I should define a first field in the table that
has "O ~" as terminator. That first field in the table is called
transaction_type and is VARCHAR(2), and it does need to be populated
with all the "O" values.
Am I creating a format file for all the "O" rows, and I specify 0 as the
column number in the format file? But I do need to import that field.
Sorry -- I'm not clear on what you're saying.
In article <Xns97CD7786BAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> I'm awfully sorry, but the hour is late, so I need to be quick.
> But it appears to me that you should be able to bulk-load this file
> without splitting.
> First you define a format file for the header, four columns, and
> use /L1 to load on the first record according to that defintion.
> For the other table, you need to defined a first field in the
> table that has "O ~" as terminator. For this field you specify 0
> in column number - that is you don't import this field. This field
> will always yield the empty string - except in the very first record.
> You should not use /F2 when you import the second table.
>
>|||Rick C. (blue--nospam--.heron3@.verizon.net) writes:
> Thanks, I'd be very happy to do this without splitting. I'm not sure I
> understand when you say I should define a first field in the table that
> has "O ~" as terminator. That first field in the table is called
> transaction_type and is VARCHAR(2), and it does need to be populated
> with all the "O" values.
> Am I creating a format file for all the "O" rows, and I specify 0 as the
> column number in the format file? But I do need to import that field.
> Sorry -- I'm not clear on what you're saying.
I think you got the message. Yes, the idea is that you would have to
sacrifice that field. It the value is always "O ", then you can always
arrange that with a default value.
If there are records further down in the file that start with a different
letter, then my suggestion cannot work.
I got some weird idea last night before I went to bed, but I will have to
test that first, because it may be a completely dead end. That will not
happen until tomorrow, so for the time being the answer appears to be
"cannot be done without splitting".
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|||In article <Xns97CD611DF49FAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> If there are records further down in the file that start with a different
> letter, then my suggestion cannot work.
The first record is a Header that starts with "H", then there are 2000
Original records that start with "O", and the file ends with one Footer
record that starts with "F". There are three tables, each to receive
one of these three types of records.
> I got some weird idea last night before I went to bed, but I will have to
> test that first, because it may be a completely dead end. That will not
> happen until tomorrow, so for the time being the answer appears to be
> "cannot be done without splitting".
Thanks very much for this help, and I look forward to your new thought.
If that doesn't work out, I'd be very happy to devise a way to simply
split off the Header and Footer records to two new files of one record
each ***while keeping all 2000 of the Original records *in the original-
named file****. (But that's not really a SQL Server issue!) In any
case, everyone's help here has been much appreciated.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The first record is a Header that starts with "H", then there are 2000
> Original records that start with "O", and the file ends with one Footer
> record that starts with "F". There are three tables, each to receive
> one of these three types of records.
Only 2000 records? And a footer too? Hey, what's wrong with writing
a program in C#, VBScript, Perl or whatever to just run INSERT statements?
2000 records is no big deal. For 200000 records I would not recommend this,
though!
Nevertheless, I tried my crazy idea, and I am completely perplexed,
it works!
Here are sample tables:
CREATE TABLE header (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE footer (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE middler (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL,
e varchar(12) NOT NULL,
f varchar(10) NULL,
g varchar(10) NULL)
Here is a sample file:
H~Goddag~~yxskaft!
M~Nu~tndas~tusen~juleljus~~
M~Vi~g~ver~daggstnkta~berg~
M~Vi~ro~musikanter~utifrn~~Skaraborg
M~Elva~elaka~elefanter~ervrade~Enkping
~
F~Hello~~goodbye!
The first table is trivial to load:
bcp torsten..header in slask.bcp -T -c -t ~ -F1 -L1
The essential is -L1, this constraints the load to the first line.
The footer table is almost equally simple:
bcp torsten..footer in slask.bcp -T -c -t ~ -F6 -L6
The catch is that you need to know the actual number, 6 in this case.
So you need to use a program that counts the line in file and compose
the SQL command with help of this.
The BCP command for the middler table:
bcp torsten..middler in slask.bcp -T -f slask.fmt -L4
And this is the format file:
8.0
8
1 SQLCHAR 0 0 "\n" 0 "" ""
2 SQLCHAR 0 0 "~" 1 a Finnish_Swedish_CS_AS
3 SQLCHAR 0 0 "~" 2 b Finnish_Swedish_CS_AS
4 SQLCHAR 0 0 "~" 3 c Finnish_Swedish_CS_AS
5 SQLCHAR 0 0 "~" 4 d Finnish_Swedish_CS_AS
6 SQLCHAR 0 0 "~" 5 f Finnish_Swedish_CS_AS
7 SQLCHAR 0 0 "~" 6 g Finnish_Swedish_CS_AS
8 SQLCHAR 0 0 "\r" 7 h Finnish_Swedish_CS_AS
The crazy idea I got that the line terminator Windows is two characters,
CR-LF. (Pray tell that your file does not come from Unix and only has
\n as separtor!) Normally you specify them together as the terminator for
the last field, but here I've split them. The net result of this is that
the first field for the first record in the file is the entire first
line. As you see, the DB-column number for this field is 0, which means
that we do not import it. For remaining records that first field is
just the empty string.
Again, I use the -L option to constrain how many records that are
imported. Note that the number is 4 - not 5. The -L option is not
required here, but without it you would get the message "Unexpected EOF
encountered in BCP data file". The row still gets imported though.
(With BCP. With BULK INSERT you need to set BATCHSIZE=1.)
What baffles me is that if I delete the footer row, I don't get this
message even if I leave out the -L option. After all, in this case,
there is an empty first field for an incomplete record in the file.
But that is good news! Maybe not for you, but for everyone else who
has a header, but not a footer. They don't have to count lines in the
file just to escape the message. (You have to anyway, because of the
footer.)
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|||Thanks VERY much for this crazy (and perplexing) idea, Erland -- I'll
try it out... Crazy ideas are always the most enjoyable.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!|||ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
The amazing is that it worked the first time I tried it. And yes, I'm
sure it's that specially-modified format file that does it with the
separation of \n and \r as delimiters. Thank so SO MUCH. Much
appreciated. This is great.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Only 2000 records? And a footer too? Hey, what's wrong with writing
> a program in C#, VBScript, Perl or whatever to just run INSERT statements?
> 2000 records is no big deal. For 200000 records I would not recommend this
,
> though!
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!
> Here are sample tables:|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
> YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
> The amazing is that it worked the first time I tried it. And yes, I'm
> sure it's that specially-modified format file that does it with the
> separation of \n and \r as delimiters. Thank so SO MUCH. Much
> appreciated. This is great.
I'm glad to hear that it worked out!
And I can tell you that I too was amazed when it worked in my first try.
BCP is really a tool that fosters your creative thinking. After all these
years, I'm still finding new angles with it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
BCP's /F switch - forposting.txt [0/1]
Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
therefore looks at line 2 and sees that there are 75 fields, and
therefore internally defines a line as 75 fields. It then goes back to
line 1 in order to start looking for line 2, determining that line 2
will come after the first 75 fields it finds. Instead of that putting
it at the *beginning* of my line 2 as I would want, it starts counting
with the four fields of line 1 and then another 71 fields into line 2,
and considers that point the "line end".
Erland, thanks much for the offer, and I'm attaching a sample file with
the first header row and then 3 rows of (approximately) 75 fields each.
I'd be very grateful for anything you can do.
In article <Xns97CC2D50F29Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> BCP
? BCP never gets , but users of BCP often gets
> over it!
> I think the best description of BCP is that it reads a *binary* file.
> The file may look like a text file to you and me, for BCP it's binary.
> When BCP reads the file, it looks the format definition of the first
> field as specified by the format file or command-line switches. When
> it has found the end of that column, it goes for the next etc. The
> row terminator is just the terminator for the last field.
> When you say -F2, that is not the second line in the file. It's the second
> record in the file according to the format specification. Given your
> description, I can guess that will be line 3 in the file. That first
> skipped record has an embedded newline in field 4, and some embedded
> field terminators in field 75.
> As for what you want to do, you will have to split the file. BCP
> is not smart enough to handle two tables or two different formats.
> Or, hm, maybe you can get away with a single file - but requires
> quite some luck. Getting that single line into a table should be
> simple. Define a format file for those four fields, and then specify
> -L 1, to get only the first row.
> Skipping that first line may be more problematic. If you can post a
> sample, I might be able to come with something. But no promises.
>
>On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
>therefore looks at line 2 and sees that there are 75 fields, and
>therefore internally defines a line as 75 fields. It then goes back to
>line 1 in order to start looking for line 2, determining that line 2
>will come after the first 75 fields it finds. Instead of that putting
>it at the *beginning* of my line 2 as I would want, it starts counting
>with the four fields of line 1 and then another 71 fields into line 2,
>and considers that point the "line end".
BCP checks to see what the field terminator - usually a tab or comma -
is for the first field. It then reads the file, byte by byte, until
it comes to that terminator. Then it continues for field 2, field 3,
and so forth. When it looks at field 4 in the file the terminator it
is looking for is still the comma or whatever, so it continues right
past the carriage return and line feed and finds the column terminator
of the first field of the second line. It then continues along the
second line, counting its way up to its 75th field. THAT field it
knows does not end in a comma, but in a carriage return / line feed
pair. SO, it keeps reading to the end of line 2 to find the next
CR/LF, and THAT is the end of what BCP sees as line 1. After that is
line 2, so that is where it starts based on the /F2 parameter.
In reaching the end of line 2, and considering it to be the end of
line 1, the first three fields are the first three on the first line.
What BCP sees as the fourth field is the last field of the first line,
the CR/LF of the first line, and the first field of the second line.
BCP then works through the second line, treating field 2 as 5, 3 as 6,
and so forth, until it looks for field 75. What BCP sees as field 75
will be field 72, 73, 74 and 75 all strung together.
Roy Harvey
Beacon Falls, CT|||On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Erland, thanks much for the offer, and I'm attaching a sample file with
>the first header row and then 3 rows of (approximately) 75 fields each.
>I'd be very grateful for anything you can do.
Using your sample data file, the following two commands split it into
two files that BCP should be able to handle. Of course it only works
if the rows are all prefixed with H and O as in the sample data.
findstr /B "H" forposting.txt > H.txt
findstr /B "O" forposting.txt > O.txt
I'd never used findstr before, thanks for prompting me to add a new
trick to the loolbox!
Roy Harvey
Beacon Falls, CT|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
> therefore looks at line 2 and sees that there are 75 fields,
Nonono! BCP does not think in lines! As Roy said, it thinks in fields.
Keep in mind that BCP can be used to import binary files as well, and
it works in a strictly binary way. The lines are only there to mislead
you. :-)
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
therefore looks at line 2 and sees that there are 75 fields, and
therefore internally defines a line as 75 fields. It then goes back to
line 1 in order to start looking for line 2, determining that line 2
will come after the first 75 fields it finds. Instead of that putting
it at the *beginning* of my line 2 as I would want, it starts counting
with the four fields of line 1 and then another 71 fields into line 2,
and considers that point the "line end".
Erland, thanks much for the offer, and I'm attaching a sample file with
the first header row and then 3 rows of (approximately) 75 fields each.
I'd be very grateful for anything you can do.
In article <Xns97CC2D50F29Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> BCP
? BCP never gets , but users of BCP often gets>
over it!> I think the best description of BCP is that it reads a *binary* file.
> The file may look like a text file to you and me, for BCP it's binary.
> When BCP reads the file, it looks the format definition of the first
> field as specified by the format file or command-line switches. When
> it has found the end of that column, it goes for the next etc. The
> row terminator is just the terminator for the last field.
> When you say -F2, that is not the second line in the file. It's the second
> record in the file according to the format specification. Given your
> description, I can guess that will be line 3 in the file. That first
> skipped record has an embedded newline in field 4, and some embedded
> field terminators in field 75.
> As for what you want to do, you will have to split the file. BCP
> is not smart enough to handle two tables or two different formats.
> Or, hm, maybe you can get away with a single file - but requires
> quite some luck. Getting that single line into a table should be
> simple. Define a format file for those four fields, and then specify
> -L 1, to get only the first row.
> Skipping that first line may be more problematic. If you can post a
> sample, I might be able to come with something. But no promises.
>
>On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
>therefore looks at line 2 and sees that there are 75 fields, and
>therefore internally defines a line as 75 fields. It then goes back to
>line 1 in order to start looking for line 2, determining that line 2
>will come after the first 75 fields it finds. Instead of that putting
>it at the *beginning* of my line 2 as I would want, it starts counting
>with the four fields of line 1 and then another 71 fields into line 2,
>and considers that point the "line end".
BCP checks to see what the field terminator - usually a tab or comma -
is for the first field. It then reads the file, byte by byte, until
it comes to that terminator. Then it continues for field 2, field 3,
and so forth. When it looks at field 4 in the file the terminator it
is looking for is still the comma or whatever, so it continues right
past the carriage return and line feed and finds the column terminator
of the first field of the second line. It then continues along the
second line, counting its way up to its 75th field. THAT field it
knows does not end in a comma, but in a carriage return / line feed
pair. SO, it keeps reading to the end of line 2 to find the next
CR/LF, and THAT is the end of what BCP sees as line 1. After that is
line 2, so that is where it starts based on the /F2 parameter.
In reaching the end of line 2, and considering it to be the end of
line 1, the first three fields are the first three on the first line.
What BCP sees as the fourth field is the last field of the first line,
the CR/LF of the first line, and the first field of the second line.
BCP then works through the second line, treating field 2 as 5, 3 as 6,
and so forth, until it looks for field 75. What BCP sees as field 75
will be field 72, 73, 74 and 75 all strung together.
Roy Harvey
Beacon Falls, CT|||On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Erland, thanks much for the offer, and I'm attaching a sample file with
>the first header row and then 3 rows of (approximately) 75 fields each.
>I'd be very grateful for anything you can do.
Using your sample data file, the following two commands split it into
two files that BCP should be able to handle. Of course it only works
if the rows are all prefixed with H and O as in the sample data.
findstr /B "H" forposting.txt > H.txt
findstr /B "O" forposting.txt > O.txt
I'd never used findstr before, thanks for prompting me to add a new
trick to the loolbox!
Roy Harvey
Beacon Falls, CT|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
> therefore looks at line 2 and sees that there are 75 fields,
Nonono! BCP does not think in lines! As Roy said, it thinks in fields.
Keep in mind that BCP can be used to import binary files as well, and
it works in a strictly binary way. The lines are only there to mislead
you. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
BCP's /F switch
We have a text file in which the first row has four fields and it needs
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets over that first short row and starts grabbing
at the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets over that first short row and starts grabbing
>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets over that first short row and starts grabbing
> at the second longer row (really row 3).
BCP? BCP never gets , but users of BCP often gets
over it!
I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
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
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets
over that first short row and starts grabbingat the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets
over that first short row and starts grabbing>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets
over that first short row and starts grabbing> at the second longer row (really row 3).
BCP
? BCP never gets , but users of BCP often gets over it!I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
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
Subscribe to:
Posts (Atom)