OPENQUERY ISSUE

I am trying to pass a variable to an openquery stmt within a proc. The
following worked a couple of times, but I am receiving the error below now ?
Any suggestions on a method to pass the variable into the OPENQUERY Statemen
t
?
CREATE PROCEDURE usp_GetData (@.REQUEST_ID as varchar(10))
AS
declare @.RESULT varchar(20)
Declare c Cursor For
SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM oracle_view
WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Open C
Fetch c into @.request_ID, @.RESULT -- Get First record data
While @.@.FETCH_STATUS = 0
Begin
INSERT INTO RESULTS (REQUEST_ID, RESULT)
VALUES (@.request_ID, @.RESULT)
Fetch c into @.request_ID, @.RESULT -- Get Next Record
End
Close C
Deallocate C
GO
-- Error received.
Server: Msg 7330, Level 16, State 2, Procedure usp_GetRequestID_Data, Line 2
2
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01722: invalid number> SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM
> oracle_view WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Usually it helps to manually evaluate the expression you are trying to execu
te.
DECLARE @.TEST VARCHAR(200)
SET @.TEST = 'SELECT REQUEST_ID, RESULT FROM oracle_view WHERE REQUEST_ID=''+
@.REQUEST_ID+'''
SELECT @.TEST|||Hi,
as far as I know, OPENQUERY cannot accept variable.
normally what I do is to create a dynamic sql string first then only execute
it.
In BOL, you should be able to find something like this:
--
OPENQUERY
Executes the specified pass-through query on the given linked server, which
is an OLE DB data source. The OPENQUERY function can be referenced in the
FROM clause of a query as though it is a table name. The OPENQUERY function
can also be referenced as the target table of an INSERT, UPDATE, or DELETE
statement, subject to the capabilities of the OLE DB provider. Although the
query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
Arguments
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
Remarks
OPENQUERY does not accept variables for its arguments.
--
As you can see, variable is not allowed. I am not so sure about SQL Server
2005.
I get this from SQL Server 2000 BOL.
hope this will help.
Leo