Index in a table being replicated

Indexes can be added and removed without changing the
publication. If you do it manually on the publisher, you
can use sp_addscriptexec to run the same script on the
subscriber, or just create it manually.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks a lot,
If I need to add a new field in a table that is being replicated? How can I
do?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:40cb01c52022$fb3c7f30$a401280a@.phx.gbl...
> Indexes can be added and removed without changing the
> publication. If you do it manually on the publisher, you
> can use sp_addscriptexec to run the same script on the
> subscriber, or just create it manually.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jennyfer,
sp_repladdcolumn
Rgds,
Paul Ibison
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uxCsMnQIFHA.3472@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot,
> If I need to add a new field in a table that is being replicated? How can
> I
> do?
> Thanks in advance
> Jennyfer
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:40cb01c52022$fb3c7f30$a401280a@.phx.gbl...
>
|||Hello I added a new column using the sp_repladdcolumn. But I need to update
this new column or any other field in this table and I get the error:
The row was updated at 'SERVER-WMS.FSL' but could not be updated at
'NASH-WMS.FSL'. Procedure or function
sp_upd_80C179F0050B4837F57C4E2252BD409E has too many arguments specified.
The updates are being reversed
What can I do?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FAUHbWIFHA.3484@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Jennyfer,
> sp_repladdcolumn
> Rgds,
> Paul Ibison
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:uxCsMnQIFHA.3472@.TK2MSFTNGP09.phx.gbl...
can
>
|||Jennyfer,
have a look to see if this applies: http://support.microsoft.com/kb/825025
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It says in this page that there's a hotfix but it may receive additional
testing, or better wait for the next Microsoft SQL Server 2000 service pack
that contains this hotfix. How can I get this hotfix at no cost? Is it
possible? One thing I don't like is that this needs additional testing. This
is a bug.
Now I have 10000 conflicts and it's trying to update the data to other site.
How can I stop this error?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u78WRp1IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Jennyfer,
> have a look to see if this applies: http://support.microsoft.com/kb/825025
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If you raise a support call with PSS and say that you
have this particluar issue they should give you the
hotfix for free. It'll be a link to a download page
specific to yourself.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Index Fragmentation

I have been tasked with a obtaining a method of manually creating Clustered Index fragmentation for testing purposes. Does anyone have a script that would cause fragmentation but would still keep the table contents the same. Thanks.

I don't have a script, but something like this for each table...

You have to reload it I beleive. Can't rebuild the clusterered index a different order and set the order back, as it would defrag at that point...so get the data off to a temp table...

select * into #tableName from tableName

Then you need to reload it in chunks, with an order by that goes against the clustered index column order... If you just try one insert from that #tableName, it won't frag enough, SQL will get it mostly in clustered order as it loads.. but if you break up the loads into unordered batches it'll frag the clusterered index... You'll need to deal with PK/FK's and copy teh data off to a temp table , then bring it back something like this, in small batches, until all teh data is back in...

Code Snippet

insert into tableName

select top 1000 *

from #tableName t1

where not exists

(select *

from tableName t2

where t1.pkcolumn = t2.pkcolumn)

order by non_clusterd_columns desc

You could generate that script if you have numerous table to reload.... I was looking to see if there is a command on a CREATE INDEX to just not rebuild the clustered index if you change it, but didn't see it.. might be a way though someone else knows......... Have fun.... Bruce

|||

This seems like it will do the job. The only issue i have is that the table does not have a unique ID. So any ideas on loading in 1000 row chunks would be appreciated.

Thanks

|||

One way of doing that... you could add a row number to the # table, like this...

Code Snippet

select

ROW_NUMBER() OVER (ORDER BY non_clustered_column) AS 'ID',*

into #tableName

from tableName

order by non_clustered_column

so your # table would then be setup with an ID and in jumbled order .... could then loop thru that by ID, in batches of 1000 and it would be loading in scrambled order. Do enough batches so it scrambles it good, the lower the batch row count the better to cause fragmentation... Bruce

|||Many thanks for this. Just need to figure out the best way to insert in 1000 row batches now.|||I am stuggling to find a method of importing 1000 row batches. What would be the best method, if i have added the ID column to the table?|||

Once you have your temp table, and the ID field in it... you could do some type of a LOOP like this...

Code Snippet

while (1=1)

BEGIN

INSERT INTO yourTable

select top 1000 *

from #tempTable t1

where not exists

(select *

from yourTable t2

where t1.ID = t2.ID)

order by nonclustereColumn desc

if (@.@.rowcount = 0 ) break

END

Might have to play some games with the ID not being in your base table. Like, add the column, then drop it after you load... But you said you have a clustered index, so just do the match based on all clustered index columns... and don't bother with the new ID column?!?...

Bruce|||Thanks Bruce

Index Fragmentation

I have been tasked with a obtaining a method of manually creating Clustered Index fragmentation for testing purposes. Does anyone have a script that would cause fragmentation but would still keep the table contents the same. Thanks.

I don't have a script, but something like this for each table...

You have to reload it I beleive. Can't rebuild the clusterered index a different order and set the order back, as it would defrag at that point...so get the data off to a temp table...

select * into #tableName from tableName

Then you need to reload it in chunks, with an order by that goes against the clustered index column order... If you just try one insert from that #tableName, it won't frag enough, SQL will get it mostly in clustered order as it loads.. but if you break up the loads into unordered batches it'll frag the clusterered index... You'll need to deal with PK/FK's and copy teh data off to a temp table , then bring it back something like this, in small batches, until all teh data is back in...

Code Snippet

insert into tableName

select top 1000 *

from #tableName t1

where not exists

(select *

from tableName t2

where t1.pkcolumn = t2.pkcolumn)

order by non_clusterd_columns desc

You could generate that script if you have numerous table to reload.... I was looking to see if there is a command on a CREATE INDEX to just not rebuild the clustered index if you change it, but didn't see it.. might be a way though someone else knows......... Have fun.... Bruce

|||

This seems like it will do the job. The only issue i have is that the table does not have a unique ID. So any ideas on loading in 1000 row chunks would be appreciated.

Thanks

|||

One way of doing that... you could add a row number to the # table, like this...

Code Snippet

select

ROW_NUMBER()OVER(ORDERBYnon_clustered_column)AS'ID',*

into #tableName

from tableName

order by non_clustered_column

so your # table would then be setup with an ID and in jumbled order .... could then loop thru that by ID, in batches of 1000 and it would be loading in scrambled order. Do enough batches so it scrambles it good, the lower the batch row count the better to cause fragmentation... Bruce

|||Many thanks for this. Just need to figure out the best way to insert in 1000 row batches now.|||I am stuggling to find a method of importing 1000 row batches. What would be the best method, if i have added the ID column to the table?|||

Once you have your temp table, and the ID field in it... you could do some type of a LOOP like this...

Code Snippet

while(1=1)

BEGIN

INSERTINTO yourTable

selecttop 1000 *

from #tempTable t1

wherenotexists

(select*

from yourTable t2

where t1.ID= t2.ID)

orderby nonclustereColumn desc

if(@.@.rowcount= 0 )break

END

Might have to play some games with the ID not being in your base table. Like, add the column, then drop it after you load... But you said you have a clustered index, so just do the match based on all clustered index columns... and don't bother with the new ID column?!?...

Bruce|||Thanks Bruce