Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Output reversed when calling a stored procedure remotely.

    Can anyone explain why this happens?

    CREATE PROCEDURE dbo.test_rpc_output
    AS
    Print '1'
    Print '2'
    Print '3'
    RETURN
    GO

    CREATE PROCEDURE dbo.test_rpc_output_nocount
    AS
    set nocount on
    Print '1'
    Print '2'
    Print '3'
    RETURN
    GO

    I load these two stored procedures on a remote server - let's call it SERVER2.

    From SERVER1 I then execute them remotely using linked servers..

    I.e. exec SERVER2.Database.dbo.test_rpc_output
    exec SERVER2.Database.dbo.test_rpc_output_nocount

    The first one outputs
    1
    2
    3

    as I would expect...

    But the second one outputs
    3
    2
    1


    If I connect directly to SERVER2 and execute them they both return 1 2 3.

    Any ideas why setting nocount on has this effect on a linked server?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have a serious problem with one or both of your servers, your example doesn't work that way for me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Strange.

    I've tested this using four of our servers across any combination of directions and I get the same result every time.

    If I set nocount on in the sp the output is reversed with the remote call. If I set it off it's correct.

    Towards the bottom of this page one of the notes it has on oledb is "If the procedure produces more than one error, you only get one error message if NOCOUNT is OFF. If NOCOUNT is ON, you may get all messages, unless there are result sets interleaved with the messages. For some reason the error messages comes in reverse order. "

    The last part of that describes the problem I'm having - so I'm not completely insane, someone else at least has noted it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •