Openquery issue

3 Nov 2005 4:40 PM

jaylou

Hi All,
I have some peocedures that call stored procedures in Oracle from SQL Server
2000. the way I am doing this is by inserting into an Oracle table that has
a trigger that executes the procs.
I have been doing this for some time now, we needed to add a new proc in
Oracle, but the new table wont let me insert into it. The Oracle DBA granted
the same permissions as all the other tables I insert into. I can select
from it.
the code I use to insert is:
insert openquery(ALPDBD2, 'select * from newsys.web_endorse_ready where 1=0')
select distinct RECORDID,POLICYNO
from EN_ChgPol_Params
where recordID = @RecordID

I get the follwoing error:
Server: Msg 7343, Level 16, State 2, Line 13
OLE DB provider 'MSDAORA' could not INSERT INTO table '[MSDAORA]'. Unknown
provider error.
[OLE/DB provider returned message: Row cannot be located for updating. Some
values may have been changed since it was last read.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow
returned 0x80040e38: Unknown provider error.].

TIA,
Joe

3 Nov 2005 7:42 PM

John Bell

Hi

Maybe this is your problem

http://support.microsoft.com/default.aspx?scid=kb;en-us;258997

In a similar train of though you may need a primary key.

You may want to look at the following post to see how to call an oracle
procedure.

http://tinyurl.com/d3jb3

John

Show quote

"jaylou" wrote:

> Hi All,
> I have some peocedures that call stored procedures in Oracle from SQL Server
> 2000. the way I am doing this is by inserting into an Oracle table that has
> a trigger that executes the procs.
> I have been doing this for some time now, we needed to add a new proc in
> Oracle, but the new table wont let me insert into it. The Oracle DBA granted
> the same permissions as all the other tables I insert into. I can select
> from it.
> the code I use to insert is:
> insert openquery(ALPDBD2, 'select * from newsys.web_endorse_ready where 1=0')
> select distinct RECORDID,POLICYNO
> from EN_ChgPol_Params
> where recordID = @RecordID
>
> I get the follwoing error:
> Server: Msg 7343, Level 16, State 2, Line 13
> OLE DB provider 'MSDAORA' could not INSERT INTO table '[MSDAORA]'. Unknown
> provider error.
> [OLE/DB provider returned message: Row cannot be located for updating. Some
> values may have been changed since it was last read.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow
> returned 0x80040e38: Unknown provider error.].
>
> TIA,
> Joe

3 Nov 2005 7:57 PM

jaylou

Thanks I sent this article down to my Oracle DBA. he will look to see if
this is the issue. Hopefully this can resolve it, even though he said he
created this table the same as the 6 I am inserting into.

As far as the tinyurl I have been there and the procedure they say to use
does not work. I had MS support trying to get it to run as well. it was to
the point where they gave me my $$ back and said you can't run an Oracle Proc
from SQL server.
Their exact wording was:
SQL doesnâ€™t support to call Oracle remote stored procedure from SQL sever
through linked server.
Under Books Online ... 'External Data and Transact-SQL'
BOL says "Stored procedures are supported only against SQL Server data
sources."

Thanks Agin for the info,
Joe

3 Nov 2005 11:30 PM

John Bell

Hi

I know of people who have got this to work, therefore I can only think it is
a version thing. Because something is not supported it does not mean it won't
work, but the risk is in your court. If this is "exactly" the same as other
tables/systems you will need to look for differences, therefore check
configuration,versions etc. It could be something like case sensitivity,
collation....

John

Show quote

"jaylou" wrote:

> Thanks I sent this article down to my Oracle DBA. he will look to see if
> this is the issue. Hopefully this can resolve it, even though he said he
> created this table the same as the 6 I am inserting into.
>
> As far as the tinyurl I have been there and the procedure they say to use
> does not work. I had MS support trying to get it to run as well. it was to
> the point where they gave me my $$ back and said you can't run an Oracle Proc
> from SQL server.
> Their exact wording was:
> SQL doesnâ€™t support to call Oracle remote stored procedure from SQL sever
> through linked server.
> Under Books Online ... 'External Data and Transact-SQL'
> BOL says "Stored procedures are supported only against SQL Server data
> sources."
>
>
> Thanks Agin for the info,
> Joe
>