creating triggers in transactional replication on the subscriber side

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?

Thanks,

Arslan.

are these triggers defined at the publisher, or were you wanting to create new triggers? Assuming the latter, you can put them in a SQL file and reference @.post_snapshot_script in sp_addpublication.|||

If what you would like to do is to replicate triggers defined on the published table, you can take a look at @.schema_option 0x100 in sp_addarticle. If you are using UI, there is also a "copy user triggers" option in article properties dialog.

Peng

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgs
Use the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgsUse the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

creating replication and not using default database name

We currently have transactional replication from our ERP system to our
datawarehouse. Our ERP system is being upgraded and will have a new database
name. I am going to break and recreate replication to our datawarehouse. I
would like to change the default database name to be the same as our current
(ie: the old version) database name so we don't have to go update all our
ODBC connection strings. Is there any reason why I should not do this? Any
problems this may cause? Also, I was thinking of not even dropping the old
database on the datawarehouse, but just simply replicating the new table to
this old database. Any red flags there? Thanks so much.
I'm confused, I realize your upgrade path requires you to rename your
database name on your ERP system.
How does the ODBC connection string fit in here? Is it pointing at the ERP
system, or the data warehouse? Replication couldn't care less what the name
of the subscriber database name is. So this shouldn't be a problem. You may
have to edit the publication scripts for the new database name.
One trick for you, the SQL Server account or the NT account used in the ODBC
DSN will have a default database. You should be able to change this on the
publisher or subscriber and avoid having to modify the ODBC DSN's.
You should also be able to leave the old database intact with the same name
on the dataware house/subscriber. Replication will fix the tables there the
way it wants them to be - which will likely be ok for you.
Things to watch out for
1) PK's are replicated as unique indexes which are very similar to PKs (only
they allow a single null). When you are creating your publication and get to
the specify articles dialog box, click on the browse button, snapshot tab,
and select include DRI to replicate the PK's as PK's and pray to your God
sysdepends is accurate on the publisher.
2) triggers on the subscriber will be dropped unless they are replicated
from the publisher (in the snapshot tab as well)
3) normally data warehouses require different sets of indexes on the
subscriber; you may have to rebuild these.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> We currently have transactional replication from our ERP system to our
> datawarehouse. Our ERP system is being upgraded and will have a new
database
> name. I am going to break and recreate replication to our datawarehouse.
I
> would like to change the default database name to be the same as our
current
> (ie: the old version) database name so we don't have to go update all our
> ODBC connection strings. Is there any reason why I should not do this?
Any
> problems this may cause? Also, I was thinking of not even dropping the
old
> database on the datawarehouse, but just simply replicating the new table
to
> this old database. Any red flags there? Thanks so much.
|||Sorry for the confusion. I was referring to all of our ASP code that hits
the datawh and has the db name in the ODBC connection. It's just a reason
why I want to keep the old name, it doesn't have anything to do with the
actual replication.
Thanks for all your answers. I don't really get #1 though. I've never done
that when I've started replication before. Is it necessary?
"Hilary Cotter" wrote:

> I'm confused, I realize your upgrade path requires you to rename your
> database name on your ERP system.
> How does the ODBC connection string fit in here? Is it pointing at the ERP
> system, or the data warehouse? Replication couldn't care less what the name
> of the subscriber database name is. So this shouldn't be a problem. You may
> have to edit the publication scripts for the new database name.
> One trick for you, the SQL Server account or the NT account used in the ODBC
> DSN will have a default database. You should be able to change this on the
> publisher or subscriber and avoid having to modify the ODBC DSN's.
> You should also be able to leave the old database intact with the same name
> on the dataware house/subscriber. Replication will fix the tables there the
> way it wants them to be - which will likely be ok for you.
> Things to watch out for
> 1) PK's are replicated as unique indexes which are very similar to PKs (only
> they allow a single null). When you are creating your publication and get to
> the specify articles dialog box, click on the browse button, snapshot tab,
> and select include DRI to replicate the PK's as PK's and pray to your God
> sysdepends is accurate on the publisher.
> 2) triggers on the subscriber will be dropped unless they are replicated
> from the publisher (in the snapshot tab as well)
> 3) normally data warehouses require different sets of indexes on the
> subscriber; you may have to rebuild these.
> --
> 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
> "AW" <AW@.discussions.microsoft.com> wrote in message
> news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> database
> I
> current
> Any
> old
> to
>
>
|||Probably not. Some apps do want to see PK's. Not many of them though, and if
they do they are probably badly written.
I think you should be ok changing the name of your ERP database, but keeping
the old name of your DataWarehouse database.
Next time you build an ODBC connection, don't specify a database name, or
use default database account to handle the database connection for you.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:13877D10-371E-4632-842F-35192B8E4CC5@.microsoft.com...
> Sorry for the confusion. I was referring to all of our ASP code that hits
> the datawh and has the db name in the ODBC connection. It's just a reason
> why I want to keep the old name, it doesn't have anything to do with the
> actual replication.
> Thanks for all your answers. I don't really get #1 though. I've never
done[vbcol=seagreen]
> that when I've started replication before. Is it necessary?
> "Hilary Cotter" wrote:
ERP[vbcol=seagreen]
name[vbcol=seagreen]
may[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
name[vbcol=seagreen]
the[vbcol=seagreen]
(only[vbcol=seagreen]
get to[vbcol=seagreen]
tab,[vbcol=seagreen]
God[vbcol=seagreen]
datawarehouse.[vbcol=seagreen]
our[vbcol=seagreen]
this?[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]

Creating Replication

Hello there
I've started creating my own replication between two databases with the same
structure:
Local server and internet server.
The Internet server is not updated.
When i configure the local server for replication it gave me an error that i
can't use the local server agent.
After that i tried to create publisher. Then it didn't let me to build
transactional publication as a result if licence
what can cause the problem?
any help would be useful
what is the exact error message you are getting?
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've started creating my own replication between two databases with the
> same
> structure:
> Local server and internet server.
> The Internet server is not updated.
> When i configure the local server for replication it gave me an error that
> i
> can't use the local server agent.
> After that i tried to create publisher. Then it didn't let me to build
> transactional publication as a result if licence
> what can cause the problem?
> any help would be useful
>
|||Whell Hilery
The problem is that i ran it from MDSE (as i think)
when i ran it afterword from Enterprize the error didn't came back
But then i stuck with diffrent issue:
After i create the publisher as transactional replication I pressed Push
Subscriber
I filled all the data that was nessesery on the wizart and it didn't gave me
in any level which table i would like to subscribe to or which database
I realy don't understand what's going on
can you help me on it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> what is the exact error message you are getting?
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:%23DM8hcWpEHA.2636@.TK2MSFTNGP09.phx.gbl...
that
>
|||I'm still unclear as to what your error/problem is. Could you enable logging
and paste the logs here?
Please follow the steps outlined here to enable logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:Og61w7WpEHA.3464@.TK2MSFTNGP14.phx.gbl...
> Whell Hilery
> The problem is that i ran it from MDSE (as i think)
> when i ran it afterword from Enterprize the error didn't came back
> But then i stuck with diffrent issue:
> After i create the publisher as transactional replication I pressed Push
> Subscriber
> I filled all the data that was nessesery on the wizart and it didn't gave
me[vbcol=seagreen]
> in any level which table i would like to subscribe to or which database
> I realy don't understand what's going on
> can you help me on it?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uq9PYmWpEHA.1272@.TK2MSFTNGP09.phx.gbl...
the
> that
>

Creating Peer-to-Peer Merge Replication

We have a situation exactly like that shown in the documentation for creating a peer-to-peer merge replication. That is, we have three servers, one each in Chicago, New York, and Bermuda. Users in each office will read and write to their respective servers and the servers will then replicate with one another. The documentation, however, only shows how to set up peer-to-peer for a transactional replication. The problem is that the various Manger Studio options and the database stored procedures are NOT the same between transactional and merge replication and on the face of it, merged replication does not support a peer-to-peer topology. Can somebody walk me through the process of creating a peer-to-peer merge replication, or else convince me that I should go with transaction with updating subscriptions -- in contravention of what the documentation seems to reccommend? I'd really appreciate it!

Thanks.

Randy

before any recommendations, what are your business requirements in terms of latency? regarding a merge replication, is there something else you require that prohibits you from doing a standard publication with two subscriptions?|||

In terms of business requirements the only issue is that three distinct set of users, in three different parts of the world, are updaing their own local servers but that update information needs to be reflected on all servers. The way my boss and I read the documentation, the two options are merge or transaction with updatable subscriptions. Between the two options, the documentation seems to encourage peer-to-peer merge replication.

As for latency, we're probably talking less than 50 transactions per second. Indeed, I suspect we are more in the 10 to 20 range. Since we have big pipes and good servers, I don't expect latency to be much of a problem no matter what we do.

Thanks for your input.

Randy

Creating Linked Server issues

I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.

I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.

Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:

Create Procedure RunSp_AM_subscription(@.servername Varchar(30), @.IAdbname Varchar(20), @.Attachedbname Varchar(20))
AS

DECLARE @.publicationname Varchar(30)
SET @.publicationname = 'REPUBLISH-'+@.IAdbname+''

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

When I call it locally ot works fine, but not otherwise......... :eek:

When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.

Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent...........

Thanks.

'Walealso how would I even call the proc assuming I'm able to create the linked server.

Creating diffrent System Account

Hello there
For starting using replication i've be ordered to change the startup service
Account
I srart do it from the Services window of my WindowsXP
I changed the System account to This Account, and choose the user i'm using
on the same computer who is members only of administrator group
Now i can't run the service due to logon fialure
Need help
Someone tell me what should i do?http://support.microsoft.com/defaul...kb;en-us;283811
"roy goldhammer" <roy@.hotmail.com> wrote in message
news:OlJcEpN$FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hello there
> For starting using replication i've be ordered to change the startup
> service Account
> I srart do it from the Services window of my WindowsXP
> I changed the System account to This Account, and choose the user i'm
> using on the same computer who is members only of administrator group
> Now i can't run the service due to logon fialure
> Need help
> Someone tell me what should i do?
>|||Thankes David
But i'm trying to do the first part of the registry key
How do i do that? with Regedit?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:%23WrWYKO$FHA.2740@.tk2msftngp13.phx.gbl...
> http://support.microsoft.com/defaul...kb;en-us;283811
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:OlJcEpN$FHA.1032@.TK2MSFTNGP09.phx.gbl...
>|||Yes, you use regedit to set registry permissions.
Although, why don't you set it back to using the system account, then use
Enterprise Manager to change the account and then you won't have to worry
about setting permissions manually.
Dan Artuso
"roy goldhammer" <roy@.hotmail.com> wrote in message
news:uORWdQO$FHA.272@.TK2MSFTNGP09.phx.gbl...
> Thankes David
> But i'm trying to do the first part of the registry key
> How do i do that? with Regedit?
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:%23WrWYKO$FHA.2740@.tk2msftngp13.phx.gbl...
>|||Whell dan
When i change the service Startup Account to winxpsp1\roy which is the name
of my compuyer and the logon
and i try to start the service it brings me an error: error 1609 (the
service did not start due to a logon failure)
what i need to do in order to start this service now?
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:uBSN0bO$FHA.216@.TK2MSFTNGP15.phx.gbl...
> Yes, you use regedit to set registry permissions.
> Although, why don't you set it back to using the system account, then use
> Enterprise Manager to change the account and then you won't have to worry
> about setting permissions manually.
>
> Dan Artuso
>
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:uORWdQO$FHA.272@.TK2MSFTNGP09.phx.gbl...
>|||Change it back to 'Local System'
Then you can use EM to change the account and it will make sure the
permissions are correct.
HTH
Dan Artuso, Access MVP
"roy goldhammer" <roy@.hotmail.com> wrote in message
news:e7HhAoO$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Whell dan
> When i change the service Startup Account to winxpsp1\roy which is the
> name of my compuyer and the logon
> and i try to start the service it brings me an error: error 1609 (the
> service did not start due to a logon failure)
> what i need to do in order to start this service now?
>
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:uBSN0bO$FHA.216@.TK2MSFTNGP15.phx.gbl...
>|||Whoops...
Forgot to get rid of the MVP thing in my sig (since I'm no longer an mvp)
:-)
HTH
Dan Artuso
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:ul3QruO$FHA.3992@.TK2MSFTNGP15.phx.gbl...
> Change it back to 'Local System'
> Then you can use EM to change the account and it will make sure the
> permissions are correct.
> --
> HTH
> Dan Artuso, Access MVP
>
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:e7HhAoO$FHA.1676@.TK2MSFTNGP09.phx.gbl...
>|||how exactlely does one unbecome a mvp.....
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:O5mmmyO$FHA.356@.TK2MSFTNGP12.phx.gbl...
> Whoops...
> Forgot to get rid of the MVP thing in my sig (since I'm no longer an mvp)
> :-)
> --
> HTH
> Dan Artuso
>
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:ul3QruO$FHA.3992@.TK2MSFTNGP15.phx.gbl...
>|||The award is based on contributions made during a 12 month period. It is
therefore awarded on a yearly basis. No contributions, no award. That's the
simplified version :-)
Go here for a better explanation:
http://mvp.support.microsoft.com/
HTH
Dan Artuso
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:OIdgk2O$FHA.1032@.TK2MSFTNGP11.phx.gbl...
> how exactlely does one unbecome a mvp.....
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:O5mmmyO$FHA.356@.TK2MSFTNGP12.phx.gbl...
>|||Whell dan
If i had the time to answer for questions like you have i could get the MVP
as well
To the problem:
When i set my username account on EM it doesn't bring me any error, but i
can't still not start the Sql server agent afterword.
The same error: 1069
what i need to do now?
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:ul3QruO$FHA.3992@.TK2MSFTNGP15.phx.gbl...
> Change it back to 'Local System'
> Then you can use EM to change the account and it will make sure the
> permissions are correct.
> --
> HTH
> Dan Artuso, Access MVP
>
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:e7HhAoO$FHA.1676@.TK2MSFTNGP09.phx.gbl...
>

Creating Diffent Account for replication

Hello there
I've tried relication many times before. It seems that it didn't always
worked ,because I still used System account instead of replacing it with
another account.
Now when i'm trying to give it another account, the agent become unuseable.
What i need to do to create another user account that i can use it by sql
server agent?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
set the sql server agent to use a domain account. Have this account in the
local adminsand in the sysadmin role on the publisher . The easiest method
is to use the same account with the same setup on both the
publisher/distributor and the Subscriber. If not, then refer to BOL for more
granular approaches
(http://msdn.microsoft.com/library/de...plsec_8dix.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Replication will never work under the system account - what exactly do you
mean by the agent has become unusable? Is it generating an error message?
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:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've tried relication many times before. It seems that it didn't always
> worked ,because I still used System account instead of replacing it with
> another account.
> Now when i'm trying to give it another account, the agent become
> unuseable.
> What i need to do to create another user account that i can use it by sql
> server agent?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
After i changed the system account to my username as account, i couldn't run
the sql server agent again, until i return it to use the system account
The error i got: 1609 Sql server agent service could not run dut to logon
failure
How can i solve it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Pp01C5$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Replication will never work under the system account - what exactly do you
> mean by the agent has become unusable? Is it generating an error message?
> --
> 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:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
>