Update: Can I Catch The Return Message And Act On It?
Nov 2, 2006
I use a storeed procedure to execure a dynamic sql string sent from an application. This string often contain a simple update. An update with anyting but one row affected is in this case to be considered an error/problem. As ut use the primary key in the where clause the and I want to store away the update values in anoter table
I want to act on the "(0 row(s) affected)" message. Is there a way to do that or do I have to query the database before I do the update?
View 1 Replies
ADVERTISEMENT
May 24, 2006
Greetings all,
When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?
When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...
Thanks
View 14 Replies
View Related
May 17, 2008
Hi guys!
the problem is this:
I have a procedure that execute a function and i want to catch the return function value but i'm getting a null value.
I'm using:
exec @a = <function>
but the @a variable is allways null...
If i exec the function directly, i get the correct value.
What am i doing wrong?
Thanks in advance.
View 10 Replies
View Related
Oct 26, 2007
Hi All,
I have the same question and error that Chopaka is getting:
"I have a SQL 2005 job that calls a stored proc. The job step returns the message "Query timeout expired....Message 7412...The step succeeded." The proc never actually ever did anything due to the query timeout, and the job continued on to other steps. I'm going to address the timeout issue eventually, but first I'd like to trap the timeout problem and force the job to end.
It appears that the query timeout isn't really an error, just a message. I've tried TRY-CATCH in the SP but the situation isn't caught, again probably due to the interpretation that it isn't an error."
I have reduced the "Remote Query time out" to 1 sec, in order to catch the error and to prevent the job from running, but the error is not caught.
Is there a way to catch this in the SQL or in the job step to prevent the job from continuing?
This is the script that I'm using without any luck
BEGIN TRY
BEGIN Transaction
Create table #tmpSummaryTable
(
)
insert into #tmpSummaryTable
select * from CDRServer01.iXtemp.dbo.gx_tbFTRSummary_test
COMMIT Transaction
END TRY
BEGIN CATCH
DECLARE @err int
SELECT @err = @@error
PRINT '@@error: ' + ltrim(str(@err))
SELECT ERROR_NUMBER() ERNumber,
ERROR_MESSAGE() Error_Message
ROLLBACK
Return
END CATCH
View 3 Replies
View Related
May 20, 2014
The problem is as follows. SQL Express 2008 R2, backup is made by the means of bat file and sp. Need to have a table that would hold columns like 'date_of_backup', 'duration', 'message' (the one that appears on 'messages' tab in the result area after backup completion/failure). This is an example of real message that I want to catch:
Server: Msg 911, Level 16, State 11, Line 1
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
View 0 Replies
View Related
Aug 15, 2006
How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!
/Jonneponne
View 1 Replies
View Related
Jan 5, 2005
hello!
im new to sql... what i'm trying to do is catch the error on insert or update statment of sql.. sound simple but please..
this is the sample table design...
tbl_Customer
CustomerID int(4) Primary AutoIncrement
CustomerCode nvarchar(25)
CustomerName nvarchar(25)
..
..
Deleted bit(1)
what i'm trying to do is when a record is deleted, it's not actually deleted
in the table but only marked 1 (true) the Deleted field.. because i don't want
to lose the relationship...
it's easy to do this on insert statement like this..
Create Procedure InsertCustomer(@param1 ....) AS
IF NOT EXIST (SELECT * FROM tbl_Customer WHERE DELETED = 0) THEN
// do insert statement here
ELSE
// Do nothing
GO
this is also easy if i create a index constraints on the table.. but this will violate my design idea..
so anybody can help me to create the procedure in update statement and insert statement
View 1 Replies
View Related
Dec 17, 2007
Hi,
I am creating update trigger(After) on a table.I want to catch an updated row and insert the values of this row into
a new table.How can i catch this row while creating a trigger after update.
View 7 Replies
View Related
Feb 15, 2007
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
View 3 Replies
View Related
Feb 6, 2007
hi,how to pass message to front end
select count(*) from emp where empno=1
if(count=0)
i want to pass "no records" to front end(vb.net)
if(count=1)
"records found"
is it possible to do this in sqlserver,if means please help me to do
View 3 Replies
View Related
Jul 30, 1999
I am new to SQL Server 7 and get the following error message when I go into the Enterprise Manager, select a specific table, right mouse click, open table, return all rows:
"An unexpected error happened during this operation.
[MS Design Tools] - Query designer encountered a MS Design Tools error: Not Implemented"
Can anyone explain what is causing this error?? I can go into SQL Query Analyzer and do a SELECT against any table and bring back data, but not as stated above.
View 1 Replies
View Related
May 7, 2008
I have a stored procedure which checks to see if a user's email address exists before it inserts a new record. If it does it should return a message that notifies the user they are already subscribed. If they are not a different message should be returned stating that a new subscription has been created. This procedure works and is shown below.
The problem I am having is with the first SELECT statement. How can I get the procedure to show just one message and not the results from the first SELECT statement too?
CREATE PROCEDURE sp_InsertSubscription
@SubscriberFirstName VARCHAR(50),
@SubscriberLastName VARCHAR(50),
@SubscriberEmailAddress VARCHAR(50),
@SubscriberZipCode INT,
@IsActive BIT,
@SubscriberOptIn BIT,
@AdditionalOffersOptIn BIT,
@Msg VARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Check to see if email address exists first
SELECT @@ROWCOUNT FROM Subscriber WHERE SubscriberEmailAddress = @SubscriberEmailAddress
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'This email address is already subscribed'
SELECT @Msg AS 'User'
RETURN
END
-- Insert statements for procedure here
INSERT INTO Subscriber (SubscriberFirstName,
SubscriberLastName,
SubscriberEmailAddress,
SubscriberZipCode,
IsActive,
SubscriberOptIn,
AdditionalOffersOptIn,
SubscriberSignUpDate)
VALUES (@SubscriberFirstName,
@SubscriberLastName,
@SubscriberEmailAddress,
@SubscriberZipCode,
@IsActive,
@SubscriberOptIn,
@AdditionalOffersOptIn,
GETDATE())
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'New user subscription created'
END
SELECT @Msg AS 'User'
END
GO
If the user's email address does not exist I get
(No column name)
User
New user subscription created
If the user's email address does exist I get
(No column name)
0
User
This email address is already subscribed
I would like for the (No column name) to go away - I know this is coming from the first SELECT statement. How do I suppress that statement from being output, yet still get the @@ROWCOUNT variable set?
View 3 Replies
View Related
May 14, 2008
I hope I'm posting this question in the proper forum, please pardon me if I am not. Here's my situation. I have an application written in VB.Net connecting to a SQL 2005 database. I have a stored procedure that is looking for duplicate entries of a "Company Name" prior to running the stored procedure. If there is no duplicate Company Name want the stored procedure to run, if there is a duplicate I want to return an error message alerting the user to the fact that the Company Name they are attempting to add already exists. I have my if statement but I'm not sure how to return an error message within this context. Any help would be greatly appreciated!
Many Thanks-
IF(SELECT COUNT(CompanyDimID) FROM dbo.CompanyDim WHERE CompanyName = @CompanyDim) = 0
BEGIN
.......................SQL query here................................
ELSE
(I want to return an error message in the ELSE portion to call out that we have a duplicate Company Name)
View 3 Replies
View Related
Jul 22, 2015
Error below was returned from an agent job:
OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]
A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?
View 3 Replies
View Related
May 15, 2007
hi all,
All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement
my problem catch does not catch the error. There is no way to find out teh what is causing this error
SqlCeConnection sqlcon = new SqlCeConnection("
Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"
);
SqlCeCommand sqlcmd = new SqlCeCommand();
sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');
sqlcmd.Connection = sqlcon;
SqlCeDataReader sqldr = null;
try
{
sqlcon.Open();
//use nonquery if result is not needed
sqlcmd.ExecuteNonQuery(); // application crashes here
}
catch (Exception e)
{
base.myErrorMsg = e.ToString();
}
finally
{
if (sqlcon != null)
{
if (sqlcon.State != System.Data.ConnectionState.Closed)
sqlcon.Close();
sqlcon.Dispose();
}
}//end of finlally
View 4 Replies
View Related
Feb 9, 2007
Hi,
I am converting a VB6 application to C# using ADO. I have the following code:
private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();
private connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename, renldate, product, fundingcode";
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open(Utilities.connectionString,null,null,0);
rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.Open(strClientSQL,cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);
rs.Fields["CaseName"].Value = this.txtCaseName.Text;
rs.Fields["CaseNo"].Value = this.txtCaseNumber.Text;
rs.Update();
When I try to compile and run it I get the following error message:
"No overload for method 'Update' takes '0' arguments"
I am not sure if I need to enter information in the update command or do something else. This seems pretty straight forward but I can't figure out how to do it. Can someone please help me?
Thanks,
Mark
View 4 Replies
View Related
Dec 16, 2007
Hello,
I have a table in SQL database. It has three Columns: C1, C2,C3 and C4.
C1 is a PrimaryKey column. I do not know the Id(value) for C1 but I do know the value of C2 and C3. I want to update C4 and then return the Id of C1. The Row is updated correctly but the return value is always 0. I hope somebody can help me with this. Thanks.
Code Block
CREATE PROCEDURE [dbo].[MyTableUpdate]
(
@C1Id int=default,
@C2Id int,
@C3Id int,
@C4V nvarchar(50)
)
AS
if exists(SELECT * FROM MyTable WHERE (C2Id=@C2Id and C3Id=@C3Id))
begin
update MyTable SET C4V=@C4V WHERE (C2Id=@C2Id and C3Id=@C3Id)
RETURN @C1Id
end
else
begin
INSERT INTO MyTable(C2Id,C3Id,C4V)VALUES(@C2Id,@C3Id,@C4V)
Select @@Identity
end
View 3 Replies
View Related
Apr 28, 2007
What does this code mean and what can I do to fix it? I need to update vista when applicable.
Thanks in advance for your help.
View 5 Replies
View Related
Mar 7, 2007
Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below
by the way I am a member of the sysadmin group
thanks in advance
km
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
View 1 Replies
View Related
Feb 1, 2007
Hi,
I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.
My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.
Are there any other idea's how can i solve this?
Best regards,
Thomas
View 2 Replies
View Related
Dec 15, 2006
When I make a call to a stored procedure to update a record, the update does not happen. No error is thrown, there are just no rows updated. When I look at SQL Server Profiler, this is what I see:
exec TTN_Update_RecurringIssueVehicle @RecurringIssueVehicleID='00962233-6EC3-42CE-ABBD-1851F1176D63',@RecurringIssueID='66FE821A-9881-4368-B975-5B04975C5E16',@RecurringIssueProblemID='ABED20CD-CB37-4491-903F-553555EEE47C',@MarketID=1,@VehicleNumber=268,@AddedDate=N'12/14/2006 3:30:00 PM',@AddedBy=12,@ResolvedDate=N'12/15/2006 10:19:45 AM',@ResolvedBy=12
It seems to be putting a carriage return in the middle of one of my date parameter values, and also seems to be putting those values in double quotes. If I try to run this in SQL Management Studio, it throws an error. If I take out the carriage return, and fix the quotes, it succeeds. Here is my VB.Net code:
<DataObjectMethod(DataObjectMethodType.Update)> _
Public Function Update_RecurringIssueVehicle( _
ByVal RecurringIssueVehicleID As Guid, _
ByVal RecurringIssueID As Guid, _
ByVal RecurringIssueProblemID As Guid, _
ByVal MarketID As Integer, _
ByVal VehicleNumber As Integer, _
ByVal AddedDate As DateTime, _
ByVal AddedBy As Integer, _
ByVal ResolvedDate As DateTime, _
ByVal ResolvedBy As Integer) As Integer
dbCmd.CommandText = "TTN_Update_RecurringIssueVehicle"
dbCmd.Parameters.Clear()
dbCmd.Parameters.AddWithValue("@RecurringIssueVehicleID", RecurringIssueVehicleID)
dbCmd.Parameters.AddWithValue("@RecurringIssueID", RecurringIssueID)
If RecurringIssueProblemID <> Guid.Empty Then dbCmd.Parameters.AddWithValue("@RecurringIssueProblemID", RecurringIssueProblemID)
dbCmd.Parameters.AddWithValue("@MarketID", MarketID)
dbCmd.Parameters.AddWithValue("@VehicleNumber", VehicleNumber)
dbCmd.Parameters.AddWithValue("@AddedDate", AddedDate.ToString())
dbCmd.Parameters.AddWithValue("@AddedBy", AddedBy)
If ResolvedDate <> DateTime.MinValue Then dbCmd.Parameters.AddWithValue("@ResolvedDate", ResolvedDate.ToString())
If ResolvedBy <> -1 Then dbCmd.Parameters.AddWithValue("@ResolvedBy", ResolvedBy)
Dim retVal As New SqlParameter("@RetVal", SqlDbType.Int)
retVal.Direction = ParameterDirection.ReturnValue
dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
Return CInt(retVal.Value)
End Function
Any ideas are greatly appreciated - thanks
View 4 Replies
View Related
Apr 11, 2004
Hi,
i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?
Below is the normal way i did in vb.net, but how to check for the return value. Please help.
========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========
Thank you.
View 12 Replies
View Related
Nov 1, 2015
I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?
ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES
[Code] ....
View 7 Replies
View Related
May 15, 2015
I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.
I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.
Here is some sample data:
use tempdb;
go
if OBJECT_ID('Tasks', 'U') is not null
drop table tasks;
go
create table tasks (
TaskId int identity primary key,
[Code] ....
And here's what my current stored proc looks like;
if OBJECT_ID('pGetNextTask', 'P') is not null
drop proc pGetNextTask;
go
create proc pGetNextTask (
@UserID char(10),
@TaskID int output
)
[Code] ....
View 3 Replies
View Related
Jul 23, 2007
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
View 3 Replies
View Related
Aug 8, 2007
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
View 5 Replies
View Related
Aug 8, 2007
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
View 1 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jan 15, 2007
Hi
for MS SQL 2000
how can I do ?:
INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers
I am having an UNIQUE INDEX on [Users].Name
how can I avoid an error ?
if the [Users].Name allready exists I want to jump over the error and keep on inserting
thank you for helping
View 14 Replies
View Related
Apr 22, 2008
Is it any better than using @@ERROR?
It doesn't seem so
Any opinions?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
View 4 Replies
View Related
May 13, 2008
Hi!
I'm trying to use Try Catch together with transaction handling but it seems like it doesn't do Rollback although I produce an error.
It looks something like this:
DECLARE @soID INT
BEGIN TRY
BEGIN TRANSACTION
UPDATE Serviceobjekt
SET ServiceobjektstypID = 13
WHERE ServiceobjektID = 26555
UPDATE .... 2 more tables
INSERT INTO Serviceobjekt (
Namn...)
VALUES ('XXXX')
SET @soID = @@IDENTITY
INSERT INTO Atgard (
Namn, ServiceobjektID)
VALUES ('sssss',@soID)
COMMIT TRANSACTION
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
I get after changing some id's in the where clause which I know is wrong I get a result like this:
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
Shouldn't it be 0 rows affected on all if just one update or insert statement is wrong?
/Magnus
View 4 Replies
View Related
May 22, 2008
Hi I use Try catch and transactions in one of my stored procedures.
Inside I make a call to another stored procedure.
Is it possible to roll back the outer sp?
Please advise.
View 1 Replies
View Related
Oct 7, 2007
Hello!
Try this (-:
BEGIN TRY
SELECT *
FROM NonExistentTable ;
END TRY
BEGIN CATCH
PRINT 'catch me if you can' ;
END CATCH ;
/*
results in:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'NonExistentTable'.
statement not printet */
BEGIN TRY
BEGIN TRY
SELECT *
FROM NonExistentTable ;
END TRY
BEGIN CATCH
PRINT 'catch me if you can' ;
END CATCH ;
END TRY
BEGIN CATCH
PRINT 'catched?'
END CATCH ;
-- same behaviour
CREATE PROC catchme
AS
BEGIN
BEGIN TRY
SELECT *
FROM nonexistingtable ;
END TRY
BEGIN CATCH
PRINT 'catch me if you can'
END CATCH ;
END ;
BEGIN TRY
EXEC catchme ;
END TRY
BEGIN CATCH
PRINT 'catched' ;
END CATCH ;
-- prints "catched"
BEGIN TRY
EXEC catchnonexistingproc ;
END TRY
BEGIN CATCH
PRINT 'catched again' ;
SELECT error_state()
END CATCH ;
-- prints "catched again"
BEGIN TRY
RAISERROR ('catch this' , 10 , 1) ;
END TRY
BEGIN CATCH
PRINT 'hello' ;
END CATCH ;
-- no hello
BEGIN TRY
RAISERROR ('catch this' , 11 , 1) ;
END TRY
BEGIN CATCH
PRINT 'hello' ;
END CATCH ;
-- prints hello
CREATE PROC catchme2
AS
BEGIN
BEGIN TRY
RAISERROR ('catch this' , 10 , 1) ;
END TRY
BEGIN CATCH
PRINT 'CATCH ME IF YOU CAN' ;
END CATCH ;
END ;
BEGIN TRY
EXEC catchme2 ;
END TRY
BEGIN CATCH
PRINT 'gotcha?' ;
END CATCH ;
-- prints only "catch this"
CREATE PROC catchme3
AS
BEGIN
BEGIN TRY
RAISERROR ('catch this' , 20 , 1) ;
END TRY
BEGIN CATCH
PRINT 'CATCH ME IF YOU CAN' ;
END CATCH ;
END ;
BEGIN TRY
EXEC catchme3 ;
END TRY
BEGIN CATCH
PRINT 'gotcha?' ;
END CATCH ;
-- prints "CATCH ME IF YOU CAN" ;-)
See also
-- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
The above behaviour is as described in BOL, but
comparing to other languages such as Java, C#, PL/SQL
I would expect that catch always catch unless you take the power from the machine, special
in the case for "catchnonexistingproc". Remark the different behaviour compared to "nonexistingtable"
Documentation shows not example for non existing proc!
That catch doesn't catch "is as bug". So is the catch of non existing proc a bug in the bug? ;^)
I hope not, because it is the behaviour we want...
Greetings
View 7 Replies
View Related
