DSO in SQL2005: delete

Showing posts with label delete. Show all posts

Thursday, March 29, 2012

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?
sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?
|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?
|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John
|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>
|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John
|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>
|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb
sql

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:
> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try i
t
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Serve
r
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to dro
p *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try
it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Ser
ver
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation,
and I have an
xp_sendmail in master and none in msdb. If you have something called xp_send
mail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Tuesday, March 27, 2012

Dropping constraint on temporary table

I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @.name
set @.name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @.name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:DROP TABLE #teFotograferen-PatP|||Pat

That's exactly what defines my problem
If i run
DROP TABLE #teFotograferen

i get
Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

because the table was a temporary table and there's no way to get back in the scope where it was defined.

If i recreate the table and then drop it the constraint still remains in my database.

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
because it is not the same table

on the other hand

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
There is already an object named 'UniqueFields' in the database.

In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

It is not a design problem in my application, i just put some garbage in that i can't get out|||This kind of problem is symptomatic of multiple sub-problems.That comment wasn't an accident.

One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

The solution to this problem is to:

a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
b) Create the constraint with a default name (which is unique for each execution).

This should get you far enough to find the next problem!

-PatP|||[smacks forehead]

why would you need contraints on a temp table?

[/smacks forehead]|||After a restart of the server the offending constraint was gone.

Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

Thanks for the advice and input. :beer:

Dropping an article

I want to drop a table from some of my transactional publications. Can I just
drop the article on the publisher and then be able to delete the table at the
subscriber?
Or do I have to drop the article and re-snapshot the subscriber?
Russell,
using the GUI you'd have to drop the subscriptions then drop the article,
but this is one of those cases where doing things in code is a little
different - you can drop the subscription to the individual article then
drop the article itself:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info Paul. If I carryout the two commands can I then just
delete the table from the subscribing database. Doese this process work for
both Merge and Transactional Publications.
"Paul Ibison" wrote:

> Russell,
> using the GUI you'd have to drop the subscriptions then drop the article,
> but this is one of those cases where doing things in code is a little
> different - you can drop the subscription to the individual article then
> drop the article itself:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Russell,
yes - you can drop the table after removing the subscriptions to it and
removing it from the publication.
no - it only works for transactional. For merge you'll need to drop the
subscription entirely before being able to drop the article and the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I also use the script to drop the article(s) I don't want.
What I do is:
1) Right click on the publication that you want to remove the table from.
2) Generate DELETE script.
3) Paste into Query Analyzer
4) Find and run the sp_dropsubscription and sp_droparticle commands for the
table you want to run.
5) All done.
This technique, in conjunction with the CREATE script can be used if the
schema of a replicated table needs to be changed.
You generate & SAVE both the DELETE & CREATE scripts. Remove the article from
replication, change the table, then add it back in with the relevant portion
of the create script.
Russell wrote:
>I want to drop a table from some of my transactional publications. Can I just
>drop the article on the publisher and then be able to delete the table at the
>subscriber?
>Or do I have to drop the article and re-snapshot the subscriber?

Thursday, March 22, 2012

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.

|||

COuld you please post the results of the sp_helpuser command.

regards

Jag

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||

COuld you please post the results of the sp_helpuser command.

regards

Jag

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

COuld you please post the results of the sp_helpuser command.

regards

Jag

sql

Wednesday, March 21, 2012

Drop tables with unknown names and unknown quantity

This is what I want to do:

1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.

For example:
(Tables in database)
Account
Account1
Account2
Binder
Binder1
Binder2
Binder3
......

I want to delete all the tables in the database with the exception
of Account and Binder.

I know that there are no wildcards in the "Drop Table tablename"
syntax. Does anyone have any suggestions on how to write this sql
statement?

Note: I am executing this statement in MS Access with the
"DoCmd.RunSQL sql_statement" command.

Thanks for any help![posted and mailed, please reply in news]

Amy (amarakunthy@.hotmail.com) writes:
> 1. Delete all tables in database with table names that ends with a
> number.
> 2. Leave all other tables in tact.
> 3. Table names are unknown.
> 4. Numbers attached to table names are unknown.
> 5. Unknown number of tables in database.

The simplest way is to say:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'

and then cut and paste and run the result. You would do this from
Query Analyzer.

