When I use the following code to execute a RAISERROR from within a CLR Routine (Stored Procedure), and I call this CLR stored procedure from T-SQL within a
TRY/CATCH block, the error is not caught in the CATCH block. Why is
this happening?
try { SqlContext.Pipe.ExecuteAndSend(cmd); } catch { }
When I use the
following code to execute a RAISERROR from within a CLR Routine (Stored
Procedure), and I call this CLR stored procedure from T-SQL within a
TRY/CATCH block, the error is not caught in the CATCH block. Why is
this happening? Is there any way around this? Any help much appreciated.try { SqlContext.Pipe.ExecuteAndSend(cmd); } catch { }
I would like to know if there was any semantic differences between the two following statements?
RAISERROR('Invalid argument.',11,1);
RETURN;
or simply
RAISERROR('Invalid argument.',11,1) WITH NOWAIT;
It seems that both statements interrupt the current transaction.
Does anyone has an idea on the subject?
Thanks in advance,
Joannes
Hi there,
I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,
job: daily backup
step1. truncate log
step2. shrink log
step3. backup
step. updateusage
job weekly admin
step1. defrag indexes (sometimes re-index + update stats)
step2. truncate log
step3. shrink db
step4. checkdb
Does this look good enough?
is there anything else I should add?
What do you have in place currently?
Thanks for your help in advance
Hi, I recently migrated from SQL*Server v7.0 to v2000. The maintenance routines on the new v2000 was set up (this is also a new server - completely new machine) are not running. When they attempt to run, I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
Consequently, no backups no repair, the only thing in the maintenance routine that does work is the rebuilding of indexes. Any ideas would be greatly appreciated.
Thanks in advance,
Nancy
We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“
select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'
However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“
select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'
0 rows are returned!
Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.
Hi,
I'm running W2K3 SP1. The MDAC Configuration Checker reports MDAC 2.8 SP2. The only discrepancy is MSADCO.DLL, where the expected version is 2.82.1830.0 and my actual version is 2.82.2651.0 (svr03_sp1_gdr.060301-1546).
The error message I'm getting when I use the ODBC Administrator dialog to set up a new entry is: "The setup routines for the SQL Server ODBC driver could not be found. Please reinstall the driver."
This is followed by a dialog titled: "Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed." The body text of the dialog is "Component not found in the registry."
Does anyone have any idea how I can repair this?
Thanks,
In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the stored procedure, In case of an error in the stored procedure I need to return an exception to application, will RAISERROR in stored procedure accomplish that?
View 1 Replies View RelatedIs there any log regarding RAISERROR and rollback in the SQL Server. I would like to know how many times it happened in a day.
View 1 Replies View RelatedHello,I am raising an error on my SQL 2005 procedure as follows: RAISERROR(@ErrorMessage, @ErrorSeverity, 1)How can I access it in my ASP.NET code?Thanks,Miguel
View 1 Replies View RelatedWhen I have an raiserror. This is what I got:
Server: Msg 50000, Level 16, State 1, Line 6
No Rows Found for 1 and Rachel.
Question: How do I get rid of this line: "Server: Msg 50000, Level 16, State 1, Line 6"
Is there a way to raise an exception inside a user function in the Sqlserver2000?.
View 5 Replies View Relatedhi
RAISERROR is used to return message to the caller.
how to contain RAISERROR :
variable
declare @name varchar(50)
and string
'Welcome'
i want to contain the RAISERROR messege 'Welcome' + @name value in the same time
ex
Welcome Zaid
can give the code to do this
thank you
hey all,
just cant figure out why my raiserror print
Wrong RoleType for customer lientID
for :- RAISERROR('Wrong RoleType for customer %ClientID', 16,1, @ClientID)
what's with missing C? is % some kind of escape char or something? (im trying to print back the parameter @clientid), or should i just use the print ''
~~~Focus on problem, not solution~~~
Im working in a Oracle to SQL migration project, I need to migrate a function
which is using Raiserror()
I have a function in Oracle like this,
create function fn_name( parameters )
returns int
as begin
if ( condition )
-- do some logic
else
raiseerror()
end
I need to migrate this to SQL server 2005.
From next version we wont have Extended procedure, so its better to avoid.
Instead that we can use CLR integration.
Can anyone help me out...
This statement adds a new message to the master..sys.messages table
EXEC sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = N'The item named %s already exists in %s.'
But if this error happens, how is an application supposed to access this message? (The average app shouldn't need to access to the master database to get this info.)
Barkingdog.
In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the stored procedure, In case of an error in the stored procedure I need to return an exception to application, will RAISERROR in stored procedure accomplish that?
View 1 Replies View RelatedIs there any way to emulate RAISERROR from CLR UDT?
Thanks
Im working in a Oracle to SQL migration project, I need to migrate a function
which is using Raiserror()
I have a function in Oracle like this,
create function fn_name( parameters )
returns int
as begin
if ( condition )
-- do some logic
else
raiseerror()
end
I need to migrate this to SQL server 2005.
From next version we wont have Extended procedure, so its better to avoid.
Instead that we can use CLR integration.
Can anyone help me out...
I have used RAISERROR on some of our pages before, and it worked fine. Now I have a page that has a formview with a sqldatasource that does an insert. If the value for a certain field exists already in the table, I am trying to use RAISERROR('message', 15, 1) to have a popup error. The page does a redirect in the iteminserted event. When I try to insert with data that should cause an error, it doesn't insert into the database, but I don't see an error. The page just redirects... any ideas what could be done to fix this?
View 1 Replies View RelatedI have an Alert and a Raiserror which I need to do 3 things.
1. Recognize the error (that works)
2.Raise the alert and email the error message to support
3. Return the error message raised to the user application.(not working)
is returning the error message to the user related to the way the application
runs or is this a fairly generic function. (This is sort of an oddball app which is compiled C++ that inteprets data to create it's screens.)
Whats the relation between SYBASE RAISERROR and SQL Server RAISERROR in terms of user-defined error codes
View 1 Replies View Relatedhi,
How can i insert variables on RAISERROR message ?
RAISERROR('There is no value to compare with the referenced Date '+@ReferenceDate,16,1)
Hello,
I can't find any documentation about this syntax, but it works in sql 2005.
RAISERROR @Error @Message
Can you give me a clue - what is it ? why it works ? why not documented ? and what possible issues can I have by using it ?
Thanks
Hi, I am trying to write a trigger in SQL 2005 and am running into a lot of issues. Here is my code
BEGIN
BEGIN TRY
DECLARE @count int
--check for timeon which is greater than timeoff
SET @count=0
SELECT @count=count(*)
FROM IVRCONFIG.dbo.C_GROUP_HOURS a WITH (NOLOCK),inserted i
WHERE a.XFER_GROUP = i.XFER_GROUP
AND a.DAY_OF_WEEK = i.DAY_OF_WEEK
AND a.TIMEON = i.TIMEON
AND a.TIMEOFF < a.TIMEON
IF @count<>0 RAISERROR (50001,16,1);
END TRY
BEGIN CATCH
RAISERROR
END CATCH
END
The error I specify is one I added to sys.messages. Can anyone tell me where I am going wrong??
I was curious if using RAISERROR in the catch block of a stored procedure does actually causes some hit on performance? I think it would, as compared to simply returning an error code in this sp's output parameter.
View 1 Replies View RelatedHi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?
Here's the sp:
CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO
Here's the ASP.Net page:
<% @Page Language="VB" debug="True" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
o_conn.Open
Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()
Response.Write(o_conn.State)
End Function
Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}" & _
"on line {4} of procedure {5} on server {6}:{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message))
Next
End Sub
Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
End Sub
</script>
I am testing a simple stored proc below.
create procedure procpooh @val1 int, @val2 int as
insert into pooh (val1, val2)
values(@val1,@val2)
declare @error int
SELECT @error = @@ERROR
IF @error <> 0
BEGIN
print 'it failed'
RAISERROR(50020,16,1,@error)
end
When I run the proc and feed it the values it fails(that' what I expect)
because there is a field not being supplied which will not allow nulls.
However neither the print ' it failed ' or the raiserror work. I get the message below instead. Any ideas why the print or raiserror don't work?
Server: Msg 515, Level 16, State 2, Procedure trpooh, Line 11
Cannot insert the value NULL into column 'val3', table 'Chapter5DB.dbo.pooh2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
We’re having trouble getting error messages to show up on clients. Our ADO research indicates that the Errors collections is populated, “automatically” – what you do with it is up to the application. Our collection is not being populated. MS says the SQLOLEDB provider has a problem (the collection is not filled) if SET NOCOUNT is OFF. We have SET NOCOUNT ON and still have the problem. We have narrowed the problem down (the example below is an abbreviated version) to “the Errors Collection is not populated if the Raiserror follows a SELECT statement that returns a recordset”.
In the code below the simple select run after the first RAISERROR appears to “block” the Error Collection. Is this by design? Are you never supposed to be able to return records and messages from the same program? We can code around it if we have to, but the documentation seems to indicate our approach is viable.
Any ideas would be most appreciated.
SQL Stored Procedure:
CREATE PROCEDURE Address_Ck
(
@Address_ID INTEGER OUTPUT
, @MailOnly_LG BIT
, @Ctry_ID SMALLINT
, @StPv_ID SMALLINT
, @PostCode_ID INT
, @PostCode_Ext_CH CHAR(10)
, @Add1_VC VARCHAR(60)
, @Add2_VC VARCHAR(60)
, @Add3_VC VARCHAR(60)
, @Add4_VC VARCHAR(60)
, @City_VC VARCHAR(30)
, @City_Lock_LG BIT
, @Directions_VC VARCHAR(2000)
)
AS
DECLARE
@Finder_VC VARCHAR(20)
, @Label1_VC VARCHAR(60)
, @Label2_VC VARCHAR(60)
, @Label3_VC VARCHAR(60)
, @Label4_VC VARCHAR(60)
, @Label5_VC VARCHAR(60)
, @Label6_VC VARCHAR(60)
-- the error in the next line shows up when not commented out
--RAISERROR ( ‘ This error always shows up.’ ,16,1)
SELECT Address_ID
, Label1_VC
, Label2_VC
, Label3_VC
, Label4_VC
, Label5_VC
, Label6_VC
FROM Address_T
WHERE Finder_VC= @Finder_VC
RAISERROR ( ‘ Why won’t this error showup?. ’ ,16,1)
-- the error above never shows up
************************************************** ****
************************************************** ****
THE VB CODE:
Option Explicit
Dim db As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Private Sub Command1_Click()
On Error GoTo errmsg
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
With db
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=Jeanne;trusted_connection = true;integrated security=sspi"
.Open
.DefaultDatabase = "DevTime21"
End With
With cmd
.ActiveConnection = db
.CommandType = adCmdStoredProc
.CommandText = "address_findck_okinsert_m"
.Parameters.Append .CreateParameter("@address_id", _
adInteger, adParamOutput, 4)
.Parameters.Append .CreateParameter("@Mailonly_lg", _
adBoolean, adParamInput, 1, False)
.Parameters.Append .CreateParameter("@ctry_id", _
adInteger, adParamInput, 4, 1)
.Parameters.Append .CreateParameter("@stpv_id", _
adInteger, adParamInput, 4, 1)
.Parameters.Append .CreateParameter("@postid_id", _
adInteger, adParamInput, 4, 0)
.Parameters.Append .CreateParameter("@postcode_ext", _
adChar, adParamInput, 10, "")
.Parameters.Append .CreateParameter("@add1_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add2_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add3_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@add4_vc", _
adVarChar, adParamInput, 60, "")
.Parameters.Append .CreateParameter("@city_vc", _
adVarChar, adParamInput, 30, "")
.Parameters.Append .CreateParameter("@city_lock_lg", _
adBoolean, adParamInput, 1, False)
.Parameters.Append .CreateParameter("@directions_vc", _
adVarChar, adParamInput, 2000, "")
End With
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
If Not IsNull(cmd("@address_id")) Then
Else
MsgBox "Please contact System Administrator. There was a problem adding address."
End If
Exit Sub
errmsg:
Call errormsginfo
End Sub
I've defined an error message 50002, severity level 16 that is logged.
I've defined and alert on error number 50002 to catch it.
I can raise the error using raiserror and it looks like this -
Raiserror ( 50002,16, 1, @tname)
The error is raised and the text is printed, but the alert is never generated.
Am I missing something?
Thanks in advance.
I am trying to get a better understanding of when to use return (witha print statement) and when to use raiserror.* Both statements can be used with stored procedures while only returncan be used with functions.* With raiserror it is easy to have multiple errors thrown. (If boththe calling procedure and called procedure both try to handle error)Wow. Thought I could think of more. So that really leaves me with verylittle info on the proper use of these two statements.
View 2 Replies View RelatedGreetings,
I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.
I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended.
I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR?
Thanks,
BCB
Hi All,
I need to raise an error in the Application Event log based on a condition. The condition is, if a query returns any rows, an error has to be logged. For that this is what I am doing:
Code Snippet
If exists()
RAISERROR ('There are agents that have not hearbeated within the last 15-30 mins', 16, 1) WITH LOG
Now, in addition to the message in the RAISERROR, I would also want to display the results of that query in the If Exists block. How can I do that? If this is not clear, below is something which I want to do. Is this or anything similar possible?
Code Snippet
If exists(<Select * from table>)
RAISERROR ('There are agents that have not hearbeated within the last 15-30 mins', 16, 1, Select ServerName from table) WITH LOG
Thanks a lot in advance,
Manoj Deshpande.