Call MSSQL 2005 SP from MSAccess (MSSQL is Linked to Oracle Server)

Seth Brundle

I'm working on something that is copying data from Oracle 9i into an MSSQL 2005 DB through a stored procedure (I have linked Oracle into my MSSQL server). The SP does a number of things including the following:

SET @strSQL = 'INSERT INTO [' + @Table_Name + '] SELECT * FROM OPENQUERY(Oracle_DSN_Name, ''SELECT * FROM Oracle_Table_Name.' + @Table_Name + ''')'
--PRINT @strSQL
EXEC (@strSQL)

The SP works perfectly from the MSSQL Mgmt Studio but if I try and execute it from MS Access 2007 (my frontend) using either a pass through query or via VBA code, I get an ODBC call failed error containing the following:


This is using a separate PC for MS Access and separate servers for MSSQL and Oracle. I'm using a DSN on the MSSQL Server to connect to Oracle and both another DSN and code on the PC to hook up to MSSQL.
I'm using NT authentication to get into MSSQL with the same login on both the PC and the MSSQL Server.

If I remove the exec code above, the SP works fine from MS Access (but there is no other interaction with oracle within this SP).

Looking at sp_helplinkedsrvlogin, my Oracle server mapping appears to look fine.

Its obviously some form of permissions issue but has anyone any idea on how I can resolve it?

Seth Brundle

I came across a potential solution for this: to check the Allow InProcess option in Linked Servers > Providers within MSSQL Mgmt Studio but no joy
Anyone able to shed some light on this?

Tom Dunne

Have you tried this? Obviously make a backup of your SQLNET.ORA first. It is read each time a new connection is made, so the change should be immediate, if it works.

That Burleson guy has saved me on so many occasions.

Dr.Silly

Hi,

Tom's solution works, also on the OTN website forum.

http://forums.oracle.com/forums/thread.jspa?threadID=332525

Cheers

Seth Brundle

Thanks a million, although (I won't know for sure until tomorrow but) I'd say that the Oracle DB Server is out of limits.

However, looking at the thread, why would that allow MSSQL to view data within Oracle but not MS Access via MSSQL?

Tom Dunne

kbannon wrote: »

Thanks a million, although (I won't know for sure until tomorrow but) I'd say that the Oracle DB Server is out of limits.

You have a local sqlnet.ora on whatever machine is the client for the Oracle database - try changing that.

kbannon wrote: »

However, looking at the thread, why would that allow MSSQL to view data within Oracle but not MS Access via MSSQL?

Not sure I am following you?

Seth Brundle

Thanks Tom.

The stored procedure in my MSSQL DB executes completely fine and manages to select all the data required in the (linked) Oracle DB and store a copy of it in MSSQL.

My MSAccess frontend is linked into MSSQL. I have also managed to link the MSSQL stored procedure into Access but when I try to run it I get the error above.

If there was something up with the sqlnet.ora would that not cause the error to occur all of the time regardless of whether its executed within MSSQL Management Studio or MSAccess?

Tom Dunne

kbannon wrote: »

If there was something up with the sqlnet.ora would that not cause the error to occur all of the time regardless of whether its executed within MSSQL Management Studio or MSAccess?

Let me see if I've got this right:

MSSQL -> Oracle : run stored procedure, works fine

Access -> MSSQL -> Oracle : does not work, running the same stored procedure?

A couple of things spring to mind:

1: Is it the same account used to log in from Access->SQL as is being used from SQL->Oracle?

2: There is an option in Oracle client to turn on tracing. It might give a better idea of exactly where the problem is occurring. A quick Google shows this.

3: Check event logs, both SQL and Operating System. Might give something.

Seth Brundle

Tom Dunne wrote: »

Let me see if I've got this right:

MSSQL -> Oracle : run stored procedure, works fine

Access -> MSSQL -> Oracle : does not work, running the same stored procedure?

Thats correct!

Tom Dunne wrote: »

A couple of things spring to mind:

1: Is it the same account used to log in from Access->SQL as is being used from SQL->Oracle?

2: There is an option in Oracle client to turn on tracing. It might give a better idea of exactly where the problem is occurring. A quick Google shows this.

3: Check event logs, both SQL and Operating System. Might give something.

1. yup
2. nope MSSQL is logged into using windows authentication and the same credentials are used to log into the compyter running MSAccess. The oracle server has a different username/password.
2. cheers
3. Will do

Tom Dunne

kbannon wrote: »

Thats correct!

1. yup
2. nope MSSQL is logged into using windows authentication and the same credentials are used to log into the compyter running MSAccess.

You have two 2's.

Ok, here's what I think is up:

MSSQL-> Oracle : Account #1
Access-> MSSQL : Account #2

What you need to do is either
(a) only use account #1 (don't think that is viable in your case)
(b) give Account #2 the same privileges as Account #1. Allow Account #2 access to the Oracle database via OS Authentication. Obviously, there are a lot of security implications in doing this, I can give you a few pointers.

To confirm (b) is working, on the machine where Access is, run SQLPLUS / at the command line - you should not need to enter username/password. Note that this assumes your ORACLE_SID environment variable is set to the Oracle database.

I don't have any Oracle database running here at the moment, so I am doing all of this from memory.

Seth Brundle

Thanks Tom.
I can't go making changes to the Oracle DB and the users will each be of different userlevels, etc. and I have no access to Active Directory.

Thinking about it today, I might set it up differently.
Currently the system copies the data from Oracle into MSSQL every two hours with a manual copy process available (which is where the issue is occuring).
Instead of having MSSQL copy the data across that way, I will set up a copy of all the tables I need (with an _temp suffix) and copy the data from Oracle every 5 or 10 minutes (there isn't much data) and the existing stored proc will copy from these - it won't be live data but its as good as!