Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-December
This is a transactional replication (immediate updating).
|||This is a transactional replication (immediate updating).
|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com
|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
>
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.comsql

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:
> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> > This is a transactional replication (immediate updating).
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
> I'm using SQL Server 2000
> amish wrote:
> > december wrote:
> >
> > > This is a transactional replication (immediate updating).
> > which version of SQL Server you are using ?
> > SQL Server 2000 or 2005?
> >
> > If you have SQL Server 2005 then alter table will work for most
> > changes.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the schema ownership on subscriber

Hi,

I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.

Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.

any help will be appriciated.

Cheers,

Furrukh baig

Just found it myself. go to publication properties. click articles and then right click on any article and select "set properties for all tables" ... this will open the property window ... look for "destination object owner" and specify the value of schema name on target.

thanks,

Furrukh Baig

Changing the data schema in Transactional replication

I am using concurrent snapshot - transactional replication. If I need to add
the column to the replicated table, do I need to drop and re-create
subscription everytime? The database is about 20Gb, it takes up to 4 hours to
re-create two subscriptions. Is there any easier way of changing the data
schema with this configuration?
Please look at sp_repladdcolumn in BOL. If you are using SQL Server 2005,
ALTER TABLE will do it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Nope, it will not help. The column need to be added into the table through
differnet software and then populated. I was doing drop article -
subscribtion then addarticle- subscription. But I had tables locked during
the snapshot creation. Then I unchecked the tables lock and received
concurrent snapshot. Drop article-subscription wokrs well, then add article
works too, but then when I add this article to the subscription I receive an
error message: needed to specify all articles? what about if I have about
100 tables-articles?
"Paul Ibison" wrote:

> Please look at sp_repladdcolumn in BOL. If you are using SQL Server 2005,
> ALTER TABLE will do it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Please can you post up the entire error message. Also, in order for me to
repro, can you tell me if there are any extra parts to the setup eg
anonymous subscribers that I need to know about. In fact, can you script out
the publication and I'll set up something similar tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Changing publisher for Merge Replication

We have a database application that is currently being replicated to 2
remote sites. In the near future, I expect that this will increase to many
more sites. My question is this, as we move forward it is probable that at
some time we will run into the need to replace the server that currently
functions as the publisher server. Is there some way to change all of the
publications to use a different server without having to remove replication
and reinstalling it?
TIA
Ron Lounsbury
Ron,
in my experience it is only possible to migrate replication databases from
one server to another if the server names are identical. If they are not,
then the best thing to do is to script out replication, change the
references to servernames and job owners appropriately, drop the 'old'
publications on the old server then recreate the publications on the new
server.
Another poster ran into this type of issue after restoring a merge published
database to another server and found when he tried to add a column he got
the error: 21260 "Schema replication failed because database '%s' on server
'%s' is not the original Publisher of table '%s'".
I investigated this and in sysmerge articles there is a publisherid. This
ID needs to have a corresponding record in sysmergepublications. However, in
sysmergepublications, the 'publisher' column needs to match the servername,
which it didn't in his case as he had restored to another servername. So the
problem is that the replication metadata tables 'hardcode' the original
server name.
HTH,
Paul Ibison
|||Paul
Thanks for the response. It's not what I wanted to hear, but I was
pretty much expecting it. One of the problems we will have is that not all
of our subscribers are connected at any given time, making it a bit
difficult to push the database out again. Also, there is a certain amount
of time (@.20 min or so) that the subscriber can't use the system while we
are doing this. Oh well, as I said, I was afraid that this would be the
answer.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6cJxkJeEHA.3476@.tk2msftngp13.phx.gbl...
> Ron,
> in my experience it is only possible to migrate replication databases from
> one server to another if the server names are identical. If they are not,
> then the best thing to do is to script out replication, change the
> references to servernames and job owners appropriately, drop the 'old'
> publications on the old server then recreate the publications on the new
> server.
> Another poster ran into this type of issue after restoring a merge
published
> database to another server and found when he tried to add a column he got
> the error: 21260 "Schema replication failed because database '%s' on
server
> '%s' is not the original Publisher of table '%s'".
> I investigated this and in sysmerge articles there is a publisherid. This
> ID needs to have a corresponding record in sysmergepublications. However,
in
> sysmergepublications, the 'publisher' column needs to match the
servername,
> which it didn't in his case as he had restored to another servername. So
the
> problem is that the replication metadata tables 'hardcode' the original
> server name.
> HTH,
> Paul Ibison
>

