Dropping FK Constraints in a Stored Procedure

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux...some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.The only justification for this technique is developer productivity. It's a
lot easier to write code without those nasty foreign key constraints getting
in the way :-)

Seriously, I can't think of a good reason to do this. There may be some
situations where temporarily removing constraints can improve batch
performance but this kind of thing should only be done during maintenance
windows. Adding and removing constraints during normal operation can lead
to blocking and data integrity issues.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dmitri" <nienna.gaia@.gmail.com> wrote in message
news:1111719489.616490.262060@.f14g2000cwb.googlegr oups.com...
>I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?
> I was furious when I found this because every once in a while I would
> notice that the constraints would be in flux...some days they were
> there, othere days there were not. I mean, this is a good enough reason
> to NOT do this, but I need some additional feedback. Maybe there *is* a
> good reason, and that the logic just needs tweaking.
> Thanks.|||Dmitri (nienna.gaia@.gmail.com) writes:
> I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?

Good reason...

There is a problem with constraints and triggers in SQL Server, as they
always fire at statemet time. Other products have commit-time constraints
and/or triggers. This is good, because that permits you to violate
constraints temporarily in a transaction where it does not matter. One
example is that you have Orders and OrderDetails, and for some reason
need to reallocating order numbers and the constraints are not defined
as cascading. (Maybe because of the many restrictions with cascading
DRI in SQL Server.)

That said, I would say that it vert bad practice to do this in application
code. For a maintenance procedure or a fix proecedure that is run once in
a blue moon it could be acceptable. If there is some really difficult
situation where you must to this, you *must* do it within the realm of a
transaction, so that the final result of the operation in case of an error
or a power failure is that the constraints are gone.

Performance implications? You bet. If, as you say, he drops and re-adds
the constraints, there is a cost for checking the constraints. An
alternative would be to disable the constraints and enabling them
again without checking. In this case, the constraints would not be
trusted by the optimizer, and this could affect query plans. (And of
course, if his logic violated the constraints, no one would know.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Standard SQL has a DEFERABLE option on constraints. They can be turned
on or off by default or by action. But all constraints have to be true
at COMMIT time. I would guess that he is used to DB2 or another SQL
that hs this feature.

SQL Server does not work this way. So this is very dangerous and he
should not be doing it. He can lock up or trash the whole system with
his stored procedures. My guess would be that his procedure ought to
update the table in the proper order, or that the schema has something
really ugly in it, like a cyclic reference.

If this has been goign on for awhile, you better do a data audit, too.|||Thanks for the input all!

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

Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >

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 article on transaction publications.

In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:

Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).

Create a new snapshot.

Synchronize the push subscription.

DROP the article on the Publication and Subscriber databases.

Replication still works!

However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.

Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.

Linda

Hi Linda, the documentation is correct. If you try to drop the artice via TSQL stored proc sp_droparticle, it will raise an error saying you you cannot drop the article because you have existing subscription. What I think BOL should say is that "the subscriptions to the article must be dropped before you can drop the article".

This is what the UI is doing - it will first drop the article from all existing subscriptions (via sp_dropsubscription), then drop the article from the publication and then invalidate the snapshot. So for existing subscriptions, there isn't anything else you need to do, but if you add a new subscription afterwards, you have to regenerate a new snapshot.

I hope this is a little more clear.

Dropping all foreign keys

I would like to have a procedure that I can call that will drop all foreign
keys. I can get the constraint name from sysobjects if I select all fkey
constraints, but how can I get the associated table name so that I can plug
that into an alter table statement?
I appreciate any suggestions.
ThanksSee the first query at http://www.aspfaq.com/2520
Just change it from "SELECT FK_Table ... FROM" to the following:
SELECT 'ALTER TABLE '+FK.TABLE_NAME+' DROP CONSTRAINT '+C.CONSTRAINT_NAME
FROM
Run it in query analyzer and you will generate a script in the bottom pane,
which you can then copy and run in a new Query Analyzer window.
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks
Here is one that I wrote. It's not pretty and it's not optimized, but it
works.
Rick Sawtell
----
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END|||WHY? Do you really want to sail through the windshield?
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
> I would like to have a procedure that I can call that will drop all
foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||*All* of them? I just hope some of them will make it back... someday.
ML

