question

John 2 1 avatar image
John 2 1 asked

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'.

sql-server-2005t-sqlquerycursor
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kristen avatar image
Kristen answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.