Linked server to RDB (oracle 7x)
-
When I create a system dsn, I can use a query like :
SELECT a.*
FROM OPENROWSET('MSDASQL',
'dsn=OracleRdbTest1;UID=myuser;Pwd=mypass;',
'select * from employees' ) AS a
It works on SQL2005 EE + sp2 + latest cumulative hotfix.
Question : How can I define this to be a linked server using the system-dsn ??
If I use
EXEC master.dbo.sp_addlinkedserver @server = N'JOBI', @srvproduct=N'SQLNCLI_ODBC', @provider=N'SQLNCLI_ODBC', @datasrc=N'OracleRdbTest1', @provstr=N'UID=myuser;Pwd=mypass;'
it says sqlncli_odbc is not registered ?????
(all is running serverside at the console)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Go to Management Studio, Server Objects then Linked server there are two Oracle defined for 7 I think you will need the OraOLEDB.Oracle because you know it is too old. Right click and go to its properties for what you are pass with that connection. Hope this helps.
Kind regards,
Gift Peddie -
Thanks for the reply.
I've tried that, but with no good results.
Now I would want to connect using the system dsn.
EXEC
master.dbo.sp_addlinkedserver @server = N'ALZDBA', @srvproduct=N'Any', @provider=N'MSDASQL', @provstr=N';Password=mypassword;Persist Security Info=True;User ID=myuser;Data Source=OracleRdbTest1;'
If I user the object explorer rightclick the linked server and click o "test connection" it states :
... data source name not found or no default driver specified ... error 7303 ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
maybe eureka
With this linked server definition, the connectiontest succeeds ...
it is a start ...
EXEC
master.dbo.sp_addlinkedserver @server = N'JOBI', @srvproduct=N'Any', @provider=N'MSDASQL', @provstr=N';DRIVER={Oracle RDB Driver};SERVER=ros;UID=myuser;PWD=mypwd;SVR=ros;CLS=testdvlp;XPT=2;CSO=2;DBA=W;DSO=0;TLL=;TLO=0;DATABASE= '
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
right click the linked server registration and update the Security to user/pass (with just read access if using it for Apps) and define the starting db. After that you can runs queries like this:
select * from LS_myserver..defaultDB.mytable
Sometimes even the UPDATE with work!!!...
-
tried that resulting in :
One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)
Query
select *
from alzdba..defaultDB.employees
Results in ...
OLE DB provider "MSDASQL" for linked server "alzdba" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "alzdba" returned message "[Oracle][ODBC][Rdb]%RDB-F-SYS_REQUEST, error from system services request
-RDMS-F-FILACCERR, error creating run-unit journal file DISK7:[RDBRUJ]MF_PERSONNEL$0002452F58D0.RUJ;
-RMS-E-PRV, insufficient privilege or file protection violation".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "alzdba". The provider supports the interface, but returns a failure code when it is used.
Query
SELECT
a.*
FROM
OPENROWSET('MSDASQL','dsn=OracleRdbTest1;UID=myuser;Pwd=mypwd;',
'select * from employees' ) AS a
returns the correct results, but I want to avoid OPENROWSET queries !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This linked server definitions is working !
EXEC sp_addlinkedserver 'RDBSvr',
'Oracle RDB Driver',
'MSDASQL',
'OracleRdbTest1' -- system dsn with all the nifty details
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RDBSvr',@useself=N'False',@locallogin=NULL,@rmtuser=N'myuser',@rmtpassword='mypwd'
GO
SELECT *
FROM OPENQUERY(RDBSvr, 'SELECT * FROM employees')
GO
SELECT *
FROM OPENQUERY(RDBSvr, 'select * from employees
where BIRTHDAY between ''1-jun-1950'' and ''2-jun-2007''
order by Birthday
')
Returning the correct results !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Many thanks !
It's the only way I found to update RDB via SQLSERVER
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply