One of the developers at my company receives the following error when executing a stored procedure :
Server: Msg 7399, Level 16, State 1, Procedure forex_Insert_Positions, Line 51
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Syntax error or access violation]
The error occurs when attempting to select data from a linked server.
Below is the query that causes the error.
select @Rate = node_value from rmt_server.dwd.dbo.curve_data where observation_date = @effective_date
and curve_id in(select curve_id from rmt_server.dwd.dbo.curve_dimension where underlying_name = @CrossCurrency
and Curve_type = 'curve') and node_term = 0
The query runs fine through query analyzer. But when it is executed from within a stored procedure then it causes the error.
This is only a small section of the actual stored procedure that uses a lot of declared variables.
Any ideas about what could be causing this problem?