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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 25, 2013 at 11:25 am

  #1610072

  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

  April 25, 2013 at 11:33 am

  #1610076

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