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 configuration problem

I am trying to configure a MS SQL Server 2000 named \\MSSQL_NEW as a
publisher but I keep getting this
error:
SQL Server Enterprise Manager can not configure 'MSSQL_NEW' as the
distributor for 'MSSQL_NEW'
Error 18483:
Could not connect to server 'MSSQL_NEW' because 'distributor_admin' is not
defined as a remote login at
the server.
I've verified that 'distributor_admin' exists and has sysadmin rights on the
server (MSSQL_NEW).
I noticed that when I execute, 'select @.@.servername', in QA, I get,
'MSSQL_OLD'. But my machine name is, 'MSSQL_NEW'
and name registered in EM is 'MSSQL_NEW'. Tried replacing the registration
'MSSQL_NEW' to 'MSSQL_OLD' but I get a
server does not exist or access denied error.
Will updating @.@.servername to 'MSSQL_NEW' solve my problem? How?
Any comments or suggestions will be highly appreciated. Thanks in advance.
...
Carlo,
please try this...
Use Master
go
Sp_DropServer 'MSSQL_OLD'
GO
Use Master
go
Sp_Addserver 'MSSQL_NEW', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is a tricky problem to solve.
I normally solve it by issuing a
sp_adddistributor and an sp_adddistributiondb procs.
These should work. Then when you go to replicate it will bomb. So disable
replication, and then re-enable it. It should work this time.
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
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:77815079-C1A6-477C-9794-391828479E38@.microsoft.com...
> I am trying to configure a MS SQL Server 2000 named \\MSSQL_NEW as a
> publisher but I keep getting this
> error:
> SQL Server Enterprise Manager can not configure 'MSSQL_NEW' as the
> distributor for 'MSSQL_NEW'
> Error 18483:
> Could not connect to server 'MSSQL_NEW' because 'distributor_admin' is not
> defined as a remote login at
> the server.
> I've verified that 'distributor_admin' exists and has sysadmin rights on
the
> server (MSSQL_NEW).
> I noticed that when I execute, 'select @.@.servername', in QA, I get,
> 'MSSQL_OLD'. But my machine name is, 'MSSQL_NEW'
> and name registered in EM is 'MSSQL_NEW'. Tried replacing the
registration
> 'MSSQL_NEW' to 'MSSQL_OLD' but I get a
> server does not exist or access denied error.
> Will updating @.@.servername to 'MSSQL_NEW' solve my problem? How?
> Any comments or suggestions will be highly appreciated. Thanks in
advance.
> --
> ...

public permissions

I have a little problem.
I'm trying to configure permissions on sql server 2000. I have a user
"intranet" wich has permissions on a couple of tables and a stored
procedures. When I set the permissions from "public" (usergroup? can't find
it) to deny, intranet can't acces the tables. When I set no permissions on
public, intranet can acces the tables en SP's. Can somebody explain how it
comes that intranet can's acces these tables while the users permissions are
set to permit?
Kind regards,
Bert
(when replying to me please remove the antispam-part in the emailaddress)Hello, Janssen!
You shouldn't really be granting permissions to public unless you intend
everybody in the DB to have those permissions.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Kind regards,
: Bert
-- Microsoft Outlook Express 5.50.4133.2400|||Every user is by default member of the role (that is what it's called in SQL
Server) 'public'. The SQL Server permissions system works in such a way that
you only have permission on a table, view or stored procedure if you have
been granted the permission directly or via one of the roles you are a
member of, _and_ it is not denied to you or one of the roles you are a
member of.
In your situation that means that members of the public role do not have
permissions to any tables if there are no permisions explicitly set (unless
they are granted to them directly or via another role), but denying a
permission to the public role will deny that permission to anyone who is a
member of the public role (= all the users in the database), regardless of
the permissions they have directly or via another role.
hth
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Janssen" <legedoosantispam@.hotmail.com> wrote in message
news:be1dqh$6783$1@.ID-200327.news.dfncis.de...
> I have a little problem.
> I'm trying to configure permissions on sql server 2000. I have a user
> "intranet" wich has permissions on a couple of tables and a stored
> procedures. When I set the permissions from "public" (usergroup? can't
find
> it) to deny, intranet can't acces the tables. When I set no permissions on
> public, intranet can acces the tables en SP's. Can somebody explain how it
> comes that intranet can's acces these tables while the users permissions
are
> set to permit?
>
> Kind regards,
> Bert
> (when replying to me please remove the antispam-part in the emailaddress)
>
>
>|||Thanx!
"Jacco Schalkwijk" wrote
> Every user is by default member of the role (that is what it's called in
SQL
> Server) 'public'. The SQL Server permissions system works in such a way
that
> you only have permission on a table, view or stored procedure if you have
> been granted the permission directly or via one of the roles you are a
> member of, _and_ it is not denied to you or one of the roles you are a
> member of.
> In your situation that means that members of the public role do not have
> permissions to any tables if there are no permisions explicitly set
(unless
> they are granted to them directly or via another role), but denying a
> permission to the public role will deny that permission to anyone who is a
> member of the public role (= all the users in the database), regardless of
> the permissions they have directly or via another role.
> hth
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>