push to subscription in non-trusted domain (Sql 2000)

Thanks for your response.
I don't know how to do pass-through authentication across domain
(particularly using SQL2k replication), I thought that would require a
trusted account on both sides.
I have been trying using the sql account and although I can connect to the
subscriber using the account from query tools and em, I keep receiving an
error when my distribution agent tries to connect: "The process could not
connect to Subscriber 'subscriptionservername'"
I've tried it before and after manipulating the Replication Distributor
authentication arguments and the Subscriber is already setup on the
Distributor to use sql authentication (I've reset this login and pwd at
least once for each trial, I'm reasonable sure it is not mistyped). Same
errant response always.
I even once tried populating the data 1st (in case the snapshot folder was
the only issue) but I did not try to populate the MS* subscription tables
which I figured would blow up anyway, but it couldn't connect so I got the
same response.
Got any ideas? Am I missing something?
Please ... Help!
Sorry, this should have been a reply to a different post.
"William Millett" <william.millett@.altivity.com> wrote in message
news:OHhOC68aHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response.
> I don't know how to do pass-through authentication across domain
> (particularly using SQL2k replication), I thought that would require a
> trusted account on both sides.
> I have been trying using the sql account and although I can connect to the
> subscriber using the account from query tools and em, I keep receiving an
> error when my distribution agent tries to connect: "The process could not
> connect to Subscriber 'subscriptionservername'"
> I've tried it before and after manipulating the Replication Distributor
> authentication arguments and the Subscriber is already setup on the
> Distributor to use sql authentication (I've reset this login and pwd at
> least once for each trial, I'm reasonable sure it is not mistyped). Same
> errant response always.
> I even once tried populating the data 1st (in case the snapshot folder
> was the only issue) but I did not try to populate the MS* subscription
> tables which I figured would blow up anyway, but it couldn't connect so I
> got the same response.
> Got any ideas? Am I missing something?
> Please ... Help!
>

Pulling data across multiple tables.

I am attempting to pull all users over the age of 30 from a table. I want the results to be returned off of their membership status and recent activity. Their membership status is stored in a seperate table (Payment). Some users have more than one membership status record, while others might not have any at all.

LEFT JOIN seems like a possible solution, however, if a user has more than one membership status record, it is returning multiple results for that user (when I only want 1 result for that user).

Here is the code that I have so far.

SELECT Users.UserID FROM Users LEFT JOIN Payment ON Users.UserID = Payment.UserID AND Users.Age > 30 AND Payment.PaymentExpirationDate > '3/28/2004' ORDER BY Payment.MembershipNumber DESC, Users.LastActive DESC

Payment.MembershipNumber - INT Field. This is the membership status for the user, if the user has any membership records.
Users.LastActive - Date/Time Field. This is the recent active for the user.

The data is being returned right now is:
UserID
=====
3
8
12
12
12
9
9
1
... and so on, when it should look like ...
UserID
=====
3
8
12
9
1

I hope this makes sense. Thanks for your time.

Jamesif a user has more than one membership status record, which one do you want?|||I need the first membership status record that has a PaymentExpirationDate closest to '3/28/2004'.|||select U.UserID
, U.lastActive
, P.PaymentExpirationDate
from Users U
left
join Payment P
on U.UserID
= P.UserID
and P.PaymentExpirationDate
> '3/28/2004'
where U.Age > 30
and P.PaymentExpirationDate
= ( select min(PaymentExpirationDate)
from Payment
where UserID = P.UserID
and PaymentExpirationDate
> '3/28/2004' )
order
by U.LastActive desc

Pull subscription and ISA server

Hi
Can anyone point me in the right direction, I am trying to do an anonymous
pull subscription across the internet. I have created a publisher and created
the snapshot ok. On the subscriber server I can FTP by IP ( the ISA servers
external IP address ) or by the servers name and can see the folders created
in the repldata\ftp directory. I can create a new SQL server registration on
the subscription server and get a connection into the publisher, I can run
sql queries against the publisher ok.
When I try and create a pull subscription, I get to the last page on the
wizard and get
SQL Server enterprise Edition could not create a pull subscription to
publication 'MyDB'
Error 21776: [SQL-DMO] The name 'MyDB' was not found in the TransPublications
collection
Any help greatly appreciated
Having reinstalled and rebooted several times yesterday I was getting nowhere
hence my post in this NG
Come back to it today and the pull subscription worked first time without
any errors on yesterdays configuration. Wierd ?
Anyway, if any one can shed any light on what was going on when it was
failing, please let me know.
thanks

Pull or Push replication

As I am planning to implement replication at one of our client. Since I need
to replicate only once per day from each server located across the globe. I
figured out that Snapshot replication is best.
I would like to know what kind of replication is feasible for this scenario.
I need to replicate from multiple servers across different parts of the
world to one central server. The central server is the subscriber and the
remaining are different publishers, would it be better to have Pull
subscription or push subscription?
In pull subscription I have an advantage and disadvantage. The disadvantage
is that in the pull subscription, the subscriber requires a shared folder
from the publisher to fetch the data from the distributor (publisher &
distributor in one PC), I was not successful in fetching the data from the
shared folder in one of our test environment. Advantage is that pull
subscription can be controlled (Time flexibility to pull the data) at one
central place i.e from the central subscriber.
I was successful in push subscription in my test environment. Advantage,
subscriber doesnt require a shared folder from the publisher(distributor).
disadvantage, No central control of the replication.
Plz suggest
Plz suggest
Hello Affy,
I would suggest central subscriber Vs multiple publisher model. As your
requirement is limited to once in a day Snapshot would be fine depending on
the size of the database. Now I suggest Push subscription as you do have
multiple site and in Push replication SQL Server use resource of Publisher
and not subscriber. Hence you will have less burden on the central
subscriber. In Pull case central server has to pull the data from all the
subscriber and there might be some performance issue else you need to
consider the replication load and hardware of the central subscriber must be
sufficient to take the load.
Still you need to evaluate your requirement and study various replication
topology to set up replication.
Cheers,
MB
"Affy" <Affy@.discussions.microsoft.com> wrote in message
news:99A896C8-80C9-4E8D-8B6B-5C9B390FA11E@.microsoft.com...
> As I am planning to implement replication at one of our client. Since I
> need
> to replicate only once per day from each server located across the globe.
> I
> figured out that Snapshot replication is best.
> I would like to know what kind of replication is feasible for this
> scenario.
> I need to replicate from multiple servers across different parts of the
> world to one central server. The central server is the subscriber and the
> remaining are different publishers, would it be better to have Pull
> subscription or push subscription?
> In pull subscription I have an advantage and disadvantage. The
> disadvantage
> is that in the pull subscription, the subscriber requires a shared folder
> from the publisher to fetch the data from the distributor (publisher &
> distributor in one PC), I was not successful in fetching the data from the
> shared folder in one of our test environment. Advantage is that pull
> subscription can be controlled (Time flexibility to pull the data) at one
> central place i.e from the central subscriber.
> I was successful in push subscription in my test environment. Advantage,
> subscriber doesnt require a shared folder from the publisher(distributor).
> disadvantage, No central control of the replication.
> Plz suggest
> Plz suggest
|||Are you replicating to the same table? If so, snapshot won't work and you'll
need transactional. Transactional with multiple publishers and one central
subscribers is ok if the data is partitioned - is that the case? If so then
this article might help:
http://www.replicationanswers.com/CentralSubscriberArticle.asp
As for push or pull, I'd consider where you want to do the work and where
you want to do the maintenance, which are the competing factors.
BTW there is another topology that could be used in this case - have the
reporting server set up as a merge publisher and the other computers set up
as merge subscribers. If the data load is light, the performance problems of
using this method are outweighed by the fact that administration is more
simplified.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply MB. I happen to study different topolgies but central
subscriber is the closer one where as republisher, central publisher,CP with
remote distributor. Its multiple publishers to a single subscriber with
publishers located in geographically different locations like eg
Shanghai,Moscow,Malaysia,Australia. The data is just a table that is being
sent to the subscriber on a daily basis. Maximum records per country could
come around 6000 records (but they are proned to increase but slowly). but
using Push will have all the configuration available at the publisher, so the
subscriber has no control over it right? But I am scared about Pull because
there can be firewall issues when the distributors are located at multiple
locations and fetching the data from the shared folder(distributor) will be
most toughest thing? am i right with this?
"MB" wrote:

> Hello Affy,
> I would suggest central subscriber Vs multiple publisher model. As your
> requirement is limited to once in a day Snapshot would be fine depending on
> the size of the database. Now I suggest Push subscription as you do have
> multiple site and in Push replication SQL Server use resource of Publisher
> and not subscriber. Hence you will have less burden on the central
> subscriber. In Pull case central server has to pull the data from all the
> subscriber and there might be some performance issue else you need to
> consider the replication load and hardware of the central subscriber must be
> sufficient to take the load.
> Still you need to evaluate your requirement and study various replication
> topology to set up replication.
> Cheers,
> MB
>
> "Affy" <Affy@.discussions.microsoft.com> wrote in message
> news:99A896C8-80C9-4E8D-8B6B-5C9B390FA11E@.microsoft.com...
>
>
|||Thanks Paul for your reply. I happen to read the topology article which you
referred. It was pretty neatly explained. Since my requirement doesnt meet
with amalgamation of data from multiple publisher. But the transactional
replication will happen even if there is a small update or insert happend,
which is not required in my scenario as well, its just one time per day dump.
Why would the snapshot replication from multiple destinations to the same
table will not work? Let me explain the plan which i have, All the publishers
to the central subscriber are subscribed at different times and it will
happen once in a day for each publisher to push the data to a table and then
there will be a job (at the subscriber end) which will process the data in
the table and once processed and inserted in to another database, after which
entire table is cleared, so the next country's data will be pushed by another
publisher. Will this scenario would not fit for Snapshot? I would like to ask
one more question Paul. If I need control on the subscriber end, like
when(time adjustment) the publisher pushes the data, then which one to chose
Push or Pull? But Pull has some disadvantages with firewall and other issues
with sharing data from the distributor right? Also Paul can you send me any
reference article on Merge publisher & Merge subscriber topology which could
explain me neatly just like the one you sent before.
Thanks
Aftab
"Paul Ibison" wrote:

> Are you replicating to the same table? If so, snapshot won't work and you'll
> need transactional. Transactional with multiple publishers and one central
> subscribers is ok if the data is partitioned - is that the case? If so then
> this article might help:
> http://www.replicationanswers.com/CentralSubscriberArticle.asp
> As for push or pull, I'd consider where you want to do the work and where
> you want to do the maintenance, which are the competing factors.
> BTW there is another topology that could be used in this case - have the
> reporting server set up as a merge publisher and the other computers set up
> as merge subscribers. If the data load is light, the performance problems of
> using this method are outweighed by the fact that administration is more
> simplified.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||What I'm wondering is how you will prevent duplicates - data being
snapshotted once one day and again the next day. Are you filtering somehow
and then updating the filter column after replication on the publisher. Or
are you dropping the table in the first publisher which will create the
table on the subscriber? I'm not too sure why there are complications with
pull through the firewall - just more configuration of IP addresses I
suppose. For merge I wouldn't set up this topology - I'd have the central
server as the publisher and not the subscriber. As it doesn't really follow
the publisher/subscriber metaphor this will work ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Actually in order to avoid duplication, We have a unique code which is sort
of universal combined with country code also we are actually deleting the
records after one publsiher has issued one snapshot of the table. So by the
next publisher publishes a copy of the records, the previous set of records
would have got updated. Is there are any article for Pull subscription, with
extra IP settings for Firewall?
"Paul Ibison" wrote:

> What I'm wondering is how you will prevent duplicates - data being
> snapshotted once one day and again the next day. Are you filtering somehow
> and then updating the filter column after replication on the publisher. Or
> are you dropping the table in the first publisher which will create the
> table on the subscriber? I'm not too sure why there are complications with
> pull through the firewall - just more configuration of IP addresses I
> suppose. For merge I wouldn't set up this topology - I'd have the central
> server as the publisher and not the subscriber. As it doesn't really follow
> the publisher/subscriber metaphor this will work ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||OK - I 'think' I understand your topology. I'd personally use transactional
in preference to this setup though as you are continually sending some of
the same data. Am not too sure what the last question means: "Is there are
any article for Pull subscription, with extra IP settings for Firewall?".
Ports 1433 (for default instance) and 21 (FTP) are usually sufficient, then
the IP addresses need to be fixed and registered in the firewall.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .