?
Solved

error capture in SQL

Posted on 2006-06-01
7
Medium Priority
?
1,100 Views
Last Modified: 2008-01-09
I have a simple SQL query that reads a table and returns a single value.  The value returned is the name of a stored procedure which is then executed.  If any of these executed SP's the rest within my table of SP names will not run because SQL fails.  Basically I want to ignore when an error occurs.

The SP I have at the moment is:
CREATE PROCEDURE dbo.DailyUpdate
AS
      DECLARE c CURSOR FOR SELECT StoredProcedure FROM PurgeCycleTable ORDER BY RunOrder
      DECLARE @proc sysname
      OPEN c
      FETCH NEXT FROM c INTO @proc
      WHILE @@fetch_status = 0
            BEGIN TRY
                  EXEC (@proc)
            END TRY
            BEGIN CATCH
--                  SELECT
--                        ERROR_NUMBER() as ErrorNumber,
--                        ERROR_MESSAGE() as ErrorMessage;
            END CATCH
            FETCH NEXT FROM c INTO @proc
      CLOSE c
      DEALLOCATE c
RETURN

This returns an error when trying to save it "Incorrect syntax near 'TRY', incorrect syntax near the keyword 'END'

This SP runs perfectly without the TRY CATCH blocks.  Perfectly if considering whatever happens with the executed SP's is not an issue.  But sometimes the executed SP's don't work and I need to not care but still run the remaining SP's.
0
Comment
Question by:dij8
7 Comments
 
LVL 15

Expert Comment

by:dave4dl
ID: 16813533
You cannot ignore the error in sql server 2000 but you can in 2005

see http://www.thescripts.com/forum/thread142533.html
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16813670
Hi dij8,


CREATE PROCEDURE dbo.DailyUpdate
AS
     SET XACT_ABORT OFF
     DECLARE c CURSOR FOR SELECT StoredProcedure FROM PurgeCycleTable ORDER BY RunOrder
     DECLARE @proc sysname
     OPEN c
     FETCH NEXT FROM c INTO @proc
     WHILE @@fetch_status = 0
     BEGIN
          EXEC (@proc)
   
          FETCH NEXT FROM c INTO @proc
     END
     CLOSE c
     DEALLOCATE c
RETURN

/*When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
*/


Aneesh
0
 
LVL 10

Author Comment

by:dij8
ID: 16813690
I'm done for the day now and Monday is a holiday.  I will test your idea on Tuesday Aneesh.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16813701
Ok.. Even I will be on Vacation till Tuesday
0
 
LVL 3

Expert Comment

by:MrHorizontal
ID: 16823390
I presume the table of sproc names is filled *after* checking sysobjects that the sproc actually exists in the database!

Nevertheless, each of those sprocs should have their own error handling in them and returning some value as an OUTPUT parameter. ie if no work can be done it still returns a value that can be interpreted as a success / fail value.

This way you can log all the responses of the sprocs as the cursor runs them, either into a #table or into a proper table for further analysis in the future.

You should avoid using try / catch wherever possible, as processing speed is *drastically* reduced when handling errors!
0
 
LVL 10

Author Comment

by:dij8
ID: 16997059
The SET XACT_ABORT seems to help.

how do I check if a SPROC exists when using the whole loop thing?  The below fails because it gets confused with "object_id(N@proc)"
      FETCH NEXT FROM c INTO @proc
      WHILE @@fetch_status = 0
            BEGIN
            if exists (select * from dbo.sysobjects where id = object_id(N@proc) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                  EXEC (@proc)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17026793
You need  dynamic query here

exec( 'if exists (select * from dbo.sysobjects where id = object_id('''+@Proc+''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)  exec '+@Proc )

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
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. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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