"Matthew" <MKr***@gmail.com> wrote in message
news:1138314691.791878.263020@o13g2000cwo.googlegroups.com...
> Here is a fun one, and I am not sure its possible.
>
> I have 2 SQL Stored Procedures.
>
> The first one calls the second one. All the first one does is grab the
> next database name and pass that variable to the second stored
> procedures. Once the second one finishes it goes back to the first one
> goes to the next database and repeats the process until all the
> databases are finished.
>
> The problem is if the second Stored Procedures fails, it fails the
> first.
>
> My Question; is there a way that if the second stored procedure fails,
> that the first stored procedure will still continue onto the next
> database? Just because one database locks up for some reason does not
> me they all will.
>
> USE [DBAdmin]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE proc [dbo].[UD_Rebuild_All_Database_Indexes]
> @maxFrag DECIMAL = 30.0, -- The maximum fragmentation to allow
> @indexSizeForRebuild INT = 100   -- Maximum index size to do a
> rebuild instead of defrag
>
> AS
>
> DECLARE @ExecSQLcmd varchar(1024)
> DECLARE @DBNum_to_Name int
>
> SELECT @DBNum_to_Name =min(dbid)
> FROM   master.dbo.sysdatabases
> WHERE dbid > 4
>
> WHILE @DBNum_to_Name  is not null
> BEGIN
> SELECT @ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes ' +
> CONVERT(VARCHAR,@maxFrag) + ', ' +
> CONVERT(VARCHAR,@indexSizeForRebuild)
> FROM master.dbo.sysdatabases
> WHERE dbid =@DBNum_to_Name
>    -- Print (@ExecSQLcmd) -- For Debugging
> EXEC (@ExecSQLcmd)
> SELECT @DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
> 0
> END
>