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

Oracle 9i -> SQL Server 2005: Schema_option parameter when adding an article to a publication

First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.

I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.

I've tried to specify the schema_option parameter as "@.schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).

Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253

The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?

If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?

Thanks!
Best regards,

JB

Ok, now I've found out how to use more of those schema options together.

The default value for Oracle Publications (according to BOL) is 0x050D3. I've taken that value and subtracted the value 0x1000 (Replicates column-level collation.) which I think is causing the problem - that gives me a value of 0x40D2 which I've tried to use. Though I get the same error as stated above, so actually I'm not that very further...

There is a note to the "0x1000 Replicates column-level collation" schema option saying that "This option should be set for Oracle Publishers to enable case-sensitive comparisons.". Does that mean it's a required option (and is therefore causing the problem)? Because then I think I have a serious challenge here?

Jeppe

|||

Hi JB,

You can start trouble-shooting by checking the following:

1) Is the ITEMTRANSLATION table created at the subscriber?

2) Is there anything obviously amiss with the "CREATE TABLE" statement in ITEMTRANSLATION_2.sch? (Would be great if you can post it here so we can have a look.)

-Raymond

|||

Argh... me not paying enough attention before the morning caffeine kicks in :) You should change your schema option from 0x40D2 to 0x40D3 otherwise the create table statement will not be scripted.

-Raymond

|||

Thank you Raymond!

I think that solved the main part of the problem :) I still got a problem with the table itemtranslation, but now it's a primary key violation problem, and I think it's caused by a nvarchar column in the primary key constraint. I'll have to look deeper into that, before I'll bug you with that problem too :)

Jeppe

Oracle 9i -> SQL Server 2005: Schema_option parameter when adding an article to a publica

First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.

I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.

I've tried to specify the schema_option parameter as "@.schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).

Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253

The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?

If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?

Thanks!
Best regards,

JB

Ok, now I've found out how to use more of those schema options together.

The default value for Oracle Publications (according to BOL) is 0x050D3. I've taken that value and subtracted the value 0x1000 (Replicates column-level collation.) which I think is causing the problem - that gives me a value of 0x40D2 which I've tried to use. Though I get the same error as stated above, so actually I'm not that very further...

There is a note to the "0x1000 Replicates column-level collation" schema option saying that "This option should be set for Oracle Publishers to enable case-sensitive comparisons.". Does that mean it's a required option (and is therefore causing the problem)? Because then I think I have a serious challenge here?

Jeppe

|||

Hi JB,

You can start trouble-shooting by checking the following:

1) Is the ITEMTRANSLATION table created at the subscriber?

2) Is there anything obviously amiss with the "CREATE TABLE" statement in ITEMTRANSLATION_2.sch? (Would be great if you can post it here so we can have a look.)

-Raymond

|||

Argh... me not paying enough attention before the morning caffeine kicks in :) You should change your schema option from 0x40D2 to 0x40D3 otherwise the create table statement will not be scripted.

-Raymond

|||

Thank you Raymond!

I think that solved the main part of the problem :) I still got a problem with the table itemtranslation, but now it's a primary key violation problem, and I think it's caused by a nvarchar column in the primary key constraint. I'll have to look deeper into that, before I'll bug you with that problem too :)

Jeppe