Use OPENROWSET from SQL Server 2005 to read data from SQL Server 2000

• Loner

  SSC-Insane

  Points: 21279

  More actions

  September 5, 2008 at 11:55 am

  #93862

  I enabled Ad Hoc Remote queries in SQL Server 2005. I tried to read a table in SQL Server 2000 from 2005 using OPENROWSET.

  SELECT a.*

  FROM OPENROWSET('MSDASQL', 'DRIVER=(SQL SERVER);Server=databaseserver;UID=userid;PWD=password',

  'SELECT * FROM table') AS a

  I have the following error.

  Msg 7415, Level 16, State 1, Line 1

  Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

  I tried to change the provider to SQLNCLI or SQLOLEDB and it still did not work.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  September 5, 2008 at 2:43 pm

  #868047

  Create a linked server. SQL 2005 doesn't like linking to SQL Server with OpenRowset.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon