RAISERROR won't compile in sproc

Question

I have an stored proc with code that looks like:

Begin

Begin try

Begin Tran

Commit

End try

Begin Catch

-- get the error values and write them to a log table by executing another stored proc

ROLLBACK

-- here is the problem: RAISERROR won't compile, even in a comment!

END CATCH

END

Please note the strangeness of this: RAISERROR won't compile (connection time out), even if it is in a comment!

Win XP XP3, SQL Server 2008 (SP1)  and SSMS 2008 R2. It doesn't make sense: existing procs that used to compile using RAISERROR no longer do. Any ideas?

 

Answer 1

Can you post the error msg as it is or the sp code?

 

---

AMB

Some guidelines for posting questions...

Answer 2

Thanks for the quick response. Essentially SQL Server times out.

The error message is:

Msg 64, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

-- END of error message

CODE:

 

BEGIN

CATCH

 

DECLARE @errorNumber int

,

@errorSeverity

int

,

@errorState

int

,

@errorMessage

nvarchar(2000

),

@errorLine

int

,

@errorProcedure

nvarchar(126

)

 

SELECT @errorNumber = ERROR_NUMBER

(),

@errorSeverity

= ERROR_SEVERITY

(),

@errorState

= ERROR_STATE

(),

@errorMessage

= ERROR_MESSAGE

(),

@errorLine

= ERROR_LINE

(),

@errorProcedure

= ERROR_PROCEDURE

()

 

-- log error

 

EXECUTE [UMLERCarData_Dev].[dbo].[LogError]

 

 

@errorNumber

 

,

@errorSeverity

 

,

@errorState

 

,

@errorMessage

 

,

@errorLine

 

,

@errorProcedure

 

 

ROLLBACK

TRAN

 

-- RAISERROR function call doesn't compile, see the log table

 

END CATCH

If I remove the string RAISERROR from the comment, it compiles in 2 seconds or less.

 

Answer 3

Sorry to bother you, but can you tell us more about your environment?

I am not clear if you are talking about having everything in one machine (Win XP, SQL Server 2008 SP1, and SSMS 2008 R2), or if you are using SSMS 2008 R2 in your machine to connect to a Server, and the edition of SQL Server (Standard - DE - EE).

 

---

AMB

Some guidelines for posting questions...

Answer 4

My bad.

Here is what @@VERSION returns: 

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Mar 29 2009 10:11:52

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

My Machine SSMS Help About returns:

Microsoft SQL Server Management Studio      10.50.1617.0
Microsoft Analysis Services Client Tools      10.50.1617.0
Microsoft Data Access Components (MDAC)      3.85.1132
Microsoft MSXML      2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer      8.0.6001.18702
Microsoft .NET Framework      2.0.50727.3623
Operating System      5.1.2600

Hope this helps.

Answer 5

The error message is not a time out. The error indicates some serious problem like SQL Server severing the connection withyou because of a fatal error. Or something like - like a network sniffer listening for foul language.

You can examine the SQL Server errorlog to see if there are any stack dumps that are correlated with this error.

If you replace RAISERROR with, say, UNDERWEAR, what happens?