Dropping / Recreating Indexes

All -
You know when you generate the SQL script for a stored procedure, you get
that initial script at the top that first checks for the procedure in
sysobjects and drops it if it is found?
I want to do the same thing with indexes. I want to create a script that
will first delete a given index if its found and then create a new one.
How would you go about do this?
Thanks,
- Mattif exists (select * from dbo.sysindexes where name = 'IX_index_01' and id =object_id('dbo.tableA'))
drop index dbo.tableA.IX_index_01
GO
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||All -
Sorry about that. I found the DROP INDEX syntax.
Now it looks like the only problem is that if the index is a primary index,
you can't drop it. You have to drop the table, create the table, and then
re-create the index.
Does this sound right?
Thanks,
- Matt
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||Primary Key indexes are created as part of the Primary Key
Constraint. You cannot directly drop the index, but
instead you drop it by dropping the Primary Key
Constraint. So you don't have to drop the table. You must,
however, first drop any Foreign Key Constraints that
reference the PK.
HTH
Vern
>--Original Message--
>All -
>Sorry about that. I found the DROP INDEX syntax.
>Now it looks like the only problem is that if the index
is a primary index,
>you can't drop it. You have to drop the table, create
the table, and then
>re-create the index.
>Does this sound right?
>Thanks,
>- Matt
>
>"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
>news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
>> All -
>> You know when you generate the SQL script for a stored
procedure, you get
>> that initial script at the top that first checks for
the procedure in
>> sysobjects and drops it if it is found?
>> I want to do the same thing with indexes. I want to
create a script that
>> will first delete a given index if its found and then
create a new one.
>> How would you go about do this?
>> Thanks,
>> - Matt
>>
>
>.
>|||Hi Matt
No, you certainly don't have to drop the whole table just to drop an index!
If an index supports a primary key constraint, you have to drop the
constraint, and that will automatically drop the index that supports the
constraint.
You can then readd the constraint.
sp_helpconstraint will show you the constraint names
to drop and re-add the constraint use:
ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT
Full details are in Books Online
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>|||What's the reason behind dropping and recreating the index? If the reason
is to rebuild it (remove fragmentation), then use DBCC DBREINDEX. This will
rebuild your index and you don't need to deal with dropping constraints.
Gail Erickson [MSFT]
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>

Dropdownlist with stored procedure problem

Hi Everyone,

I am trying to load the data into the dropdownlist using stored procedure. But when I run the code, the dropdownlist is empty. The code is shown below. Please help! Thanks.

public DataSet getProvince()
{
DataSet ds = new DataSet();
SqlParameter myParam;

string conString;
SqlConnection myConnection;

conString = ConfigurationManager.AppSettings["connectionString"];
myConnection = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand("stored_procedure_GetProvinces", myConnection);

SqlDataAdapter adpt = new SqlDataAdapter(cmd);

try
{

cmd.CommandType = CommandType.StoredProcedure;
myParam = cmd.Parameters.Add("@.province_key", SqlDbType.Int);
myParam.Direction = ParameterDirection.Output;

myParam = cmd.Parameters.Add("@.province_name", SqlDbType.NVarChar, 200);
myParam.Direction = ParameterDirection.Output;

adpt.Fill(ds, "Provinces");

myConnection.Close();

}
catch (SqlException ex)
{
Response.Write("Error: " + ex.Message);
}
return ds;
}

-----------

create procedure stored_procedure_GetProvinces( @.province_key int output, @.province_name nvarchar (200) output)AsSelect @.province_key=province_key, @.province_name=province_name From provinceGO

--------

<asp:DropDownList id="Dropdownlist_Province" DataValueField="province_key" DataTextField="province_name" DataSource='<%# getProvince() %>' Runat="server" >
</asp:DropDownList>

----------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO

CREATE TABLE province (
province_key int IDENTITY PRIMARY KEY,
province_name nvarchar (200) NULL
)
GO

INSERT INTO province(province_name) VALUES
('- Select -');
GO
INSERT INTO province (province_name) VALUES
('Ontario');
GO
INSERT INTO province (province_name) VALUES
('Alberta');
GO
INSERT INTO province (province_name) VALUES
('British Columbia');
GO
INSERT INTO province (province_name) VALUES
('Manitoba');
GO
INSERT INTO province(province_name) VALUES
('New Brunswick');
GO
INSERT INTO province (province_name) VALUES
('Newfoundland');
GO
INSERT INTO province (province_name) VALUES
('Northwest Territories');
GO
INSERT INTO province (province_name) VALUES
('Nova Scotia');
GO
INSERT INTO province (province_name) VALUES
('Nunavut');
GO
INSERT INTO province (province_name) VALUES
('Prince Edward Island');
GO
INSERT INTO province (province_name) VALUES
('Quebec');
GO
INSERT INTO province (province_name) VALUES
('Saskatchewan');
GO
INSERT INTO province (province_name) VALUES
('Yukon Territory');
GO

What is the value of your parameter?|||

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

|||

bullpit:

What is the value of your parameter?

Sorry, I don't know. Thanks.

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

After I modify my code and my stored procedure. I still get the empty dropdownlist. Please help. Thanks.

|||did you change the proc to the way I suggested? Also please post your new .NET code..|||

ndinakar:

did you change the proc to the way I suggested? Also please post your new .NET code..

My updated code is shown below. Thanks!

create procedure

stored_procedure_GetProvinces

(

@.province_key

intoutput,

@.province_name nvarchar (200)

output

)

As

Select

province_key, province_nameFromprovince

GO

--------

publicDataSet getProvince()

{

DataSet ds =newDataSet();SqlParameter myParam;string conString;SqlConnection myConnection;

conString =

ConfigurationManager.AppSettings["connectionString"];

myConnection =

newSqlConnection(conString);SqlCommand cmd =newSqlCommand("stored_procedure_GetProvinces", myConnection);SqlDataAdapter adpt =newSqlDataAdapter(cmd);try

{

cmd.CommandType =

CommandType.StoredProcedure;

adpt.Fill(ds,

"Provinces");

myConnection.Close();

}

catch (SqlException ex)

{

Response.Write(

"Error: " + ex.Message);

}

return ds;

}

--------- the end -------

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

I have just tried using ExecuteReader. And I still get empty dropdownlist. Please help. Thanks.

SqlCommand objCmd;

SqlConnection objConn;

SqlDataReader dataReader;

String strSql;

objConn =newSqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));

strSql ="EXECUTE stored_procedure_GetProvinces";

objCmd =newSqlCommand(strSql, objConn);

try

{

objConn.Open();

dataReader = objCmd.ExecuteReader(

CommandBehavior.CloseConnection);

Dropdownlist_Provinces.DataSource = dataReader;

Dropdownlist_Provinces.DataTextField =

"province_name";

Dropdownlist_Provinces.DataValueField =

"province_name";

Dropdownlist_Provinces.DataBind();

}

catch

{

}

finally

{

objConn.Close();

objConn.Dispose();

}

|||

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

|||

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

|||

MayLam:

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

I replace SqlCmd1 with myCommand. And it is working now. Thank you very much for your help! Bye!

May

DropDownList instead of Textbox in Reporting Services

Hello,
I have a report that uses a stored procedure. The parameter from the stored procedure is a name field defined nvarchar(50). But I have a list of values for this field in another table; is there anyway to use the values from the other table to populate a dropdownlist of these values, instead of prompting the user for a textbox?
Thanks.Hi ,
for populating drop down list instead of default text box, you have to create another dataset which will fetch fields from another table.
while in layout, go to Report --> Report Parameters..
click on the parameter on left and on right side, select radio btn From query and specify dataset and select fields.
you can also select default value also. i think this is what u want right?
Thanks
Rohit
|||

Hey,

Yes, that is exactly what I was looking to do. Thanks a lot.

|||That's good for designing reports on the server (RDL). I'm using Visual Web Developer Express and cliking on Report-Report Parameters it seems there's no option for configuring parameters to come from a dataset. Is there a way around this?|||No takers so far. So let me re-phrase the question. Is there any way by which I can move parametrized reports from the server (RDL) to the client (RDLC)?|||Sure you can. You need to use the parameter objects to pass parameters to the report.

DropdownLIst box question, Stored Procedure

Hi All,

I have a dropdownlist box with values (All, Paris, London, New York) -- Cities

Another dropdwonlist box with values (All, Bank of America, City Bank, CIBC) -- Banks

And have a stored procedure that populates the info:

CREATE Procedure Search
(
@.City nvarchar(50),
@.Bank nvarchar(50)
)
AS SELECT * FROM TableA WHERE
city = @.city AND bank= @.bank GO

If user selects a value anything other than "All" in both dropdownbox, everything is OK.

My question is, if user selects "All" in any of these dropdown combobox, how can I seach for all Cities or Banks.

SQL needs to run as a stored procedure. If it was on client end, it would be alot easier.

Any ideas?

Thanks for your help.

Duplicate post, see
http://forums.asp.net/1138420/ShowPost.aspx

drop. temp. table proc.

CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:

>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:

> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Drop User Command

What is the stored procedure for drop a user? Drop_user
userx...
Thanks,
Brady Snow
McKinney, Texas
See:
sp_dropuser
sp_revokedbaccess
sp_droplogin
in Books Online (BOL)
Rohtash Kapoor
http://www.sqlmantra.com
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas
|||Hi,
In SQL Server you will be having Login and Users.
Login : Login to authenticate inside SQL server when you use SQL server
authnetication
User: Who got previlege to access the databases
So before deleting the Login you have to drop the user
Command to drop user:
sp_dropuser <user_name>
Command to drop Login
sp_droplogin <login_name>
Apart from this refere the below commands in books online:
1. sp_revokelogin <Loginame>
2.sp_revokedbaccess <user_name>
Thanks
Hari
MCDBA
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas

Drop table , procedure Problems

Hi,
I'm creating table, procedure from my c# codings in sql server.
I'm having database owner rights.
My problem is sometimes the table, procedure is created with
username.tablename instead of dbo.tablename. so i'm unable to drop that
table or procedure while my c# program is run by some other user.Kindly
suggest me a way to create table, procedure as dbo.tablename,
dbo.procedurename.how to drop the tables and procedures created by an
another user.What are the rights i required to create table,procedure
as dbo.tablename, dbo.procedurename in sql server 2000
Thanks & Regards,
ManiBOL says
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object
<plmanikandan@.gmail.com> wrote in message
news:1163675950.788703.71100@.i42g2000cwa.googlegroups.com...
> Hi,
> I'm creating table, procedure from my c# codings in sql server.
> I'm having database owner rights.
> My problem is sometimes the table, procedure is created with
> username.tablename instead of dbo.tablename. so i'm unable to drop that
> table or procedure while my c# program is run by some other user.Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.how to drop the tables and procedures created by an
> another user.What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
> Thanks & Regards,
> Mani
>|||> Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.
CREATE TABLE dbo.tblname(...)
CREATE PROC dbo.procname ... AS ...
> to drop the tables and procedures created by an
> another user.
DROP TABLE username.tblname
DROP PROC username.procname
> What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
db_ddladmin, db_owner or higher.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<plmanikandan@.gmail.com> wrote in message
news:1163675950.788703.71100@.i42g2000cwa.googlegroups.com...
> Hi,
> I'm creating table, procedure from my c# codings in sql server.
> I'm having database owner rights.
> My problem is sometimes the table, procedure is created with
> username.tablename instead of dbo.tablename. so i'm unable to drop that
> table or procedure while my c# program is run by some other user.Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.how to drop the tables and procedures created by an
> another user.What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
> Thanks & Regards,
> Mani
>sql

Drop table , procedure Problems

Hi,
I'm creating table, procedure from my c# codings in sql server.
I'm having database owner rights.
My problem is sometimes the table, procedure is created with
username.tablename instead of dbo.tablename. so i'm unable to drop that
table or procedure while my c# program is run by some other user.Kindly
suggest me a way to create table, procedure as dbo.tablename,
dbo.procedurename.how to drop the tables and procedures created by an
another user.What are the rights i required to create table,procedure
as dbo.tablename, dbo.procedurename in sql server 2000
Thanks & Regards,
ManiBOL says
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object
<plmanikandan@.gmail.com> wrote in message
news:1163675950.788703.71100@.i42g2000cwa.googlegroups.com...
> Hi,
> I'm creating table, procedure from my c# codings in sql server.
> I'm having database owner rights.
> My problem is sometimes the table, procedure is created with
> username.tablename instead of dbo.tablename. so i'm unable to drop that
> table or procedure while my c# program is run by some other user.Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.how to drop the tables and procedures created by an
> another user.What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
> Thanks & Regards,
> Mani
>|||> Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.
CREATE TABLE dbo.tblname(...)
CREATE PROC dbo.procname ... AS ...

