Replication error 7212

Last Post 13 Jun 2005 07:42 PM by rickni. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rickni
New Member
New Member

--
08 Jun 2005 11:13 PM
Hi,

I am experiencing some difficulties with DataBase Replication. I have
two machines, One is running Windows 2000 Server with Microsoft SQL
Server, the other is running Windows Professional with MSDE.

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=sa;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...

The error is:
Server: Msg 7212, Level 17, State 1, Line 11
Could not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server 'SQLOLEDB'.

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 isn't working.

Any help is much appreciated.

Thanks in Advance.
Rick 8-)
rickni
New Member
New Member

--
09 Jun 2005 02:18 PM
SQL Service Account?
I assume this means the account under which SQL Server is run. I have set this to be a local administrator.

As for the sp_link_publication, I havn't run this, as I configured replication by using the Enterprise Manager Replication Wizard.

Cheers
Rick 8-)
ashu007
New Member
New Member

--
09 Jun 2005 08:03 PM
Immediate updating internally uses linkserver to propagate the changes from subscriber to publisher. I think u need to check for the valid credential to execute 'sp_MSsync_upd_<tablename>_1'.

meanwhile just let me know if u are using sa with blank password( which is by default it takes) or u ahve provided and password for SA???

rickni
New Member
New Member

--
12 Jun 2005 03:04 PM
Thanks for your feedback. It is greatly appreciated.

I am using SA and it does have a blank password. I have also tried changing this, and still produces the same error.

Also both SQL Server machines are NOT on a domain, but an isolated network. (At the moment these are the only two machines on the local network).

I will look in the 'sp_link_publication' procedure.

Once again thanks for your help. I'll let you know how I get on.

Cheers
Rick 8-)
rickni
New Member
New Member

--
12 Jun 2005 10:23 PM
I have executed the following against the subscribing database:

exec sp_link_publication @publisher='SQLHO', @publisher_db='TestDB', @publication='TestDB', @security_mode=0, @login='sa', @password=''

and still get the same error.
This is from the Query Analyiser: (Where 'Site' is the name of the table being updated against the subscription).

Server: Msg 7212, Level 17, State 1, Line 5
Could not execute procedure 'sp_MSsync_upd_Site_1' on remote server 'SQLOLEDB'.
The statement has been terminated.


Please Note: SQLOLEDB, is not the name of the Publisher.
Publisher = SQLHO and
Subscriber = SQLSITE

Any help would be greatly appreciated.

Cheers
Rick 8-)
rickni
New Member
New Member

--
13 Jun 2005 07:42 PM
I have just created a new user 'SQLAdmin' (and grated it administrator privileges) on both machines. I also changed the SQLSERVER & MSDTC services so that they start-up under this user.

After rebooting, I tried to get the Site to update the Head-Office, and I got the following error:
Server: Msg 8501, Level 16, State 3, Procedure trg_MSsync_upd_Site, Line 78
MSDTC on server 'SQLTEST' is unavailable.


I then started the MSDTC on the Site machine (SQLTEST), and tried to update again, this time I get this error (which is the same as before);
Server: Msg 7212, Level 17, State 1, Line 5
Could not execute procedure 'sp_MSsync_upd_Site_1' on remote server 'SQLOLEDB'.
The statement has been terminated.


Why is it expecting the MSDTC to be running on the Subscribing machine, when the publication and Subscription were setup with the Transaction Distributor running at the Publisher?

Any additional help would be greatly appreciated.

Cheers
Rick 8-)
You are not authorized to post a reply.

Acceptable Use Policy