Problem with linked server query

  • rich

    SSC Journeyman

    Points: 98

    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?

  • Andy Warren

    SSC Guru

    Points: 119676

    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

  • rich

    SSC Journeyman

    Points: 98

    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

  • Andy Warren

    SSC Guru

    Points: 119676

    That sounds right. Have you tried creating a test proc with ONLY the code that accesses the remote server, see what that does?

    Andy

  • rich

    SSC Journeyman

    Points: 98

    I will give this a try. I will update you on the result.

    Richard

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply