Print issue of the SQL Server stored procedure

user_5408130 0
Viewed 293

I have a problem about that I will get reverse sequence message if the stored procedure called from linked server. I'm much appreciated if anyone know the root cause or provide a solution. Thanks in advance.

This is my test code:

CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SET NOCOUNT ON;

    PRINT '1'
    PRINT '2'
    PRINT '3'
END

Calling it like this:

EXEC [dbo].[TestSP] (call by local)

Output:

1
2
3

It will show output with reverse order if executed by another linked server.

For example,

EXEC [XXX.XXX.XXX.XXX].[dbo].[TestSP]

Output:

3
2
1
linked-server stored-procedures sql-server
1 Answers

Print is a debug command not a functionnal one. If you want to have a consistant order of the sequencement, you must use the RAISERROR at level 10 with the keyword NOWAIT.

CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SET NOCOUNT ON;
    RAISERROR('1', 10, 1) WITH NOWAIT;
    RAISERROR('2', 10, 1) WITH NOWAIT;
    RAISERROR('3', 10, 1) WITH NOWAIT;
END
user_126598720
Related