Re: "Severe" Linked Server Error

SQLDex (SirSql@xxxxxxxxxxx) writes:
Using SQL2K SP4, I'm getting a weird error when I run a stored
procedure from a remote server:

exec remoteserver.database.dbo.proc

returns:

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

Googling around, I did find a reference to the 'set nocount on'
command as a fix for this. Sure enough, when a add the clause above
the exec statement, the error goes away.

The thing is, set nocount on is already in the procedure, so this
shouldn't happen. Or so I thought. The data does return, but I and
our monitoring tools do not like to see "severe error" messages.

Any ideas as to what is going on? As this is 2K, and not 2005, I
don't think kb928372 applies.

What happens if you run the batch from Query Analyzer or SQLCMD?

I ask, because it may be an error in SqlClient, and QA and SQLCMD
use ODBC and OLE DB respectively. On the other hand, if they also
say weird things, then it's some bug in SQL Server. You could also
examine the SQL Server error log for stack dumps.

The fact that SET NOCOUNT ON helps is not so strange. You may have
SET NOCOUNT ON in the remote procedure, but that does not affect the
local session.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: A severe error has occurred - HELP!!!
    ... Executing sp_addmergepublication in SQL Server requires membership in ... the sysadmin or db_owner roles--does your client app connection string ... >A severe error has occurred on the current command. ... >This is command works fine from query analyzer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Item Cannot be Found in Collection
    ... The "SET NOCOUNT ON" suggestion is not relevant for Access databases. ... setting that can only be performed in SQL Server. ... > I tried Set NOCOUNT = ON and I got a syntax error. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQL 2000 Stored Procedure Problem
    ... I added SET NOCOUNT ON as the first line in spTestTempTable - same ... SELECT 'Test Temp Table' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Bcp and temp tables
    ... Here is the proc: ... bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ... without SET NOCOUNT ON with the same results. ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie question; Why is my procedure slow when a parameter is used?
    ... since your WHERE clause refers to columns in the unpreserved ... Columnist, SQL Server Professional ... Alter Procedure prInvDet ... set nocount on /* set nocount on */ ...
    (microsoft.public.sqlserver.programming)