Oracle info..

7 posts / 0 new
Last post
benzzon
benzzon's picture
Oracle info..
Oracle online documentation: http://tahiti.oracle.com/

Vid länkning till SQL Server (linked server) så konverteras datatypen "number" till "nvarchar(384)" ifall "number" datatypen har en precision > 38...

Vertyg för sql-query: "sqlplusw.exe"..
benzzon
benzzon's picture
.
Jämförelse mellan Oracle 10.2 och SQL Server 2005:
 
 
 
Datakoppling mot Oracle ifrån SQL Server sker indirekt via informationen som
finns i Oracle-filen "tnsnames.ora".
 
NLS_CHARACTERSET (tnsnames, CADQ1): "WE8ISO8859P1"
Vad är motsvarande i SQL Server.....?
 
 
Add Oracle linked server, "MSDAORA" kontra "OraOLEDB.Oracle"..: 
 
 
benzzon
benzzon's picture
.
The error-message below may be due to a recent installation of "Oracle-client-tools" on a SQL-server (for linking to Oracle..), this problem was solved by restarting the SQL-server...
 
 
Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDAORA" for linked server "LS_LEB_Prod" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "LS_LEB_Prod".

 

benzzon
benzzon's picture
.

Useful statement for listing tables that are accessible from a "server-alias".. (from MS SQL Server)

sp_tables_ex <server-alias>

(sample: sp_tables_ex LS_LEB_Prod)

 
 
benzzon
benzzon's picture
.

Jävligt irriterande skit, länkning från SQL2000/SQL2005 till Oracle 9.2.0.4.0:

 
SET xact_abort ON
GO
BEGIN DISTRIBUTED TRANSACTION
 SELECT * FROM OpenQuery(LS_LEB_Prod,'select * from FSK.IGR_DATA')
COMMIT TRAN
GO
 
ger felmeddelande:
 
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
 
 
Har bl.a testat: 
 
 
Löst 080325 på "lebhdoc1" via registry-justering och omstart!
(Eventuellt behövdes även "GRANT FORCE ANY TRANSACTION TO FSK")

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"  
"OracleOciLib"="oci.dll"
 
För Oracle 10 så ändras ovanstående "oraclient9.dll" till "oraclient10.dll" och "orasql9.dll" till "orasql10.dll". (testas ev 080930 hos kund.)
 
På följande länk står att Oracle 10g ej supportar "MSDAORA", har dock fått det att funka hos kund.
(som alternativ anges oracles egen provider "OraOLEDB.Oracle")
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1933804&SiteID=17
 
How to setup linked servers for SQL Server and Oracle 64 bit client:
 
 
benzzon
benzzon's picture
.

*** Oracle SQL-kommandon:

Lista aktuell Oracle-version: SELECT * FROM PRODUCT_COMPONENT_VERSION;

GRANT FORCE ANY TRANSACTION TO "FSK"; ...?

Two user accounts are automatically created with the database, Oracle 8,9:
SYS (default password: CHANGE_ON_INSTALL)
SYSTEM (default password: MANAGER)
Scott (default password: tiger)

Oracle 10g:
sys: d_syspw
system: d_systpw
 
 
List all tables in database: select * from all_tables order by table_name;
List users: select * from all_users order by username; 
benzzon
benzzon's picture
.
Oracle "materialized views".. Skulle dessa vyer möjligen kunna länkas
till SQL Server, och att man fick med sig eventuella index..?