Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Exclamation Unanswered: Joins with MSSQL to Turbo Image Linked Server [Solved]

    One of my current projects is to convert the data access for a newley converted Access app from a csv download that was linked into access to be a database on our SQL server. Problem is that the data is, for the time being, housed in a Turbo Image data store on our HP3000 that is accessed via Data eXpress.

    I can get to the data directly with the ODBC32 driver and a linked server configured. But I have a problem on this one.

    The data is being pulled from 2 tables and a left join needs to be performed.

    I have tried:

    select
    sid,
    stu_prg_appl,
    yrq_plan_strt,
    stu_name,
    test_name,
    substring(test_score,25,4) as score into Jeff.dbo.tblODBC32_RS3 from
    openquery(hp3000,'select stu_d.sid, stu_d.stu_prg_appl, stu_d.yrq_plan_strt, stu_d.stu_name,
    test_score_d.test_name, test_score_d.test_score from stu_d, test_score_d
    where stu_d.sid = test_score_d.sid and stu_d.yrq_plan_strt = ''9892'' and stu_d.stu_prg_appl = ''25RS''')

    But I get an access error. This happens whenever I try to access something that is not there or a function that doesn't exist. I am able to perform a natural join, so I started thinking perhaps the ansi style join is the problem, so I trie the old syntax for a left join.

    select
    sid,
    stu_prg_appl,
    yrq_plan_strt,
    stu_name,
    test_name,
    substring(test_score,25,4) as score into Jeff.dbo.tblODBC32_RS3 from
    openquery(hp3000,'select stu_d.sid, stu_d.stu_prg_appl, stu_d.yrq_plan_strt, stu_d.stu_name,
    test_score_d.test_name, test_score_d.test_score from stu_d left join test_score_d
    on stu_d.sid = test_score_d.sid and stu_d.yrq_plan_strt = ''9892'' and stu_d.stu_prg_appl = ''25RS''')

    Same error.

    I even tried a cross join but it fails as well.

    So, I am stuck downloading the 2 seperate datasets and joining them here, but that quintuples the processing time.

    Thanks for your time.
    Last edited by JeffJones72; 10-21-04 at 19:21.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  2. #2
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Thumbs up Solved

    For those interested, the ODBC32 driver has a function for this.

    The corrected query is below.

    select
    sid,
    stu_prg_appl,
    yrq_plan_strt,
    stu_name,
    test_name,
    test_score as score from
    openquery(hp3000,'select stu_d.sid, stu_d.stu_prg_appl,
    stu_d.yrq_plan_strt, stu_d.stu_name, test_score_d.test_name,
    test_score_d.test_score from {oj stu_d left outer join test_score_d on
    stu_d.sid = test_score_d.sid} where stu_d.yrq_plan_strt = ''9892'' and stu_d.stu_prg_appl = ''25RS''')
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •