question

AleVecca-4285 avatar image
0 Votes"
AleVecca-4285 asked ErlandSommarskog commented

¡AYUDA! SQL SERVER 2008 R2 MANAGEMENT STUDIO A SQL SERVER 2019 EXPRESS

Buenas noches, comunidad. Soy nuevo en esto y requiero de su generosa ayuda, si no es mucho pedir.
Desde hace unos años vengo practicando la programación con el Visual Studio y el SQL 2008, como lo indiqué en el título.

Resulta ser que tengo una pequeña aplicación de escritorio, para la cuál uso el SQL Server 2008 R2.

Descargué la versión más actual para actualizarme y migrar mi base de datos, pero grande fue mi sorpresa cuando me percaté de que dentro de todos mis procedimientos almacenados (en el SQL 2019) me generaba error en el "raiserror", entonces intenté utilizar otros controles de error, pero no obtuve buenos resultados.

Lo más raro de esto es que en otro disco duro tengo montado el Sql 2008 y 2019, y al ejecutar el 2019 (existiendo el sql 2008), sí reconoce correctamente mi procedimiento.

No sé si es alguna cuestión de características, pero hasta ahora no he podido resolver el problema.

Si es que alguien es tan amable de ayudarme, se lo agradecería bastante.

Les dejo parte de mi código para que se fijen y puedan entender de lo que estoy hablando.



Este es mi procedimiento almacenado y más abajo les indicaré la línea que me genera error.

USE [bodega]
GO
/ Object: StoredProcedure [dbo].[sp_insUsuario] Script Date: 03/05/2021 20:38:27 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_insUsuario]
@nombre varchar(50),
@nick varchar(20),
@pass varchar(20),
@estado bit
AS
BEGIN
declare @verrno int, @verrmsg varchar(255)
begin tran
--Si ya existe el usuario con el mismo nombre.
If Exists(select * from usuario where nombre=@nombre)
begin
select @verrno=50000,
@verrmsg='El nombre del usuario ingresado ya existe'
goto error
end
--Insertar
Declare @idUsuarioAlta int
Set @idUsuarioAlta = (Select isNull(Max(idUsuarioAlta)+1,1) From usuario)
Insert into usuario(idUsuarioAlta,nombre,nick,pass,estado)
Values(@idUsuarioAlta,upper(@nombre),upper(@nick),@pass,@estado)
set @verrno=@@error
if (@verrno <> 0)
goto error

 commit transaction
 Return @@rowcount

 error:
 raiserror @verrno @verrmsg --Esta es la línea que en el SQL 2008 corre perfecto y en el 2019 marca error.
 rollback transaction

END

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is an English spoken forum, so please post your question/issue in English.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

The smallest modification would be

 RAISERROR (@verrmsg, 10, 1)

=> adding bracket, parameter separation with comma and serverity + state

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much for answering. I just tried the line of code, it works, but it doesn't trigger the error, it just displays the error message that I set. Do you happen to know why it does work in SQL Server 2008 R2 and not in 2019?

0 Votes 0 ·
   RAISERROR (@verrmsg, 10, 1)

Shouldn't that be:

RAISERROR (@verrmsg, 11, 1)

Level 10 is only an informational message.

0 Votes 0 ·

Thank you, but what is the difference between 10 and 11?

0 Votes 0 ·

See Database Engine Error Severities for more details about the different values for that parameter.


1 Vote 1 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

raiserror @verrno @verrmsg

RAISERROR without brackets and comma was possible in older SQL Server version, meanwhile they are mandatory for correct syntax =>see RAISERROR (Transact-SQL) for correct syntax.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AleVecca-4285 avatar image
0 Votes"
AleVecca-4285 answered

Hello, thanks for answering. I had not realized that I was in an English forum, sorry. And I had already seen that forum with those examples and when trying to implement them I keep getting errors. Could you please show me with my code an example of how it should look? Thank you in advance. Greetings.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered AleVecca-4285 commented

Hi @AleVecca-4285,

The syntax of RAISERROR had some change for SQL Server 2008 and SQL Server 2019. The syntax in SQL Server 2019 shows below:

 RAISERROR ( { msg_id | msg_str | @local_variable }  
     { ,severity ,state }  
     [ ,argument [ ,...n ] ] )  
     [ WITH option [ ,...n ] ]  

but what is the difference between 10 and 11?

It is the severity level of the error. The higher the level, the more severe the level and the transaction should be aborted. Please refer to this thread to get more information.


Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your answer. It served me well. Now it only remains to test and see how the validations I have behave.

0 Votes 0 ·