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.

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