LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this?
For any kind of information... many thanks
klaus
Hi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this?
>
> For any kind of information... many thanks
>
> klaus
>
>
>
|||Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas

"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>

LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>

LINKED SERVER Problems

Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>

Linked Server

I have an oracel linked server that I use openqueryset statements to read the oracle tables.

However, I want to update some data in a couple of these oracle tables. The linked server is setup using a readonly user. I'd like to be able to call an Oracle Set Role to grant me update capability and then perform the update.

Can anyone help me out with some possibilities?

See SQL Server 2005 Books Online

OPENQUERY (Transact-SQL)

This is a KB error article but it has useful information on using OPENQUERY

http://support.microsoft.com/kb/q270119/

|||

Well... does not really apply. What I am thinking about doing is calling an oracle stored procedure that grants the linked server account update privledges on the table. Then submit an openquery statement to do the update.

However, I am not sure if the privledge granted from the prcale stored procedure will still be in effect when the second openquery statement is issued.

The reason why I am doing this is for security reasons to not allow a linked server "default" update privledges on the Oracle database.

|||

In case anyone needs to do this... or something similar... I have figured it out:

On Oracle server, create the Oracle Package and Procedure:

create or replace
PACKAGE BODY Call_SEC_ROLE_Package
AS
PROCEDURE sec_roles
(ReturnVal OUT SqlReturnTbl)
is
begin
dbms_session.set_role('apply_security identified by secure_pass');
ReturnVal(1) := '1';
end sec_roles;
end Call_SEC_ROLE_Package;

On SQL Server create your linked server and then call the Oracle stored procedure by:

SELECT *
FROM OPENQUERY
(LinkServerName, '{CALL Call_SEC_ROLE_Package.sec_roles({resultset 1, ReturnVal})}')
go