March 6, 2009 at 6:25 am
I could manage to create the linked server from SQL 2000 to Oracle 9i like this:
EXEC sp_addlinkedserver
@server = 'Oracle',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'server:port/SID'
GO
EXEC sp_addlinkedsrvlogin Oracle, FALSE, sa, apps, apps
GO
When I run this (in order to test the connection) I get a list of the tables in Oracle
EXEC sp_tables_ex ORACLE
GO
The problem now is that now, when I try to run queries I get no data... I know this is because I need to set ORG_ID before trying to execute the queries...
I don't know (and couldn't find in the internet) how to set run this (which I execute in TOAD to before running queries in Oracle):
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('136')
END;
And the other thing I think I'm missing is to set NLS_LANGUAGE for the session
"ALTER SESSION SET NLS_LANGUAGE = 'LATIN AMERICAN SPANISH';"
How can I achieve these two things from a SQL2000 Stored Procedure??
Any help will be greatly appreciated...
JP
Juan P. Realini
Buenos Aires, Argentina
Web Developer, .Net Developer,
Windows Applications, SQL Server 2000
"A man's gotta do what a man's gotta do... that is, do all his wife says... "
May 4, 2009 at 4:05 pm
Since an Oracle package is nothing but a collection of storedprocs example next applies, please check this http://blogs.objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply