Can't read from Linked Server-

Can't read from Linked Server-

Post by Ahmed Jewaha » Mon, 29 Oct 2001 01:20:41



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

 
 
 

Can't read from Linked Server-

Post by Umachandar Jayachandra » Mon, 29 Oct 2001 23:22:40


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

 
 
 

Can't read from Linked Server-

Post by Ahmed Jewaha » Thu, 01 Nov 2001 01:16:32


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!

 
 
 

Can't read from Linked Server-

Post by Umachandar Jayachandra » Thu, 01 Nov 2001 04:53:37


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

 
 
 

Can't read from Linked Server-

Post by Ahmed Jewaha » Thu, 01 Nov 2001 16:43:40


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!

 
 
 

1. Can't read from Linked Server table

Dear All,

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

The problem is I'm not able to read one data field from
linked server's table. When I include that specific data
filed in my SELECT statement it popups with following
error message. The datatype of that particular field in
Informix table is 'DateTime Hour to Minute'

[Microsoft][ODBC SQL Server Driver][SQL Server] Error in
Converting data type DBTYPE_DBTIMESTAMP to Datetime.

If I exculde that partcual filed from the SELECT statement
it will work. I tried with convert but it doesnt work.

Hope that someone can really help me on this.

Any helps/tips shall highly be appreciated.

Thanks & regards,
Ahmed

2. Foxpro 2.6 win

3. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

4. Backup Database From NT Side

5. Error creating linked server using 'MSIDXS'

6. Q: Foxpro Files & VB

7. Linked Server - Invalid user '\'

8. WHERE NOT ...

9. LINKED SERVER (Login Failed For User '\')

10. Losing 'Linked server' connection to Oracle

11. 'Driver not capable' in Linked Server

12. LINKED server: SQL Server doesn't exist or Access denied

13. Can't connect linked server to index server on seperate machines