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
ENDHow are you running this procedure? Have you tried it in QA? Without
testing it, I believe it should continue on, as long as the caller lets it.
It does depend on the error you are getting (an error can stop the batch.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Matthew" <MKruer@.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
>|||Matthew (MKruer@.gmail.com) writes:
> 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.
Error handling in SQL Server is a messy topic. Some errors cancels the
entire batch, and in SQL 2000 there is no way you can catch that from
SQL.
If you are on SQL 2005, you can use the new TRY-CATCH construct.
If you are on SQL 2000, there is actually a workaround. It's not really
useful in the general case, but for a sy
min script like yours, it
would be OK. Since I have this in an article on my web site, I just post
the link: http://www.sommarskog.se/error-hand...#linked-servers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
