Oracle linked server function return unexpected values

Question

Oracle linked server function return unexpected values

Hernán Rojas

Right there with Babe

Points: 774
More actions
November 10, 2009 at 12:03 pm

#227817

Hello everybody:
I have a linked server from SQL Server 2000 and SQL Server 2005 to Oracle 10g. Both installations are Win2K3 R2 x64, SQL Server 2000 SP4, SQL Server 2005 SP2 and have the Oracle client x64 installed correctly, in fact, I have tested the connection successfully and I have run a command like
select * from openquery(ORACLESERVER,'SELECT to_char(sysdate,''DD-MON-YY'') FROM dual').
However when I try to use the following sentence
select * from openquery(BAN2,'SELECT PK_SERVICES.TS(to_char(sysdate,''DD-MON-YY''),''USD'',''COP'') FROM dual')
I get an unexpected value in SQL Server 2005 (I get -2), while in SQL Server 2000 I get the right value.
The function PK_SERVICES.TS receives three character parameters. I have tried changing the sentence in many ways with no result. All tests show me that it seems to be a parameter problem... I mean oracle is not understanding what SQL Server 2005 is sending to.
Please help, does anyone have any idea about what could be happening??
Thanks in advance.
Hernan.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nice Marmot SSCrazy Points: 2278 More actions · Answer 1

Hi Hernan,

Are both the 2000 and 2005 SQL Servers using the same collation? I wonder if there is some sort of character string conversion happening. If you try -

select * from openquery(BAN2,'select ''SELECT PK_SERVICES.TS(to_char(sysdate,''DD-MON-YY''),''USD'',''COP'') FROM dual'' from dual')

- do you get the same query text echoed back from Oracle on both 2000 and 2005? HTH.

Hernán Rojas Right there with Babe Points: 774 More actions · Answer 2

Both SQL Servers have the same collations setting:

SQL Server 2000 SQL_Latin1_General_CP1_CI_AS

SQL Server 2005 SQL_Latin1_General_CP1_CI_AS

select * from openquery(BAN2,'select ''SELECT PK_SERVICES.TS(to_char(sysdate,''DD-MON-YY''),''USD'',''COP'') FROM dual'' from dual')

In SQL Server 2000 I get the right result, which is a currency conversion from USD to COP. While in SQL 2005 I get always -2. I have noticed that when I change the second parameter in SQL 2000, for example, from COP to ''COU'' or ''COY'' the function returns -2, the same I always get form SQL Server 2005.

abatej SSChasing Mays Points: 636 More actions · Answer 3

does the select query return the expected results when you run it directly on oracle using sqlplus?

Hernán Rojas Right there with Babe Points: 774 More actions · Answer 4

Hello, if I use the same TSN name, user/password information and SQLPlus from the SQL 2005 Server, I can get the right result.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

trace it

_____________________________________
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.