x

exec command fail when it execute a variable

Hi Everyone,

I am getting an error message with exec command. Below is the query:

                    
drop table #schema                    
                    
select schema_name into #schema from information_schema.schemata                     
    where schema_name not in ('dbo','guest','INFORMATION_SCHEMA','sys')                    
                    
DECLARE @string sysname, @schemaname sysname                    
                    
DECLARE string_cursor CURSOR for select schema_name from #schema;                    
                    
open string_cursor;                    
                    
FETCH NEXT FROM string_cursor INTO @schemaname;                    
                    
WHILE (@@FETCH_STATUS <> -1)                    
                    
BEGIN;                    
                    
set @string='drop schema ['+@schemaname+']'                    
                    
exec (@string)                    
                    
FETCH NEXT FROM string_cursor INTO @schemaname;                    
                    
end;                    
                    
CLOSE string_cursor;                    
                    
DEALLOCATE string_cursor;                    

Exec is failing on the first row and does not read other rows in the table. It should continue reading all the rows until it read entire table. Even if there are error on any row, it should not abort the transaction. Below is the error message:

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'appdba' because it is being referenced by object 'BACKUP_TITAN2'.

more ▼

asked Dec 07, 2009 at 09:14 PM in Default

John 2 1 gravatar image

John 2 1
3 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You need to check if the scehma you are trying to drop has dependencies before attempting to drop it.

You might also be able to use a TRY / CATCH block to prevent any errors being terminal, but that isn't able to catch all errors, and I don't know off-hand if it can catch this one.

more ▼

answered Dec 08, 2009 at 08:28 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Thanks for your help. Try/Catch worked for me.
Dec 08, 2009 at 06:23 PM John 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x985
x369
x58

asked: Dec 07, 2009 at 09:14 PM

Seen: 1450 times

Last Updated: Dec 08, 2009 at 08:26 AM