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?
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 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,
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.
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.
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."
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?
Hello,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
hi 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
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 ''
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.)
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?
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?
I 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.)
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.
Hi. 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.
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
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.
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?
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