Do replication for a non-Administrators

Can I do replication to copy tables in one database which was not create by
me to another database?
possibly, you need to be system administrator or dbo to create publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"M" <mxchen@.hotvoice.com> wrote in message
news:%23Lwd3w38EHA.1188@.tk2msftngp13.phx.gbl...
> Can I do replication to copy tables in one database which was not create
by
> me to another database?
>

Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription Turn off distribution agent Set the publisher to "sync with backup" Backup the publisher, full then log Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on) Turn off "sync with backup" Restore the full and tran log backups to new subscriber db Create subscriber stored procs in subscriber Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

OK, I just did a search and came across this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320499

However this method still requires a brief time in single user mode (ie killing all connections), whereas my method doesn't. I just don't like that my method involves deleting the MSrepl_ tables...

do I need to enable cross database ownership chaining for merge repl?

I have seen conflicting info on this one:
Do I need to enable cross database ownership chaining on databases that
participate in Merge replication (publishers and subscribers)? I have seen
info pointing to maybe yes, maybe no...
anyone have comments on this?
DJC,
I'm not aware of any issues having been reported.
In what context have you heard of problems arising? Maybe from the SP3
readme - there was an issue which has been clarified in the ReadMe
additions:
Clarification to Section 5.3.17 - Change to Requirements for Attaching or
Restoring a Replication Database
Section 5.3.17 "Change to Requirements for Attaching or Restoring a
Replication Database" in the SQL Server 2000 SP3 Readme file incorrectly
implies that enabling cross-database ownership chaining is a requirement for
attaching or restoring replication databases after SQL Server 2000 SP3 has
been applied. The preferred method for attaching or restoring databases
after applying SQL Server 2000 SP3, is to have a member of the sysadmin
fixed server role run the sp_changedbowner stored procedure to assign
ownership of the database to the sa built-in administrator logon. For
example:
Regards,
Paul Ibison
sql

Do I need stop Agents during primary Server power down?

Hi,
Does anyone can tell that our production primary site need perform server
power down, and I have the databases transactional replication with push
setup on primary site? The secondary site will run all the time. Before,
primary site perform the power down, do I need stop the Log Reader and
Distribution Agents? What if I am not stopping those agents?
Regards,
Chen
You will be fine. Transactional replication replicates transactionally. So
if you power off in the middle of something, when it starts up again, it
will pick up where it left off.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:D91896AE-5BF7-47FA-A0D7-D4BFC0CAF422@.microsoft.com...
> Hi,
> Does anyone can tell that our production primary site need perform server
> power down, and I have the databases transactional replication with push
> setup on primary site? The secondary site will run all the time. Before,
> primary site perform the power down, do I need stop the Log Reader and
> Distribution Agents? What if I am not stopping those agents?
> Regards,
> Chen
>
sql

do I need Indexed Views for Replication

sql2k sp3
Howdy all. Ive been trying to avoid using Indexed Views
for Replication in my scenario because Im not totally
faliliar with the whole schemabinding deal. Plus I like
the convenience of sp_repladdcolumn. I was going to use
Custom Sync Objects instead but I think they may not work
for me. As you've all read in my previous posts, my
Subscriber has a different schema than my publisher. But I
just realized its different in ways I didnt see and now I
think Indexed Views are my only way to go.
I currently have a reporting server that at night has dts
packages that truncate the tables and repopulate from
production. But what I just realized is that some of the
destination tables are actually made up from two or more
source tables.
Source tables:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c2 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Example insert from nightly DTS package:
insert into bla(c1, c2, c3)
select t1.c1,t1.c2,t2.c2
from table1 t1
inner join table2 t2 on t1.c1 = t2.c1
So as you see, the destination is made up from two source
tables. I wrote a view for the initial population and the
data bcp'd successfully. But when I tried to insert a row
I had problems because of column c3. It makes sense that I
would need an Indexed View for this scenario because using
Custom Sync Objects the data is still coming from the
table, not a view. And since the Publisher doesnt have
column c3, it would seem a view would be needed. However,
before I flip flop back to and Indexed View, I would like
to see what you pros think.
TIA, ChrisR
is it possible to convert the DTS package to a publication and replicate
from the DTS source server to what is currenly your publisher and also to
your subscriber?
What are your requirements? It looks like data changes at discrete intervals
and then is static until the next package is run. Replication is the
replication of transactions or deltas (merge). If you have no real time
requirements DTS might be the best solution end to end. Either that or
perhaps snapshot replication.
Secondly I take it you are making a mistake in the schema.
I hope you mean this
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Instead of this

> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
As you can see there is no c3. Again we are going to need complete schemas
to address this, and tell us exactly what you are trying to do, and give the
exact requirements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
> sql2k sp3
> Howdy all. Ive been trying to avoid using Indexed Views
> for Replication in my scenario because Im not totally
> faliliar with the whole schemabinding deal. Plus I like
> the convenience of sp_repladdcolumn. I was going to use
> Custom Sync Objects instead but I think they may not work
> for me. As you've all read in my previous posts, my
> Subscriber has a different schema than my publisher. But I
> just realized its different in ways I didnt see and now I
> think Indexed Views are my only way to go.
> I currently have a reporting server that at night has dts
> packages that truncate the tables and repopulate from
> production. But what I just realized is that some of the
> destination tables are actually made up from two or more
> source tables.
> Source tables:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> Example insert from nightly DTS package:
> insert into bla(c1, c2, c3)
> select t1.c1,t1.c2,t2.c2
> from table1 t1
> inner join table2 t2 on t1.c1 = t2.c1
>
> So as you see, the destination is made up from two source
> tables. I wrote a view for the initial population and the
> data bcp'd successfully. But when I tried to insert a row
> I had problems because of column c3. It makes sense that I
> would need an Indexed View for this scenario because using
> Custom Sync Objects the data is still coming from the
> table, not a view. And since the Publisher doesnt have
> column c3, it would seem a view would be needed. However,
> before I flip flop back to and Indexed View, I would like
> to see what you pros think.
>
> TIA, ChrisR
>
>
|||> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run.
Those have been the requirements. Now I need it done continuosly.

