SQLOLEDB from sql2005 to sql2008?

Question

Can I use the SQLOLEDB provider in an OPENROWSET query from sql2005 to sql2008???

SELECT * FROM OPENROWSET ('SQLOLEDB','[servername]';'login';'pw',
      'set fmtonly off 
      EXEC DB.dbo.SP 6,1,''B20121110'' ')

Thanks

Answer 1

Yes the OpenRowset approach does work in SQL Server 2008 and SQL Server 2005 will accept a connection with the SQLOLEDB provider.

You'll need to enable Ad Hoc Distributed Queries to allow that type of access though (on your source server). Please note this is a security risk and this article highlights that risk. That link also shows you how to enable this option, I don't share the code here so you can read the warning yourself.

Depending on what you are trying to do, this may not be the best way to get data from a destination server. You may be better of loading data from that server to your server via an ETL process in SSIS, do some of the work in the application of getting data from each source, etc. You can also create and define a linked server to use to connect to the other SQL Server, but some of the concerns with linked servers (some security concerns, some performance concerns in running remote queries and sending data back and forth and joining cross servers, etc.).