locked
SQL Linked Server to ODBC, OpenQuery Data Gets truncated RRS feed

  • Question


  • I am using Simple Query

    select * from openquery(test,'SELECT * FROM Sample/hstrh3.current')

    To retrieve data from ODBC Linked Server.



    I am facing 2 Issues,



    1) When i run select * from openquery(test,'SELECT * FROM Sample/hstrh3.current')



    I get Catastrophic Failure Error. But if i select only 3 Columns

    select * from openquery(test,'SELECT Time,Source,Value FROM Sample/hstrh3.current')

    I get results but data gets truncated in Source Column. Source column defined as VARCHAR(MAX). My data length in Source column never exceeds 32 Chars..then also i am having data cutt off randomely.



    Please advise what should be done to avoid this issue.
    Wednesday, August 10, 2016 12:16 PM

Answers

  • Hi kalpesh23,

    Could you please share more information to analysis? We want to know which edition of ODBC did you use and the information of linked server, for example, which data source do you link to. We also want to know how many lows of data you query.

    Firstly, for the first question, when we execute the query select * from, we will get all the rows form all the tables that match the conditions. And it will waste the system resource, if you have a huge amount of rows, it can cause Catastrophic Failure.

    Secondly, please test the following items and give us the result:

    1. Create a new table, then insert the data got from openquery(test,'SELECT Time,Source,Value FROM Sample/hstrh3.current') to this table to find if the data been cut off the same way.
    2. Use some other driver like ADO, OLEDB to try it again to find the result.

    Regards,
    Teige
    Thursday, August 11, 2016 9:02 AM

All replies

  • Hi kalpesh23,

    Could you please share more information to analysis? We want to know which edition of ODBC did you use and the information of linked server, for example, which data source do you link to. We also want to know how many lows of data you query.

    Firstly, for the first question, when we execute the query select * from, we will get all the rows form all the tables that match the conditions. And it will waste the system resource, if you have a huge amount of rows, it can cause Catastrophic Failure.

    Secondly, please test the following items and give us the result:

    1. Create a new table, then insert the data got from openquery(test,'SELECT Time,Source,Value FROM Sample/hstrh3.current') to this table to find if the data been cut off the same way.
    2. Use some other driver like ADO, OLEDB to try it again to find the result.

    Regards,
    Teige
    Thursday, August 11, 2016 9:02 AM
  • Hi,

    Please find attached ODBC Edition information. i am trying to retrieve around 50000 Rows.

    When i use Select * Query..... i get Catastrophic failure error and retrieves only 1 row.

    When i use select * from OPENQUERY(LINKEDSERVER,'Select Time,Source,Value from Sample/hstrh3.current')  

    I get desired coloumns data but data gets truncated i don't know why.... same result even if i insert it into local permanent database.

    Friday, August 26, 2016 7:08 AM