Search code examples
sql-serversql-server-2000

PRINT from nested stored procedure causes "A severe error occurred on the current command."

Calling PRINT from a child stored procedure causes:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

How to make it not give an error?

Note: Not SQL Server 2005

i have a stored procedure

ALTER PROCEDURE [dbo].[Archive_SessionDeleteOnly] AS

  SET XACT_ABORT ON

  BEGIN DISTRIBUTED TRANSACTION
  EXECUTE ASILIVE.ContosoLive.dbo.Archive_Delete 
  ROLLBACK TRANSACTION
  PRINT 'Fargodeep Mine'

With the child stored procedure being:

ALTER PROCEDURE [dbo].[Archive_Delete] AS

  PRINT 'You no take candle'

When i run my outer procedure Archive_SessionDeleteOnly it all runs to completion, including the PRINT after the call to my child stored procedure

You no take candle
Fargodeep Mine
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

The entire operation runs to completion, but i get an error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

If i remove the print from the "child" stored procedure:

ALTER PROCEDURE [dbo].[Archive_Delete] AS
  --PRINT 'You no take candle'

it runs without error.

i've also tried randomly adding NOCOUNT (since nobody knows how to fix it anyway):

ALTER PROCEDURE [dbo].[Archive_Delete] AS

  SET NOCOUNT ON
  PRINT 'You no take candle'

it still causes:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

ServerA: SQL Server 2000

SELECT @@version
Microsoft SQL Server  2000 - 8.00.818 (Intel X86)   May 31 2003 16:08:15   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

ServerB: SQL Server 2000

SELECT @@version
Microsoft SQL Server  2000 - 8.00.2055 (Intel X86)   Dec 16 2008 19:46:53   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

There are no errors in either SQL Server Error Log.

How can i make SQL Server not choke when a PRINT is used in a child stored procedure. i have other cases where i call print from child stored procedures, without error.

Update Trimmed down to minimal reproducible case. Exact sql is shown

Solution

  • Seems to be an issue with SQL 2000 when the query is run from Management Studio

    Have you tried running it in Query Analyzer?