Problem with fixed-length column query from Oracle from SQL server

• muntasiriub

  SSCommitted

  Points: 1810

  More actions

  July 7, 2006 at 6:13 am

  #366630

  Hi,

  I am quering Oracle DB from SQL server as below:

  SELECT sysdate,a

  FROM OPENQUERY([Myserver.xp.COM],'

  select sysdate,to_char(''muntasir'') as a from dual')

  shows:

  Server: Msg 7347, Level 16, State 1, Line 1

  OLE DB provider 'MSDAORA' returned an unexpected data length for the fixed-length column '[MSDAORA].A'. The expected data length is 32, while the returned data length is 8.

  OLE DB error trace [Non-interface error:  Unexpected data length returned for the column:  ProviderName='MSDAORA', TableName='[MSDAORA]', ColumnName='A', ExpectedLength='32', ReturnedLength='8'].

  But ok with

  SELECT sysdate,'Muntasir' as a

  FROM OPENQUERY(Myserver.xp.COM],'

  select sysdate  from dual')

  What is the problem? Can any one please explain or help to solve it?

  Thanks in  advance

  
  
  ..Better Than Before...

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 7, 2006 at 6:42 am

  #647951

  What is to_char function?

  The Select from Dual statement is run on Oracle, not SQL. Thus can't column be used in a SQL function then, but first when returned to SQL Server. See code below.

   

  SELECT sysdate,

         to_char(xyz)

  FROM   OPENQUERY([Myserver.xp.COM], 'select sysdate, colB as xyz from dual')

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• muntasiriub

  SSCommitted

  Points: 1810

  More actions

  July 8, 2006 at 8:52 am

  #648109

  Enthusiast Thx 4 ur suggestion,

  But I think it's the problem with SQL server "OPENQUERY" Which try to map each constant column with length 32. It generates same error "[MSDAORA].A'. The expected data length is 32, while the returned data length is 8."

  For :

  SELECT  *

  FROM OPENQUERY(Myserver.xp.COM],'Muntasir' as a

  select sysdate  from dual')

  Where 'Muntasir' is constant lenght column return by oracle... which is not 32 char long....

  IS it....?

   

   

   

   

  
  
  ..Better Than Before...

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 9, 2006 at 2:36 pm

  #648131

  What you wrote in last post is not even proper syntax.    

  And why would you try to query Oracle for a constant string when you can do that in SQL for better performance?

  SELECT sysdate,

         'Muntasir' a,

         to_char(xyz) f

  FROM   OPENQUERY([Myserver.xp.COM], 'select sysdate, colB as xyz from dual')

  ---

  N 56°04'39.16"
  E 12°55'05.25"