SQL Server Performance Forum – Threads Archive

OPENROWSET question

I was looking for an alternative to Linked Servers, and thought that OPENROWSET might be the answer. Any thoughts about this? It this bad in terms of performance? (seems slow)
And what is the right way to do this? TIA!

Linked server will be more efficient and your data parocessed locally… http://www.sql-server-performance.com/linked_server.asp Use of ad hoc distributed queries is disabled as part of the default security configuration. A system administrator can enable the use of ad hoc distributed queries by using sp_configure. For more information, see Surface Area Configuration. It is better to use windows authentication in OPENROWSET for security reasons…
MohammedU.
Moderator
SQL-Server-Performance.com

Thanks! When I tried OPENROWSET however:
Select * from
openrowset
(
‘SQLOLEDB’,
‘Trusted_Connection=yes;Integrated Security=SSPI;Data Source=MyServer;’,
‘SELECT *
FROM MyServer.database.dbo.table’
) I get this error, which makes me think you have to have linked server set up for this to work? Please clarify?? Server: Msg 7202, Level 11, State 1, Line 1
Could not find server MyServer’ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
[OLE/DB provider returned message: Deferred prepare could not be completed.] I also tried and got SQL Server Access Denied even though I know that user and password exists on the target. Do I need this on the source server?: THX! Select * from
OPENROWSET(‘SQLOLEDB’,
‘[MyServer]’;’test1′;’qwerty’,
‘SELECT *
FROM [MyServer].database.dbo.table’ ) And, Select * from
OPENROWSET(‘MSDASQL’,
‘DRIVER={SQL Server};SERVER=[MyServer];test1;qwerty’,
‘SELECT *
FROM [MyServer].database.dbo.table’ )

Don’t use server name again in your query… Select * from
openrowset
(
‘SQLOLEDB’,
‘Trusted_Connection=yes;Integrated Security=SSPI;Data Source=MyServer;’,
‘SELECT *
FROM database.dbo.table’
)
MohammedU.
Moderator
SQL-Server-Performance.com

B-I-N-G-O! Thanks, I goofed on that. So, my understanding is Linked Server executes on the local server, whereas the OPENROWSET executes on the remote server? Or is that OPENQUERY, and what is the difference. Many thanks again. I will be reading the article link you posted on my way home <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

]]>