> to drop the tables and procedures created by an
> another user.
DROP TABLE username.tblname
DROP PROC username.procname

> What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
db_ddladmin, db_owner or higher.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<plmanikandan@.gmail.com> wrote in message
news:1163675950.788703.71100@.i42g2000cwa.googlegroups.com...
> Hi,
> I'm creating table, procedure from my c# codings in sql server.
> I'm having database owner rights.
> My problem is sometimes the table, procedure is created with
> username.tablename instead of dbo.tablename. so i'm unable to drop that
> table or procedure while my c# program is run by some other user.Kindly
> suggest me a way to create table, procedure as dbo.tablename,
> dbo.procedurename.how to drop the tables and procedures created by an
> another user.What are the rights i required to create table,procedure
> as dbo.tablename, dbo.procedurename in sql server 2000
> Thanks & Regards,
> Mani
>

Drop Stored Procedure causing Dropped Tables

Hey guys, has anyone ever seen this happen:

Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.

Three tables referenced by that stored proc have been dropped and re-created with the same table structure.

I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?You will have to check the options you picked for the Transfer task in your DTS package. Did you ask it to move dependent objects also? For more help on the DTS package/tasks, please post in the SQL Server Integration Services forum.|||It was selected for dependent objects, but those tables are not dependent on the stored proc. As far as I know, the DTS method I used was the same as using DROP PROCEDURE, since it failed after the drop.|||I don't know how the DTS task determines dependencies. If it uses say sp_depends SP then you can check by running the SP for your SP to see the dependencies. Note, that this SP only gives immediate dependencies. If this doesn't help find how DTS determines dependencies then ask in the SSIS forum or run your package and trace the calls to SQL Server.

drop schema and its objects

I want to be able to drop a schema and all its objects if they exist. Can someone help me with such a stored procedure. I see the sql server does not allow dropping schema directly if it contains some objects.

It is not always true. if you use indexed views, foreign keys then you are correct.

You can use sp_depends 'Your table name' to find all the dependent views/sps & others..

|||

I think I did not explain my question clearly.

I am using :

Code Snippet

DROP SCHEMA [new]

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'new' because it is being referenced by object 'cattr'.

I want to be able to drop the schema, even if some objects reference it. If it is mandatory to first drop all objects that the schema contains, then how can I programmitically delete all such objects and then delete the schema ?

So given a schema name the stored procedure should be able to drop the schema.

|||

Is there any way to do the above mentioned.

|||

You could use following statement for determining objects in schema and there types:

Code Snippet

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<SCHEMA NAME>'

Then, you could use cursor for calling needed DROP statements

|||

I think there is one problem with the above approach. We need to drop tables in a schema in a particular order as there may be referencences, foreign key relationships etc.

How do I determine the order ?

|||

You could delete constrains at first, then delete tables.

Also you could include each DROP statement in TRY/CATCH block and when some DROP fail analyze error message

|||

Or even try following:

Code Snippet

declare @.cnt int

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

--iterate while all objects deleted

while @.cnt >0

Begin

--Drop objects with cursor

--Each drop statemnt include into TRY/CATCH block, but ignore error

--New objects count

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

end

|||

>> obj.type, obj.name

How do I construct the drop statement from these ?

Say I got 'U' as type and 'cattr' as table name, I need the statement "drop table cattr"

But how to get the mapping between type 'U' and what it represents 'table'

or do I have write explit if else statements like

if type = 'U'

drop table

elsif type = 'P'

drop procedure....

But that's going to be a long list.

|||

I am not sure as to why it is so difficult to drop a schema.... especially when someone has permissions to do it. When we can drop a table eventhough it contains some data, why can't we drop a schema even if it contains some objects ?

And How do I construct drop statements for each object , the problem which I described above ?

|||

You could use dynamic sql for constructing drop statements.

Use CASE statement for constructing varchar variable:

@.dropQuery = 'DROP '+<case statements that returns TABLE, CONSTRAINT etc>+' '+obj.name

Then: EXECUTE(@.dropQuery)

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.