July 31, 2001 at 9:29 am
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?
August 1, 2001 at 8:04 am
If it runs fine in QA then I'd say you can rule out syntax. Are you the person running the proc will have rights on the other server to do the select? And that you're telling the other server how to figure that out by setting the correct security settings for the linked server?
Andy
August 2, 2001 at 5:54 am
I created a login on the remote server with SELECT permissions. I then configured the linked server to enable all users to be mapped to this specific login.
I configured the linked server through EM and specified "Server type" as SQL Server.
Have I done this correctly or is there something else I need to do?
Richard
August 2, 2001 at 6:34 am
That sounds right. Have you tried creating a test proc with ONLY the code that accesses the remote server, see what that does?
Andy
August 2, 2001 at 6:43 am
I will give this a try. I will update you on the result.
Richard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply