?
Solved

Raise an error in sql server 2005

Posted on 2011-05-11
17
Medium Priority
?
262 Views
Last Modified: 2012-06-27
What i am trying to do is to raise My own error from sql server 2005, the problem is as follow:

-What range of error i can use because what i know is sql server reserve a range of errors from it self.
-What is the specified command to raise an error because i want to catch the error from a .net application.

Thank You.
0
Comment
Question by:MKItani
  • 8
  • 8
17 Comments
 
LVL 61

Accepted Solution

by:
HainKurt earned 1000 total points
ID: 35737303
http://msdn.microsoft.com/en-us/library/ms178592.aspx
 
RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

msg_id
Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35737387
RAISERROR('Message')
0
 

Author Comment

by:MKItani
ID: 35737743
thank u all,

i read about what you are wrote and what i do is as follows:
RAISERROR (N'This is message %s %d.', 20, 1, N'number', 50005);

in my .net application i am trying to catch the sql error 50005 but i have failed why?

please send me a solution.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 61

Expert Comment

by:HainKurt
ID: 35737904
how do you catch the error in .net?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737931
try specify a severity of 10 or lower instead of 20
0
 

Author Comment

by:MKItani
ID: 35737950
try
{
My Code
}
catch (SqlException SqlExc)
{
 if (SqlExc.Number == 50005)
{
messagebox.show("It is my error");
}
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35737954
from MS

http://support.microsoft.com/kb/321903

Catch SqlEx As SqlException
            Dim myError As SqlError
                        Debug.WriteLine("Errors Count:" & SqlEx.Errors.Count)
            For Each myError In SqlEx.Errors
                Debug.WriteLine(myError.Number & " - " & myError.Message)
            Next
        End Try
0
 

Author Comment

by:MKItani
ID: 35737992
i have used severity=1:
RAISERROR (N'This is message %s %d.', 1, 1, N'number', 50005);
but the sql error number isn't equal to 50005  Why?

i am waiting for your response.

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35738027
did you try solution above? like this:

SqlExc.Errors(0).Number
0
 

Author Comment

by:MKItani
ID: 35738091
Yes but what i want to know that the mentioned code
RAISERROR (N'This is message %s %d.', 1, 1, N'number', 50005);
even it is not catched by the followed block

Catch SqlEx As SqlException
            Dim myError As SqlError
                        Debug.WriteLine("Errors Count:" & SqlEx.Errors.Count)
            For Each myError In SqlEx.Errors
                Debug.WriteLine(myError.Number & " - " & myError.Message)
            Next
        End Try
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35738205
what is your sql code... are you sure you are raising the error? can you get any exception from .net?
0
 

Author Comment

by:MKItani
ID: 35738255
Yes i catch the built in sql exception like error number 2627 Violation of PRIMARY KEY constraint 'PK_Project_Tracker'. Cannot insert duplicate key in object 'dbo.Project_Tracker'.

but when i am using RAISERROR (N'This is message %s %d.', 1, 1, N'number', 50005);
i didn't get any error why?

My sql code is as follows:

USE [Beam]
GO
/****** Object:  StoredProcedure [dbo].[TrackerProjectContact_Insert]    Script Date: 05/11/2011 16:23:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TrackerProjectContact_Insert]
(
      @ProjectId int,
      @No_ varchar(20),
      @CreatedBy varchar(20),
      @ModifiedBy varchar(20)
)
AS
      SET NOCOUNT OFF;
set @CreatedBy=@ModifiedBy;

RAISERROR (N'This is message %s %d.', 1, 1, N'number', 50005);
return
INSERT INTO [dbo].[TrackerProjectContact] ([ProjectId], [No_], [CreatedBy], [ModifiedBy]) VALUES (@ProjectId, @No_, @CreatedBy, @ModifiedBy);
      
SELECT Id, ProjectId, No_, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, LastUpdate FROM TrackerProjectContact WHERE (Id = SCOPE_IDENTITY())


0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35738425
when you use severity 19,20 did you get any error in .Net?
0
 

Author Comment

by:MKItani
ID: 35738455
In severity 20 i got an sql error number "2754"
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35738459
check this ms page

http://support.microsoft.com/kb/321903

here they have a sample that raises errors from sp and catch them in .net app...

when you catch the exception from .net you should loop all errors in sqlException and get ErrorCode...
0
 

Author Comment

by:MKItani
ID: 35744215
i read the page and look what i did,
in my stored procedure i set " RAISERROR (50001, 11, 1) " and when i set the severity equal to 11 i have captured the error in my .net application but the sql error number equal to 50000 and not 50001 so what i can do to capturated the error 50001 because in RAISERROR  i set 50001, maybe there is a missing parameter i should to set it in RAISERROR, i don't know?

i am wiating for your response.
0
 

Author Closing Comment

by:MKItani
ID: 35744441
no clear  instructions
no previous experience in question
but the good issue is follow up the question and it is trying to find a soultion
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question