My Oracle Support Banner

Calling A Sql*Server Stored Procedure With Text Columns Causes Errors ORA-2055 and Ora-28511 (Doc ID 374989.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database Gateway for SQL Server - Version 9.2 to 10.2
Information in this document applies to any platform.
Oracle Transparent Gateway for Microsoft SQL Server - Version: 9.2 to 10.2

Checked for Relevancy on 30-Nov-2010


Symptoms

Using TG4MSQL to call a SQL*Server stored procedure that uses a table with a TEXT column gives errors ORA-2055 and ORA-28511.


The table in SQL*Server is created as -

create table KW_TAB_A (COL_A varchar(10),
COL_B text,
COL_C text
)

The stored procedure is in the format -

CREATE PROCEDURE dbo.kwsp2 @STR1 VARCHAR(10), @STR2 text, @STR3 text, @STR4
bigint OUT AS
INSERT KW_TAB_A VALUES (@STR1,@STR2,@STR3)
SELECT @STR4 = 1
GO

This can be called from SQL*Server successfully -

exec kwsp2 'TEST2','Long String 2','Longer string 2',null

 

Calling the procedure from Oracle causes the errors -

SQL> declare 
var1 varchar2(10); 
var4 number(10); 
ret integer; 
begin 
var1 := 'OraTest1'; 
var4 := null; 
"dbo"."kwsp2"@msql(var1,null,null,var4); 
dbms_output.put_line('Return value = '||var4); 
end; 
/
declare
*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from MSQL
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=tg4msql)))
ORA-06512: at line 8

 

 










Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.