it's just the wrong provider;

jet is for 32 bit Access/Excel, and not for SQL Servers.

here's your command adapted, and also one I know works for sure:

--query directly without open rowset:

SELECT t.person_id

, t.tran_date

, t.service_code

FROM yourservernamehere.Database.dbo.table t

JOIN person p

ON t.person_id = p.person_id

--your openrowset

SELECT d.*

FROM OPENROWSET('SQLOLEDB', 'Server=servername;Trusted_Connection=Yes;Database=Master',

'SELECT t.person_id

, t.tran_date

, t.service_code

FROM Database.dbo.table t

JOIN person p

ON t.person_id = p.person_id'

) AS d;

--other openrowset examples

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master',

'Set FmtOnly OFF;EXEC dbo.sp_Who')