Cannot drop triggers using dynamic SQL

Hi,
I'm using:
SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
I've used this on a number of occaisions but it doesn't seem to be dropping
the triggers this time.
Can anybody think of what I may be doing wrong here?
Many thanks for any assistance in advance
AntHi Ant
This SELECT will generate DROP TRIGGER statements, but it will not execute
them to actually drop the triggers. You need to take the output and execute
it.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> Hi,
> I'm using:
> SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> I've used this on a number of occaisions but it doesn't seem to be
> dropping
> the triggers this time.
> Can anybody think of what I may be doing wrong here?
> Many thanks for any assistance in advance
> Ant|||Hi Kalen,
Apoligies but you might have to walk me through that.
I tried:
exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
)
but naturally I got an error.
How is this done?
Many thanks for your answer.
Ant
"Kalen Delaney" wrote:
> Hi Ant
> This SELECT will generate DROP TRIGGER statements, but it will not execute
> them to actually drop the triggers. You need to take the output and execute
> it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> > Hi,
> > I'm using:
> >
> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >
> > I've used this on a number of occaisions but it doesn't seem to be
> > dropping
> > the triggers this time.
> >
> > Can anybody think of what I may be doing wrong here?
> >
> > Many thanks for any assistance in advance
> >
> > Ant
>
>|||Ant
DECLARE @.DeleteTrigger nvarchar(4000)
DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
FOR
SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
OPEN DeleteTrigger
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
EXEC(@.DeleteTrigger)
END
CLOSE DeleteTrigger
DEALLOCATE DeleteTrigger
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> Hi Kalen,
> Apoligies but you might have to walk me through that.
> I tried:
> exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> )
> but naturally I got an error.
> How is this done?
> Many thanks for your answer.
> Ant
>
> "Kalen Delaney" wrote:
>> Hi Ant
>> This SELECT will generate DROP TRIGGER statements, but it will not
>> execute
>> them to actually drop the triggers. You need to take the output and
>> execute
>> it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> > Hi,
>> > I'm using:
>> >
>> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >
>> > I've used this on a number of occaisions but it doesn't seem to be
>> > dropping
>> > the triggers this time.
>> >
>> > Can anybody think of what I may be doing wrong here?
>> >
>> > Many thanks for any assistance in advance
>> >
>> > Ant
>>|||Thank you.
Can this be done with Dynamic SQL? I'm sure I've done this before without
having to resort to using a Cursor(?)
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>|||Uri's solution IS dynamic SQL. Dynamic SQL means that we are building the
SQL command and then using EXEC to execute it, and he has this:
EXEC(@.DeleteTrigger)
I am assuming you are looking for a way to do it all with one statement.
You have not said what version you are using.
SQL 2000 has a procedures sp_execresultset that lets you execute the output
of another statement. This was not included in SQL 2005; I don't know why.
However, you should be able to get the definition of sp_execresultset from a
SQL 2000 server and create it in the master database of a SQL 2005 server
and get the same behavior.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:E46E8721-4815-4025-8C0E-D2092AF975DF@.microsoft.com...
> Thank you.
> Can this be done with Dynamic SQL? I'm sure I've done this before without
> having to resort to using a Cursor(?)
>
> "Uri Dimant" wrote:
>> Ant
>> DECLARE @.DeleteTrigger nvarchar(4000)
>> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
>> FOR
>> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> OPEN DeleteTrigger
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
>> IF @.@.FETCH_STATUS <> 0 BREAK
>> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
>> EXEC(@.DeleteTrigger)
>> END
>> CLOSE DeleteTrigger
>> DEALLOCATE DeleteTrigger
>>
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
>> > Hi Kalen,
>> >
>> > Apoligies but you might have to walk me through that.
>> >
>> > I tried:
>> >
>> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> > )
>> >
>> > but naturally I got an error.
>> >
>> > How is this done?
>> >
>> > Many thanks for your answer.
>> > Ant
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Ant
>> >>
>> >> This SELECT will generate DROP TRIGGER statements, but it will not
>> >> execute
>> >> them to actually drop the triggers. You need to take the output and
>> >> execute
>> >> it.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> >> > Hi,
>> >> > I'm using:
>> >> >
>> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >> >
>> >> > I've used this on a number of occaisions but it doesn't seem to be
>> >> > dropping
>> >> > the triggers this time.
>> >> >
>> >> > Can anybody think of what I may be doing wrong here?
>> >> >
>> >> > Many thanks for any assistance in advance
>> >> >
>> >> > Ant
>> >>
>> >>
>> >>
>>|||Hello Uri,
Thank you for this solution. I thought I'd exhaust all possiblities before
commiting to this. It does seem to ber the best one.
Much appreciated & sorry for the late reply
Ant
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>