If you would like to do it programmatically, because you are doing
it routinely, you could set up a cursor over sysobjects, and then
use dynamic SQL to drop the tables:

DECLARE @.tbl sysname
DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE name like '%[0-9]'
OPEN CURSOR drop_tbl_cur
WHILE 1 = 1
BEGIN
FETCH drop_tbl_cur INTO @.tbl
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP TABLE ' + @.tbl)
END
DEALLOCATE drop_tbl_cur

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I would also add ' AND xtype = 'U' ' in the where statement so that it
includes only user tables. This way it would include any object in the
statement and you would get errors when trying to execute.
it would look something like this:
SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9] and
xtype = 'U'

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951EEFFFCC91AYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Amy (amarakunthy@.hotmail.com) writes:
> > 1. Delete all tables in database with table names that ends with a
> > number.
> > 2. Leave all other tables in tact.
> > 3. Table names are unknown.
> > 4. Numbers attached to table names are unknown.
> > 5. Unknown number of tables in database.
> The simplest way is to say:
> SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'
> and then cut and paste and run the result. You would do this from
> Query Analyzer.
> If you would like to do it programmatically, because you are doing
> it routinely, you could set up a cursor over sysobjects, and then
> use dynamic SQL to drop the tables:
> DECLARE @.tbl sysname
> DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
> SELECT name FROM sysobjects WHERE name like '%[0-9]'
> OPEN CURSOR drop_tbl_cur
> WHILE 1 = 1
> BEGIN
> FETCH drop_tbl_cur INTO @.tbl
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP TABLE ' + @.tbl)
> END
> DEALLOCATE drop_tbl_cur
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

DROP TABLE

