none
RAISERROR won't compile in sproc RRS feed

  • 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?

     


    Monday, September 19, 2011 5:33 PM

Answers

  • 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!

     

    • Proposed as answer by Cyphyr Tuesday, November 22, 2011 5:54 PM
    • Marked as answer by Naomi NModerator Tuesday, November 22, 2011 6:07 PM
    Tuesday, November 22, 2011 5:43 PM

All replies

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

     


    AMB

    Some guidelines for posting questions...

    Monday, September 19, 2011 5:46 PM
    Moderator
  • 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.

     
    Monday, September 19, 2011 6:23 PM
  • 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...

    Monday, September 19, 2011 6:34 PM
    Moderator
  • 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.

    Monday, September 19, 2011 7:20 PM
  • 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, esquel@sommarskog.se
    Monday, September 19, 2011 9:41 PM
  • 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/
    Monday, September 19, 2011 10:12 PM
  • > 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, esquel@sommarskog.se
    Tuesday, September 20, 2011 7:17 AM
  • 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/
    Tuesday, September 20, 2011 10:58 AM
  • 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.

    Tuesday, September 20, 2011 2:15 PM
  • 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
    Tuesday, September 20, 2011 2:34 PM
    Moderator
  • Thanks for the idea. Sorry, but it didn't work.
    Tuesday, September 20, 2011 4:20 PM
  • 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
    Tuesday, September 20, 2011 4:31 PM
    Moderator
  • 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.

     

    Tuesday, September 20, 2011 5:38 PM
  • 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
    Tuesday, September 20, 2011 7:19 PM
    Moderator
  • 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/
    Tuesday, September 20, 2011 7:29 PM
  • 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. 

    Tuesday, September 20, 2011 9:04 PM
  • > 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, esquel@sommarskog.se
    Tuesday, September 20, 2011 10:03 PM
  • 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
    Wednesday, September 21, 2011 2:46 AM
    Moderator
  • 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/
    Wednesday, September 21, 2011 3:06 AM
  • 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.

    Wednesday, September 21, 2011 2:02 PM
  • 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
    Wednesday, September 21, 2011 2:08 PM
    Moderator
  • 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!

    Wednesday, September 21, 2011 3:43 PM
  • 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, esquel@sommarskog.se
    Wednesday, September 21, 2011 10:20 PM
  • 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.

    Thursday, September 22, 2011 1:49 PM
  • 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
    Thursday, September 22, 2011 1:55 PM
    Moderator
  • 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.
    • Edited by roadkill4299 Thursday, September 22, 2011 2:53 PM more info
    Thursday, September 22, 2011 2:42 PM
  • 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
    Thursday, September 22, 2011 3:04 PM
    Moderator
  • 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.

    Thursday, September 22, 2011 3:53 PM
  • 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
    Thursday, September 22, 2011 3:56 PM
    Moderator
  • 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...:-((

    Thursday, September 22, 2011 4:15 PM
  • 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
    Thursday, September 22, 2011 4:21 PM
    Moderator
  • 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.


    • Edited by roadkill4299 Thursday, September 22, 2011 7:32 PM clarify
    Thursday, September 22, 2011 7:05 PM
  • 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, esquel@sommarskog.se
    Thursday, September 22, 2011 10:10 PM
  • 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.


    Friday, September 23, 2011 1:30 PM
  • 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, esquel@sommarskog.se
    Friday, September 23, 2011 10:02 PM
  • 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.

    Monday, October 31, 2011 5:21 PM
  • 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, esquel@sommarskog.se
    Monday, October 31, 2011 10:27 PM
  • 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.

    Tuesday, November 1, 2011 4:23 PM
  • 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, esquel@sommarskog.se
    Tuesday, November 1, 2011 10:58 PM
  • 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.

     


    • Edited by Cyphyr Friday, November 18, 2011 5:13 PM
    Friday, November 18, 2011 2:15 PM
  • 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. 

     


    • Edited by Cyphyr Friday, November 18, 2011 7:59 PM
    Friday, November 18, 2011 7:54 PM
  • 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, esquel@sommarskog.se
    Friday, November 18, 2011 11:14 PM
  • 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!

     

    • Proposed as answer by Cyphyr Tuesday, November 22, 2011 5:54 PM
    • Marked as answer by Naomi NModerator Tuesday, November 22, 2011 6:07 PM
    Tuesday, November 22, 2011 5:43 PM
  • 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, esquel@sommarskog.se
    Tuesday, November 22, 2011 11:08 PM
  • 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.

    Thursday, November 24, 2011 5:18 PM
  • 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, esquel@sommarskog.se
    Thursday, November 24, 2011 10:58 PM
  • 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, esquel@sommarskog.se
    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.
    Friday, November 25, 2011 2:26 PM
  • 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: raiserrorformatmessage 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-542http://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:

    Tuesday, January 28, 2014 7:54 PM