Home » RDBMS Server » Networking and Gateways » nText columns not showing up in SELECT (11g Release 2 Windows)
nText columns not showing up in SELECT [message #592461] Thu, 08 August 2013 11:04 Go to next message
Duane
Messages: 380
Registered: December 2002
Senior Member
Our DBA set up a Heterogeneous Service to one of our MS SQL Servers and we can use a DB Link to SELECT a table from the SQL Database. This works just fine but the problem is any nText column on the SQL Database Table doesn't appear. All the columns are shown EXCEPT the nText columns.

Has anyone seen this situation before? Does anyone know how to correct this? We need the ability to select the nText columns also.
Re: nText columns not showing up in SELECT [message #592462 is a reply to message #592461] Thu, 08 August 2013 11:24 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have run into that problem. In my case FreeTDS/UnixODBC wasn't able to handle nText, similar to the problem discussed here.

My workaround was that I created a view on the SQLServer side where I casted the nText to varchar, and did the select on the view.
Re: nText columns not showing up in SELECT [message #593140 is a reply to message #592462] Wed, 14 August 2013 09:32 Go to previous message
Duane
Messages: 380
Registered: December 2002
Senior Member
I created a view within MSSQL and did a select from Oracle on that view. I was able to select the nText column but Oracle shows the first row for the nText for all records. That's not right.

Any ideas on why Oracle would duplicate the nText for the first row on MSSQL for all records? The DBA who created the link told me that he used the Oracle Data Direct driver for the connection.

CREATE VIEW Essay_Evaluation_VW
AS
  Select [ID],
         [Evaluator],
         [StudentID],
         [EssayID],
         [TopicID],
         [PromptID],
         cast(Essay as varchar(max)) Essay 
    from [XX-XXXXX].[dbo].[XXXX_XXXXX];



select * from Essay_Evaluation_VW@mssql_xxxxx

This produces:

111 ..... Some Long Text
222 ..... Some Long Text
333 ..... Some Long Text
444 ..... Some Long Text
555 ..... Some Long Text
Previous Topic: Connection fail two database with identical sid name on different hosts
Next Topic: JDBC connection error
Goto Forum:
  


Current Time: Sat Apr 19 08:34:22 CDT 2014

Total time taken to generate the page: 0.08506 seconds