March 12, 2008 at 4:55 pm
Hi
I m using SQL SERVER 2000 as my database. The System is divided into 2 sub system - INTERNAL SYSTEM and EXTERNAL SYSTEM.
Twisting part: We have OLD SYSTEM IN ORACLE 10G which is also working live.
Now Problem comes here.
We have architecture like
Oracle 10g Application
||
INTERNAL SYSTEM
||
EXTERNAL SYSTEM
Now in my INTERNAL System i've created 2 linked server. One oldb provider which connect to Oracle 10g Application
and 2nd which connect to EXTERNAL SYSTEM Server.
And in my EXTERNAL SYSTEM database i m trying to fetch database thru Internal system linkserver. but no success.
Then i tried to create one more linkserver in EXTERNAL which connect to ORACLE 10g Application server. But it giving me error like : " Error 7399 : OLEDB Provider "MSADORA' reported error'
Wht to do with this ?
I knw its quite confusing but if anebody get the idea what i mean to say then plz help to solve this weired problem
Thanks.
March 13, 2008 at 1:40 pm
We have architecture like
Oracle 10g Application
||
INTERNAL SYSTEM
||
EXTERNAL SYSTEM
1)Can Internal System fetch data from the Oracle 10g?
2)External System can fetch non-oracle data from Internal Sytem?
If both are true, there is likely a security-issue/login mismatch.
Any clue if the external system accesses more data?
March 13, 2008 at 8:14 pm
1)Can Internal System fetch data from the Oracle 10g?
2)External System can fetch non-oracle data from Internal Sytem?
If both are true, there is likely a security-issue/login mismatch.
Any clue if the external system accesses more data?
Thanks for your reply.
Yes Internal system can fetch data from Oracle 10g. On Internal System we have 2 linkserver.
1) which connect INTERNAL System to ORACLE 10g
2) Connect INTERNAL System to EXTERNAL SYSTEM.
But my External Server and Internal server both are on different machine. and now in my External System i m writing a procedure which call tables from INTERNAL Linkserver.
For E.g.
in EXTERNAL System i have 1 table
Table: Employee
And in ORACLE 10g i have 1 table called
Table: Dept
Now i m writing a query in External Server that
Select
e.EmpName,
e.EmpNumber,
e.Salary,
Temp.DepartmentName
From
(select * from OPENQUERY(XYZ,'Select DepartmentName From XYZ.dept') as LinkDb
Inner Join Empoyee e on e.deptNo = Temp.deptNo
Note: This is just Example. Main Query is quite big. If u want main query then i can provide u that also.
Now when i m executing above query it giving me Erro like
---------------------------
SQL Server Enterprise Manager
---------------------------
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
---------------------------
OK
---------------------------
Hope now u get clear idea.
On my EXTERNAL System I do not have any Linkserver. And if i am trying to Create Linkserver with ORACLE 10g databse server it giving me same above error message. I donot know how to come out from this..
March 14, 2008 at 1:19 pm
*I assume the query is run against the internal system because openquery requires a linked server connection.
*Strange that you repeat the server name in the openquery
I would expect
OPENQUERY(XYZ,'Select DepartmentName From dept')
instead of OPENQUERY(XYZ,'Select DepartmentName From XYZ.dept')
*Oracle uses a slightly different naming convention
*Check the securityconfiguration of the linked server connection
(always impersonate, use current context,...)
*Is the Oracle configured to use Active Directory/LDAP or do you use a standard login for the application?
I haven't an Oracle linked server handy at the moment, have you checked the following articles?
Good luck
March 17, 2008 at 10:16 am
Hey Jo,
Thanks for your support.
I read that article.I tried to connect with DTS but not able to connect with DTS also. I dont know why?
See below is original scenario:
In INTERNAL System below are Linkserver:
1) EOPI - Which connects Oracle 10g using "Microsoft OLEDB Provider for Oracle"
2) EXTLink - Which connects External System using "Microsoft OLEDB Provider for SQL Server"
In External System below are LinkServer:
1) INTLink - Which connects INTERNAL System using "Microsoft OLEDB Provider for SQL Server"
Note: All SQL Database uses SQL Server 2000.
Below procedure is return in EXTERNAL System.
***************
ALTER PROCEDURE spGetNewExhibitJSubContractorsDetails
@contractid VARCHAR(25),
@transdate DATETIME
AS
SET NOCOUNT ON
BEGIN
DECLARE @SQL VARCHAR(8000)
SET @SQL =
'SELECT DISTINCT TOP 1
0 AS Pk_prime_contractor_id ,
tcv.Pk_Contract_vendor_Id ,
Temp.L_VENDORID ,
Temp.COMPANYNAME ,
Temp.CONTRACTNO ,
Temp.BRIEFDESCRIPTIONOFWORK AS contract_title ,
Temp.FEDERALTAXID ,
Temp.ORIGINALAWARDAMOUNT ,
Temp.GOAL_PRCNT AS "Total_original_contracted_ldbe_participation" ,
''CURRENT_CONTRACT_AMOUNT'' ,
SUM(Temp.AMOUNTTOVENDOR) AS CURRENTCONTRACTAMOUNT ,
SUM(CONVERT(FLOAT,tejsc.LDBE_percentage)) AS "LDBE_ACTUAL" ,
Temp.CONTRACTID ,
Temp.TRANSDATE ,
tejh.prime_contractor_invoice_amount ,
tejh.total_current_amount_of_prime_contract ,
tejh.payments_received ,
tejh.current_scheduled_ldbe_participation ,
tejh.retainage_withheld ,
tejh.invoice_number ,
CONVERT(VARCHAR,tejh.submitted_date,101) AS submitted_date,
tejh.title ,
CONVERT(VARCHAR, tejh.signature_date,101) AS signature_date,
tejh.signature ,
''Save'' AS is_finalized
FROM
(SELECT * FROM OPENQUERY(EOPI, ''SELECT LV.L_VENDORID ,
VENDOR_TYPE ,
LV.COMPANYNAME ,
C.CONTRACTNO ,
C.BRIEFDESCRIPTIONOFWORK ,
LV.FEDERALTAXID ,
C.ORIGINALAWARDAMOUNT ,
CG.GOAL_PRCNT ,
CL.AMOUNTTOVENDOR,
C.CONTRACTID ,
CL.TRANSDATE
FROM EOP.CONTRACT C
INNER JOIN EOP.C_PRIMESUB CP ON C.CONTRACTID = CP.CONTRACTID
INNER JOIN EOP.L_VENDOR LV ON CP.L_VENDORID = LV.L_VENDORID
LEFT JOIN EOP.C_GOAL CG ON C.CONTRACTID = CG.CONTRACTID
AND CG.CERT_CODE = ''''LDBE''''
INNER JOIN EOP.C_LEDGER CL ON C.CONTRACTID = CL.CONTRACTID
WHERE CL.CONTRACTID = '+@contractid+''' ) ) AS Temp
INNER JOIN tbl_contracts tc
ON Temp.CONTRACTID = tc.Fk_eop1_contract_id
INNER JOIN tbl_contract_vendors tcv ON Temp.L_VENDORID = tcv.Fk_EOP1_vendor_Id
AND Pk_contract_id = tcv.Fk_contract_id
LEFT OUTER JOIN tbl_exhibit_J_header teJh ON tcv.Pk_Contract_vendor_Id = teJh.Fk_Contract_vendor_Id
LEFT OUTER JOIN tbl_exhibit_J_sub_details tejsc ON Pk_prime_contractor_id = tejsc.Fk_prime_contractor_id
WHERE Temp.CONTRACTID = '+@contractid+'
AND Temp.VENDOR_TYPE = ''P''
GROUP BY Temp.TRANSDATE, tejh.Pk_prime_contractor_id,
tcv.Pk_Contract_vendor_Id ,
Temp.L_VENDORID ,
Temp.COMPANYNAME ,
Temp.CONTRACTNO ,
Temp.BRIEFDESCRIPTIONOFWORK ,
Temp.FEDERALTAXID ,
Temp.ORIGINALAWARDAMOUNT ,
Temp.CONTRACTID ,
Temp.GOAL_PRCNT ,
tejh.prime_contractor_invoice_amount ,
tejh.total_current_amount_of_prime_contract ,
tejh.payments_received ,
tejh.current_scheduled_ldbe_participation ,
tejh.retainage_withheld ,
tejh.invoice_number ,
tejh.submitted_date ,
tejh.title ,
tejh.signature_date ,
tejh.signature ,
tejh.is_finalized
ORDER BY Pk_prime_contractor_id, tejh.submitted_date DESC'
print @sql
EXEC(@SQL)
END
*******************
Now when i m executing this procedure it giving me error like :
---------------------------
SQL Server Enterprise Manager
---------------------------
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
---------------------------
OK
---------------------------
I am not able to solve this 🙁
I have to solve this i m new with this topic.. plz help me .....
March 17, 2008 at 1:24 pm
I'll try to answer this on Tuesday.
Does a basic query works?
It could be that distributed transactions ain't configured correctly.
(Are the server machines windows 2003?)
*spGetNewExhibitJSubContractorsDetails is located on the external system (returns = output to external or called at external from another client?)
March 17, 2008 at 1:37 pm
I'll try to answer this on Tuesday.
Does a basic query works?
It could be that distributed transactions ain't configured correctly.
(Are the server machines windows 2003?)
*spGetNewExhibitJSubContractorsDetails is located on the external system (returns = output to external or called at external from another client?)
Thanks for your Help.
Yes, External System Database is on Windows 2003. Application on Windows XP.
I dont know anything else. I think problem is in FROM clause i.e OPENQuery. I am not sure that its called properly or not. This is first time for me to call OpenQuery.
March 17, 2008 at 2:00 pm
hey first of all can you please let me know physically and logically is it possible to connect Oracle server to sql server via another sql server ?
March 18, 2008 at 6:36 am
As far I know linked servers don't expose sublinked servers.
Linkedserver1 with sublinkedserver3
Server 2 can't query Linkedserver1.Sublinkedserver3.dbo.table1.
Linked server1 could create a view MyView based on sublinkedserver3.dbo.table1
March 18, 2008 at 12:59 pm
Yes i think u r right.
So do u have any other solution to do same thing? View is not reliable option as i have so many tables in the system. So i can not write view for each and every table as per the current situation.
If u have any other idea then plz let me know
Thanks
March 19, 2008 at 12:41 am
You may need to upgrade to a more recent version of the Oracle drivers, MSDORA was written to support Oracle 8, you may well have run into a compatability issue - I know Oracle typically supports +- 2 versions... maybe except when it's Tuesday.
You may want to check Oracle support forums for simliar issues in regards to MSDORA.
March 20, 2008 at 8:17 am
You know what i did.. i tried to link with Oracle provider and trying to join all the server. let's see i m going right way or not.
If you have anymore suggestion then plz let me know
Thanks you.
March 21, 2008 at 9:37 am
Thanks jo,
I am working on your View option. I think that's the very good idea. coz after doing all this i m just wasting my time neither i resolved that error nor i m able to explain whole idea.
So i m trying you View idea. From this i got another idea to write procedure into Internal Server and just call that procedure into External Server.
I don't know i m going on right way or not coz i haven't implemented yet but hope it will works.
Once again Jo thank you.
If you have another idea which saves m efforts and time then plz let me know before i start implementation.It would be appreciated.:)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply