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

Cannot Connect using IP to Clustered Named Instance

I am having trouble connecting when using the IP address to a named
instanced using dynamic port assignments and hosted on a 2K3 cluster (even
when at the console on the node hosting the SQL instance). I receive "SQL
Server does not exist or access denied". There are no entries created in
the event log. I am able to connect with no problems using the instance
name. I'm attempting to connect usingQuery Analyzer. Any ideas?
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
Hi Michael,
In order to connect to a Named Instance you need to either:
a. Supply the Servername\InstanceName
b. Supply the Servername, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||It seems that you have trouble connecting through TCP/IP. Verify in event log that SQL is listening on TCPIP and not only on Shared Memory / Named Pipes.
To verify that this is a problem from QueryAnalyzer try to connect with tcp:ServerName\InstanceName. If it isn't work then server is not listening on TCP at all...
post back after this and we will see what then...
Rgds
Sinisa Perovic
|||Thanks for the help.
Thanks!
______________________________________
Michael DiGiuseppe
Technical Services - Enterprise
North Carolina Department of Transportation
Century Center - Building B
______________________________________
E-mail correspondence to and from this address
may be subject to North Carolina Public Records
Law "NCGS.Ch.132" and may be disclosed to
third parties by an authorized state official.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:51hmVwbIEHA.3636@.cpmsftngxa06.phx.gbl...
> Hi Michael,
> In order to connect to a Named Instance you need to either:
> a. Supply the Servername\InstanceName
> b. Supply the Servername, port
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>