SELECT * FROM OPENQUERY(xxx,'select * from mytable ') where item in ('A','B')
The above query is a poorly performing method. You should let the remote server handle as much processing as the business will allow. (i.e. SELECT * FROM OPENQUERY(xxx, 'select field1, field2 from mytable where item in (''A'',''B'')') ) This query will allow the remote server to only send over the filtered records.

Local testing, on a table with 241000 rows, shows a >60% performance increase by remote filtering.

Now, I should qualify that OPENQUERY can only be used if the query has been predetermined and fixed. If you need use dynamic sql or use r-value parameters, sp_executesql or exec should be used. In either case, let the remote server do the work and always benchmark before you release.

Adam Gossage
Lake Wylie, SC, USA

here is another solution:

Just a note: best to use the sp_executesql method as opposed to just plain Exec() as sp_executesql 's execution plans are inserted into the procedure cache and can be reused. Also get do some of the stuff below:

--Dynamically writing a Select string from mysql using an output parameter
DECLARE @VCArticleId VARCHAR(20), @Id INT, @SQLString nvarchar(500),
@ParmDefinition nvarchar(500)

SET @SQLString = N'SELECT @Id_Out = id FROM OPENQUERY(mylinkedserver,''Select id From jos_content where metadesc = "'+ @VCArticleId+'" ORDER BY id DESC limit 1'')'
SET @ParmDefinition = N'@Id_Out INT OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @id_out = @Id OUTPUT;

--Then you can do what you want with @Id

You can also use the above approach with input parameters.

I also had fun and games with inserting data into joomla's mysql. Some daft programmer had used a reserved word for a field name , thus it took me ages and much pain to get the right escape character. in my case the following worked. I'm talking from mssql2005 to my sql 5

--here is the rendered SQL query I used, take note of the escape ditti's around fulltext, they ain't the normal ''
insert openquery (joomlamysql, 'select `fulltext` from jos_content')
select A.Body AS Ftext FROM mssql.dbo.table A where id = @articleid

--so never ever use a reserved word in a field name......