We have a domain, say OUR_DOMAIN. Within this I replication running happily
for several months from MYSQLSERVER . We now have another "trusted" domain
at a co-location, OTHERDOMAIN.
To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
However, when I set up a pull replication from the OTHERDOMAIN machine, say
MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even though I
refer to it as MYSQLSERVER.OUR_DOMAIN.
The Pull subscription is created properly, and I go to the job list and run
the job. The step is
-Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
[MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
However it is unable to connect, The process could not connect to
Distributor 'MYSQLSERVER'.
If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
error "The process could not retrieve security information from the
Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which suggests
adding the domain to the publisher worked. However if I add the domain to
the distributor
-Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
I get "The process could not retrieve security information from the
Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
Both agents are running under administrator level accounts on their
respective domains, and the subscriber account is in the Publication Access
List.
Any help with naming conventions used here or any resources or pointers
would be greatly appreciated.
Cheers!
Simon
I prefer to use NetBIOS names to register the servers in EM. Then I hide the
FQDN or the IP address of the server using Client Network Utility. I then
use a single account in both domains with the same password to connect to
both server and I also use this account to run the SQL Server agent account
under.
So on OUR_Domain the account would be SimonCar and the password could be
Racnomis. On OtherDomain the account would be SimonCar and the password
could be Racnomis. Another option is to use local machine accounts with the
same name and password.
A third option is to use SQL Server authentication.
One final point is that a patch has changed the way agent security works.
You must now run the agents with the SA job owner.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:%23LmtjUb8FHA.3132@.TK2MSFTNGP12.phx.gbl...
> We have a domain, say OUR_DOMAIN. Within this I replication running
> happily for several months from MYSQLSERVER . We now have another
> "trusted" domain at a co-location, OTHERDOMAIN.
> To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
> However, when I set up a pull replication from the OTHERDOMAIN machine,
> say MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even
> though I refer to it as MYSQLSERVER.OUR_DOMAIN.
> The Pull subscription is created properly, and I go to the job list and
> run the job. The step is
> -Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
> [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
> However it is unable to connect, The process could not connect to
> Distributor 'MYSQLSERVER'.
> If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
> error "The process could not retrieve security information from the
> Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which
> suggests adding the domain to the publisher worked. However if I add the
> domain to the distributor
> -Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
> 1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB
> [MyDBName]
> I get "The process could not retrieve security information from the
> Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
> Both agents are running under administrator level accounts on their
> respective domains, and the subscriber account is in the Publication
> Access List.
> Any help with naming conventions used here or any resources or pointers
> would be greatly appreciated.
> Cheers!
> Simon
>
>
|||Hilary Cotter wrote:
> I prefer to use NetBIOS names to register the servers in EM. Then I
> hide the FQDN or the IP address of the server using Client Network
> Utility. I then use a single account in both domains with the same
> password to connect to both server and I also use this account to run
> the SQL Server agent account under.
> So on OUR_Domain the account would be SimonCar and the password could
> be Racnomis. On OtherDomain the account would be SimonCar and the
> password could be Racnomis. Another option is to use local machine
> accounts with the same name and password.
> A third option is to use SQL Server authentication.
>
The network guy has now set it so I can use just <servername>. I have set
the ReplData to be a share and point to that from the subscriber (it has
permissions to this share). The <Otherservername> is in the list of
subscribers but SQL claims it isn't from <Otherservername> when I request a
Pull subscription, but if I allow anonymous I can get it replicating fine.
The only downside of that I am aware of is the retention of replicated
transactions if I allow anonymous?
The same user/password seems simple enough for me to appreciate!
Thanks for your help.
Cheers!
Simon
|||> The <Otherservername> is in the
> list of subscribers but SQL claims it isn't from <Otherservername>
> when I request a Pull subscription, but if I allow anonymous I can
> get it replicating fine. The only downside of that I am aware of is
> the retention of replicated transactions if I allow anonymous?
There is some connectivity/authentication issue still here becuause if I add
the subscriber to the publisher from a copy of EM on the subscriber it
works, if I add the subscriber to subscribers through EM from my machine or
the publisher it doesn't recognise it even though the same machine name is
used.
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
Replication to server with different Port
I have a SQL server running on a remote location that has a different port
enabled to connect other than the default one, which means that when I want
to register I have to specify the port number as well eg.
Server1\InstanceName,4001 .
I am trying to carry out merge replication between the server and the MSDE
on my box. I have set up the pubisher, registered the SQL Server from
Enterprise Manager on my machine and on setting up the subscriber I am able
to view the publication in the list of registered servers. Creating the
subscription works fine, but when I try to sync , I get the error 'The
process could not connect to Distributor 'Server1\InstanceName' . I feel that
this is because of the port since it is not mentioned in the message and SQL
is trying to use the default port. Is there some workaround for this?
Jax,
for a named instance, there is no default port - it is assigned at creation
from the list of available ports, and your port number won't necessarily be
the same as mine, for the first named instance. Usually the port number is
not specified in the replication definition, and it is dynamically picked up
(hence the slammer virus on UDP, port 1434
). Do you have 1434 blocked? If
not, then please try designing without the port number and see if this
works.
Also, The SQL Server Agent service (SQLServerAgent) at the client should not
use the LocalSystem account. It needs to use a standard domain account.
Finally, please check that the agents use impersonation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
enabled to connect other than the default one, which means that when I want
to register I have to specify the port number as well eg.
Server1\InstanceName,4001 .
I am trying to carry out merge replication between the server and the MSDE
on my box. I have set up the pubisher, registered the SQL Server from
Enterprise Manager on my machine and on setting up the subscriber I am able
to view the publication in the list of registered servers. Creating the
subscription works fine, but when I try to sync , I get the error 'The
process could not connect to Distributor 'Server1\InstanceName' . I feel that
this is because of the port since it is not mentioned in the message and SQL
is trying to use the default port. Is there some workaround for this?
Jax,
for a named instance, there is no default port - it is assigned at creation
from the list of available ports, and your port number won't necessarily be
the same as mine, for the first named instance. Usually the port number is
not specified in the replication definition, and it is dynamically picked up
(hence the slammer virus on UDP, port 1434
). Do you have 1434 blocked? Ifnot, then please try designing without the port number and see if this
works.
Also, The SQL Server Agent service (SQLServerAgent) at the client should not
use the LocalSystem account. It needs to use a standard domain account.
Finally, please check that the agents use impersonation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication to Mainframe Db2 OSZ
What would I need in order to setup replication between SQL2000 SP3 and MVS Subsystem running Db2 database . I mean, does it need any 3rd party tool or regular replication setup between SQL and Db2 is enoughAsk IBM for the DB2 Federated Server. If you have DB2 license, they should let you use that software free. It comes with a Replicator which allows you to replicate data from a different platform.
Replication Timeout Issue
Greetings!
I am running into this error when running Replication:
The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.
(f) The process could not connect to Distributor 'OURDATABASE-DB'.
(f) The process could not connect to Distributor 'OURDATABASE-DB'.
(f) Unable to complete login process due to delay in opening server connection
*******************************************************
01/11/2007 18:26:09 - Error during FINAL DB Replication. The process could not connect to Distributor 'OURDATABASE-DB'.
I am running Replication on a Windows XP Pro laptop, with 1 GB of RAM, 1.83 GHz CPU. Any ideas, suggestions and/or help would be appreciated!
Thanks!
Cristian E.
Please provide more information. What version of SQL server are you using? Are you using standard edition, enterprise, or express? Transactional or merge replication?
Gary
|||Hello,
1. Is the Laptop computer your Subscriber or Publisher/Distributor?
2. If you do not do web sync, can you run sync through merge agent (replmerg.exe) from Console window command line using fully connected mode?
This posting is provided AS IS with no warranties, and confers no rights.
|||I am using SQL Server 2005 Express version, Merge replication. I changed the History Verbose Level to a 2 and I got a different error:
(f) The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) Named Pipes Provider: Could not open a connection to SQL Server [2].
(f) 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.
(f) Login timeout expired
(f) The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
*******************************************************
01/13/2007 09:31:39 - Error during FINAL DB Replication. The process could not connect to Distributor 'ABCD-DB'.
*******************************************************
I have already increased the timeout to 15-20 mins. Any other suggestions?
Thanks!
Cristian E.
I am using SQL Server 2005 Express version, Merge replication. I changed the History Verbose Level to a 2 and I got a different error:
(f) The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) Named Pipes Provider: Could not open a connection to SQL Server [2].
(f) 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.
(f) Login timeout expired
(f) The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
*******************************************************
01/13/2007 09:31:39 - Error during FINAL DB Replication. The process could not connect to Distributor 'ABCD-DB'.
*******************************************************
I have already increased the timeout to 15-20 mins.
The Laptop computer is the Publisher and it is running web synchronization (web services).
|||Has it ever worked before?
Can you try this on your machine?
osql -S"ABCD-DB" -U"login"
See if you get the same error message for "Named Pipes Provider: Could not open a connection to SQL Server [2]. "
If you get the same message, then the problem is as simple as enable the protocols in your sql server setting.
I also noticed that the name "ABCD-DB" sounds more like a database name than a server name...
Regards,
Gary
Replication that causes blocking of process
Hi ,
My Replication also got this timeout error while running the last command :
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
blocking
SPid73 --> which is doing the CALL sp_MSdel_SL030100
How can i resolve it as i am afraid that if i kill any process the sync is
out and i would be forced to re-create the snapshot and also shldn't the
SP_MSdel be done first ?
apreciate ur advise
Message posted via http://www.droptable.com
This looks like an update which is being performed as a delete insert pair.
Study what the procs are doing and see if it can't benefit from new indexes
of updating the indexes.
You might also want to look at this trace flag.
http://support.microsoft.com/kb/238254/en-us
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a944df8034f0@.uwe...
> Hi ,
> My Replication also got this timeout error while running the last command
> :
> {CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL
> sp_MSins_SL030100
> (N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
> i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
> blocking
> SPid73 --> which is doing the CALL sp_MSdel_SL030100
> How can i resolve it as i am afraid that if i kill any process the sync is
> out and i would be forced to re-create the snapshot and also shldn't the
> SP_MSdel be done first ?
>
> apreciate ur advise
> --
> Message posted via http://www.droptable.com
|||Hi,
First of all tks for the links provided.
as menitoned in the link it's doing a so-called "deferred" update when the
unique constraint (i.e the PK) has been changed
However , from the stored procedure below
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19
BJX0047 & 26.01.06AF are the 2 PKs' values and both the DELETE/INSERT are
showing the same values, so i am confused
appreciate ur further advice
btw : after leaving this replication for the whole nite , it somehow resolved
by itself
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>This looks like an update which is being performed as a delete insert pair.
>Study what the procs are doing and see if it can't benefit from new indexes
>of updating the indexes.
>You might also want to look at this trace flag.
>http://support.microsoft.com/kb/238254/en-us
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
My Replication also got this timeout error while running the last command :
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
blocking
SPid73 --> which is doing the CALL sp_MSdel_SL030100
How can i resolve it as i am afraid that if i kill any process the sync is
out and i would be forced to re-create the snapshot and also shldn't the
SP_MSdel be done first ?
apreciate ur advise
Message posted via http://www.droptable.com
This looks like an update which is being performed as a delete insert pair.
Study what the procs are doing and see if it can't benefit from new indexes
of updating the indexes.
You might also want to look at this trace flag.
http://support.microsoft.com/kb/238254/en-us
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a944df8034f0@.uwe...
> Hi ,
> My Replication also got this timeout error while running the last command
> :
> {CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL
> sp_MSins_SL030100
> (N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
> i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
> blocking
> SPid73 --> which is doing the CALL sp_MSdel_SL030100
> How can i resolve it as i am afraid that if i kill any process the sync is
> out and i would be forced to re-create the snapshot and also shldn't the
> SP_MSdel be done first ?
>
> apreciate ur advise
> --
> Message posted via http://www.droptable.com
|||Hi,
First of all tks for the links provided.
as menitoned in the link it's doing a so-called "deferred" update when the
unique constraint (i.e the PK) has been changed
However , from the stored procedure below
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19
BJX0047 & 26.01.06AF are the 2 PKs' values and both the DELETE/INSERT are
showing the same values, so i am confused
appreciate ur further advice
btw : after leaving this replication for the whole nite , it somehow resolved
by itself
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>This looks like an update which is being performed as a delete insert pair.
>Study what the procs are doing and see if it can't benefit from new indexes
>of updating the indexes.
>You might also want to look at this trace flag.
>http://support.microsoft.com/kb/238254/en-us
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
replication technique
Hello,
I read serveral articles about the mechanism of replication from a sql
server 2000 database on
desktop with an mobile device running windows mobile.
I want to remark my understanding fo the replication mechanism and wanted to
ask you if you
could give me feedback if I understand it in a correct way.
-----
1) First of all I create a publication - which is a collection of articles -
rows. It is
possible to define certain tables and rows for publication.
2) After the publication a windows ce-based application can call the methods
of the SQL Server
Ce Replication object to subscribe to the publication. When the mobile
application has executed
the subscription the initial snapshot of the published table is stored on
the mobile device and
so the database file is created on the device.
3) The subscription database on the device can then be updated by
synchronzing it with the desktop
database. For that the synchronisation is always intiated by the device. For
that the SQL Server CE
Client Agent extracts all modified rows from the subscription database put
these rows in a message
file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
Server CE Server Agent
creates a new message file on the desktop where the IIS is running and
writes in that file the
modified rows which are sent by the SQL Server CE Client Agent.Then the SQL
Server CE Server Agent
initiates the SQL Server Reconciler process. The SQL Server Reconciler
Process loads the SQL Server CE
Replication Provider which reads the message file which was created by the
SQL Server CE Server Agent
and apply the changes to the publisher database. The SQL Server CE
Replication Provider informs the SQL Server
Reconciler about the changes which were made in the subscription
database.The SQL Server Reconciler
also solves conflicts if more than one subscriber or publisher updates the
same record.
On the other hand the SQL Server Reconciler informs the SQL Server CE
Replication Provider of
changes which occured at the publisher database. The SQL Server CE
Replication writes this
information - especially the modified rows - to an output message file.
After the Reconciler
process is completed the SQL Server CE Server Agent locates the the output
message file reads
the information and transfer it to the SQL Server CE Client Agent which
apply the changes from
the output message file to the subscription databse on the windows ce device.
-----
a) Are these steps correct or have I forgotten anything important or have I
understand something
wrong?
b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
Server CE Server Agent,
SQL Server Reconciler and the SQL Server CE Replication Provider? What are
these terms exactly?
c) When I publish a database, especially tables in the enterprise manager
the tables get automatically
a row guid. What part plays these row guid's in my above mentioned
replication process?
d) The Replication Mechanism uses static and dynamic filtering. Is it right
that the dynamic filter
could not use on the application layer? - means that it is not possiblt to
replicate for example
the information according to the user which is currently logged in the
application? - I think it
is only possible to synchronize according to a certain device especially
certain host, is that right?
I know this is a extensive posting but I would appreciate it very much if
anybody find time to
answer me my questions. :-/
Regards.
patrick
answers inline
--
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
"pat" <pat@.discussions.microsoft.com> wrote in message
news:273513F0-8240-4E1A-9598-9ED04BC448CE@.microsoft.com...
> Hello,
> I read serveral articles about the mechanism of replication from a sql
> server 2000 database on
> desktop with an mobile device running windows mobile.
> I want to remark my understanding fo the replication mechanism and wanted
> to
> ask you if you
> could give me feedback if I understand it in a correct way.
> -----
> 1) First of all I create a publication - which is a collection of
> articles -
> rows. It is
> possible to define certain tables and rows for publication.
Yes, you will need to set up a merge publication. When you say rows for
publication you mean horizontal filtering.
> 2) After the publication a windows ce-based application can call the
> methods
> of the SQL Server
> Ce Replication object to subscribe to the publication. When the mobile
> application has executed
> the subscription the initial snapshot of the published table is stored on
> the mobile device and
> so the database file is created on the device.
Yes, you will code something using vb or vc embedded or the .Net compact
framework to pull a merge subscription. This will use a SQL CE database on
your PocketPC.
> 3) The subscription database on the device can then be updated by
> synchronzing it with the desktop
> database. For that the synchronisation is always intiated by the device.
> For
> that the SQL Server CE
> Client Agent extracts all modified rows from the subscription database put
> these rows in a message
> file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
> Server CE Server Agent
> creates a new message file on the desktop where the IIS is running and
> writes in that file the
> modified rows which are sent by the SQL Server CE Client Agent.Then the
> SQL
> Server CE Server Agent
> initiates the SQL Server Reconciler process. The SQL Server Reconciler
> Process loads the SQL Server CE
> Replication Provider which reads the message file which was created by the
> SQL Server CE Server Agent
> and apply the changes to the publisher database. The SQL Server CE
> Replication Provider informs the SQL Server
> Reconciler about the changes which were made in the subscription
> database.The SQL Server Reconciler
> also solves conflicts if more than one subscriber or publisher updates the
> same record.
> On the other hand the SQL Server Reconciler informs the SQL Server CE
> Replication Provider of
> changes which occured at the publisher database. The SQL Server CE
> Replication writes this
> information - especially the modified rows - to an output message file.
> After the Reconciler
> process is completed the SQL Server CE Server Agent locates the the output
> message file reads
> the information and transfer it to the SQL Server CE Client Agent which
> apply the changes from
> the output message file to the subscription databse on the windows ce
> device.
>
This is basically it.
> -----
> a) Are these steps correct or have I forgotten anything important or have
> I
> understand something
> wrong?
that's basically it
> b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
> Server CE Server Agent,
> SQL Server Reconciler and the SQL Server CE Replication Provider? What are
> these terms exactly?
> c) When I publish a database, especially tables in the enterprise manager
> the tables get automatically
> a row guid. What part plays these row guid's in my above mentioned
> replication process?
it uniquely identifys each row, so SQL Server can figure out which row has
changed or needs changing.
> d) The Replication Mechanism uses static and dynamic filtering. Is it
> right
> that the dynamic filter
> could not use on the application layer? - means that it is not possiblt to
> replicate for example
> the information according to the user which is currently logged in the
> application? - I think it
> is only possible to synchronize according to a certain device especially
> certain host, is that right?
>
No, you define your filter on the publication. I don't believe you can
filter your subscribers.
> I know this is a extensive posting but I would appreciate it very much if
> anybody find time to
> answer me my questions. :-/
> Regards.
> patrick
>
|||Hello,
Thank you very much for your reply but few things are still unclear:
1) Why do I need the row guid? - the changed rows are saved in a file and
are sent to the SQL Server CE Server Agent, so he knows which rows were
modified when he looks into the message file. Why does he need then a row
guid?
2) The SQL Server CE Client Agent, the SQL, Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - are these
processes or components or parts of the os? what I have to imagine behind
these terms?
3) Referring to dynamic filtering I thought it is possible to synchronize
after a certain host on the mobile device? - What can I then realize with
dynamic filterin?
(static filtering is when I define some rows or columns or tables which I
can then synchronize or?)
Hopefully you could answer me once more.
regards
patrick
3)
|||Hi,
Thank you very much for your reply but unfortunately few things are unclear:
1)Why do I need this row guid's, because the SQL Server CE Server Agent
reads the modified rows from the message file which is sent by the SQL
Server CE Client Agent and so the SQL Server CE Replication Provider knows
what rows he has to modify. So why do I neet this row guids?
2)Tthe SQL Server CE Client Agent, the SQL Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - what do I have
to imagene behind these terms? Are these processes or components or part of
the os or anyting else?
3)Referring to the filtering. So it is not possible to define a filter
according to a certain host on a mobile device? - The static filtering allows
to define some rows or columns or tables which can be modified, but then for
what is the dynamic filtering?
regards
patrick
|||hi,
is it possible to answer me the last time on my three posted questions?
regards
patrick
sql
I read serveral articles about the mechanism of replication from a sql
server 2000 database on
desktop with an mobile device running windows mobile.
I want to remark my understanding fo the replication mechanism and wanted to
ask you if you
could give me feedback if I understand it in a correct way.
-----
1) First of all I create a publication - which is a collection of articles -
rows. It is
possible to define certain tables and rows for publication.
2) After the publication a windows ce-based application can call the methods
of the SQL Server
Ce Replication object to subscribe to the publication. When the mobile
application has executed
the subscription the initial snapshot of the published table is stored on
the mobile device and
so the database file is created on the device.
3) The subscription database on the device can then be updated by
synchronzing it with the desktop
database. For that the synchronisation is always intiated by the device. For
that the SQL Server CE
Client Agent extracts all modified rows from the subscription database put
these rows in a message
file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
Server CE Server Agent
creates a new message file on the desktop where the IIS is running and
writes in that file the
modified rows which are sent by the SQL Server CE Client Agent.Then the SQL
Server CE Server Agent
initiates the SQL Server Reconciler process. The SQL Server Reconciler
Process loads the SQL Server CE
Replication Provider which reads the message file which was created by the
SQL Server CE Server Agent
and apply the changes to the publisher database. The SQL Server CE
Replication Provider informs the SQL Server
Reconciler about the changes which were made in the subscription
database.The SQL Server Reconciler
also solves conflicts if more than one subscriber or publisher updates the
same record.
On the other hand the SQL Server Reconciler informs the SQL Server CE
Replication Provider of
changes which occured at the publisher database. The SQL Server CE
Replication writes this
information - especially the modified rows - to an output message file.
After the Reconciler
process is completed the SQL Server CE Server Agent locates the the output
message file reads
the information and transfer it to the SQL Server CE Client Agent which
apply the changes from
the output message file to the subscription databse on the windows ce device.
-----
a) Are these steps correct or have I forgotten anything important or have I
understand something
wrong?
b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
Server CE Server Agent,
SQL Server Reconciler and the SQL Server CE Replication Provider? What are
these terms exactly?
c) When I publish a database, especially tables in the enterprise manager
the tables get automatically
a row guid. What part plays these row guid's in my above mentioned
replication process?
d) The Replication Mechanism uses static and dynamic filtering. Is it right
that the dynamic filter
could not use on the application layer? - means that it is not possiblt to
replicate for example
the information according to the user which is currently logged in the
application? - I think it
is only possible to synchronize according to a certain device especially
certain host, is that right?
I know this is a extensive posting but I would appreciate it very much if
anybody find time to
answer me my questions. :-/
Regards.
patrick
answers inline
--
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
"pat" <pat@.discussions.microsoft.com> wrote in message
news:273513F0-8240-4E1A-9598-9ED04BC448CE@.microsoft.com...
> Hello,
> I read serveral articles about the mechanism of replication from a sql
> server 2000 database on
> desktop with an mobile device running windows mobile.
> I want to remark my understanding fo the replication mechanism and wanted
> to
> ask you if you
> could give me feedback if I understand it in a correct way.
> -----
> 1) First of all I create a publication - which is a collection of
> articles -
> rows. It is
> possible to define certain tables and rows for publication.
Yes, you will need to set up a merge publication. When you say rows for
publication you mean horizontal filtering.
> 2) After the publication a windows ce-based application can call the
> methods
> of the SQL Server
> Ce Replication object to subscribe to the publication. When the mobile
> application has executed
> the subscription the initial snapshot of the published table is stored on
> the mobile device and
> so the database file is created on the device.
Yes, you will code something using vb or vc embedded or the .Net compact
framework to pull a merge subscription. This will use a SQL CE database on
your PocketPC.
> 3) The subscription database on the device can then be updated by
> synchronzing it with the desktop
> database. For that the synchronisation is always intiated by the device.
> For
> that the SQL Server CE
> Client Agent extracts all modified rows from the subscription database put
> these rows in a message
> file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
> Server CE Server Agent
> creates a new message file on the desktop where the IIS is running and
> writes in that file the
> modified rows which are sent by the SQL Server CE Client Agent.Then the
> SQL
> Server CE Server Agent
> initiates the SQL Server Reconciler process. The SQL Server Reconciler
> Process loads the SQL Server CE
> Replication Provider which reads the message file which was created by the
> SQL Server CE Server Agent
> and apply the changes to the publisher database. The SQL Server CE
> Replication Provider informs the SQL Server
> Reconciler about the changes which were made in the subscription
> database.The SQL Server Reconciler
> also solves conflicts if more than one subscriber or publisher updates the
> same record.
> On the other hand the SQL Server Reconciler informs the SQL Server CE
> Replication Provider of
> changes which occured at the publisher database. The SQL Server CE
> Replication writes this
> information - especially the modified rows - to an output message file.
> After the Reconciler
> process is completed the SQL Server CE Server Agent locates the the output
> message file reads
> the information and transfer it to the SQL Server CE Client Agent which
> apply the changes from
> the output message file to the subscription databse on the windows ce
> device.
>
This is basically it.
> -----
> a) Are these steps correct or have I forgotten anything important or have
> I
> understand something
> wrong?
that's basically it
> b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
> Server CE Server Agent,
> SQL Server Reconciler and the SQL Server CE Replication Provider? What are
> these terms exactly?
> c) When I publish a database, especially tables in the enterprise manager
> the tables get automatically
> a row guid. What part plays these row guid's in my above mentioned
> replication process?
it uniquely identifys each row, so SQL Server can figure out which row has
changed or needs changing.
> d) The Replication Mechanism uses static and dynamic filtering. Is it
> right
> that the dynamic filter
> could not use on the application layer? - means that it is not possiblt to
> replicate for example
> the information according to the user which is currently logged in the
> application? - I think it
> is only possible to synchronize according to a certain device especially
> certain host, is that right?
>
No, you define your filter on the publication. I don't believe you can
filter your subscribers.
> I know this is a extensive posting but I would appreciate it very much if
> anybody find time to
> answer me my questions. :-/
> Regards.
> patrick
>
|||Hello,
Thank you very much for your reply but few things are still unclear:
1) Why do I need the row guid? - the changed rows are saved in a file and
are sent to the SQL Server CE Server Agent, so he knows which rows were
modified when he looks into the message file. Why does he need then a row
guid?
2) The SQL Server CE Client Agent, the SQL, Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - are these
processes or components or parts of the os? what I have to imagine behind
these terms?
3) Referring to dynamic filtering I thought it is possible to synchronize
after a certain host on the mobile device? - What can I then realize with
dynamic filterin?
(static filtering is when I define some rows or columns or tables which I
can then synchronize or?)
Hopefully you could answer me once more.
regards
patrick
3)
|||Hi,
Thank you very much for your reply but unfortunately few things are unclear:
1)Why do I need this row guid's, because the SQL Server CE Server Agent
reads the modified rows from the message file which is sent by the SQL
Server CE Client Agent and so the SQL Server CE Replication Provider knows
what rows he has to modify. So why do I neet this row guids?
2)Tthe SQL Server CE Client Agent, the SQL Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - what do I have
to imagene behind these terms? Are these processes or components or part of
the os or anyting else?
3)Referring to the filtering. So it is not possible to define a filter
according to a certain host on a mobile device? - The static filtering allows
to define some rows or columns or tables which can be modified, but then for
what is the dynamic filtering?
regards
patrick
|||hi,
is it possible to answer me the last time on my three posted questions?
regards
patrick
sql
Wednesday, March 28, 2012
Replication subscription validation in Microsoft SQL Server 2000
Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
Oskar
Oskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
Oskar
Oskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Replication subscription validation in Microsoft SQL Server 2000
Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Replication subscription validation in Microsoft SQL Server 2000
Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
--
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
--
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Monday, March 26, 2012
replication security
What’s the best way to secure merge replication over the internet? The cl
ient is running MSDE and could be a home user with broadband. If it’s usi
ng a VPN would this be SSL or IPSec?Either would secure the traffic from MSDE to the Publisher.
VPN would allow the client to use other corporate resources as well and
authenticate using Windows
Authentication.
At a minimum put a server certificates on the Publisher and update the
Trusted Root Authority
on the Subscriber (MSDE) and use SSL.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have an ISA Server, you could publish the SQL resource as well.
Here's some docs on using ISA.
http://www.microsoft.com/technet/pr...tain/proxy.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin - I left out some important details. Actually, the client side
would be the Publisher (MSDE). We would like to use replication to backup
the database with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:
> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Thanks Kevin - I left out some important details. Actually, the client side
(MSDE) would be the publisher. We would like to use replication to backup
the databases with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:
> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||OK. Then the MSDE machine (Publisher) would require the server certificate
then.
Also, SQL Replication is not really a backup strategy... Many customers use
Transactional Replication
to provide a warm standby for their Published database, but you should also
have a good backup strategy in place in case there
are hardware failures, that require complete recovery.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
I'm working on a similar situation (trying to implement SSL between 2
MSSQL 2000 boxes for replication).
Is there any way to implement the envryption (SSL) for the connections
used during the replication, other than the "force encryption" options
in the client and/or server network utility?
TIA.
Mike
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Mike,
In SQL 2000 we use protocol encryption to enable SSL. If you are using
Default instances of SQL for Replication you can also use the older network
library called MultiProtocol.
164667 INF: Replication Setup Over a Firewall
http://support.microsoft.com/?id=164667
Other options are: IPSec between the two machines or a VPN connection
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
ient is running MSDE and could be a home user with broadband. If it’s usi
ng a VPN would this be SSL or IPSec?Either would secure the traffic from MSDE to the Publisher.
VPN would allow the client to use other corporate resources as well and
authenticate using Windows
Authentication.
At a minimum put a server certificates on the Publisher and update the
Trusted Root Authority
on the Subscriber (MSDE) and use SSL.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have an ISA Server, you could publish the SQL resource as well.
Here's some docs on using ISA.
http://www.microsoft.com/technet/pr...tain/proxy.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin - I left out some important details. Actually, the client side
would be the Publisher (MSDE). We would like to use replication to backup
the database with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:
> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Thanks Kevin - I left out some important details. Actually, the client side
(MSDE) would be the publisher. We would like to use replication to backup
the databases with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:
> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||OK. Then the MSDE machine (Publisher) would require the server certificate
then.
Also, SQL Replication is not really a backup strategy... Many customers use
Transactional Replication
to provide a warm standby for their Published database, but you should also
have a good backup strategy in place in case there
are hardware failures, that require complete recovery.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
I'm working on a similar situation (trying to implement SSL between 2
MSSQL 2000 boxes for replication).
Is there any way to implement the envryption (SSL) for the connections
used during the replication, other than the "force encryption" options
in the client and/or server network utility?
TIA.
Mike
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Mike,
In SQL 2000 we use protocol encryption to enable SSL. If you are using
Default instances of SQL for Replication you can also use the older network
library called MultiProtocol.
164667 INF: Replication Setup Over a Firewall
http://support.microsoft.com/?id=164667
Other options are: IPSec between the two machines or a VPN connection
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Replication Security
Hi All,
I'm looking at the replication security on one of my servers (admittedly I
haven't used replication that much before), we are running transactional
replication and I wondered what the distributer_admin login does... ...is it
safe to remove the distributer_admin login? - or at least change it to a
windows authenticated login?
Thanks in advance
Ben
Don't touch this account, if you remove it replication will not work. It is
an account which is used to proxy RPC calls between the
Publisher/Distributor/Subscriber.
IIRC, you can remove it after you have created your publications,
subscribers, distributor, but after you remove it you will no longer be able
to configure any more publishers or subscribers.
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
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:C16AE4CB-6D92-4DCF-98B5-AFD0DBCCC6ED@.microsoft.com...
> Hi All,
> I'm looking at the replication security on one of my servers (admittedly I
> haven't used replication that much before), we are running transactional
> replication and I wondered what the distributer_admin login does... ...is
> it
> safe to remove the distributer_admin login? - or at least change it to a
> windows authenticated login?
> Thanks in advance
> Ben
sql
I'm looking at the replication security on one of my servers (admittedly I
haven't used replication that much before), we are running transactional
replication and I wondered what the distributer_admin login does... ...is it
safe to remove the distributer_admin login? - or at least change it to a
windows authenticated login?
Thanks in advance
Ben
Don't touch this account, if you remove it replication will not work. It is
an account which is used to proxy RPC calls between the
Publisher/Distributor/Subscriber.
IIRC, you can remove it after you have created your publications,
subscribers, distributor, but after you remove it you will no longer be able
to configure any more publishers or subscribers.
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
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:C16AE4CB-6D92-4DCF-98B5-AFD0DBCCC6ED@.microsoft.com...
> Hi All,
> I'm looking at the replication security on one of my servers (admittedly I
> haven't used replication that much before), we are running transactional
> replication and I wondered what the distributer_admin login does... ...is
> it
> safe to remove the distributer_admin login? - or at least change it to a
> windows authenticated login?
> Thanks in advance
> Ben
sql
Replication Security
Hi All,
I'm looking at the replication security on one of my servers (admittedly I
haven't used replication that much before), we are running transactional
replication and I wondered what the distributer_admin login does... ...is it
safe to remove the distributer_admin login? - or at least change it to a
windows authenticated login?
Thanks in advance
BenDon't touch this account, if you remove it replication will not work. It is
an account which is used to proxy RPC calls between the
Publisher/Distributor/Subscriber.
IIRC, you can remove it after you have created your publications,
subscribers, distributor, but after you remove it you will no longer be able
to configure any more publishers or subscribers.
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
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:C16AE4CB-6D92-4DCF-98B5-AFD0DBCCC6ED@.microsoft.com...
> Hi All,
> I'm looking at the replication security on one of my servers (admittedly I
> haven't used replication that much before), we are running transactional
> replication and I wondered what the distributer_admin login does... ...is
> it
> safe to remove the distributer_admin login? - or at least change it to a
> windows authenticated login?
> Thanks in advance
> Ben
I'm looking at the replication security on one of my servers (admittedly I
haven't used replication that much before), we are running transactional
replication and I wondered what the distributer_admin login does... ...is it
safe to remove the distributer_admin login? - or at least change it to a
windows authenticated login?
Thanks in advance
BenDon't touch this account, if you remove it replication will not work. It is
an account which is used to proxy RPC calls between the
Publisher/Distributor/Subscriber.
IIRC, you can remove it after you have created your publications,
subscribers, distributor, but after you remove it you will no longer be able
to configure any more publishers or subscribers.
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
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:C16AE4CB-6D92-4DCF-98B5-AFD0DBCCC6ED@.microsoft.com...
> Hi All,
> I'm looking at the replication security on one of my servers (admittedly I
> haven't used replication that much before), we are running transactional
> replication and I wondered what the distributer_admin login does... ...is
> it
> safe to remove the distributer_admin login? - or at least change it to a
> windows authenticated login?
> Thanks in advance
> Ben
Replication Screwup
Running SQL 2005 on Win 2003....
First, I cannot find sp_MSload_replication_status. When I run it, I
get the following error
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSload_replication_status'.
Is the SP not a part of 2005?
Second... I attempted to remove publications & push subscriptions,
from the publisher, via a script, see below...
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'EDI'
-- Remove replication objects from a subscription database (if
necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
This removed the Publication & Subscriptions from SMS, but Replicaiton
Manager still shows them. I have tried numerous scripts with no luck.
How do I clean up replication manger? I tought
sp_removedbreplication removed everything.
AHIA,
Larry...
I have deleted all jobs that reference this database and run
sp_removedbreplication 'EDI'
sp_replicationdboption 'edi','publish','False'
The subscriptions still exist in the replication monitor with the
message...
The concurrent snapshot for publication 'EDI to 04' is not available
because it has not been fully generated or the Log Reader Agent is not
running to activate it. If generation of the concurrent snapshot was
interrupted, the Snapshot Agent for the public
HELP!!!!
|||Did you check the logreader and snapshot jobs on the distribution server?
It could be the snapshot job is still enabled and trying to run.
-Chuck
On 3 May 2007 11:13:58 -0700, LPR-3rd wrote:
> I have deleted all jobs that reference this database and run
> sp_removedbreplication 'EDI'
> sp_replicationdboption 'edi','publish','False'
> The subscriptions still exist in the replication monitor with the
> message...
> The concurrent snapshot for publication 'EDI to 04' is not available
> because it has not been fully generated or the Log Reader Agent is not
> running to activate it. If generation of the concurrent snapshot was
> interrupted, the Snapshot Agent for the public
>
> HELP!!!!
|||sql 2000 only.
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1178211126.029986.247150@.y80g2000hsf.googlegr oups.com...
> Running SQL 2005 on Win 2003....
> First, I cannot find sp_MSload_replication_status. When I run it, I
> get the following error
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sp_MSload_replication_status'.
> Is the SP not a part of 2005?
>
> Second... I attempted to remove publications & push subscriptions,
> from the publisher, via a script, see below...
> DECLARE @.subscriptionDB AS sysname
> SET @.subscriptionDB = N'EDI'
> -- Remove replication objects from a subscription database (if
> necessary).
> USE master
> EXEC sp_removedbreplication @.subscriptionDB
> GO
> This removed the Publication & Subscriptions from SMS, but Replicaiton
> Manager still shows them. I have tried numerous scripts with no luck.
> How do I clean up replication manger? I tought
> sp_removedbreplication removed everything.
> AHIA,
> Larry...
>
|||I had this issue after recoving a crashed database. on server 2005
Dropping / recreating the publications and subscriptions didn't do the trick.
I had to "Disable Replication" , causing all replication related data to be
dropped from the server.
Then I just went through the "Disrtibution" wizard, connected to a
publisher, recreated the pubs and subs and all was well. Didn't have to
rename or do anything else with the source database.
First, I cannot find sp_MSload_replication_status. When I run it, I
get the following error
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSload_replication_status'.
Is the SP not a part of 2005?
Second... I attempted to remove publications & push subscriptions,
from the publisher, via a script, see below...
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'EDI'
-- Remove replication objects from a subscription database (if
necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
This removed the Publication & Subscriptions from SMS, but Replicaiton
Manager still shows them. I have tried numerous scripts with no luck.
How do I clean up replication manger? I tought
sp_removedbreplication removed everything.
AHIA,
Larry...
I have deleted all jobs that reference this database and run
sp_removedbreplication 'EDI'
sp_replicationdboption 'edi','publish','False'
The subscriptions still exist in the replication monitor with the
message...
The concurrent snapshot for publication 'EDI to 04' is not available
because it has not been fully generated or the Log Reader Agent is not
running to activate it. If generation of the concurrent snapshot was
interrupted, the Snapshot Agent for the public
HELP!!!!
|||Did you check the logreader and snapshot jobs on the distribution server?
It could be the snapshot job is still enabled and trying to run.
-Chuck
On 3 May 2007 11:13:58 -0700, LPR-3rd wrote:
> I have deleted all jobs that reference this database and run
> sp_removedbreplication 'EDI'
> sp_replicationdboption 'edi','publish','False'
> The subscriptions still exist in the replication monitor with the
> message...
> The concurrent snapshot for publication 'EDI to 04' is not available
> because it has not been fully generated or the Log Reader Agent is not
> running to activate it. If generation of the concurrent snapshot was
> interrupted, the Snapshot Agent for the public
>
> HELP!!!!
|||sql 2000 only.
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1178211126.029986.247150@.y80g2000hsf.googlegr oups.com...
> Running SQL 2005 on Win 2003....
> First, I cannot find sp_MSload_replication_status. When I run it, I
> get the following error
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sp_MSload_replication_status'.
> Is the SP not a part of 2005?
>
> Second... I attempted to remove publications & push subscriptions,
> from the publisher, via a script, see below...
> DECLARE @.subscriptionDB AS sysname
> SET @.subscriptionDB = N'EDI'
> -- Remove replication objects from a subscription database (if
> necessary).
> USE master
> EXEC sp_removedbreplication @.subscriptionDB
> GO
> This removed the Publication & Subscriptions from SMS, but Replicaiton
> Manager still shows them. I have tried numerous scripts with no luck.
> How do I clean up replication manger? I tought
> sp_removedbreplication removed everything.
> AHIA,
> Larry...
>
|||I had this issue after recoving a crashed database. on server 2005
Dropping / recreating the publications and subscriptions didn't do the trick.
I had to "Disable Replication" , causing all replication related data to be
dropped from the server.
Then I just went through the "Disrtibution" wizard, connected to a
publisher, recreated the pubs and subs and all was well. Didn't have to
rename or do anything else with the source database.
Friday, March 23, 2012
Replication Question
I have continuous transactional replication running from a production server to a reporting server. Both servers are SQL 2000. A week or so ago, a new column was added to a table being replicated, both production and reporting servers. I need to get that field included in the replication. Can this be done without having to drop the subscription, adding the field, re-create the subscription and then re-creating a snapshot?Go to BOL. Look up sp_repladdcolumn. Here is part of what it says:
sp_repladdcolumn
Adds a column to an existing table article that has been published. Allows the new column to be added to all publishers that publish this table, or just add the column to a specific publication that publishes the table. This stored procedure is executed at the Publisher on the publication database.|||Thank you!
sp_repladdcolumn
Adds a column to an existing table article that has been published. Allows the new column to be added to all publishers that publish this table, or just add the column to a specific publication that publishes the table. This stored procedure is executed at the Publisher on the publication database.|||Thank you!
Replication question
Dear Friends,
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Replication Question
I have continuous transactional replication running from a production server
to a reporting server. Both servers are SQL 2000. A week or so ago, a new
column was added to a table being replicated (on both production and
reporting servers). I need to get that field included in the replication.
Can this be done without having to drop the subscription, adding the field,
re-create the subscription and then re-creating a snapshot?
Message posted via http://www.droptable.com"cynault via droptable.com" <u7602@.uwe> wrote in message
news:61851d6b86210@.uwe...
> I have continuous transactional replication running from a production
server
> to a reporting server. Both servers are SQL 2000. A week or so ago, a
new
> column was added to a table being replicated (on both production and
> reporting servers). I need to get that field included in the replication.
> Can this be done without having to drop the subscription, adding the
field,
> re-create the subscription and then re-creating a snapshot?
>
Not if the column has already been added.
But look up sp_repladdcolumn for future reference.
> --
> Message posted via http://www.droptable.com|||Thank you!
Message posted via http://www.droptable.com|||Thank you!
Message posted via http://www.droptable.com
to a reporting server. Both servers are SQL 2000. A week or so ago, a new
column was added to a table being replicated (on both production and
reporting servers). I need to get that field included in the replication.
Can this be done without having to drop the subscription, adding the field,
re-create the subscription and then re-creating a snapshot?
Message posted via http://www.droptable.com"cynault via droptable.com" <u7602@.uwe> wrote in message
news:61851d6b86210@.uwe...
> I have continuous transactional replication running from a production
server
> to a reporting server. Both servers are SQL 2000. A week or so ago, a
new
> column was added to a table being replicated (on both production and
> reporting servers). I need to get that field included in the replication.
> Can this be done without having to drop the subscription, adding the
field,
> re-create the subscription and then re-creating a snapshot?
>
Not if the column has already been added.
But look up sp_repladdcolumn for future reference.
> --
> Message posted via http://www.droptable.com|||Thank you!
Message posted via http://www.droptable.com|||Thank you!
Message posted via http://www.droptable.com
Replication Question
I have continuous transactional replication running from a production server
to a reporting server. Both servers are SQL 2000. A week or so ago, a new
column was added to a table being replicated (on both production and
reporting servers). I need to get that field included in the replication.
Can this be done without having to drop the subscription, adding the field,
re-create the subscription and then re-creating a snapshot?
--
Message posted via http://www.sqlmonster.com"cynault via SQLMonster.com" <u7602@.uwe> wrote in message
news:61851d6b86210@.uwe...
> I have continuous transactional replication running from a production
server
> to a reporting server. Both servers are SQL 2000. A week or so ago, a
new
> column was added to a table being replicated (on both production and
> reporting servers). I need to get that field included in the replication.
> Can this be done without having to drop the subscription, adding the
field,
> re-create the subscription and then re-creating a snapshot?
>
Not if the column has already been added.
But look up sp_repladdcolumn for future reference.
> --
> Message posted via http://www.sqlmonster.com|||Thank you!
--
Message posted via http://www.sqlmonster.comsql
to a reporting server. Both servers are SQL 2000. A week or so ago, a new
column was added to a table being replicated (on both production and
reporting servers). I need to get that field included in the replication.
Can this be done without having to drop the subscription, adding the field,
re-create the subscription and then re-creating a snapshot?
--
Message posted via http://www.sqlmonster.com"cynault via SQLMonster.com" <u7602@.uwe> wrote in message
news:61851d6b86210@.uwe...
> I have continuous transactional replication running from a production
server
> to a reporting server. Both servers are SQL 2000. A week or so ago, a
new
> column was added to a table being replicated (on both production and
> reporting servers). I need to get that field included in the replication.
> Can this be done without having to drop the subscription, adding the
field,
> re-create the subscription and then re-creating a snapshot?
>
Not if the column has already been added.
But look up sp_repladdcolumn for future reference.
> --
> Message posted via http://www.sqlmonster.com|||Thank you!
--
Message posted via http://www.sqlmonster.comsql
replication problems sql server to db2
I am experiencing problems replicating (transactional) from SQL Server
2000 sp3 to DB2 v8.2 (running on Red Hat Linux).
2000 sp3 to DB2 v8.2 (running on Red Hat Linux).
I have created an OLE-DB linked server to DB2. The connection connects
successfully in EM and I can query the DB2 tables using the OPENQUERY
function.
I have successfully added the linked server connection as a Subscriber.
I have now set up a Push Subscription to DB2 from one of my
Publications.
My Distribution Agent errors with:
"The process could not connect to Subscriber xxx "
Any suggestions will help, thank youI have no idea myself, but you might get a better response in
microsoft.public.sqlserver.replication.
Simon|||I have no idea myself, but you might get a better response in
microsoft.public.sqlserver.replication.
Simon
Replication Problems from Subscriber to Publisher.
Hi,
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)
Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>
|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publisher
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>
sql
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)
Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>
|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publisher
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>
sql
Replication Problems from Subscriber to Publisher.
Hi,
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').
[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;'
).[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F8
75-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publishe
r
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher
or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
>|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').
[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;'
).[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F8
75-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publishe
r
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher
or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
>|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:
> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>
Subscribe to:
Posts (Atom)