oracle publication

hi, i am trying to add an oracle publisher but i get the following
error
TITLE: Distributor Properties
Oracle server instance 'PS4289' cannot be enabled as a Publisher
because of the following error:
ADDITIONAL INFORMATION:
Unable to connect to Oracle database server 'PS4289' using the
Microsoft OLEDB provider MSDAORA. For addition information, see SQL
Server Error 21627 in Troubleshooting Oracle Publishers in SQL Server
Books Online. (Microsoft SQL Server, Error: 21627)
i do have an oracle 9i client installed on my machine and i can connect
to the oracle server through SQL PLUS.
any ideas?
i did find one difference in the requirements
my HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI shows
OracleOciLib = oct.dll
OracleSqlLib = SQLLib80.dll
OracleXalib = xa80.dll
while it requires
OracleOciLib = oci.dll
OracleSqlLib = orasql9.dll
OracleXaLib = oraclient9.dll
Udit Ghai wrote:
> hi, i am trying to add an oracle publisher but i get the following
> error
> TITLE: Distributor Properties
> --
> Oracle server instance 'PS4289' cannot be enabled as a Publisher
> because of the following error:
> --
> ADDITIONAL INFORMATION:
> Unable to connect to Oracle database server 'PS4289' using the
> Microsoft OLEDB provider MSDAORA. For addition information, see SQL
> Server Error 21627 in Troubleshooting Oracle Publishers in SQL Server
> Books Online. (Microsoft SQL Server, Error: 21627)
> i do have an oracle 9i client installed on my machine and i can connect
> to the oracle server through SQL PLUS.
> any ideas?
|||Sounds like you have not configured the oracle drivers correctly on your sql
server distributor. Ensure you can connect through the odbc drivers, and if
so, try to connect using the same servername, acccount and password in the
oracle publishing wizard.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Udit Ghai" <ghai.udit@.gmail.com> wrote in message
news:1163153354.699793.317120@.e3g2000cwe.googlegro ups.com...
> hi, i am trying to add an oracle publisher but i get the following
> error
> TITLE: Distributor Properties
> --
> Oracle server instance 'PS4289' cannot be enabled as a Publisher
> because of the following error:
> --
> ADDITIONAL INFORMATION:
> Unable to connect to Oracle database server 'PS4289' using the
> Microsoft OLEDB provider MSDAORA. For addition information, see SQL
> Server Error 21627 in Troubleshooting Oracle Publishers in SQL Server
> Books Online. (Microsoft SQL Server, Error: 21627)
> i do have an oracle 9i client installed on my machine and i can connect
> to the oracle server through SQL PLUS.
> any ideas?
>
|||suppose i have my TSN name as abc_server and the username and password
are
scott and tiger
then i use the server instance as abc_server and the username and
password as i have mentioned above and it does not connect to it and
sends the above error message.
then how do i connect to it. should i only mention the servername?
can u tell me which kind of connection string to use or a particular
driver if i also want it as a linked server?
Hilary Cotter wrote:[vbcol=seagreen]
> Sounds like you have not configured the oracle drivers correctly on your sql
> server distributor. Ensure you can connect through the odbc drivers, and if
> so, try to connect using the same servername, acccount and password in the
> oracle publishing wizard.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Udit Ghai" <ghai.udit@.gmail.com> wrote in message
> news:1163153354.699793.317120@.e3g2000cwe.googlegro ups.com...
|||Please can you post up the relevant bit of your TNSNames.ora file and
details of what you've entered in the ODBC settings dialogue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||If you newly installed the oracle client on the distributor make sure you
reboot the machine.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23EJNfNBHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Please can you post up the relevant bit of your TNSNames.ora file and
> details of what you've entered in the ODBC settings dialogue.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||thanks for your reply,
here is the TSNnames.ora entry
MEENU_PS4289.PERSISTENT.CO.IN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PS4289)(PORT = 1521))
)
(CONNECT_DATA =
(SID = meenu)
(SERVER = DEDICATED)
)
)
i didnt get the part about the ODBC settings. doesnt SQL SERVER use the
microsoft OLEDB provider MSDAORA in adding the oracle publisher
i use the following settings
server instance "meenu_PS4289"
"oracle standard authentication"
username "scott"
password "tiger"
do i have to set any other properties execpt these properties
Gopal Ashok[MSFT] wrote:[vbcol=seagreen]
> If you newly installed the oracle client on the distributor make sure you
> reboot the machine.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23%23EJNfNBHHA.4844@.TK2MSFTNGP02.phx.gbl...
|||i also created a naming service for the oacle database on my server by
the name meenu and tried it out on SQL*PLUS and it works fine and if i
create a linked server
EXEC sp_addlinkedserver @.server = N'meenu', @.srvproduct=N'Oracle',
@.provider=N' MSDAORA', @.datasrc=N'meenu'
GO
sp_addlinkedsrvlogin 'meenu', false, 'sa', 'scott', 'tiger'
and then if i query
select * from meenu..scott.aaaa;
it gives the following error
"Msg 7403, Level 16, State 1, Line 5
The OLE DB provider " MSDAORA" has not been registered."
Udit Ghai wrote:[vbcol=seagreen]
> thanks for your reply,
> here is the TSNnames.ora entry
> MEENU_PS4289.PERSISTENT.CO.IN =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = PS4289)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SID = meenu)
> (SERVER = DEDICATED)
> )
> )
> i didnt get the part about the ODBC settings. doesnt SQL SERVER use the
> microsoft OLEDB provider MSDAORA in adding the oracle publisher
> i use the following settings
> server instance "meenu_PS4289"
> "oracle standard authentication"
> username "scott"
> password "tiger"
> do i have to set any other properties execpt these properties
> Gopal Ashok[MSFT] wrote:
|||Please try using "MEENU_PS4289.PERSISTENT.CO.IN" as the server name in the
OLEDB name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||So you can connect from the SQL Server system to the Oracle System using
SQL*Plus? Just need to confirm that you arte not talking about the client
tools on the Oracle Server. If connection formm the SQL Server box is
successful, that removes the issue of firewalls at any rate.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com