Dear All,
Can I use DROP TABLE statement to drop more than one table at once. If
not, how can I drop (delete) so many tables from the data base at the same
time with the condition that I know a constant part on its name.
Best Regards
--
*********
IT Manager
DeLaval Ltd.
Cairo-Egypt
*********
|--|
|Islam is peace not Terror|
|--|Ibrahim
create table #t1 (col int)
create table #t2 (col int)
create table #t3 (col int)
DROP TABLE #t1,#t2,#t3
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> Dear All,
> Can I use DROP TABLE statement to drop more than one table at once. If
> not, how can I drop (delete) so many tables from the data base at the same
> time with the condition that I know a constant part on its name.
> Best Regards
> --
> *********
> IT Manager
> DeLaval Ltd.
> Cairo-Egypt
> *********
> |--|
> |Islam is peace not Terror|
> |--||||Ibrahim
Copy-Paste the output in the QA and press F5
USE NorthWind
SELECT 'DROP TABLE '+
QUOTENAME(TABLE_SCHEMA) +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
'%Customers%'
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> Dear Uri,
> First thanks for your reply, but let me give you an idea about the
> problem I have. Our DB is having about thousand tables and some of them
> replicated automatically each year. So the tables take the pattern
(SC010001)
> so for example this file become (SC010101) for year 2001 and (SC010201)
for
> year 2002 ...etc. So all what I need for example is to delete all
'01'
> tables as example one time without riting theiere name one by one. I hope
> that you got my idea.
> Best Regards
> "Uri Dimant" wrote:
> > Ibrahim
> > create table #t1 (col int)
> > create table #t2 (col int)
> > create table #t3 (col int)
> >
> > DROP TABLE #t1,#t2,#t3
> >
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > Dear All,
> > > Can I use DROP TABLE statement to drop more than one table at once.
If
> > > not, how can I drop (delete) so many tables from the data base at the
same
> > > time with the condition that I know a constant part on its name.
> > >
> > > Best Regards
> > > --
> > > *********
> > > IT Manager
> > > DeLaval Ltd.
> > > Cairo-Egypt
> > > *********
> > > |--|
> > > |Islam is peace not Terror|
> > > |--|
> >
> >
> >|||Hi Again Uri,
I tried what you did, it gave me the result DROP TABLE [dbo][Customers]
.. What was this for... Can you tell me more in depth -if you can-...
"Uri Dimant" wrote:
> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > Dear Uri,
> >
> > First thanks for your reply, but let me give you an idea about the
> > problem I have. Our DB is having about thousand tables and some of them
> > replicated automatically each year. So the tables take the pattern
> (SC010001)
> > so for example this file become (SC010101) for year 2001 and (SC010201)
> for
> > year 2002 ...etc. So all what I need for example is to delete all
> '01'
> > tables as example one time without riting theiere name one by one. I hope
> > that you got my idea.
> >
> > Best Regards
> >
> > "Uri Dimant" wrote:
> >
> > > Ibrahim
> > > create table #t1 (col int)
> > > create table #t2 (col int)
> > > create table #t3 (col int)
> > >
> > > DROP TABLE #t1,#t2,#t3
> > >
> > >
> > >
> > >
> > >
> > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > Dear All,
> > > > Can I use DROP TABLE statement to drop more than one table at once.
> If
> > > > not, how can I drop (delete) so many tables from the data base at the
> same
> > > > time with the condition that I know a constant part on its name.
> > > >
> > > > Best Regards
> > > > --
> > > > *********
> > > > IT Manager
> > > > DeLaval Ltd.
> > > > Cairo-Egypt
> > > > *********
> > > > |--|
> > > > |Islam is peace not Terror|
> > > > |--|
> > >
> > >
> > >
>
>|||Ibrahim
I tested it on Nortwind database.You have to modify it for your needs.
Modify a LIKE operator in the WHERE condition to the actual table you want
to remove.
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:E2FFB248-D667-4D0D-A4BE-56E5C38B1C3C@.microsoft.com...
> Hi Again Uri,
> I tried what you did, it gave me the result DROP TABLE [dbo][Customers]
> .. What was this for... Can you tell me more in depth -if you can-...
>
> "Uri Dimant" wrote:
> > Ibrahim
> > Copy-Paste the output in the QA and press F5
> >
> > USE NorthWind
> > SELECT 'DROP TABLE '+
> > QUOTENAME(TABLE_SCHEMA) +
> > QUOTENAME(TABLE_NAME)
> >
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE
> > TABLE_TYPE = 'BASE TABLE' AND
> > OBJECTPROPERTY(OBJECT_ID(
> > QUOTENAME(TABLE_SCHEMA) +
> > '.' +
> > QUOTENAME(TABLE_NAME)),
> > 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> > '%Customers%'
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > > Dear Uri,
> > >
> > > First thanks for your reply, but let me give you an idea about the
> > > problem I have. Our DB is having about thousand tables and some of
them
> > > replicated automatically each year. So the tables take the pattern
> > (SC010001)
> > > so for example this file become (SC010101) for year 2001 and
(SC010201)
> > for
> > > year 2002 ...etc. So all what I need for example is to delete all
> > '01'
> > > tables as example one time without riting theiere name one by one. I
hope
> > > that you got my idea.
> > >
> > > Best Regards
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Ibrahim
> > > > create table #t1 (col int)
> > > > create table #t2 (col int)
> > > > create table #t3 (col int)
> > > >
> > > > DROP TABLE #t1,#t2,#t3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote
in
> > > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > > Dear All,
> > > > > Can I use DROP TABLE statement to drop more than one table at
once.
> > If
> > > > > not, how can I drop (delete) so many tables from the data base at
the
> > same
> > > > > time with the condition that I know a constant part on its name.
> > > > >
> > > > > Best Regards
> > > > > --
> > > > > *********
> > > > > IT Manager
> > > > > DeLaval Ltd.
> > > > > Cairo-Egypt
> > > > > *********
> > > > > |--|
> > > > > |Islam is peace not Terror|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Again,
I think I got your idea, that to get a statement saying Drop Table ******
with all table names and execute them as a punch from the QA?
Am I right..
"Uri Dimant" wrote:
> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > Dear Uri,
> >
> > First thanks for your reply, but let me give you an idea about the
> > problem I have. Our DB is having about thousand tables and some of them
> > replicated automatically each year. So the tables take the pattern
> (SC010001)
> > so for example this file become (SC010101) for year 2001 and (SC010201)
> for
> > year 2002 ...etc. So all what I need for example is to delete all
> '01'
> > tables as example one time without riting theiere name one by one. I hope
> > that you got my idea.
> >
> > Best Regards
> >
> > "Uri Dimant" wrote:
> >
> > > Ibrahim
> > > create table #t1 (col int)
> > > create table #t2 (col int)
> > > create table #t3 (col int)
> > >
> > > DROP TABLE #t1,#t2,#t3
> > >
> > >
> > >
> > >
> > >
> > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > Dear All,
> > > > Can I use DROP TABLE statement to drop more than one table at once.
> If
> > > > not, how can I drop (delete) so many tables from the data base at the
> same
> > > > time with the condition that I know a constant part on its name.
> > > >
> > > > Best Regards
> > > > --
> > > > *********
> > > > IT Manager
> > > > DeLaval Ltd.
> > > > Cairo-Egypt
> > > > *********
> > > > |--|
> > > > |Islam is peace not Terror|
> > > > |--|
> > >
> > >
> > >
>
>|||Correct
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:27F96530-6006-4E08-8E65-A08E816D5050@.microsoft.com...
> Hi Again,
> I think I got your idea, that to get a statement saying Drop Table
******
> with all table names and execute them as a punch from the QA?
> Am I right..
>
> "Uri Dimant" wrote:
> > Ibrahim
> > Copy-Paste the output in the QA and press F5
> >
> > USE NorthWind
> > SELECT 'DROP TABLE '+
> > QUOTENAME(TABLE_SCHEMA) +
> > QUOTENAME(TABLE_NAME)
> >
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE
> > TABLE_TYPE = 'BASE TABLE' AND
> > OBJECTPROPERTY(OBJECT_ID(
> > QUOTENAME(TABLE_SCHEMA) +
> > '.' +
> > QUOTENAME(TABLE_NAME)),
> > 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> > '%Customers%'
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > > Dear Uri,
> > >
> > > First thanks for your reply, but let me give you an idea about the
> > > problem I have. Our DB is having about thousand tables and some of
them
> > > replicated automatically each year. So the tables take the pattern
> > (SC010001)
> > > so for example this file become (SC010101) for year 2001 and
(SC010201)
> > for
> > > year 2002 ...etc. So all what I need for example is to delete all
> > '01'
> > > tables as example one time without riting theiere name one by one. I
hope
> > > that you got my idea.
> > >
> > > Best Regards
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Ibrahim
> > > > create table #t1 (col int)
> > > > create table #t2 (col int)
> > > > create table #t3 (col int)
> > > >
> > > > DROP TABLE #t1,#t2,#t3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote
in
> > > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > > Dear All,
> > > > > Can I use DROP TABLE statement to drop more than one table at
once.
> > If
> > > > > not, how can I drop (delete) so many tables from the data base at
the
> > same
> > > > > time with the condition that I know a constant part on its name.
> > > > >
> > > > > Best Regards
> > > > > --
> > > > > *********
> > > > > IT Manager
> > > > > DeLaval Ltd.
> > > > > Cairo-Egypt
> > > > > *********
> > > > > |--|
> > > > > |Islam is peace not Terror|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >sql

Monday, March 19, 2012

Drop system table?

I am trying to get rid of a table that a former employee created... it is lised as a system table. When I try to delete it I get an error telling me I cannot drop a system table. How can I get rid of it?
Thanks for your help.
I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.

Drop system table?

I am trying to get rid of a table that a former employee created... it is lised as a system table. When I try to delete it I get an error telling me I cannot drop a system table. How can I get rid of it?
Thanks for your help.I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.

Drop system table?

I am trying to get rid of a table that a former employee created... it is li
sed as a system table. When I try to delete it I get an error telling me I
cannot drop a system table. How can I get rid of it?
Thanks for your help.I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.

Drop subscription locking users

Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
|||The new publication is on the same server, same database, the subscriber is a new server, so in essence your assumption is correct. I want to drop the old publication on the existing publishing server/database, since the subscribing server will be retired.
Ok, so I need to drop my articles on this publication one by one. Ugh! That's 1600+ articles.
Thanx for the answer.
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:%23KCcOfJ5FHA.2432@.TK2MSFTNGP10.phx.gbl...
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!

Drop Relationships

Hi there,

I would Like to delete a relationship by SQL Syntax!

When I go to M-Access I see the relationship with the name companiesVisitors
and this is how it is represented...

Companies(Company_ID) -- Visitors(VisitorCompany)

one-to-many

How can I drop the relationship using SQL?

Hope you can help me... I am searching a solution for daysyou might need to know the name of the constraint

see ACC2000: Create and Drop Tables and Relationships Using SQL DDL (http://support.microsoft.com/kb/q209037/)

but if you're in the relationship window already, just click on the line joining the two boxes and delete it|||That didn't help to much... I need the SQL to remove a relationship...

I tried to delete the constraints, but that does not help me to much... because my Constraints have strange names such as "Rel_158FB2CC_919D_462B"... It's a totally random name given by Access|||then when you create the relationship, you should assign it a constraint name|||but I can't do anything now, because is a software update... it's to import data to new database formats... I can't guess wich names the constraint have... it should be done in the beggining!

drop or delete view

hello,

im creating a sp that creates a view from a query then bcp to csv file.

my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail

DROP IF EXISTS v_participantTrades

now, does that syntax not work for views?

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'v_participantTrades'))

DROP VIEW v_participantTrades

|||

aah that explains the whole, create view needs to be the first statement!

thank you

|||

exuse my incompetence however,

Invalid Object name 'sys.views'.

blaaa was my ineptitude - sysobjects for my version

cheers

Drop one article from transaction publication without drop the

Hi ,
I have to delete large amount of data from a table that is one of many
articles in Transaction publication.
I want to dropt the specific article , delete the data and than add the
article again (initial the specific article only) In order to save
transfer the heavily transactions from the replication (~ 2 GB of data for
subscribers with bad connectivity)). Can I drop one article from
transaction publication without drop the subscribers first? (The whole
publication is very heavy - drop all subscribers , add it again and
reinitial all subscribers will take hours .)
BTW - is it possible in merge replication ?
Thanks,
Eyal
Eyal,
what you are requesting is not possible through the gui but is possible
through scripts. This sort of query should work for you:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sunday, March 11, 2012

