Replication to different table structure

I had set up some Snapshot and Transactional replication in the past but
would like to see if following is possible.
Is it possible to do either snapshot or transactionl replication between
tables with 2 different table structure? Lets say I have a tableA which i
like to replicate to TableB. But TableB has few additional columns from
tableA. Also, How about datatype difference on some columns between TableA
and TableB?
Version is Sql server 2000 sp4.
I appreciate your reply.
Filtering (vertical) can be used to restrict the columns. Where the
datatypes are different there are a few posibilities, but replicating
indexed views is quite straightforward.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Have a look at this:
http://www.dbazine.com/sql/sql-articles/cotter1
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
"James" <kush@.brandes.com> wrote in message
news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>I had set up some Snapshot and Transactional replication in the past but
>would like to see if following is possible.
> Is it possible to do either snapshot or transactionl replication between
> tables with 2 different table structure? Lets say I have a tableA which i
> like to replicate to TableB. But TableB has few additional columns from
> tableA. Also, How about datatype difference on some columns between TableA
> and TableB?
> Version is Sql server 2000 sp4.
> I appreciate your reply.
>
|||http://www.dbazine.com/sql/sql-articles/cotter1
I just get a page prompting to renew the domain name... do you have another
link Hilary?
"Hilary Cotter" wrote:

> Have a look at this:
> http://www.dbazine.com/sql/sql-articles/cotter1
> --
> 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
>
> "James" <kush@.brandes.com> wrote in message
> news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>
>
|||Wow, I just checked this link and it was valid, and now I get the domain
expired as well
Have a look at this:
http://209.85.165.104/search?q=cache:4m4CLStYfWIJ:www.dbazine.com/sql/sql-articles/cotter1+http://www.dbazine.com/sql/sql-articles/cotter1&hl=en&ct=clnk&cd=1&gl=us
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
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:4E6A1EF4-E639-4ED7-B4BA-F6DEE42F016E@.microsoft.com...[vbcol=seagreen]
> http://www.dbazine.com/sql/sql-articles/cotter1
> I just get a page prompting to renew the domain name... do you have
> another
> link Hilary?
>
> "Hilary Cotter" wrote:
|||Thanks Hilary,
I got the same text minus the images from google cache and
waybackmachine.org. Just got finished reading it and a few of your other
articles on dbdirections.com.
Steve
"Hilary Cotter" wrote:

