Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

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

share|improve this answer
    
Thanks, even with your suggestion, i continue to receive: –  Jim Dec 3 '12 at 17:35
    
Continue to receive? –  Mike Walsh Dec 3 '12 at 17:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.