Ad Hoc Query with a linked server?

Question

Post reply

Ad Hoc Query with a linked server?

nicoleaslater

SSC Enthusiast

Points: 150
More actions
April 25, 2013 at 11:15 am

#394673

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:
USE Report;
GO
SELECT d.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Server=servername;',
'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;
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nicoleaslater SSC Enthusiast Points: 150 More actions · Answer 2

Wonderful! Thank you, the most direct way without the openrowset seems to work. I didn't even try that because I thought you could only have 3 identifiers (db, schema, table). Thank you!!!! 😀

(also, the error is what I get for copying and pasting the work of others.)