> Secondly I take it you are making a mistake in the schema.
Yes. You are correct.
I'll have to post back everything later. Based on what you're seeing, have
you ever had a setup like this without Indexed Views? I dont think it can be
done, but know that you have lots more experience.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
> is it possible to convert the DTS package to a publication and replicate
> from the DTS source server to what is currenly your publisher and also to
> your subscriber?
> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run. Replication is the
> replication of transactions or deltas (merge). If you have no real time
> requirements DTS might be the best solution end to end. Either that or
> perhaps snapshot replication.
> Secondly I take it you are making a mistake in the schema.
> I hope you mean this
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
> Instead of this
>
> As you can see there is no c3. Again we are going to need complete schemas
> to address this, and tell us exactly what you are trying to do, and give
the
> exact requirements.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
>
|||I'm still kind of confused.
It sounds like there are 3 servers, server 1, server 2, and server 3.
Currently a DTS package reads table 1 and table 2 on server 1, and sends
data to server 2 to a table called table 3. Then server 2 publishes table 3
to server 3 to a table called table 4.
All of these tables are different.
Does this describe your situation.
I'd replicate from table 1 & 2 to server 2 table 3, and have a second
publication on server 1 replicating to table 4 on server 3. Replicating two
tables to 1 is a little more complex as you need to figure out which table
will generate a complete row.
It if table 1 contributes column a, b, c, and table 2 contributes column d,
you need the log reader to wirte a command to the distribution database when
there will be a complete set of columns for the row, and there has to be
some relationship between table 1 and table 2. Normally there is an
intersection table or join table and key off this table as a row in the join
table means that there is a row in table 1 and table 2. Then you also have
to replicate table 1 and table 2 as well, so that you have all the columns
for table 3 on the subscriber. The store procedure has to read tables 1 and
tables 2 to build table 3.
I normally use custom sync objects for this sort of stuff. Indexed views are
an option, but there is a space and performance penalty to pay (slight) for
using them, and they aren't resilient to schema changes.
As you have seen custom sync objects aren't problem free either, but they
seem to be an option in your case.
"ChrisR" <chris@.noemail.com> wrote in message
news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
> intervals
> Those have been the requirements. Now I need it done continuosly.
>
> Yes. You are correct.
> I'll have to post back everything later. Based on what you're seeing, have
> you ever had a setup like this without Indexed Views? I dont think it can
be[vbcol=seagreen]
> done, but know that you have lots more experience.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
> intervals
schemas
> the
>
|||No. Two servers. Currently, a DTS Package runs nightly to populate one from
the other. I need to change that and start using replications because it
needs to be done continuosly. As you mentioned earlier:
Source table:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
These tables cannot be changed. I was able to replicate this scenario using
Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
BCP the snapshot with a view that matched the destination table, but the
insert and updates were causing problems because the source is two tables
and the destination is only one.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||Is there a one to one mapping of table 1 to table 2? Which is the parent and
which is the child?
"ChrisR" <chris@.noemail.com> wrote in message
news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> No. Two servers. Currently, a DTS Package runs nightly to populate one
from
> the other. I need to change that and start using replications because it
> needs to be done continuosly. As you mentioned earlier:
> Source table:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> These tables cannot be changed. I was able to replicate this scenario
using[vbcol=seagreen]
> Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
> BCP the snapshot with a view that matched the destination table, but the
> insert and updates were causing problems because the source is two tables
> and the destination is only one.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||For this scenario yes, there is a one to one. Table1 being the parent.
However this is only one of my scenarios. I will also be dealing with some
one to many, and many to many. How does this affect the outcome out of
curiosity. FYI I wont be replicating the DRI. Also. I was just rereading
you're previous reply:

>It if table 1 contributes column a, b, c, and table 2 contributes column d,
>you need the log reader to wirte a command to the distribution database
when
>there will be a complete set of columns for the row, and there has to be
>some relationship between table 1 and table 2. Normally there is an
>intersection table or join table and key off this table as a row in the
join
>table means that there is a row in table 1 and table 2. Then you also have
>to replicate table 1 and table 2 as well, so that you have all the columns
>for table 3 on the subscriber. The store procedure has to read tables 1
and
>tables 2 to build table 3.
If Im reading this correctly, it looks like alot of work in place of Indexed
Views. I mean how hard is it to have the Log Reader write a command to the
Dist db when there's a complete set of columns for a row? Are there schema
change problems with Indexed Views other than not being able to use
sp_repladdcolumn?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:epYGQ6FmEHA.596@.TK2MSFTNGP11.phx.gbl...
> Is there a one to one mapping of table 1 to table 2? Which is the parent
and[vbcol=seagreen]
> which is the child?
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> from
> using
to[vbcol=seagreen]
tables[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
ws[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>
|||I just re-read this once more:
Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber.
If Im reading this correctly, it's one of the things I was afraid of. Are
you saying I need to have table1 and table2 on the Subscriber so I can
create table3? Im pretty sure you are, but wanted to make sure?
As always, thanks so much for all you're help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||yes, you will need table 1 and table 2 to be replicated to the subscriber as
well.
Consider this, the log reader will extract all the columns in a table which
is being replicated and construct an insert/update/or delete statement only
consisting of columns from that table.
So if you replicate table 2 the commands written to the distribution agent
will be the pk and c3. Where will the distribution agent get the value for
c2 from?
It will have to join on table 1 in the subscriber to get this value. If its
not there it will never be able to plug it in.
You need to have the log reader key off table 2 when a complete record for
t3 exists which means that c1, c2 & c3 must be either in the proc or some of
these columns must be already on this table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I just re-read this once more:
> Then you also have
columns[vbcol=seagreen]
> If Im reading this correctly, it's one of the things I was afraid of. Are
> you saying I need to have table1 and table2 on the Subscriber so I can
> create table3? Im pretty sure you are, but wanted to make sure?
> As always, thanks so much for all you're help.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||This is as I expected. Since this is a denormalized reporting server, I
think I will revert back to Indexed Views. To have both Table1 and Table2 on
the Subscriber so I can make up Table3 just doesnt make sense to me. It
almost seems to me as though its adding another layer of complexity. Can you
tell me if there are any schema change problems to watch out for with
Indexed Views besides the fact that I cant use sp_repladdcolumn? Any other
type of big gotchas that you can think of would be appreciated as well.
Also, whens the book coming out?
Thank you once more,
ChrisR
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekB3ztOmEHA.1376@.TK2MSFTNGP12.phx.gbl...
> yes, you will need table 1 and table 2 to be replicated to the subscriber
as
> well.
> Consider this, the log reader will extract all the columns in a table
which
> is being replicated and construct an insert/update/or delete statement
only
> consisting of columns from that table.
> So if you replicate table 2 the commands written to the distribution agent
> will be the pk and c3. Where will the distribution agent get the value for
> c2 from?
> It will have to join on table 1 in the subscriber to get this value. If
its
> not there it will never be able to plug it in.
> You need to have the log reader key off table 2 when a complete record for
> t3 exists which means that c1, c2 & c3 must be either in the proc or some
of[vbcol=seagreen]
> these columns must be already on this table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...
> columns
Are[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
views[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>

Do I have to reinitialize snapshot if a new table added?

Hello,

After creating a publication for Merge Replication, if I need to add or drop a simple table, do I have to reinitialize the snapshot all over again?

Thanks

Ekrem ?nsoy

Hello again,

I'm sorry for taking your time but I've found the answer from the Books Online (http://msdn2.microsoft.com/en-us/library/ms152493.aspx)

Thanks

Ekrem ?nsoy

DMo Example of merge replication

I purchased the SQL DMO book that Mark Allison participated in writing. I
read the chapter on merge replication , however, the chapter only discusses
the setup / config of merge replication. I am interested in the code where a
user would click a button and start a pull subscription. Does anyone have
any examples to share or guidance?
Paul - the first link doesn't work and the second one is a link to info about
the ActiveX object.
I am looking for info specifically on SQL DMO and merge replication.
Any other ideas?
"MSSQLServerDeveloper" wrote:

> I purchased the SQL DMO book that Mark Allison participated in writing. I
> read the chapter on merge replication , however, the chapter only discusses
> the setup / config of merge replication. I am interested in the code where a
> user would click a button and start a pull subscription. Does anyone have
> any examples to share or guidance?
>
|||The first link works - it's just that the text has wrapped in the message. I
don't know of a specifically SQLDMO script which initializes or synchronizes
replication, but the ActiveX object is designed for specifically this use.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||try something like this
Option explicit
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Const SQLDMOMergeSubscriber_Default = 2
dim objServer,objReplication,objReplicationDatabases,o bjReplicationDatabase,
objMergePullSubscription,objMergePullSubscriptions , objReplicationSecurity
set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect
"SubscriberServerName","sa","PaulIbisonIsADoofusAn dHeDancesLikeAGurl"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication.Replication Databases
set objReplicationDatabase=objReplicationDatabases("su b")
set objMergePullSubscription=CreateObject("SQLDMO.Merg ePullSubscription2")
set objReplicationSecurity=objMergePullSubscription.Di stributorSecurity
objReplicationSecurity.SecurityMode=SQLDMOReplSecu rity_Normal
objReplicationSecurity.StandardLogin="sa"
objReplicationSecurity.StandardPassword="PaulIbiso nIsADoofusAndHeDancesLikeA
Gurl"
with objMergePullSubscription
.AltSnapshotFolder="C:\temp\"
.Distributor="PublisherServerName"
.DistributorSecurity.SecurityMode=SQLDMOReplSecuri ty_Normal
.DistributorSecurity.StandardLogin="sa"
..DistributorSecurity.StandardPassword="PaulIbison IsADoofusAndHeDancesLikeAGu
rl"
.Publisher= "PublisherServerName"
.PublicationDB="pubs"
.Publication="test"
.PublisherSecurity.SecurityMode=SQLDMOReplSecurity _Normal
.PublisherSecurity.StandardLogin="sa"
.PublisherSecurity.StandardPassword="se1cure#"
.SubscriberType =SQLDMOMergeSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.SubscriberSecurityMode=SQLDMOReplSecurity_Normal
.SubscriberLogin="sa"
.SubscriberPassword="PaulIbisonIsADoofusAndHeDance sLikeAGurl"
.UseFTP=False
end with
set objMergePullSubscriptions=objReplicationDatabase.M ergePullSubscriptions
objMergePullSubscriptions.Add objMergePullSubscription
set objMergePullSubscription=Nothing
set objMergePullSubscriptions=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:AAE00CF7-C4FD-48D2-A815-FF60B824D249@.microsoft.com...
> I purchased the SQL DMO book that Mark Allison participated in writing. I
> read the chapter on merge replication , however, the chapter only
discusses
> the setup / config of merge replication. I am interested in the code
where a
> user would click a button and start a pull subscription. Does anyone have
> any examples to share or guidance?
>
|||Hilary - Thanks very much - I'll give it a try. Not til next week, but I
will keep you posted. Regards
"Hilary Cotter" wrote:

> try something like this
> Option explicit
> Const SQLDMOSubscription_Anonymous = 2
> Const SQLDMOReplSecurity_Normal = 0
> Const SQLDMOReplSecurity_Integrated = 1
> Const SQLDMOSubscription_All = 3
> Const SQLDMOMergeSubscriber_Default = 2
> dim objServer,objReplication,objReplicationDatabases,o bjReplicationDatabase,
> objMergePullSubscription,objMergePullSubscriptions , objReplicationSecurity
> set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect
> "SubscriberServerName","sa","PaulIbisonIsADoofusAn dHeDancesLikeAGurl"
> set objReplication=objServer.Replication
> set objReplicationDatabases=objReplication.Replication Databases
> set objReplicationDatabase=objReplicationDatabases("su b")
> set objMergePullSubscription=CreateObject("SQLDMO.Merg ePullSubscription2")
> set objReplicationSecurity=objMergePullSubscription.Di stributorSecurity
> objReplicationSecurity.SecurityMode=SQLDMOReplSecu rity_Normal
> objReplicationSecurity.StandardLogin="sa"
> objReplicationSecurity.StandardPassword="PaulIbiso nIsADoofusAndHeDancesLikeA
> Gurl"
> with objMergePullSubscription
> .AltSnapshotFolder="C:\temp\"
> .Distributor="PublisherServerName"
> .DistributorSecurity.SecurityMode=SQLDMOReplSecuri ty_Normal
> .DistributorSecurity.StandardLogin="sa"
> ..DistributorSecurity.StandardPassword="PaulIbison IsADoofusAndHeDancesLikeAGu
> rl"
> .Publisher= "PublisherServerName"
> .PublicationDB="pubs"
> .Publication="test"
> .PublisherSecurity.SecurityMode=SQLDMOReplSecurity _Normal
> .PublisherSecurity.StandardLogin="sa"
> .PublisherSecurity.StandardPassword="se1cure#"
> .SubscriberType =SQLDMOMergeSubscriber_Default
> .SubscriptionType = SQLDMOSubscription_Anonymous
> .SubscriberSecurityMode=SQLDMOReplSecurity_Normal
> .SubscriberLogin="sa"
> .SubscriberPassword="PaulIbisonIsADoofusAndHeDance sLikeAGurl"
> .UseFTP=False
> end with
> set objMergePullSubscriptions=objReplicationDatabase.M ergePullSubscriptions
> objMergePullSubscriptions.Add objMergePullSubscription
> set objMergePullSubscription=Nothing
> set objMergePullSubscriptions=Nothing
> set objReplicationDatabases=Nothing
> set objReplication=Nothing
> set objServer=Nothing
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
> wrote in message news:AAE00CF7-C4FD-48D2-A815-FF60B824D249@.microsoft.com...
> discusses
> where a
>
>
|||btw - I like the password.
"Hilary Cotter" wrote:

> try something like this
> Option explicit
> Const SQLDMOSubscription_Anonymous = 2
> Const SQLDMOReplSecurity_Normal = 0
> Const SQLDMOReplSecurity_Integrated = 1
> Const SQLDMOSubscription_All = 3
> Const SQLDMOMergeSubscriber_Default = 2
> dim objServer,objReplication,objReplicationDatabases,o bjReplicationDatabase,
> objMergePullSubscription,objMergePullSubscriptions , objReplicationSecurity
> set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect
> "SubscriberServerName","sa","PaulIbisonIsADoofusAn dHeDancesLikeAGurl"
> set objReplication=objServer.Replication
> set objReplicationDatabases=objReplication.Replication Databases
> set objReplicationDatabase=objReplicationDatabases("su b")
> set objMergePullSubscription=CreateObject("SQLDMO.Merg ePullSubscription2")
> set objReplicationSecurity=objMergePullSubscription.Di stributorSecurity
> objReplicationSecurity.SecurityMode=SQLDMOReplSecu rity_Normal
> objReplicationSecurity.StandardLogin="sa"
> objReplicationSecurity.StandardPassword="PaulIbiso nIsADoofusAndHeDancesLikeA
> Gurl"
> with objMergePullSubscription
> .AltSnapshotFolder="C:\temp\"
> .Distributor="PublisherServerName"
> .DistributorSecurity.SecurityMode=SQLDMOReplSecuri ty_Normal
> .DistributorSecurity.StandardLogin="sa"
> ..DistributorSecurity.StandardPassword="PaulIbison IsADoofusAndHeDancesLikeAGu
> rl"
> .Publisher= "PublisherServerName"
> .PublicationDB="pubs"
> .Publication="test"
> .PublisherSecurity.SecurityMode=SQLDMOReplSecurity _Normal
> .PublisherSecurity.StandardLogin="sa"
> .PublisherSecurity.StandardPassword="se1cure#"
> .SubscriberType =SQLDMOMergeSubscriber_Default
> .SubscriptionType = SQLDMOSubscription_Anonymous
> .SubscriberSecurityMode=SQLDMOReplSecurity_Normal
> .SubscriberLogin="sa"
> .SubscriberPassword="PaulIbisonIsADoofusAndHeDance sLikeAGurl"
> .UseFTP=False
> end with
> set objMergePullSubscriptions=objReplicationDatabase.M ergePullSubscriptions
> objMergePullSubscriptions.Add objMergePullSubscription
> set objMergePullSubscription=Nothing
> set objMergePullSubscriptions=Nothing
> set objReplicationDatabases=Nothing
> set objReplication=Nothing
> set objServer=Nothing
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
> wrote in message news:AAE00CF7-C4FD-48D2-A815-FF60B824D249@.microsoft.com...
> discusses
> where a
>
>
|||Hilary,
what makes me think you're getting that Friday feeling?
Paul
|||was it the virus I emailed you? The postings I have being doing in your name
in alt.bedwetting? The huge amount of spam I have been sending out in your
name?
Please help me figure this one out?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:071101c4ffe3$937a9580$a501280a@.phx.gbl...
> Hilary,
> what makes me think you're getting that Friday feeling?
> Paul
|||Ah,
so you're the reason I keep getting orders for caseloads
of Viagra and penile extension operations. Might have
known. Anyway, must dash - it's 7:15 on Friday evening,
there's nobody else here and I'm the mug who'se doing the
database rollouts.
Paul
|||um...that wasn't me - must have been your wife!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:077b01c4ffed$3e18a0f0$a501280a@.phx.gbl...
> Ah,
> so you're the reason I keep getting orders for caseloads
> of Viagra and penile extension operations. Might have
> known. Anyway, must dash - it's 7:15 on Friday evening,
> there's nobody else here and I'm the mug who'se doing the
> database rollouts.
> Paul
>

Distriubtion Error on Stored Procedure

Setting up Transactional Replication.

Log Reader Agent successfull.

Snapshot Agent successfull.

Distribution Agent Error (Replication is failing because of this error. I don't know what is wrong with the stored procedure that causes it not to replicate):

Date 1/19/2007 10:28:13 AM
Log Job History (IS36-MMS_20061213-MMSRepTest-IS4-30)

Step ID 2
Server IS4
Job Name IS36-MMS_20061213-MMSRepTest-IS4-30
Step Name Run agent.
Duration 00:00:11
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2007-01-19 16:28:24.700 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE PROCEDURE "dbo"."spBTG_GetEventsSince"(@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from
2007-01-19 16:28:24.700 Category:NULL
Source: Microsoft SQL Native Client
Number: 1018
Message: Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Here is the entire source of the sp:

GO

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Linda, you can either change the stored procedure definition in the script generated by the snapshot agent to the following:

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh with (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh with (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Or, you can change the compatibility level of your subscriber database to 80 or lower.

-Raymond

|||

Raymond,

This sp currently runs on SQL2000. However, before we implement replication, it will be running on 2005. Is the change "adding with" something that will be required for SQL 2005 or does it have to do with replication?

Linda

|||

The use of the "with" keyword with query hints is a SQL2005 requirement that is not directly related to replication. As I mentioned before, you can always change the dbcmptlevel of your subscriber database to 80 or lower so the old syntax in your procedure can be accepted.

-Raymond

|||

Raymond,

On the subscriber database, do I run this command:

EXEC sp_dbcmptlevel MMS_20061213, 80;

to change the cmptlevel?

I did that and now I am getting this error:

Command attempted:

/* ============================================================ */
/* View: vMcsHeaders */
/* ============================================================ */

CREATE VIEW "dbo"."vMcsHeaders" AS

/* VIEW: vMcsHeaders
ABSTRACT: Used by the Contract System to work with contract headers
AUTHOR DATE
EJB 3/31/98 Created
SRM 4/8/98 - Added Customer_Code
EJB 5/1/98 - added filter for only contracts that are not deleteme = 1
EJB 6/10/98 - Ad
(Transaction sequence number: 0x00046CEA0000F21D005600000000, Command ID: 3385)

Error messages:

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

I notice the McsDemurrageSetUp is it referencing is spelled McsDemurrageSetup. How do I track down what kind of error this is? (Perhaps, I did not get the compatibility set correctly to 80?)

|||

It would appear that you are replicating from a case-insensitive database to a case-sensitive database, this is not a supported scenario.

|||

Raymond,

If I run the command: sp_server_info, both databases say "MIXED" under identifier case. However, I did figure out the problem. The storedprocedure is referencing a table that was not replicated because it did not have a primary key. I did not notice that initially when I noticed the case was different. The database I am working with needs a lot of work before it can successfully be replicated!

Thanks,

Linda

distributor_admin problem when configuring replication 18483

I have read many of the postings on the 18483 error, but I must be
missing something because I can't get it to work and I have been trying
for days. I have also read about this topic in BOL which says the same
thing as the postings.
Local replication has been working for almost a year, but I ran into
problems when I wanted to add a remote site to the process. I
therefore decided to disable replication and reenable it to both sites.
I decided to get it working properly to the local server and felt that
I could then setup it up for the remote.
The problem that I am having is the old 'Error 18483
'distributor_admin' is not setup for remote access. I don't understand
why I am receiving this error since I just disabled replication and
immediately tried to reenable it. (NOTE: I have changed the alias
names for easier recognition since the original setup, although I still
point to the ip addesses of the boxes both local and remote.) I have
since read a posting which suggest to alias to the computer name which
I have done.
BOL says that I need to add 'distributor_admin' as a remote login even
though the publisher and the distributor are the same box. I have run
the 'sp_addremotelogin' stored procedure with several permutations i.e.
Local and Remote user 'distributor_admin', Local user
'distributor_admin' Remote user an admin account on the server and vice
versa. If I use 'distributor_admin' as both the Local and Remote user
I still received the 18483 error. When I used 'distributor_admin' as
Local and an admin account for remote, I receive an error that I am
using the wrong password. I know the password of the admin account
because it is mine. I have also set the 'distributor_admin' account
password to match mine via the 'sp_changedistributor_password' stored
procedure. The same happens if 'distributor_admin' is remote and an
admin account is local.
Does anyone have an idea of what I am missing?
Are you running SQL 2000? If so, I think you have misread BOL. BOL states
"The connection between a Publisher and a remote Distributor is a hybrid of
a linked server and remote server. The connection uses the login
distributor_admin. At the remote Distributor, the Publisher can be
configured to be either trusted (no password is required for the
distributor_admin login) or non-trusted (a password is required). It is
recommended that you use a non-trusted connection for the Publisher
connection to the Distributor, requiring a distributor_admin password."
They are referring to the Administrative Link password here - not the
distributor_admin account.
If I were you I would disable replication, if the distributor_admin account
remains, delete it, if repl_distributor remails as a remote server, delete
it.
Then try to enable replication again.
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
<pw@.indii.com> wrote in message
news:1108397908.962930.70700@.g14g2000cwa.googlegro ups.com...
> I have read many of the postings on the 18483 error, but I must be
> missing something because I can't get it to work and I have been trying
> for days. I have also read about this topic in BOL which says the same
> thing as the postings.
> Local replication has been working for almost a year, but I ran into
> problems when I wanted to add a remote site to the process. I
> therefore decided to disable replication and reenable it to both sites.
> I decided to get it working properly to the local server and felt that
> I could then setup it up for the remote.
> The problem that I am having is the old 'Error 18483
> 'distributor_admin' is not setup for remote access. I don't understand
> why I am receiving this error since I just disabled replication and
> immediately tried to reenable it. (NOTE: I have changed the alias
> names for easier recognition since the original setup, although I still
> point to the ip addesses of the boxes both local and remote.) I have
> since read a posting which suggest to alias to the computer name which
> I have done.
> BOL says that I need to add 'distributor_admin' as a remote login even
> though the publisher and the distributor are the same box. I have run
> the 'sp_addremotelogin' stored procedure with several permutations i.e.
> Local and Remote user 'distributor_admin', Local user
> 'distributor_admin' Remote user an admin account on the server and vice
> versa. If I use 'distributor_admin' as both the Local and Remote user
> I still received the 18483 error. When I used 'distributor_admin' as
> Local and an admin account for remote, I receive an error that I am
> using the wrong password. I know the password of the admin account
> because it is mine. I have also set the 'distributor_admin' account
> password to match mine via the 'sp_changedistributor_password' stored
> procedure. The same happens if 'distributor_admin' is remote and an
> admin account is local.
> Does anyone have an idea of what I am missing?
>
|||Hilary,
Thanks for your response it was very helpful. Also I did read the BOL
about linked servers, but with my limited knowledge I didn't understand
it the way that a more experienced person might. I was going to delete
the linked server and recreate it, but I didn't know what the affect
would be on my production environment.
I followed your advice and deleted the distributor_admin login and the
repl_distributor remote server. I also ran sp_removedbreplication and
then tried to reestablish it. I again got the 18483 remote login
error. DO I NEED TO RESTART THE SERVICE IN ORDER FOR MY CHANGES TO
FULLY TAKE AFFECT?
Thanks,
Peter
|||I re-read BOL, and it is confusing. Now I am not sure if they were referring
to the administrative link password after all. I am going to try to research
this.
I would try the restart - but I am not sure if this is going to fix it.
Someone in the past had similar problems to you and he/she deleted this
account, and then recreated it as a standard SQL Server login, assigned it a
password and then put it in the System Administrator group. It worked for
them. I tried this myself and it also worked.
You might want to try this as a last resort. I would advise you to call PSS
for their help on this one.
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
<pw@.indii.com> wrote in message
news:1108477637.970068.101100@.o13g2000cwo.googlegr oups.com...
> Hilary,
> Thanks for your response it was very helpful. Also I did read the BOL
> about linked servers, but with my limited knowledge I didn't understand
> it the way that a more experienced person might. I was going to delete
> the linked server and recreate it, but I didn't know what the affect
> would be on my production environment.
> I followed your advice and deleted the distributor_admin login and the
> repl_distributor remote server. I also ran sp_removedbreplication and
> then tried to reestablish it. I again got the 18483 remote login
> error. DO I NEED TO RESTART THE SERVICE IN ORDER FOR MY CHANGES TO
> FULLY TAKE AFFECT?
> Thanks,
> Peter
>
|||Hilary,
I appreciate your comments about BOL. With my limited experience I
felt that I was chasing my tail.
Also I did restart the SQL Server service which cleared up some
problems, but in the end I received the imfamous 'Error 18483'. I will
try your suggestion and manually delete and recreate the
'distributor_admin' login and add it to the SA group.
I will let you know if this works.
Peter
|||Peter,
Yo tena el mismo problema...
Solucin:
http://support.microsoft.com/default...b;en-us;818334
y exec sp_helpserver
Recuerdos
<pw@.indii.com> escribi en el mensaje
news:1108560299.305220.159700@.l41g2000cwc.googlegr oups.com...
> Hilary,
> I appreciate your comments about BOL. With my limited experience I
> felt that I was chasing my tail.
> Also I did restart the SQL Server service which cleared up some
> problems, but in the end I received the imfamous 'Error 18483'. I will
> try your suggestion and manually delete and recreate the
> 'distributor_admin' login and add it to the SA group.
> I will let you know if this works.
> Peter
>
|||Hilary,
I am sorry that I missed you at the last NYC SQL Server User Group
meeting. I just became a member.
On another note, I would like to tell you the outcome of my problem. I
was having problems with my replication setup and decided to redue it.
Lucky for me that I made a copy of the original replication script.
All of the attempts described in this posting didn't work for me, but I
learned a lot about Transact-SQL and using stored procedures. As a
last resort, I edit the oringinal script and managed to get replication
setup again. It took me a few iterations, but it worked.
I don't know why I could setup replication via Enterprise Manager, but
I wanted to let you know this because you assist many people.
Thanks.

distributor_admin

Runing SQL 2k, Win 2k
We had to rebuild a server and when I attepmt to configure replication I
receive the error...
Could not connect to server 'newSERVERNAME' because 'distributor_admin' is
not defined as a remote login at the server.
When I look at the Logins, 'distributor_admin' login is not there. The only
logins there are BULITIN\Administrators & domainname\Administrator.
So the question is, how do I create this login or all the system created
logins? Is there an SP to create them from another server?
TIA,
Larry...
Your best bet is to remove replication and reinstall it.
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C40E4D07-7D98-4CFF-8160-26B072C6E3F2@.microsoft.com...
> Runing SQL 2k, Win 2k
> We had to rebuild a server and when I attepmt to configure replication I
> receive the error...
> Could not connect to server 'newSERVERNAME' because 'distributor_admin' is
> not defined as a remote login at the server.
> When I look at the Logins, 'distributor_admin' login is not there. The
> only
> logins there are BULITIN\Administrators & domainname\Administrator.
> So the question is, how do I create this login or all the system created
> logins? Is there an SP to create them from another server?
>
> TIA,
> Larry...
>

Distributor server

I have a merge replication environment with 1 publisher/distributor in the
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>

Distributor password in distributor Properties

I am using SQL2k with sp4 using push transactional replication.
When I select 'configure publishing, subscribers and Distribution' from the
SQL EM's tool menu\replication, under the distributor tab, there is the
administrator link password, what do they use for password? When I set up
replication for the first time, it doesn't ask me for it so it is unknown to
me. If I change the password, will it affect anything? I also noticed that
during replication setup, it also creates a SQL login for Distribution_Admin
and I also don't know what password it use. Any ideas for these two
passwords? Just curious, why do they set up the password behind the scene?
wingman
Wing
If you didn't set it (remote distributor) this means you have a local
distributor, and it is randomly generated. If you want to change it, you can
use sp_changedistributor_password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I didn't set it. I assume local distributor means it locates in the
same server as the publisher server.
The reason I ask for the password is that I tried to use the 'generating SQL
script' feature in replication to script the setup. But the script purposely
leaves out the passwrod for security reason so in order for me to run the
script, I need to know the password. So should I run the
sp_changedistributor_password and put the new password in the script to make
it work?
Or is there another way to script a replication setup?
Wing
"Paul Ibison" wrote:

> If you didn't set it (remote distributor) this means you have a local
> distributor, and it is randomly generated. If you want to change it, you can
> use sp_changedistributor_password.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The 'old' password doesn't really need to be retained, and sp_adddistributor
can be fed any value you want for the administrative link password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distributor on another server?

Can the distributor be on another server that does not have SQL Server on
it. Using SQL 2k merge replication. Thanks.
David
No, it must have a version of SQL Server on it.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:eofDI%232IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Can the distributor be on another server that does not have SQL Server on
> it. Using SQL 2k merge replication. Thanks.
> David
>

Distributor Lock Timeouts

I have transactional replication set up and running with a remote
distributor. Replication latency continues to be good (3 - 10 seconds) but
after we added several indexed views to the subscriber, we are seeing lock
timeouts on the distribution database. I haven't been able to tie the
timeouts to any specific activity or activity levels. When I do see the
timeouts I also see a spike in lock requests, but not lock escallations.
Through it all, replication latency seems to stay about the same.
Finally, whenever I fire up Replication Monitor the lock timeouts increase
quite a bit, but they still occur when replication monitor isn't running.
Any thoughts or ideas?
Replication applies the sync commands in a batch and holds a lock while
applying that batch. The locking you are seeing is probably a result of
this. If you are using SQL Server 2005, try using the snapshot isolation
model.
http://www.zetainteractive.com - Shift Happens!
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
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:O76HbG3OIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I have transactional replication set up and running with a remote
>distributor. Replication latency continues to be good (3 - 10 seconds) but
>after we added several indexed views to the subscriber, we are seeing lock
>timeouts on the distribution database. I haven't been able to tie the
>timeouts to any specific activity or activity levels. When I do see the
>timeouts I also see a spike in lock requests, but not lock escallations.
>Through it all, replication latency seems to stay about the same.
> Finally, whenever I fire up Replication Monitor the lock timeouts increase
> quite a bit, but they still occur when replication monitor isn't running.
> Any thoughts or ideas?
>

Distributor cannot connect to subscriber

I am setting up my 2005 Replication system...

publisher = 2005 sp1

Subscriber = 2005 sp1

I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.

Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.

I am not sure where to look at next... I never had this issue in 2000.

Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.

I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.

Could it be that my pub/dist is using the wrong client files?

|||

Hi William,

Are you using merge or transactional replication?

Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.

In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?

Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.

This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx

Hope this helps,

Tom

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

|||

Hey Tom,

I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.

The subscription is a push subscription. I will take a look at the link you sent.

=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.

Is there anything I can do to ensure it is working?

|||

update

Since my subscriber is a dev server and if I mess it up it does not matter I did the following...

I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.

No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...

Any idea? or is this a Bug?

|||

Can you try the following connectivity test from command prompt?

at your dev machine, try to connect to the other box

osql -S<publisher_server> -U<user id> -P<password>

and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.

Gary

|||

Ok... I did as suggested and the publisher has no problem connecting to the subscriber...

So what does that mean?

I can connect via command line,SMS but not replication.

|||

Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....

So when I execute from 80/tools/binn I can connect without issue.

From 90/tools/binn I get this error

[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.

|||

Can you check the protocal interface are enabled for TCP and name pipe?

You can find the instruction on the following posting from Mahesh,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1

Gary

|||

I double checked and I already have that set. I cycled the server anyway... Still no luck.

What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...

Is there anyway to check what libraries each client is using?

|||

What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?

Thanks.

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

|||

OS is Windows 2003 SP1...

I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...

I

|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||

That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...

So I am asking my boss for other options...

Thanks for all the help.

Distributor (Server) Name Changed... No Replication Monitor what to do ?

Hi there,
We have changed the name of our SQL Server. Now the Replication Monitor is not appearing on the Enterprise Manager.
I tried to change the name of Distributor but there is no option. We have plenty of publications which are still showing old SQL Server instance name.
How we can change the name of Publisher without deleting all the publications ?
Any Help will be appreciated.
Abid Malik
the simplest way to fix this is to remove replication using the Tools -
Replication - Disable Publishing menu options and then to reinstall it.
"Abid Malik" <abidmunirmalik@.hotmail.com> wrote in message
news:69D97665-3983-4400-BE1A-028D9A1D5EAF@.microsoft.com...
> Hi there,
> We have changed the name of our SQL Server. Now the Replication Monitor is
not appearing on the Enterprise Manager.
> I tried to change the name of Distributor but there is no option. We have
plenty of publications which are still showing old SQL Server instance name.
> How we can change the name of Publisher without deleting all the
publications ?
> Any Help will be appreciated.
> Abid Malik

Distributor

Hi,
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,

>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison

Distribution times out...

I have a transactional replication working fine till to date. I have
the publisher on ServerA and subscriber on ServerB. The transactional
replication is running fine...
I need to get a copy of the subscriber to another server and replicate
the Publisher to that serverC while the replication on ServerB is
running.
ServerA.Publisher --> ServerB.Subscriber (runs transactional
replication)
ServerA.Publisher --> ServerC.NewSuscriber (This need to run without
impacting ServerB.Subscription). I cannot resynch the databases as It
will take days to resynch and will also kill some of my data on the
ServerB.subscriber that is not in ServerA.publisher
I went with the way they had explained on MSKB 320499. I stoped the
transactions coming into the publisher and copied a full backup of
publisher to ServerC. I created the new subscription wihout re-synch
option to ServerC. I also copied all the sp's that are needed from
ServerB.Subscriber.
When i try to enable the distribution agent between ServerA and ServerC
i get the following error..
{call sp_MSget_repl_commands(11, ?, 0, 7500000)}
Timeout expired
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
------
The replication agent from ServerA to ServerB is running fine..! (Thank
God..!)
Can anyone help me figure out how to fix this issue between
ServerA.Publisher to ServerC.Subscriber?
Thanks in advance...!
Please can you confirm that you have the latest service pack on each server
(there were issues with "sp_MSget_repl_commands" before).
If this is not a service pack issue, try increasing the -QueryTimeOut
parameter of the distribution agent and also enable logging:
http://support.microsoft.com/?id=312292 to trap any more details for us.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul,
I have SP4 Installed on both servers. SQL 2000 Standard Edition on a
Windows 2003 Enterprise server.
Paul Ibison wrote:
> Please can you confirm that you have the latest service pack on each server
> (there were issues with "sp_MSget_repl_commands" before).
> If this is not a service pack issue, try increasing the -QueryTimeOut
> parameter of the distribution agent and also enable logging:
> http://support.microsoft.com/?id=312292 to trap any more details for us.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK - it's not that then
In that case try increasing the QueryTimeout parameter and do some logging
if there are any issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary

Distribution table cleanup

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

You can reduce the retention period using sp_changedistributiondb.

For more info, please refer to the following articles.

http://msdn2.microsoft.com/fr-fr/library/ms146868.aspx

http://msdn2.microsoft.com/fr-fr/library/ms181877.aspx

Regards,

Gary