Answered by:
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?
- Edited by roadkill4299 Monday, September 19, 2011 5:34 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
All replies
-
- Edited by HunchbackMVP, Moderator Monday, September 19, 2011 5:47 PM
-
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:
CATCH
,
@errorSeverity
int,
@errorState
int,
@errorMessage
nvarchar(2000),
@errorLine
int,
@errorProcedure
nvarchar(126)
(),
@errorSeverity
= ERROR_SEVERITY(),
@errorState
= ERROR_STATE(),
@errorMessage
= ERROR_MESSAGE(),
@errorLine
= ERROR_LINE(),
@errorProcedure
= ERROR_PROCEDURE()
-- log error
@errorNumber
@errorSeverity
@errorState
@errorMessage
@errorLine
@errorProcedure
TRAN
-- RAISERROR function call doesn't compile, see the log table
If I remove the string RAISERROR from the comment, it compiles in 2 seconds or less.
-
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
- Edited by HunchbackMVP, Moderator Monday, September 19, 2011 6:35 PM
-
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.2600Hope this helps.
-
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 -
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/ -
> 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- Proposed as answer by Naomi NModerator Tuesday, September 20, 2011 1:49 PM
-
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/ -
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.
-
-
-
-
-
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 -
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/ -
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.
-
> 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 -
-
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/ -
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.
-
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 -
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!
-
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 -
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.
-
-
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
-
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 -
Ran the statements above and got back the same information as @@VERSION gave:
Edition ProductVersion ProductLevel
Enterprise Edition (64-bit) 10.0.2531.0 SP1Thanks 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.
-
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 -
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...:-((
-
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 -
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
-
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 -
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.
- Edited by roadkill4299 Friday, September 23, 2011 2:56 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 -
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.)
- Edited by roadkill4299 Monday, October 31, 2011 5:54 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 -
-
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 -
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
-
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
-
-
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
-
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 -
-
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 -
Some googling lead me to:
Erland, I should have added this. The security guy that opened the IPS for sent us this link regarding the vulnerability:
http://technet.microsoft.com/en-us/security/bulletin/ms01-060
Yes, it was very long ago.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
http://www.securityfocus.com/bid/3733/info
That link references Security Bulletin MS01-060, so I believe you are correct. -
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)