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>...
>