How can I execute a select with OPENROWSET and extract only the record that
I request with the WHERE clause?

For example:

SELECT a.*
FROM OPENROWSET('MSDAORA','DBORACLE'; 'USER'; 'PWD', 'select * from
TABELLA' ) AS a
WHERE
a.Cognome like @Cognome

With this SQL statement, SQLServer extract first all the record of TABELLA
(500.000 records) and than apply the WHERE clause, while I want to apply the
WHERE clause in Oracle.
Does Someone known the right statement?

Thanks in advance

Do you want something like this that passes the variable?

-- I didn't verify the exact syntax, but this is the general idea if you need to build a dynamic string
-- note that there are not any double quotes, only pairs of single quotes
declare @cmd varchar(500)
select @cmd = 'SELECT a.*
FROM OPENROWSET(''MSDAORA'',''DBORACLE''; ''USER''; ''PWD'', ''select * from
TABELLA WHERE a.Cognome like''' + @Cognome + ') AS a'
select @cmd -- for testing to see what the actual command you are executing is
exec @cmd


Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

This works for me

create procedure usp_get_product_data @prod_cd char(10)
as
set nocount on

declare @qry varchar(2000)
create table #facts (fact_item_code char(10),
fact_type_name varchar(30), Fact_value varchar(100),
BAR_CODE_DNM CHAR(14))
set @qry = 'INSERT INTO #FACTS SELECT * FROM OPENQUERY
(SMAUAT,''SELECT FS.ITEM_CODE, FT.FACT_TYPE_NAME,
FS.FACT_VALUE, BAR_CODE_DNM
FROM SMAADM1.ITEM_FACT_SIMPLE FS, SMAADM1.FACT_TYPE
FT, PPT_PRODUCT PPT
WHERE FS.FACT_TYPE_CODE = FT.FACT_TYPE_CODE
AND PPT.PRODUCT_CODE = substr(FS.ITEM_CODE,5,6)
AND FS.ITEM_CODE = ''''' + @prod_cd +
''''' AND FACT_VALUE is not null'')'
--print @qry
exec (@qry)


[quoted text, click to view]