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

OPENQUERY and variable question

I am trying to create a stored proc that uses OPENQUERY to get data from a db2 database. Can anyone look at the code below and let me know what I am doing wrong.

This is my error msg:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'SELECT'.

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near ')'.

This is my code:

USE [FVDashBoard]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spDaily_TBT_Load_OpenQuery]

as

-- define variables

declare @.FM_DT char(10)

set @.FM_DT = (SELECT CALENDAR_DT FROM dbo.PROD_SCHD WHERE(ISSUER_NO = 90)AND (CALENDAR_DT =CONVERT(VARCHAR(10), GETDATE() - 2, 120)))

declare @.remotesql nvarchar(4000)

set @.remotesql =

'select * from OPENQUERY (BACARDI, '+

'SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ')'

PRINT @.remotesql

EXEC (@.remotesql)

Thanks!

The second parameter of OPENQUERY (sql statement) should be string – enclosed by single quote. Try the below query..

Code Snippet

set @.remotesql =

'select * from OPENQUERY (BACARDI, '+

'''SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ''')'

|||

This statement

'select * from OPENQUERY (BACARDI, '+

'SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ')'

seems to be missing a closing parenthesis.

|||

Thanks so much for your reply.

As a follow-up question, how could I change the vaule of @.FM_DT? Currently the value of the variable is something like 2007-08-03. What I need is for the variable to be something like '2007-08-03'. Do you know of a way to do this.

Thanks for your help!

|||

You need to add a few more quotes...

Something like this:

= '''+ @.FM_DT + ''')'

Do a PRINT or SELECT on your statement until it is correct.

opening cursor on result set from stored proc

In SQL how can a cursor be opened to iterate the result set returned
from a stored proc

-Rahul Sood
rsood22@.yahoo.comYou can insert the procedure result set into a table. For example:

CREATE TABLE #ProcResults
(
SomeData int
)

INSERT INTO #ProcResults
EXEC MyProcedure

DECLARE MyCursor CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM #ProcResults

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Rahul Sood" <rsood22@.yahoo.com> wrote in message
news:aa3020f6.0308191503.68af9ec5@.posting.google.c om...
> In SQL how can a cursor be opened to iterate the result set returned
> from a stored proc
> -Rahul Sood
> rsood22@.yahoo.comsql