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

avatar image

John 2 1
3 2 2 3

(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

avatar image

Kristen ♦
2.2k 7 11 14

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

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:

x2017
x1069
x428
x68

asked: Dec 07, 2009 at 09:14 PM

Seen: 1749 times

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

Copyright 2016 Redgate Software. Privacy Policy