OPENQUERY vs. sp_executesql which is better?

I have recently seen the OPENQUERY function in a stored procedure which
was used to INSERT values into a table on a remote server. However, I
normally use the sp_executesql stored proc.
Can any one shed some light on which method is better and in what
cases?
The following code is from Microsoft website, not my own.
http://msdn.microsoft.com/library/d...br />
5xix.asp
<MS CODE>
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
</MS CODE>
VS.
<MYCODE>
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
-- OR
OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
</MYCODE>
Thanks for your time folks.
Johnny DWhy would you even consider sp_executesql in your case? There is no need to
use dynamic sql to access a linked server.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148649898.280530.174390@.j73g2000cwa.googlegroups.com...
>I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
> The following code is from Microsoft website, not my own.
> http://msdn.microsoft.com/library/d... />
z_5xix.asp
> <MS CODE>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
>
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
> </MS CODE>
> VS.
> <MYCODE>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
> -- OR
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
> </MYCODE>
>
> Thanks for your time folks.
>
> Johnny D
>|||Sorry Andrew, this was an oversight in me writing a simple query to
illustrate my question...
The reason for this is I would use a cursor to loop through my
different servernames
DECLARE @.rc INT
DECLARE @.v_sql NVARCHAR(4000)
DECLARE @.vc_servername VARCHAR(100)
DECLARE c_myservers CURSOR
FOR SELECT
servername
FROM listservers
WHERE active=1
FOR READ ONLY
OPEN c_myservers
FETCH NEXT FROM c_myservers INTO @.vc_servername
WHILE (@.@.FETCHSTATUS = 0 )
BEGIN
SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
'].dbname.joe.titles'
EXEC @.rc = sp_executesql @.v_sql
-- if @.rc <> 0
-- etc...
FETCH NEXT FROM c_myservers INTO @.vc_servername
END
CLOSE c_myservers
DEALLOCATE c_myservers
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'|||Well one key point of using OPENQUERY is that it passes the statement to the
other server where it is executed as is. That way the remote server can
choose the proper plan for that statement without regard to what the rest of
the statement is that was issued locally. Kind of hard to explain but it is
truly a pass-through query where as a linked server query may be influenced
by the rest of the statement. For instance a join to a local table.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148652836.663893.44660@.g10g2000cwb.googlegroups.com...
> Sorry Andrew, this was an oversight in me writing a simple query to
> illustrate my question...
> The reason for this is I would use a cursor to loop through my
> different servernames
> DECLARE @.rc INT
> DECLARE @.v_sql NVARCHAR(4000)
> DECLARE @.vc_servername VARCHAR(100)
> DECLARE c_myservers CURSOR
> FOR SELECT
> servername
> FROM listservers
> WHERE active=1
> FOR READ ONLY
> OPEN c_myservers
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> WHILE (@.@.FETCHSTATUS = 0 )
> BEGIN
> SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
> '].dbname.joe.titles'
> EXEC @.rc = sp_executesql @.v_sql
> -- if @.rc <> 0
> -- etc...
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> END
> CLOSE c_myservers
> DEALLOCATE c_myservers
>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
>|||Johnny D (john.dacosta@.gmail.com) writes:
> I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
I think they are as comparable as apples and oranges.
Apparently you loop over servers. That is nothing OPENQUERY can help
you with - the server name must be a constant.
Rather your choice is between:
SELECT @.sql = 'SELECT ... FROM ' + @.server + 'catalog.schema.tbl'
and
SELECT @.sql = 'SELECT ... FROM OPENQUERY(' + @.server + ', ' +
'''SELECT ... FROM catalog.schema.tbl'')'
That is, accessing the table in four-partnotation, or running a
passthrough query.

><MYCODE>
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
></MYCODE>
Ehum, I don't think you will find sp_executesql on Oracle...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx