SQL Server Performance

source table name with "/"

Discussion in 'SQL Server DTS-Related Questions' started by dskohli, Oct 21, 2003.

  1. dskohli New Member

    Hello,

    I am trying to load data from informix and my source table starts with / (eg. /ab0/cd_ef). I have the informix server as my linked server. I can load any other table which does not have / in the beginning. What will be the code to load data from that table in activex script.

    I am making the connections and then I am executing the follwoing string:

    "insert into table1" & _
    "select col1 from server.database.owner.table2"

    The table1 is in sql server. table2 is in informix and starts with /

    Any help will be appreciated.
    Thanks
    DK
  2. satya Moderator

    Have you tried using DTS to import the data from informix?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. Luis Martin Moderator

    I don´t think you can access the fox files direct from SQL.
    Like Satya sed, use DTS with Fox ODBC



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  4. satya Moderator

    Bit more information on Informix using with DTS :

    The Informix ODBC driver is not supported for use with DTS.

    The Merant Informix OLE DB provider is supported for DTS imports from Informix, but not DTS exports to Informix. This driver also cannot be used to import meta data.

    The Intersolv Informix ODBC driver is supported, but with the following restrictions:
    BLOBs cannot be exported to Informix.

    When creating new tables on Informix, the DTS Import/Export Wizard will incorrectly map the SQL Server 2000 datetime columns to the Informix 'Datetime year to fraction' data type. Manually change this to the Informix Date type.

    The DTS meta data import will not import Informix catalog or table information.

    And, Luis actually Informix is difference and Fox dbf is different with regard to your reply.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. dskohli New Member

    Hello,

    Thanks for your prompt replies. I am using the DTS ActiveX Script task. If I use the transform data task, I can load the data with Informix 32bit connection.

    My problem is I want to pass some variables to informix. The select statement to informix will be:

    select col1, col2 from table1
    where col1 > @date

    And I was thinking of getting the @date from the global variable. I am not sure how to use DTS parameterized queries (specially with informix), so I was trying to build the query in DTS activeX task. But the problem remains with the fact that the table name starts with a "/" and I cannot get the VB code to work.

    Actually if anyone can help me out with data driven query or transform data task, it will be great. All I want to do is use the above query to an informix table and I have to populate the variable before I pass that query. I can get the value of the parameter from the global variable, but I cannot get the global variable to be my input parameter in the query.

    Thanks
    DK
  6. ykchakri New Member

    '/' is reserved in SQL and not recommended to use for the name of an object. But, when you must use this, wrap the name in square brackets. In your example use the table name as [/ab0/cd_ef].
  7. dskohli New Member

    Thanks for your reply. But I cannot use [/abc/defg] in informix. I have to use "/abc/defg" in order to retrieve data from informix.
  8. ykchakri New Member

    I assume you are linking the Informix server to SQL Server and then running the statement on the SQL Server. If not, can you try doing that ?
  9. satya Moderator

Share This Page