I have never run a query accross two servers before. I have created a linked server between two 2008 r2 servers, I have configured to allow ad hoc queries on both, I have sys admin permissions on my user name on both. Just in case, I have configured my main server to access the linked server using my sa username and password. I run my query and I get this error: Msg 7308, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. Now maybe I'm not understanding the provider syntax, I'm not sure. What am I doing wrong?
Here is my query:
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Server=servername;',
FROM Database.dbo.table t
JOIN person p
ON t.person_id = p.person_id'
) AS d;