> Wow, I just checked this link and it was valid, and now I get the domain
> expired as well
> Have a look at this:
> http://209.85.165.104/search?q=cache:4m4CLStYfWIJ:www.dbazine.com/sql/sql-articles/cotter1+http://www.dbazine.com/sql/sql-articles/cotter1&hl=en&ct=clnk&cd=1&gl=us
> --
> 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
>
> "Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
> news:4E6A1EF4-E639-4ED7-B4BA-F6DEE42F016E@.microsoft.com...
>
>
|||Thanks Hilary! Helpful article.
I am still having some trouble though. I am trying to implement some of the
suggestion from that Article for Case 2 (where subscriber has more columns
than publisher) and I am using Snapshot replication only.
Suggestion1: I precreated the table on the subscriber and provide default
value for the column (which doesn't exist on publisher), but when the
distributer agent runs it gives me following error:
"Unexpected EOF encountered in BCP data-file"
Suggestion 2: "Supply a value for those columns in the replication stored
procedure". Where can I find this SP? It doesn't exist on Subscription
database.
Suggestion 3: "Index View". haven't tried yet. going to try very soon.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoznYMuaHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Have a look at this:
> http://www.dbazine.com/sql/sql-articles/cotter1
> --
> 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
>
> "James" <kush@.brandes.com> wrote in message
> news:uc8kggmaHHA.808@.TK2MSFTNGP04.phx.gbl...
>
|||Could you post the table schema on the publisher and what you want it to
look like on the subscriber?
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
"James" <kush@.brandes.com> wrote in message
news:ux0YSQwaHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Thanks Hilary! Helpful article.
> I am still having some trouble though. I am trying to implement some of
> the suggestion from that Article for Case 2 (where subscriber has more
> columns than publisher) and I am using Snapshot replication only.
> Suggestion1: I precreated the table on the subscriber and provide default
> value for the column (which doesn't exist on publisher), but when the
> distributer agent runs it gives me following error:
> "Unexpected EOF encountered in BCP data-file"
> Suggestion 2: "Supply a value for those columns in the replication stored
> procedure". Where can I find this SP? It doesn't exist on Subscription
> database.
> Suggestion 3: "Index View". haven't tried yet. going to try very soon.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoznYMuaHHA.4396@.TK2MSFTNGP06.phx.gbl...
>

Replication target table shrinks to zero

I want to replicate a database to a subscriber that will be used as a read
only copy. The data has to be replicated as close to instantly as possible.
To do this I set up a database export of objects and data to populate the
subscriber, then I set up transactional replication. To verify that
replication is working successfully, I count the rows in each table, there
are 3 tables in total. For one of the tables, the replication completes but
almost immediately afterward, the table starts to shrink, and after several
hours the record count is zero. This isn't happening to the other two
tables, and I can't figure out why.

If you have no idea what might be causing this, perhaps you can suggest
some places to start looking. This is Win2k SP4 with SQL 2000 SP3.

Thanks much."someguy" <nospam@.thanks.com> wrote in message
news:Xns945451BFDB728nospamthankscom@.207.35.177.13 5...
> I want to replicate a database to a subscriber that will be used as a read
> only copy. The data has to be replicated as close to instantly as
possible.
> To do this I set up a database export of objects and data to populate the
> subscriber, then I set up transactional replication. To verify that
> replication is working successfully, I count the rows in each table, there
> are 3 tables in total. For one of the tables, the replication completes
but
> almost immediately afterward, the table starts to shrink, and after
several
> hours the record count is zero. This isn't happening to the other two
> tables, and I can't figure out why.

Sounds like there's a trigger or another user operating on that table.

Replication itself should not cause a table to shrink (unless of course the
original source table is shrinking.)

> If you have no idea what might be causing this, perhaps you can suggest
> some places to start looking. This is Win2k SP4 with SQL 2000 SP3.
> Thanks much.

replication tables with forign key

Hi all

i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.

can anyone help me? :confused:

Thanx
M.J.Daneshhttp://www.microsoft.com/technet/prodtechnol/sql/2000/books/c09ppcsq.mspx for reference on configuring Merge/Transactional/snapshot replication types.

replication table size

Hi,
I would like to know the following :
1. During replication , is the data being transmitted in binary format
2. where do i get to check the size of the whole replication(in bytes/KB) ?
3. where do i get to check the size of a single table being replicated (in
bytes/KB)
appreciate any advice
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
Hi,
I know from some previous postings that sp_spaceused (see BOL for more
details) can be used to find out the size of a db/table but is that the size
being transmitted during Replication ?
and also how do i check the size of a packet (is it defaulted to 4096 k) ?
tks & rdgs
maxzsim wrote:
>Hi,
> I would like to know the following :
> 1. During replication , is the data being transmitted in binary format
> 2. where do i get to check the size of the whole replication(in bytes/KB) ?
> 3. where do i get to check the size of a single table being replicated (in
>bytes/KB)
> appreciate any advice
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1

Replication Table Permissions

Is there a way to replicate the table permissions from publisher to
subscriber? I noticed that when replication takes place, the permissions
that were set up on tables on the subscriber are wiped out. I need the permissions to be send to the subscriber automatically.I believe that is not part of the snapshot. However, you can script it and make it the post snapshot script.|||Can yoy give me the script or recommend a side with the script because that exactly what i am looking for and can't figure it out as is not discussed on microsoft help|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replmon_9pwh.asp for reference.|||Satya, i don't see anything to do with permissions on the website you send me|||As per the BOL :
How to script replication (Enterprise Manager)
At the Publisher, open SQL Server Enterprise Manager, expand a server group, right-click the Replication folder, and then click Generate SQL Script.

Select the replication component to script (Distributor properties, publications and push subscriptions, or pull subscriptions) and whether you want the script to enable or create the components or disable or drop the components. .

HTH|||I am sorry about this Satya, but i can see only the logins granted for my publicatiom on my Publisher/Distributor, my question is how do you take those permissions automatically to the subsciber?

replication SQLServer 2-way

We have 2 SQLServer-databases with the same table in it. In both databases the table can be updated by users.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.

Hope this helps.|||joejcheng,

We tried the solution you provided and it works fine now. Thank you for your information.

Replication snapshot error

The process could not bulk copy out of table '[dbo].[syncobj_0x4333454238413435]'.

Under Error Information, Full Message:
ODBCBCP/Driver version mismatch

This was working fine, until we had to drop the publication to make a change to the table. Now we get this, after creating the publication and subscriptions
We did apply Service Pack 3 recently (running SQL Server 2000).I suspect the .dll used in MDAC has the problem, refer to MS Data (http://www.microsoft.com/data/) link and download the compnent checker tool and check for any inconsistencies.

replication setup

Hi,
I am using SQL 2005 server. If I want to replicate a table from A
server to B server, using transactional publication, do I setup a
publication in A server with article of the table and then setup a
subscription in B server? Am I over-simplifying or there is some thing I
need to set up? Cause now it is not changing the record in B server when I
change the record in A server.
Please help. Thanks.
Michael - have you set this up already? If the table is on Server B then I
assume that the initialization has worked and therefore the snapshot and
distribution agent have worked. Can you see if the distribution agent is on
a schedule and if it is, start the relevant job to send the recent
transactions over. Assuming this is the case, you might want to set the
agent to run continuously to reduce latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication scnerio

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

sql

Replication scnerio

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

Replication scenario

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

Replication rows getting deleted at publisher

I am using merge replication between two servers.
When i insert data into a perticular table ..the data gets deleted after some time.
Anyone faced this problem?In your publisher table, how many rowguid id in each table?|||The each table has one rowguid...

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon Shargorodsky
Run stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky
|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:

> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> exist
> get
>
>
|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"[vbcol=seagreen]
> "Mike Wengler" wrote:
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:

> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> replication"
> problem.
> in
> I
> the
>
>

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon ShargorodskyRun stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:
> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > I have disabled publisher, distributor and subscriber; no publications
> exist
> > on publisher. However, when I try to alter a table on former publisher I
> get
> > this error:
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> > table 'dbo.MAIN' because it is being used for replication.
> >
> > Please help, thank you in advance for your time!
> >
> > Leon Shargorodsky
>
>|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"
> "Mike Wengler" wrote:
> > Run stored proc sp_removedbreplication and see if that fixes your
problem.
> >
> > Mike
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
in
> > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > I have disabled publisher, distributor and subscriber; no publications
> > exist
> > > on publisher. However, when I try to alter a table on former publisher
I
> > get
> > > this error:
> > >
> > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
the
> > > table 'dbo.MAIN' because it is being used for replication.
> > >
> > > Please help, thank you in advance for your time!
> > >
> > > Leon Shargorodsky
> >
> >
> >|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:
> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> > No, I'm still getting the same error: "table is being used for
> replication"
> >
> > "Mike Wengler" wrote:
> >
> > > Run stored proc sp_removedbreplication and see if that fixes your
> problem.
> > >
> > > Mike
> > >
> > > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
> in
> > > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > > I have disabled publisher, distributor and subscriber; no publications
> > > exist
> > > > on publisher. However, when I try to alter a table on former publisher
> I
> > > get
> > > > this error:
> > > >
> > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
> the
> > > > table 'dbo.MAIN' because it is being used for replication.
> > > >
> > > > Please help, thank you in advance for your time!
> > > >
> > > > Leon Shargorodsky
> > >
> > >
> > >
>
>

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Canno
t drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon ShargorodskyRun stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Can
not drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:

> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> exist
> get
>
>|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"[vbcol=seagreen]
> "Mike Wengler" wrote:
>
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:

> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> replication"
> problem.
> in
> I
> the
>
>

Replication Question

Hi,

I am trying to set-up a transactional replication.

1. Created a Publication with 1 table containing only the first four columns (out of 10 columns).

2. When I create a subscription, how do I map the Publication Columns to Subscription columns? In my subscriber DB, I need different column names. I don't get an option to map the columns ...

How do I do it? TIA.

1. To create article with part of columns, please refer to Books On line, title <To define a column filter for an article published in a snapshot or transactional publication>

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/504fd568-13ae-4dc4-88da-950c1ec1e8c1.htm

2. Subscription can have table name different to published table name, but columns name has to be same.

Thanks

Yunjing

replication question

I have a transactional replication set up Between servers a as publisher and b as subscriber with tables aaa and bbb.
If i make a change on table aaa on the subscriber side should it over written by the value of the publication table value.

means

i have table aaa

id name date

1 bbbb 7/7/77

I have changed name as cccc on subscriber side, should it be over written to bbbb which is the value on publisher table.

Thanks.Transactional replication is a "one way street", from the publisher to the subscriber(s). You can enable RPC calls to funnel changes from the subscriber back to the publisher, but these aren't enabled by default. They are also a positive pain in the patoot to maintain!

Methinks thou desires Merge replication, bubba!

-PatP|||Thanks and I appreciate your response But, I was not asking whether the changes in the suscriber effects at publisher or not?If i made a change at subscriber like changing name from bbbb to cccc.So now the name at publisher database is bbbb and at subscriber is cccc.
Does the changed name at subscriber database(cccc) will be overwritten as bbbb which the name at publisher database. or it will be cccc at subscriber and bbbb at publisher.If it wont over write that name at subscriber how can we maintain same name at both the ends.
Thanks.|||Nothing will automagically force an update at the subscriber, but the next update to that row at the publisher will either a) update the name value at the subscriber, b) ignore the name value at the subscriber, or c) cause the replication distribution agent to fail. Which of the three will be determined by a combination of subscription and distributor settings.

-PatP

replication question

I am a replication newbie so bear with me .

1. I want to add another table to an existing replication . Table has been created on Pub and Sub . How do I add it to the publication of 10 existing published table ?
- Do I disable the Distribution agent for those 10 tables , add this 11th table and re enable the agent ?

Please help

ThanksMy solution has always been to just create a new publication until I can declare down time to consolidate the publications. That way I don't need to declare downtime just to add a table.

When you are going to declare downtime anyway:

1) Stop all end user access!
2) Drop the old publications
3) Create a new publication that combines the old publications
4) Allow user access again

When you create the new publication, you can do it without forcing the initial snapshot. On small databases (under 10 Gb) it doesn't make much difference, but on larger databases that snapshot can take quite a while.

-PatP|||So Only way to do it is Either create a new publication or RECreate the whole publication (which is impossible almost !..) The Publishing datbase is 6 Gb but i subscribing to an Archive which is 280Gb . Informastion is very Critical and too risky to drop existing Pub and recreate it.|||We have a very similar environment as Pat has described.

We don't do snapshot either. We announce the downtime first, pause the publisher server on time, make sure no transactions can occur on all subscribers. Then drop subscribers, drop the article(s) that need schema change, or simply add a new article, and lastly, push to all subscribers, continue servers.

Your scenario is kind of straight forward. Use sp_add_article to add the new table in the publication. No need to drop the whole pub. If you 're not sure about all those parameters, script out from the publication and use the command in it as a template.|||Originally posted by aashu
So Only way to do it is Either create a new publication or RECreate the whole publication (which is impossible almost !..) The Publishing datbase is 6 Gb but i subscribing to an Archive which is 280Gb . Informastion is very Critical and too risky to drop existing Pub and recreate it. You can create a new publication for the table(s) you want to add, and let them snapshot normally. This can be done at any time, although the users won't be able to use the newly added tables until they finish replicating (and you do any housekeeping like granting permissions, etc).