Drop hidden trigger - how

I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
charm. I then made a similar CLR trigger and deployed it to the
server. The T-SQL trigger seemed to disappear from the Database
Triggers folder for that database. However, when I delete a row from
the table I get e-mail from both the T-SQL trigger and the CLR trigger
in the Assembles folder. I would like to DROP the T-SQL trigger but it
is not visible in the object explorer. Any help on how to proceed.
Paul SullivanPaul,
Use the DROP TRIGGER T-SQL statement if refresh of objects doesn't show the
trigger.
HTH
Jerry
"Paul Sullivan" <paul-v-sullivanHATESPAM@.worldnet.att.net> wrote in message
news:4cudl15jj47engm5b9tt7dd66fmu71k1qn@.
4ax.com...
>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan|||Try looking at the output of: sp_helptrigger 'table_name'
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W

in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw

/default.mspx[/url]
"Paul Sullivan" <paul-v-sullivanHATESPAM@.worldnet.att.net> wrote in message
news:4cudl15jj47engm5b9tt7dd66fmu71k1qn@.
4ax.com...
>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan|||Hi Paul
Just check the link:
http://msdn.microsoft.com/library/d...br />
8wj6.asp
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Paul Sullivan" wrote:

> I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan
>|||Thank you, thank you, etc
Thanks to you particularly, Itzik Ben-Gan, since I didn't remember the
exact trigger name.
Trigger is now history
Paul Sullivan
On Wed, 19 Oct 2005 22:05:21 -0400, Paul Sullivan
<paul-v-sullivanHATESPAM@.worldnet.att.net> wrote:

>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
>charm. I then made a similar CLR trigger and deployed it to the
>server. The T-SQL trigger seemed to disappear from the Database
>Triggers folder for that database. However, when I delete a row from
>the table I get e-mail from both the T-SQL trigger and the CLR trigger
>in the Assembles folder. I would like to DROP the T-SQL trigger but it
>is not visible in the object explorer. Any help on how to proceed.
>Paul Sullivan

Thursday, March 29, 2012

Dropping Extended Stored Proc

Dropping Extended Stored Proc

Dropping Extended Stored Proc

Tuesday, March 27, 2012

Dropping constraint on temporary table

Dropping an article

Thursday, March 22, 2012

drop user

drop user

drop user

Wednesday, March 21, 2012

Drop tables with unknown names and unknown quantity

DROP TABLE

DROP TABLE

DROP TABLE

Monday, March 19, 2012

Drop system table?

Drop system table?

Drop system table?

Drop subscription locking users

Drop Relationships

drop or delete view

Drop one article from transaction publication without drop the

Sunday, March 11, 2012

Drop hidden trigger - how

DSO in SQL2005

Blog Archive

About Me