Could you post the full code the procedure? (Please use the squiggly button in the web UI to post code, so that it doesn't get mangled.)

Are there any DDL triggers on the database?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

I believe you have your concepts all confused... from BOL:

Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. If a remote stored procedure calls RAISERROR with severity less than 20 and the remote stored procedure is scoped within a TRY block on the local server, RAISERROR does not cause control to pass to the CATCH block of the TRY…CATCH construct. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.

I would say this is what you are experiencing. In other words, DO NOT MIX RAISERROR WITH TRY...CATCH!

Something like this should do:

 

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_LINE () AS ErrorLine
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_MESSAGE() AS ErrorMessage;
GO

CREATE PROCEDURE yourProcName
AS
BEGIN TRANSACTION;

BEGIN TRY
    -- Some database operation here (DELETE/UPDATE/INSERT etc).
    SELECT 1;
END TRY
BEGIN CATCH
    EXEC usp_GetErrorInfo;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 7

> I would say this is what you are experiencing. In other words, DO NOT MIX RAISERROR WITH TRY...CATCH!

It's perfectly reasonable to reraise an error in a CATCH handler. Returning the error information in a SELECT statement would be good for test, but not in real application code.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Thanks for the clarification and you are absolutely correct.

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 9

Thank you all for your replies. To clarify: The RAISERROR call is inside a CATCH block. The problem is NOT at runtime - I can't execute the procedure the create the procedure. Simply removing the RAISERROR from the catch block - and commenting it out doesn't remove it - and the same procedure runs (i.e. compiles) correctly. So if the procedure I am trying to create is an AddThis procedure, the code would be:

Create Procedure AddThis
  param 1 int
  param2 whatever

Begin
  Begin Try
  ... some code
  End Try
  Begin Catch
   ... catch the error information
   RAISERROR (... error information ...)
  EndCatch
End

This command will NOT execute to create the stored proc because of the RAISERROR line. Commenting it out does nothing. Remove it completely and the script runs perfectly, the procedure is created and does what it supposed to do.

There was a comment recommending looking at the log files, which I just did - nothing in the file of interest.

Answer 10

I suggest to re-type this line completely - from your last post it sounds like there are some invisible characters in this line.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Thanks for the idea. Sorry, but it didn't work.

Answer 12

The other idea is to create a new SP and type it completely again and see if you can save it having RAISERROR in the catch block.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Thanks again, but I have been there and done that. Just for fun, I tried a proc that has been in use for some time and just tried to execute the Alter Procedure with no changes - it didn't run either.

 

Answer 14

Sounds like you have bigger problems. Did you run DBCC CHECKDB command ?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

Maybe I overstated the "Do not mix" statement above. What I meant was, I personally don't mix the two, but here is something you must consider:

RAISERROR can be used in either the TRY or CATCH block of a TRY…CATCH construct to affect error-handling behavior.

RAISERROR that has a severity of 11 to 19 executed inside a TRY block causes control to transfer to the associated CATCH block. RAISERROR that has a severity of 11 to 19 executed inside a CATCH block returns an error to the calling application or batch. In this way, RAISERROR can be used to return information to the caller about the error that caused the CATCH block to execute. Error information provided by the TRY…CATCH error functions can be captured in the RAISERROR message, including the original error number; however, the error number for RAISERROR must be >= 50000.

RAISERROR that has a severity 10 or lower returns an informational message to the calling batch or application without invoking a CATCH block.

RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.

USE AdventureWorks2008R2;
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
    DROP PROCEDURE usp_RethrowError;
GO

-- Create the stored procedure to generate an error using 
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE usp_RethrowError AS
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL
    DROP PROCEDURE usp_GenerateError;
GO

-- Create a stored procedure that generates a constraint violation
-- error. The error is caught by the CATCH block where it is 
-- raised again by executing usp_RethrowError.
CREATE PROCEDURE usp_GenerateError 
AS 
    BEGIN TRY
        -- A FOREIGN KEY constraint exists on the table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;
    END TRY
    BEGIN CATCH
        -- Call the procedure to raise the original error.
        EXEC usp_RethrowError;
    END CATCH;
GO

-- In the following batch, an error occurs inside 
-- usp_GenerateError that invokes the CATCH block in
-- usp_GenerateError. RAISERROR inside this CATCH block
-- generates an error that invokes the outer CATCH
-- block in the calling batch.
BEGIN TRY  -- outer TRY
    -- Call the procedure to generate an error.
    EXECUTE usp_GenerateError;
END TRY
BEGIN CATCH  -- Outer CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO

 

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 16

Mariano: Thanks very much for taking the time to load all that code. We are just using RAISERROR to return error information from inside the CATCH block. You mentiond this as one way to use it.

I copied the code above into a blank query window and it ran perfectly. No problems with the RAISERROR call or anything else. The thing is, it ran in SQL Server on my computer. Going to the remote (64-bit) computer: it didn't work at all. So i removed RAISERROR and replaced it with a dummy proc of my own. It didn't run, so I removed all the RAISERROR strings in the comments - and it ran with lots of perfectly reasonable errors. There was one comment containing the string 'raise' and I got an error message that the procedure 'raise' could not be found but 'raise' was in a comment!

Naomi: Thanks for the DBCC idea. It returned 0 errors, not a surprise since all I have to do to get the Alter Procedure to run is remove the RAISERROR string and it runs just fine.

Again, thanks to both of you for your time and ideas. 

Answer 17

> It didn't run, so I removed all the RAISERROR strings in the comments - and it ran with lots of perfectly reasonable errors. There was one comment containing the string 'raise' and I got an error message that the procedure 'raise' could not be found but 'raise' was in a comment!

Can you post the full repro for this? That is, the exact code you are submitting, and the exact error message(s). Copy and paste from SQL Server Management Studio, don't retype anything.

Currently, we are only working with your fairly vague description, and it is taking us nowhere.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 18

It now sounds like comments are not being interpreted as comments. Perhaps instead of -- you're using a close enough character?

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 19

That's a good thought, what about using /* */ versus -- for commenting, does that work?

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Answer 20

Again, thanks to all who have replied to this thread.

For Eland:

EXACT Error message (in red, in Messages, after 00:01:30 of waiting, Query window status at bottom is 'Disconnected.'):

Msg 64, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

After one run, the query window should be closed: an attempt to execute another procedure fails.

@@VERSION gives:

 

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 
	Mar 29 2009 10:11:52 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

NO other error message, it just stops trying to execute the procedure:

USE [UMLERCarData_Dev]
GO
/****** Object:  StoredProcedure [dbo].[UpdateCarData]    Script Date: 09/20/2011 12:45:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		John Mealing
-- Create date: Sept 19, 2011
-- Description:	Update one row in the cardata table
-- =============================================
ALTER PROCEDURE [dbo].[UpdateCarData] 
	@CarNumber		varchar(11),
	@CarInitials	varchar(10),
	@CarType		varchar(10),
	@Axels			int,
	@TareWeight		int,
	@TotalAllowWt	int,
	@Length			int,
	@NumberBrakes	int,
	@BrakeForce		int,
	@StdBrake		char(1),
	@BuildDate		datetime,
	@RebuildDate	datetime,
	@LastBrakeTest	datetime
AS
BEGIN
	
	BEGIN TRY

	BEGIN TRAN

	UPDATE UMLERCarData_Dev.dbo.CarData 
	SET  CarNumber = @CarNumber,
		 CarInitials = @CarInitials,
		 CarType = @CarType,
		 Axels = @Axels,
		 TareWeight = @TareWeight,
		 TotalAllowWt = @TotalAllowWt,
		 Length = @Length,
		 NumberBrakes = @NumberBrakes,
		 BrakeForce = @BrakeForce,
		 StdBrake = @StdBrake,
		 BuildDate = @BuildDate,
		 RebuildDate = @RebuildDate,
		 LastBrakeTest = @LastBrakeTest,
		 Updated = 'Y'
	WHERE
		(CarNumber = @CarNumber AND
		 CarType = @CarType)
	COMMIT
	END TRY
	BEGIN CATCH
		DECLARE @errorNumber int,
		@errorSeverity int,
		@errorState int,
		@errorMessage nvarchar(2000),
		@errorLine int,
		@errorProcedure nvarchar(126)
	SELECT @errorNumber = ERROR_NUMBER(),
		@errorSeverity = ERROR_SEVERITY(),
		@errorState = ERROR_STATE(),
		@errorMessage = ERROR_MESSAGE(),
		@errorLine = ERROR_LINE(),
		@errorProcedure = ERROR_PROCEDURE()
		-- log error
	EXECUTE [UMLERCarData_Dev].[dbo].[LogError] 
		   @errorNumber
		  ,@errorSeverity
		  ,@errorState
		  ,@errorMessage
		  ,@errorLine
		  ,@errorProcedure
	ROLLBACK TRAN
 		
 	RAISERROR (N'Error Number %d "%s" occurred at line %d in procedure [%s].', -- Message text.
			@errorSeverity, -- Severity,
			@errorState, -- State,
			@errorNumber, @errorMessage, @errorLine, @errorProcedure)

	END CATCH
	Return @@ROWCOUNT		 
END

Note that /*  */ for comments will allow 'raise' to run, but not RAISERRROR. It never finishes and there is no error message. Removal of the RAISERROR block and the procedure runs in less than 2 seconds.

 I also noticed that the parser is not at all happy with parens in a procedure call.

Conclusion: The parser is REALLY messed up.

Answer 21

The procedure seems simple enough - are you getting the error when you try to execute this SP or when you simply try to save it by executing this script (Add GO after last line).

What happens if you comment out EXECUTE statement in the CATCH block? May be the problem lies in the second SP you're trying to call.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 22

My apologies to all who have looked at this: the problem is in the script interpreter, NOT the stored procedure. Sloppy of me. Since the interpreter will not run to completion, the procedure is never compiled into SS.

I left out the GO at the end of the listing above: I am just trying to execute the script to compile the procedure. It looks like there is a problem in the parser in the script interpreter in this particular version.

Removing the EXECUTE from the script has no effect on the compile: if RAISERROR is there, the script doesn't run. I have tested that procedure with the EXECUTE and it works just fine. In fact, without the RAISERROR function, I must have it to see if there is a problem.

Naomi, thanks for reminding me of the correct terminology. My bad!

Answer 23

Note that *  * for comments will allow 'raise' to run, but not

What do you mean with "allow 'raise' to run"? I can't see any "raise"?

And when I asked for an error message, it was this particular text I quoted:

There was one comment containing the string 'raise' and I got an error message that the procedure 'raise' could not be found but 'raise' was in a comment!

That is the error message, I am interested in seeing. My theory is that you have a DDL trigger or a PBM policy which is pulling your legs. The error message about the procedure 'raise' could not be found would help.

Since you posted your procedure, I have a couple of comments. They are not related to your problem, but just a general review.

In your CATCH block, you call a procedure LogError and then you roll back the transaction. You should rollback first thing:

BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION

You could make your code less tedious by calling error_message & co in LogError. You could also have that procedure to call RAISERROR. (But don't make that change until we have tracked down the cause to the problem you are seeing.)

Finally, your last statement is "RETURN @@rowcount". Customary, return values are used to return to indicate success (0) or failure (anything else). But if you want to return a rowcount, that's OK. But not as you do now: @@rowcount is set after each statement, and the most recent statement when you retrieve @@rowcount is END TRY. (I am not joking.) You need to capture @@rowcount into a local variable directly after the UPDATE.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 24

Thanks for the comments Erland, especially the ones on SQL coding. I will take them to heart.

The comment on "allowing raise to run" was careless wording on my part: I meant to say that the script interpreter would run to completion if 'raise' is in a /* */ comment, but not if it is in a -- comment. Hope this is clearer. The interpreter's message was that it could not find the function 'raise' when 'raise' was in a -- comment, not in SQL code.

As far as DDL triggers, none in the target database - I know because I just created it. As for PBM policy, I had to look that up to see what it meant - I'm a developer, not a DBM. Since this is a two table database that I own and just created, and is not yet in production, it seems unlikely that PBM has been applied, especially since removing the work 'raise' from the comment allows the script to run to completion.

I would note again that this problem looks very much like it is tied to the actual SQL Server release (see above) on the remote server. When I run tests on my local server, there is no problem.

Answer 25

Are you using SSMS and remotely connecting to the server? Also, can you post again that server's version?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 26

Yes, using a remote server, and here is the result of @@VERSION again:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 

	Mar 29 2009 10:11:52 

	Copyright (c) 1988-2008 Microsoft Corporation

	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

That shows a 3 year old build date, but this problem just showed up, implying to me, that perhaps there was an update that broke the script interpreter.

On my 32 bit SQL Server 2008, this problem doesn't exist.

I just confirmed that we are running Sql Server 2008 R2 on the server, even though that doesn't show up in the block above.

Answer 27

The above clearly shows SQL Server 2008 SP1 (BTW, there is SP2 available). May be you're somehow connecting to the Express version?

In addition to version run

SELECT 
	SERVERPROPERTY('Edition') AS Edition,
	SERVERPROPERTY('ProductVersion') AS ProductVersion,
	SERVERPROPERTY('ProductLevel') AS ProductLevel

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 28

Ran the statements above and got back the same information as @@VERSION gave:

Edition                                ProductVersion  ProductLevel
Enterprise Edition (64-bit) 10.0.2531.0        SP1

Thanks for the info on SP2, I'll pass that on. As for SS Express, that seems unlikely given the edition of Enterprise and the size of this operation.

I think that R2 is the problem, even if the docs say RAISERROR is supported.

Answer 29

I think -- character somehow transforms into something else. Save this file as sql file and then examine it in the Hex editor and compare with the one saved in your local server. To me it sounds like the problem is with the -- used as a comment.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 30

same problem with /* */ comment: RAISERROR anywher in the SQL code seems to crash the interpreter, leading to a disconnect.

Also, I think that I am seeing inserts fail unless they are now in transactions...which breaks lots of our stored procedures. But the existing stored procedures cannot be updated because they also use RAISERROR...:-((

Answer 31

Sounds like either your local SSMS or server has something corrupted. It may be time to re-install.

First try re-installing local SSMS (or local SQL Server installation). If that will not help, consider speaking with DBA, explaining the problem and re-installing SQL Server on the server.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 32

Good idea, but no joy with upgrading SSMS. I'll send it on to the DBAs, maybe they can find a patch. Point to note: my computer is running Win XP SP3. This shouldn't have anything to do with it, but I'm not the only person here who has seen this problem.

A bit of clarification: by logging into the server box itself, I was able to run the interpreter and add the RAISERROR calls to my procs. So this indicates that the problem is in the interface between 32 bit SSMS 2008 R2 and the SQL Server instance on the 64-bit computer.

Thanks again to all who replied to this thread, especially Naomi and Mariano.

Answer 33

A bit of clarification: by logging into the server box itself, I was able to run the interpreter and add the RAISERROR calls to my procs. So this indicates that the problem is in the interface between 32 bit SSMS 2008 R2 and the SQL Server instance on the 64-bit computer.

Actually, more likely in the network somewhere. The error message about "transport-level error" is the kind of message you would get if someone
pulled the network cable.

But I am still confused about this:

The comment on "allowing raise to run" was careless wording on my part: I meant to say that the script interpreter would run to completion if 'raise' is in a * * comment, but not if it is in a -- comment. Hope this is clearer. The interpreter's message was that it could not find the function 'raise' when 'raise' was in a -- comment, not in SQL code.

What is this 'raise' thing? Where does it come from? Or is just short for RAISERROR? I would still like to see the full error message not being able to find the 'raise' function - and the code that generates it.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 34

The 'raise' thing was the string 'raise' in a comment - but sometimes the interpreter sees it and, even though it is a comment, gives an error message that it can't find the 'raise' function. Interesting point is that the interpreter doesn't always do this.

As far as pulling the cable - then how does the script run a few seconds later after the 'raise' or RAISERROR have been removed? More likely, the interpreter crashes and the TCP socket is destroyed without properly closing. That would look like the cable had been pulled.

Answer 35

The 'raise' thing was the string 'raise' in a comment - but sometimes the interpreter sees it and, even though it is a comment, gives an error message that it can't find the 'raise' function. Interesting point is that the interpreter doesn't always do this.

Yes, but can you please post the code and complete error message for this scenario?

Look, you are having a problem and you are asking for help. We don't see your system, we have no idea on what's going on in your shop. We are completely reliant on the information you give us. If you don't care to give us the information you ask for, we cannot help you.

As far as pulling the cable - then how does the script run a few seconds later after the 'raise' or RAISERROR have been removed?

Because the cable is put back instantly. (And I am not talking about the physical cable.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 36

Point taken. Please remember that this ONLY occurs when doing a remote SP creation. When I log into the server using a Remote Desktop Connection, there is NO problem - it works perfectly. Others in my group have had the same problem - it isn't just me.

Code block:

 

USE [UMLERCarData_ProdBase]
GO


CREATE PROCEDURE [dbo].[UpdateBaseData] 
	@TrainId	varchar(30),
	@TareWeight	int,
	@GrossWght	int,
	@BldDate	datetime,
	@EquipAddDt	datetime,
	@DupRow		int  OUTPUT
AS
BEGIN
	DECLARE @trnid  	varchar(14)
	DECLARE @trnlen		int
	DECLARE @Gwgt		int
	DECLARE	@Twgt		int
	DECLARE @EqpAdd		datetime
	DECLARE @Rows		int
	
	SET @trnlen = -1
	SET @Rows = 0
	
	SELECT @trnid = TrainEqpId from DownloadData
	WHERE TrainEqpId = @TrainId
	
	SELECT @trnlen = DATALENGTH(@trnid)
	BEGIN TRY		
	
	IF (@trnlen > 0)
	BEGIN
		BEGIN TRAN
			SELECT @Twgt = TareWgt, @Gwgt = GrossRailWgt, @EqpAdd = EqpAddDate
			FROM DownloadData 
			WHERE TrainEqpId = @TrainId
			IF (@Twgt != @TareWeight OR @Gwgt != @GrossWght OR @EqpAdd != @EquipAddDt)
				SELECT @DupRow = 1
			ELSE
				SELECT @DupRow = -1
				 
			UPDATE dbo.DownloadData
			SET		--TrainEqpId  = @TrainID,
					TareWgt     = @TareWeight,
					GrossRailWgt = @GrossWght,
					BldDate		= @BldDate,
					EqpAddDate	= @EquipAddDt
			WHERE TrainEqpId  = @TrainID
			SET @Rows = @@ROWCOUNT
		COMMIT		
	END
	ELSE
	BEGIN
		BEGIN TRAN
			INSERT INTO dbo.DownloadData
				(TrainEqpId,
				 TareWgt,
				 GrossRailWgt,
				 BldDate,
				 EqpAddDate)
			VALUES
				(@TrainId,
				 @TareWeight,
				 @GrossWght,
				 @BldDate,
				 @EquipAddDt)				
   		SET @Rows = @@ROWCOUNT
		COMMIT
	END	
		END TRY
		
		BEGIN CATCH
			DECLARE @errorNumber int,
					@errorSeverity int,
					@errorState int,
					@errorMessage nvarchar(2000),
					@errorLine int,
					@errorProcedure nvarchar(126)
			SELECT @errorNumber = ERROR_NUMBER(),
					@errorSeverity = ERROR_SEVERITY(),
					@errorState = ERROR_STATE(),
					@errorMessage = ERROR_MESSAGE(),
					@errorLine = ERROR_LINE(),
					@errorProcedure = ERROR_PROCEDURE()		
			ROLLBACK TRAN 	
			-- log error
			EXECUTE [UMLERCarData_ProdBase].[dbo].[LogError] 
			   @errorNumber
			  ,@errorSeverity
			  ,@errorState
			  ,@errorMessage
			  ,@errorLine
			  ,@errorProcedure		
			  
	  	RAISERROR (N'Error Number %d "%s" occurred at line %d in procedure [%s].', -- Message text.
			@errorSeverity, -- Severity,
			@errorState, -- State,
			@errorNumber, @errorMessage, @errorLine, @errorProcedure)
			  		
		END CATCH
	RETURN @Rows
END


GO

Error message after 1.5 minute timeout:

Msg 64, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

 

Thanks again.

Answer 37

Point taken. Please remember that this ONLY occurs when doing a remote SP creation. When I log into the server using a Remote Desktop Connection, there is NO problem - it works perfectly. Others in my group have had the same problem - it isn't just me.

Which makes it all reason to believe that something is funky on your network.

I recall that you said this when we discussed this a month ago:

The 'raise' thing was the string 'raise' in a comment - but sometimes the interpreter sees it and, even though it is a comment, gives an error message that it can't find the 'raise' function. Interesting point is that the interpreter doesn't always do this.

And I asked repeated times for the exact code and the exact error message for this scenario.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 38

My last reply was all the code and the error message: that is all there is.

It just may be in our network - has anybody else seen this problem? I'm seeing lots of views on thie thread.

Answer 39

My last reply was all the code and the error message: that is all there is.

Oh well, in the thread back in September, you mentioned a different error message, one where the connection was not broken, but you got an "error message that it can't find the 'raise' function.".

But if you are not interesting clarified what you were talking about, not interested in sharing that error message, oh well.

By the way, did you ever look in the SQL Server errorlog to see if there is any messages related to the error you get when the connection is severed?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 40

Roadkill,

I'm getting the EXACT same issue.  Suppose I have a very simple stored proc created with the following SQL (basically it does nothing):

create procedure [dbo].[TempProc]
as
go

 

This code will execute instantly with success:

 

alter procedure [dbo].[TempProc]
as
	--raise
go

 

This code will cause a timeout/error/whatever:

 

alter procedure [dbo].[TempProc]
as
	--raiserror
go

 

Here is the error after about 18s of execution:

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

 

My @@VERSION:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

 

I ran across this while trying to install ASP.NET SQL Session State on a new SQL Server.  Some of the stored procs Microsoft generates contain RAISERROR statements, which were causing these problems.  Took me a while to track it down.  I was incredulous at discovering the actual issue!  As you said, even commenting the RAISERROR lines still causes the same error.  Unfortunately, I do not have a solution.  I do not have access to the actual machine that this SQL Server is running on, so I can't verify that it would work locally, but I can tell you that that machine is running on a separate network.

UPDATE:  I asked the DBAs to execute the ALTER command with the RAISERROR statement in the comment:

alter procedure [dbo].[TempProc]
as
	--raiserror
go

They were successfully able to execute it both from their desktops and remoted into the server.  They suggested I check my SQL Server Management Studio version.  I was still using the SQL 2008 non-R2 version.  So I upgraded to Microsoft SQL Server Management Studio 10.50.1600.1.  Still same problem.

 

Answer 41

WORKAROUND:  This works as a workaround.

 

DECLARE @cmd nchar(4000)
SET @cmd = N'
alter procedure [dbo].[TempProc]
as
	--rais' + 'error
'
EXEC(@cmd)    
go

 

Putting the command in a text variable and splitting up the RAISERROR with a concatenation is fooling whatever seems to be killing the connection.

For completeness, I did try the following, which resulted in the same error as previously stated.

 

DECLARE @cmd nchar(4000)
SET @cmd = N'
alter procedure [dbo].[TempProc]
as
	--raiserror
'
EXEC(@cmd)    
go

The DBAs are getting networking and security folks involved.  I'll post more when I know something. 

 

Answer 42

Interesting. Seems that you and Roadkill has the same evil network listener installed.

It will be interesting if you security guys finds anything.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 43

It turns out that the network that our SQL Server is on has an intrusion prevention system.  In that system there is a filter with the following description:

This filter detects access to the raiserror procedure on port 1433. The raiserror procedure in a Microsoft SQL Server contains a buffer overflow vulnerability, which can be remotely exploited to execute arbitrary computer code on the affected system, thus allowing an attacker to gain complete control of the server. The procedures known to be vulnerable are: xp_sprintf, raiseerror() and formatmessage(). If the overruns are exploited, the code runs in the context of a local administrator account.

The security folks added an exception to this filter for traffic from my subnet to the database server.  We are now able to successfully execute statements containing the text "raiserror".

Coincidentally, this filter was also preventing us from using the Attach Database UI.  When right-clicking on the "Databases" folder in SSMS and choosing Attach, the client would seem to freeze up for a bit and finally come back with a dialog box that said:

Cannot show requested dialog.
Additional Information:
Parameter name: nColIndex
Actualy value was -1. (Microsoft.SqlServer.GridControl)

After doing a little searching, this particular issue is usually caused by someone trying to attach with a non sysadmin user account.  However, even before the filter exception was applied to the IPS we were still able to attach using SQL commands:

CREATE DATABASE [databasename] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\databasename.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\databasename.LDF' )
FOR ATTACH

 

Hope this helps!

 

Answer 44

Thanks Cyphyr for this report! I hope it will help Roadkill.

I am not entirely surprised. I have never heard of this intrusion prevention system before, but something like that was the only explanation that made any sense.

As for that vulnerability in RAISERROR, I would suspect that it was fixed long ago, but that is just speculation on my part.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 45

Thanks to all who took the time to read this thread and especially to Cypher for two valuable contributions: a clever workaround and a good understanding of the root cause.

Yes, Eland, it is a great help as are the forums as a whole.

Answer 46

As for that vulnerability in RAISERROR, I would suspect that it was fixed long ago, but that is just speculation on my part.

Some googling lead me to:
http://technet.microsoft.com/en-us/security/bulletin/ms01-060
Yes, it was very long ago.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 47

Some googling lead me to:

http://technet.microsoft.com/en-us/security/bulletin/ms01-060
Yes, it was very long ago.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Erland, I should have added this.  The security guy that opened the IPS for sent us this link regarding the vulnerability:
http://www.securityfocus.com/bid/3733/info

That link references Security Bulletin MS01-060, so I believe you are correct.

Answer 48

Network filters for RAISERROR appear safe to remove if a network is confirmed free of live, unpatched* instances of SQL Server 7.0 or SQL Server 2000.

Vulnerability CVE-2001-542 in the National Vulnerability Database (NVD) references Microsoft Security Bulletin MS01-060.

• The NVD record identifies the vulnerable functions: raiserror, formatmessage and xp_sprintf.
• The NVD record and the MS bulletin list the same vulnerable products: SQL Server 7.0 and Microsoft SQL Server 2000

Ref: http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2001-542, http://technet.microsoft.com/en-us/security/bulletin/ms01-060

As of 2014-1-28, CVE-2001-542 remains the only NVD record containing the keyword "raiserror".
http://web.nvd.nist.gov/view/vuln/search-results?query=raiserror&search_type=all&cves=on

* In this context, unpatched would mean:

• SQL Server 7 below version 7.00.1020 (ref: http://smallbusiness.support.microsoft.com/en-us/kb/304851)
• SQL Server 2000 below Service Pack 2 (ref: http://support.microsoft.com/kb/304850)