Once everything is running nicely:

1) Announce downtime a few days in advance.
2) Lock the users out, 100% for certain.
3) Drop the new publication (and all its subscriptions)
4) Drop only the subscriptions to the main publication
5) Add the tables from the new publication you just dropped to the main publication.
6) Create new subscriptions, with the "existing data" option.
7) Re-enable user access to the publisher and the subscribers

This gives you a downtime window that is only minutes wide if everything goes as planned (and it almost always does).

-PatP|||Thanks for all the replies. Let me make it more simple for myself.

Table is already on Prod Server and in use . Has got about 280,000 records in 2 days. Table exists in Subscriber server(No data)

Can I just create a separate Publication and new scbscription ? Without affecting users

Thanks|||the answer is NO. You can create publication for this table at any time without affecting users. However, if you snapshot during subsribing, the table will be exclusively locked until all records have been bcped to the subsriber, and the log reader starts to monitor updates, deletes and inserts. How many rows are there in the table?

One workaround we often perform in this case, is to restore the publisher db on the subscriber, and apply all following transaction dumps all the way up to the downtime, or to a short periond we can ensure there is no transactions. Then backup the publisher db and restore the tlog backup to the subscriber in recovery mode. At this moment, push to the subsriber using no snapshot option. This could finish in a few minutes or even sooner. If the publisher table is continuously modified, there is no easy way to get it subsribed without affecting users.|||That is the catch here .. Table grows crazy (suppporting Online users on web) it grows like a Million rows a week and is never "Not in Use"

Is there any option where I can specify do not lock table while doing snapshot ?

Other options are really out of question

Thanks|||There isn't such an option. If there is, you'll get in big trouble. You will have replication errors complaining about PK violations on duplicate rows, non-existing rows, and so on. Is the web server accessed 24x7?|||Yes it is . While the databse doing a snapshot for the first time , I assume it blocks the table to maintain data integrity till subscriber is up to date . Any Idea , how long it takes to snapshot about 600000 rows ?

Row size is not too big . Its about 100 bytes.. Also is it necessary to use those stored procs sp_MInsTbaname, sp_InsDel.. sp_Upd...

I just unchecked them while creating the Publication.|||How long it takes to get 600k rows really depends on a lot of factors, the server processors, memory, network speed, etc. My benchmark in a 8xPIII 550 Mhz cpu, 4 GB RAM, 100 mbps LAN, 600,000 rows with 100 bytes in size should finish in 5 minutes. But the result could be drastically different.

You do need the sp_MSins, del, upd sprocs to do the actual work. snapshot agent will create them if you check the option. you can leave them unchecked if you prefer creating them manually (I do this most of the time 'cause snapshot is not an option).|||Will try it tonight probably and will update you how it went.

Thanks for Help to you and all|||Thanks it worked out all well . Table has grown 1.3 million rows in 2 days

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!
Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegr oups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>
|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilarysql