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 create databases (and other issues) from Management Studio

I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...

TITLE: Microsoft SQL Server Management Studio
----------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

----------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

----------

The server could not load DCOM. (Microsoft SQL Server, Error: 7404)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476

However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).

From the Event Log...What does it mean?

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17102
Date: 06/02/2006
Time: 20:12:06
User: N/A
Computer: TITAN
Description:
Failed to initialize Distributed COM (CoInitializeEx returned 80010119). Heterogeneous queries and remote procedure calls are disabled. Check the DCOM configuration using Component Services in Control Panel.

|||Finally discovered it was the nVidia (motherboard) firewall client, you have to uninstall it - I had a similar problem with IE7

Cannot connect to SQL instance without port information

Hi all,

I'm having some trouble with connecting to SQL server instance lately
without adding a port number. I used to be able to always connect to
instance without having to input SQLSERVER,portnumber. However now
this is not the case. I doing this through an ADO connection object,
and creating the connection string. Does anyone know if there was a
recent Microsoft update or patch that would've cause this to stop
allowing me to connect to a SQL instance without having to put in the
port information? Thanks.Flash08 (stuart.karp@.gmail.com) writes:

Quote:

Originally Posted by

I'm having some trouble with connecting to SQL server instance lately
without adding a port number. I used to be able to always connect to
instance without having to input SQLSERVER,portnumber. However now
this is not the case. I doing this through an ADO connection object,
and creating the connection string. Does anyone know if there was a
recent Microsoft update or patch that would've cause this to stop
allowing me to connect to a SQL instance without having to put in the
port information? Thanks.

More likely this is a change in your environment. You don't say which
version of SQL Server you are using, but with SQL 2005 I think the
requirement is that the Browser service is running. On SQL 2000, you
need to be able to access port 1434 on UDP.

In both cases, firewalls may get in the way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.sewrote in
news:Xns996556346440Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Flash08 (stuart.karp@.gmail.com) writes:

Quote:

Originally Posted by

>I'm having some trouble with connecting to SQL server instance lately
>without adding a port number. I used to be able to always connect to
>instance without having to input SQLSERVER,portnumber. However now
>this is not the case. I doing this through an ADO connection object,
>and creating the connection string. Does anyone know if there was a
>recent Microsoft update or patch that would've cause this to stop
>allowing me to connect to a SQL instance without having to put in the
>port information? Thanks.

>
More likely this is a change in your environment. You don't say which
version of SQL Server you are using, but with SQL 2005 I think the
requirement is that the Browser service is running. On SQL 2000, you
need to be able to access port 1434 on UDP.
>
In both cases, firewalls may get in the way.
>
>

Is there a reason you can't connect using Server\Instance type syntax?