Have SQL Server 2k treat Oracle CHAR as VARCHAR

[mikebp]

Hi All,

I'm banging my head against the desk with this one. I have a fairly simple scenerio that I'm SURE has to be easily overcome.

I have two servers, and Oracle 8i server and an SQL Server 2k server. The Oracle server holds tables with some data I'm interested in, and the SQL Server has the Oracle server as a Linked Server. So far so good...

Here's my problem. The data on the Oracle machine is stored in CHAR columns, and when I query the linked server from SQL Server the data comes back with trailing spaces in the columns. I'd like the data to infact come back more resembling a VARCHAR column where the spacing / padding is actually removed. If I just connect using a tool like TOAD directly to the Oracle box, no probs. I see the data without the padding. However, it's coming out with the padding when I go through SQL Server (either as a pass-through query or a standard query).

So, my question is this. Short of me doing a CAST for every CHAR column coming out of the Oracle machine (and there are a LOT of columns that would make this rather difficult) how can I fool SQL Server to treat the CHAR values as VARCHAR values, and remove the padding.

Quite desperate for a solution for this, so any help would be greatly appreciated.

Many thanks in advance,

Mike BP

 

[JohnDTampaBay]

You could create a VIEW that references the Oracle data and include the CAST AS VARCHAR statements in the VIEW. Then query the VIEW instead of the linked Oracle table. Hopefully, there's an easier answer than this. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.