changing owner of the subscriber

Hello there
Afrer i replicate my database, the tables that i replicated have been
created with the owner of the user i've accessed to the subscriber database,
instead of updating the data on my original tables that has dbo owner.
Is there a way to use replication to update the dbo tables?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Right click on your publication, select properties, click on the articles
tab. Click on the browse button to the right of each of your tables, and in
the general tab, enter dbo for destination table owner.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ezEA$uCHGHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello there
> Afrer i replicate my database, the tables that i replicated have been
> created with the owner of the user i've accessed to the subscriber
> database, instead of updating the data on my original tables that has dbo
> owner.
> Is there a way to use replication to update the dbo tables?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

changing fieldsize in a replicated database

I am looking for a way to increase the size of a varchar field in a
replicated database, without disturbing the data of course. I do not
want to remove the replication just for this one issue. What would the
process be in writing a SQL query to accomplish this? The table name is
tblBid, and the field is bidNotes. It is a varchar(500), and I would
like it to be 900. Is there a size limitation on varchars?
Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Daniel,
Directly this can't be done. Indirectly it can, but not nicely! You could
add a new column with the new datatype (sp_repladdcolumn), do an update on
the table to populate the column, then drop the column (sp_repldropcolumn).
Do this again to create the column having the same original name.
BTW this is available directly using Alter Table in SQL 2005.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Changing data types and lengths in replicated tables

Okay, this is a real bummer. I setup a one way replication from one SQL
server to another. Now, the data types and lengths in some of my tables needs
to change to fit a new data structure but I keep receiving and error that SQL
cannot drop the table because it is replicated. I can make the changes on the
Subscriber with no problem, but the publisher is a no-go!! I thought any
table changes I made would be replicated as well - I did not realize this was
going to be an issue.
Can anyone offer any help - our process is running tonight and I have to get
this working by 1AM.
Thanks,
Nicole Hagler
Nicole, this is just the way it is. The best way IMO, is to RClick the
Publication/ Generate SQL Script(Script the steps to delete)/ Preview/ grab
the
exec sp_dropsubscription @.publication = N'', @.article = N'', @.subscriber =
N'all', @.destination_db = N'all'
exec sp_droparticle @.publication = N'', @.article = N'',
@.force_invalidate_snapshot = 1
GO
that applies to your table. Then once you make your changes you will need to
resnapshot. (Unless you can do this when there will be no data
manipulations.) Do this first in test. 99.99% of the time that I've done
this, it only wanted to resnapshot the table in question. But recenlty it
wanted to resnapshot all of the table in the Publication and I still havent
figured out why. Worst case, create a new Publication and add this table to
that.
CR
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
> needs
> to change to fit a new data structure but I keep receiving and error that
> SQL
> cannot drop the table because it is replicated. I can make the changes on
> the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
> was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
> get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Create a temp table which holds the pk and the columns you wish to change.
use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
it back with the same name, but the new data type. Then update this column
to have the same value as the column in the temp table which corresponds to
your pk.
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
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
needs
> to change to fit a new data structure but I keep receiving and error that
SQL
> cannot drop the table because it is replicated. I can make the changes on
the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Hilary this is an awesome idea and one I will definatley take advantage of
when possible. But Im curious if you've run into problems on updating large
tables in this way due to locking?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ODahu$3pFHA.820@.TK2MSFTNGP09.phx.gbl...
> Create a temp table which holds the pk and the columns you wish to change.
> use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
> it back with the same name, but the new data type. Then update this column
> to have the same value as the column in the temp table which corresponds
> to
> your pk.
> --
> 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
> "Nicole" <Nicole@.discussions.microsoft.com> wrote in message
> news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> needs
> SQL
> the
> was
> get
>

Changing Data Source of Existing Cube

Hi Guys,

We had existing cubes in our Analysis Server, we were required to move them on another Reporting Server which would be using Data Replicated every night to that server. Problem is now source data is divided into 2 Reporting Database Servers. Table Names/View Names are the same in all the Databases. I just want to change the data source pointing to existing Database to the new Reporting Server. Can you tell me how this can be achieved?

Regards,

Kaushal

I have the same question while i also can't find better ways

in fact I have to create cube for another time to change datasource

|||

Looks like RS-related question.

Moving to reporting services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||By Reporting Server, do you mean SSRS? It looks like you just need to change the connection string inside the SSAS database.