Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 RAISEERROR Command

Author  Topic 

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-17 : 02:58:12
Both RAISERROR and PRINT can be used to return informational or warning messages to an application. The message text returned by RAISERROR can be built using string substitution functionality similar to the printf_s function of the C standard library, whereas PRINT can only return a character string or character expression. A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block. Specify a severity of 10 or lower to return messages using RAISERROR without invoking a CATCH block. PRINT does not transfer control to a CATCH block.

My question is I want to know how to add this as an error message to a business application such as an accounting system, is it a requirement for the application to have an API to allow this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 03:34:22
you need to add IF check based on your business requirement and call RAISERROR inside IF check block. Then control will get transfered to catch block if present where code can be written to return the error message to calling application. There's no need to separate API for that.
Also keep in mind that if using SQL 2012 RAISERROR is deprecated and you should be using THROW instead inside TRY CATCH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-17 : 19:12:25
Thanks, the application I have is not mine and I do not have access to the source code.

Basically the software has a feature missing that I can place at DB level but I need to tell the end user via a on screen message that this feature has occurred.

I believe the RAISEERROR command cannot do this, is that correct?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 00:29:51
quote:
Originally posted by KingCarlos

Thanks, the application I have is not mine and I do not have access to the source code.

Basically the software has a feature missing that I can place at DB level but I need to tell the end user via a on screen message that this feature has occurred.

I believe the RAISEERROR command cannot do this, is that correct?


Nope it can do it
Based on yur conditions you can write a custom error message and raise it through RAISERROR but obviouly you need access to db for that.

If you cant even access db only solution would be to do the check and display message using your front end code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-18 : 02:20:25
OK, thanks

So I have created a table to warn the individual staff member, as follows

create table X_MYpeople (staffno int, spid int)
GO

Then a trigger

create trigger X_GetSPID on staff for update as
set nocount on
if update(last_login)
begin
delete from X_MYpeople where spid = @@spid

insert into X_MYpeople (staffno, spid)
select i.staffno, @@spid
from inserted i
end
GO

Then the function

create function dbo.X_GetMYpeopleno () returns int
as begin
return (select top 1 staffno from X_MYpeople where spid = @@SPID)
end

This trigger works with the rollback but no error is displayed on screen

Create TRIGGER [dbo].[X_PREVENT_ALLOCATION]
ON [dbo].[ALLOCATIONS]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
BEGIN
IF DBO.X_GETMYPEOPLENO() IN (1,8)
BEGIN
RAISERROR ('YOU ARE NOT ALLOWED TO ALLOCATE PAYMENTS',16,1)
ROLLBACK TRAN
END
END
END

Any advice on what I am missing
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 02:26:33
sorry i didnt get what you're trying to implement here. whats the deal with @@SPID?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-18 : 02:29:58
I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 02:32:03
quote:
Originally posted by KingCarlos

I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task.


How can you identify individuals by means of @@SPID? it will just return you current processid.
why not use SUSER_SNAME() or similar functions to get logged in user and see if they're member of unauthorised group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-21 : 01:14:19
quote:
Originally posted by visakh16

quote:
Originally posted by KingCarlos

I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task.


How can you identify individuals by means of @@SPID? it will just return you current processid.
why not use SUSER_SNAME() or similar functions to get logged in user and see if they're member of unauthorised group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





Cheers for that, I have tried using the SUSER_SNAME() but cannot get it to Raise an error.

Can you provide a simple example on how I can capture the SQL logon and raise an error to that user that they cannot change data.

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:04:30
Ok...tell me under what condition you need to raise the error? do you've a table where you store which users should have access to what etc details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-23 : 19:39:34
Thanks, so lets say they enter data into a table I want to rollback that transaction and then raise an error to the end user that the record was not accepted.

I will have a table of SQL authentication users that I can call upon that will be used as the criteria that they cannot enter the record.

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 00:46:09
ok. in that case you need a check like this

..
IF EXISTS(SELECT 1 FROM YourUserTable WHERE user_name = SUSER_SNAME() AND Access_Right = 'Restricetd')
RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1
ELSE
... actual logic here


I've assumed column names so make sure you use correct columnnames instead. I also assume you've a column which gives details on user access in table like granted, restricted etc

If your table structure is different please post it otherwise we cant guess on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-24 : 01:25:57
thanks for that, unfortunately hit a snag,

here is my table of users

create table X_Staffs (staffno int, enduser nvarchar(256), access_right varchar(50), task varchar(50))
GO

I have added into my table a end user

insert into X_staffs (staffno, enduser, access_right, task)
values ('1','sa','Restricetd','allocation')

but this trigger is not working.

Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]
ON [dbo].[DR_ALLOCATIONS]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
BEGIN
IF (SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd')
RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1
ROLLBACK TRAN
END
END
END

I would hope it would capture the end user doing an activity, cross reference the table holding their details, if restricted roll back the transaction and then raise an error message.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 01:31:06
i cant see any condition in your trigger code where you're checking for task details. The way its written currently it will throw error for any activity by the user on the table DR_ALLOCATIONS
I think it should be like

Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]
ON [dbo].[DR_ALLOCATIONS]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
BEGIN
IF (SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)
RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1
ROLLBACK TRAN
END
END
END


You can pass the task value inside or just use a control table to capture and pass it inside trigger

Also keep in mind that whatever you're implementing in this trigger will get executed only based on INSERT and UPDATE activity on DR_ALLOCATIONS table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-24 : 02:15:41
I think there is something fundamentally wrong in the code, this is the error when trying to create the trigge.r

Msg 4145, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'RAISERROR'.
Msg 102, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 13
Incorrect syntax near 'END'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 02:20:04
You missed an EXISTS condition in your posted code and I just carried it all along as I copied from it


Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]
ON [dbo].[DR_ALLOCATIONS]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)
BEGIN
RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1
ROLLBACK TRAN
END

END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-24 : 02:30:54
there seems to be a problem on the RAISEERROR command line, see below

Msg 102, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 9
Incorrect syntax near 'The logged in user doesnt have access to perform the current operation'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 02:35:27
[code]
Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]
ON [dbo].[DR_ALLOCATIONS]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)
BEGIN
RAISERROR (N'The logged in user doesnt have access to perform the current operation',16,1)
ROLLBACK TRAN
END

END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-24 : 02:47:02
thanks, still the raise error does not give an error when performing an action in the application.
If you can give me an example fo a raiseerror command when a record is deleted I will test that but I cannot see the RAISEERROR working, happy to be proven wrong.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 02:49:13
If you can give me an example fo a raiseerror command when a record is deleted

thats not the case we're dealing here

as trigger you've written is only for INSERT and UPDATE actions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-06-24 : 06:49:52
Does it matter? I can write simple triggers based on INSERT, UPDATE and DELETE. The question is can it raise an error on screen to the end user when they perform one of these functions in an application.

so lets take an UPDATE, a user adds a record and the application has no prompt to tell the user to add Data for ABC.

My original question was can the SQL RAISEERROR inform the user on screen that this is required.

My suspicion is no it cannot, you claim it can, I am yet to see my suspicion proved wrong.

If you can show me the way it would be appreciated based on an application which uses SQL authentication for all users.

thanks
Go to Top of Page
    Next Page

- Advertisement -