Publishing from Oracle to Sql Server 2005

HI guys,

Am trying to do the above. Managed to configured the distributer. However, while trying to configure my oracle publisher, it failed as follows:

====================================================
Creating Publication

- Creating Publication 'oraTest' (Success)
* SQL Server created publication 'oraTest'.

- Adding article 1 of 1 (Error)
Messages
* SQL Server Management Studio could not create article 'USERS'. (New Publication Wizard)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Objects cannot be published from the publisher login schema [SCOTT].
Changed database context to 'distribution'. (Microsoft SQL Server, Error: 20802)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1187&EvtSrc=MSSQLServer&EvtID=20802&LinkId=20476

======================================================

As u guys can see, the publisher was created smoothly. It is the adding of the article, ie. my oracle table. I have ran the permission script as instructed in the BOL.

Wat am I missing? Can anyone pls help?

Thanks!

Cheers,
Kevin
-

Hi Kevin,

Can you please make sure that you have granted read permissions on the tables to the Oracle user account for managing Oracle publisher?|||Kevin -

The reason that you get this message is that you tried to publish tables that were in the schema account that you provided for replication. This is not supported since the replication account in Oracle is used strictly for supporting replication. As Mahesh mentions above, you just need to grant select on your tables (which shouldn't be owned by the replication user) to the replication user you created.

Matt Hollingsworth
Program Manager

Publisher, Distribution, Subscriber all on one SQL Server?

Should this work? In production we have one server acting as the
publisher/distributer, and then another as the subscriber.
In the test environment, I'd like to have all of this on the same
server. Will this work? I've run into what I hope is a minor gotcha
(running the drop replication scripts created by enterprise manager
doesn't work, exec sp_dropsubscription returns "The remote server is
not defined as a subscription server").
thanks for any hints.
Sylvia
Hey. I've the same setup at my company for testing. It works fine. I have 2
instances of SQL, one acting as a subscriber and the other acting as a
publisher/distributor for transactional and merge replication. I've 3
publications so far.
For your structure, how is it defined? On your publication, have you defined
the same server as a publisher as well as a subcriber?
|||I even do pull subscriptions using FTP on a single server.
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
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1130800544.680463.133070@.z14g2000cwz.googlegr oups.com...
> Should this work? In production we have one server acting as the
> publisher/distributer, and then another as the subscriber.
> In the test environment, I'd like to have all of this on the same
> server. Will this work? I've run into what I hope is a minor gotcha
> (running the drop replication scripts created by enterprise manager
> doesn't work, exec sp_dropsubscription returns "The remote server is
> not defined as a subscription server").
> thanks for any hints.
> Sylvia
>