Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ad Hoc Query with a linked server? Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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
Post #1446640
Posted Thursday, April 25, 2013 11:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 12,897, Visits: 32,105
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1446648
Posted Thursday, April 25, 2013 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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.)
Post #1446652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse