Very Computer

Dear all.

Have created a linked server from SQL Server 2000 to
Informix using ODBC Data Source and it is fine.

But, I'm not able to read a particualr field or when I
include that particular field in my SELECT statement it
popus with following error messages. The data type of that
particular field in Informix is 'DateTime Hour to Minute'

The error message is:

[Microsoft][ODBC Server Driver][SQL Server] Error
converting data type DBTYPE_DBTIMESTAMP to datetime.

I tried with convert and still it doesn't work. If I
exclud that particular field from my  SELECT statement it
works well!. How can I read such datatype field from SQL
Server.

Any of tips and advise shall highly be appreciated.

Thanks in advance.

B'regards,

Ahmed Jewahar

    Create a view in Informix that converts the data type to one of the
standard ones / what is supported by SQL Server? You can then query the view
instead. So you would do something like:

-- Informix side
CREATE VIEW ExportView
AS
SELECT CAST( t.DateCol AS varchar ) AS DateCol, ....
  FROM tbl AS t;

-- SQL Server side
SELECT * FROM Informix..User.ExportView AS e

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Hi,

I check with out Informix DBA and have been told that there is no function available in Informix similar to CAST or CONVERT.

I tried the same Informix table from MS-Acess with Link Table method using the same ODBC DSN and succeded.

I'm really stuck with my project and really appreciate any of your tips and help to resolve this problem.

Thanks in advance.

B'regards,
Ahmed Jewahar

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Quote:>> I check with out Informix DBA and have been told that there is no
function available
>> in Informix similar to CAST or CONVERT.

    That sounds very strange to me. Informix is an enterprise-level RDBMS
like SQL Server & it will have some form of a convert function. The CAST is
ANSI SQL version. And CONVERT is T-SQL specific. There must be some other
function like that. For instance, in Oracle, you can do TO_CHAR.
    And BTW, what provider are you using for the linked server. Are you
going through the same ODBC driver & the MSDASQL provider (OLEDB Provider
for ODBC Drivers)? I believe that the problem is really the OLEDB provider -
it probably cannot consume this column data type from the ODBC driver since
it works fine if you use ODBC directly.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Hi,

Thanks for your reply.

I'll defenitly check with our Informix DBA regarding the CONVERT functions availabe in Informix.

I'm using OLE DB Provide for ODBC Driver for linking the server and using the ODBC DSN name as Data Source. Created ODBC DSN with "Informix 3.30 32 bit" Driver (version: 3.30.00.10137, Informix Software Inc.)

I Would like to explain one more incident wich I had with VB 6.0 for your kind referece.

I opend an ADO connection with same ODBC DSN in my VB. Opened a record set with a "SELECT *from Table1" (this table contain that particulat DateTime field). I could pass all the field's values into an array except that particular "Time Field".

After failing of above task, I palced a MSHflex grid and assigned above recorset as Data Souce of the Grid. I got all the values in the grid including that 'Time field' without any errors!. So, what I understand from this there is some type of converion working well with that particular  'time field'.

Sorry for bothering u much.

Looking forward to hearing from you.

Thanks and regards,
Ahmed Jewahar

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!