Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?
Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?
CheersHi,
If resources arent an issue on your sqlserver you dont need to drop temps yourself. sqlserver will drop them when the sp is done.
Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).
Thursday, March 29, 2012
Dropping tempe tables
My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
Dropping tables from a article
Just a quick question , when you have a article is their any way to drop a table from a article.
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
dropping table from publication
I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
Dropping table Column in SQL server 6.5
I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>
Dropping table Column in SQL server 6.5
I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>
Dropping System Stored Procedures
When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?
> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A
|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>
|||and what kind of security audit tool/application are you using.
Thanks, Liliya
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
[vbcol=seagreen]
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...[vbcol=seagreen]
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz
|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
>
|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords
.
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?
> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A
|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>
|||and what kind of security audit tool/application are you using.
Thanks, Liliya
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
[vbcol=seagreen]
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...[vbcol=seagreen]
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz
|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
>
|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords
.In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz
Dropping System Stored Procedures
When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql
Dropping stored procedures
Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>
Dropping scripts into sql 2005
Something that really irritates me with sql2005 is when I try to drag and drop a script into sql2005 I have to relog basically. Say I'm dropping in 20 different scripts, I have to relog and select the database every time for each script. Sql2000 did not do this, I drag and drop in scripts and whatever database I was currently in, it would use. Is this just a setting that I need to change or is it a horrible "enhancement"?
Thanks for your help.
Hi,no I think currently this is not possible, one query windows per query will be opened and prompt you for the information where to connect to.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
dropping replication
Hi,
Is there any way to drop a single replication. one subscriber server was taken down without dropping first. Now I have a pull at publisher which is in state of reinitialized and waiting for the server to be back on.
I don not want to drop all replication and create them again. So I wonder if there is anyway to just drop that single replication belong to the taken off server.
Thanks for the help in advance.Yes, just execute 'sp_dropsubscription' with the correct parameters in the publication.
Just for future references, the pull subscription should be dropped in the subscriber before you take the server down.|||ty.
Edit:
according to BOL.
sp_dropsubscription is used in snapshot and transactional replication.
but this is merge.|||Did you mention it was a Merge?|||yup it is merge replication. I am sorry I didn't mention on first msg.
Is there any way to drop a single replication. one subscriber server was taken down without dropping first. Now I have a pull at publisher which is in state of reinitialized and waiting for the server to be back on.
I don not want to drop all replication and create them again. So I wonder if there is anyway to just drop that single replication belong to the taken off server.
Thanks for the help in advance.Yes, just execute 'sp_dropsubscription' with the correct parameters in the publication.
Just for future references, the pull subscription should be dropped in the subscriber before you take the server down.|||ty.
Edit:
according to BOL.
sp_dropsubscription is used in snapshot and transactional replication.
but this is merge.|||Did you mention it was a Merge?|||yup it is merge replication. I am sorry I didn't mention on first msg.
Dropping relationships
Is there an easy way to drop all of the relationships in a database in one
swoop?
Thanks.
Archeryou can use the following query to create the needed 'alter table' script:
select 'alter table '+ctu.table_name+' drop constraint
'+rc.constraint_name+char(13)
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on
rc.constraint_name=ctu.constraint_name
dean
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Try,
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare fk_constraints_cursor cursor local fast_forward
for
select
table_schema,
table_name,
constraint_name
from
information_schema.CONSTRAINT_TABLE_USAGE
where
objectproperty(object_id(quotename(const
raint_schema) + '.' +
quotename(constraint_name)), 'IsForeignKey') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open fk_constraints_cursor
while 1 = 1
begin
fetch next from fk_constraints_cursor into @.ts, @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
drop constraint '+ quotename(@.cn)
print @.sql
exec sp_executesql @.sql
end
close fk_constraints_cursor
deallocate fk_constraints_cursor
go
AMB
"bagman3rd" wrote:
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Here is my proc that I use. It gives you a lot of flexibility (use at your
own risk, obviously)
if object_id('utility$removeRelationships')
is not null
begin
drop procedure utility$removeRelationships
end
go
create procedure utility$removeRelationships
(
@.table_schema sysname = 'dbo', --does not do a like comparison
@.parent_table_name sysname = '%', --it is the parrent when it is being
referred to
@.child_table_name sysname = '%', --it is the child table when it is the
table referring
--to another
@.constraint_name sysname = '%', --can be used to drop only a single
constraint
@.display_only_fl bit = 1 --displays the statements to execute, but does not
execute them
) as
-- ---
-- Drop all of the foreign key contraints on and or to a table
-- ---
-- Runs on table name in the current database
-- ---
-- 01/11/2005 lbdavi created
-- ---
begin
set nocount on
declare @.statements cursor
set @.statements = cursor for
select 'alter table ' + ctu.table_schema + '.' + ctu.table_name +
' drop constraint ' + cc.constraint_name
from information_schema.referential_constraints as cc
join information_schema.constraint_table_usage as ctu
on cc.constraint_catalog = ctu.constraint_catalog
and cc.constraint_schema = ctu.constraint_schema
and cc.constraint_name = ctu.constraint_name
where ctu.table_schema = @.table_schema
and ctu.table_name like @.child_table_name
and cc.constraint_name like @.constraint_name
and exists (select *
from information_schema.constraint_table_usage ctu2
where cc.unique_constraint_catalog = ctu2.constraint_catalog
and cc.unique_constraint_schema = ctu2.constraint_schema
and cc.unique_constraint_name = ctu2.constraint_name
and ctu2.table_schema = @.table_schema
and ctu2.table_name like @.parent_table_name)
open @.statements
declare @.statement varchar(1000)
fetch from @.statements into @.statement
While @.@.fetch_status = 0
begin
if @.display_only_fl = 1
begin
select @.statement as statementPreview
end
else
begin
exec (@.statement)
end
fetch next from @.statements into @.statement
end
end
go
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer
swoop?
Thanks.
Archeryou can use the following query to create the needed 'alter table' script:
select 'alter table '+ctu.table_name+' drop constraint
'+rc.constraint_name+char(13)
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on
rc.constraint_name=ctu.constraint_name
dean
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Try,
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare fk_constraints_cursor cursor local fast_forward
for
select
table_schema,
table_name,
constraint_name
from
information_schema.CONSTRAINT_TABLE_USAGE
where
objectproperty(object_id(quotename(const
raint_schema) + '.' +
quotename(constraint_name)), 'IsForeignKey') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open fk_constraints_cursor
while 1 = 1
begin
fetch next from fk_constraints_cursor into @.ts, @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
drop constraint '+ quotename(@.cn)
print @.sql
exec sp_executesql @.sql
end
close fk_constraints_cursor
deallocate fk_constraints_cursor
go
AMB
"bagman3rd" wrote:
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Here is my proc that I use. It gives you a lot of flexibility (use at your
own risk, obviously)
if object_id('utility$removeRelationships')
is not null
begin
drop procedure utility$removeRelationships
end
go
create procedure utility$removeRelationships
(
@.table_schema sysname = 'dbo', --does not do a like comparison
@.parent_table_name sysname = '%', --it is the parrent when it is being
referred to
@.child_table_name sysname = '%', --it is the child table when it is the
table referring
--to another
@.constraint_name sysname = '%', --can be used to drop only a single
constraint
@.display_only_fl bit = 1 --displays the statements to execute, but does not
execute them
) as
-- ---
-- Drop all of the foreign key contraints on and or to a table
-- ---
-- Runs on table name in the current database
-- ---
-- 01/11/2005 lbdavi created
-- ---
begin
set nocount on
declare @.statements cursor
set @.statements = cursor for
select 'alter table ' + ctu.table_schema + '.' + ctu.table_name +
' drop constraint ' + cc.constraint_name
from information_schema.referential_constraints as cc
join information_schema.constraint_table_usage as ctu
on cc.constraint_catalog = ctu.constraint_catalog
and cc.constraint_schema = ctu.constraint_schema
and cc.constraint_name = ctu.constraint_name
where ctu.table_schema = @.table_schema
and ctu.table_name like @.child_table_name
and cc.constraint_name like @.constraint_name
and exists (select *
from information_schema.constraint_table_usage ctu2
where cc.unique_constraint_catalog = ctu2.constraint_catalog
and cc.unique_constraint_schema = ctu2.constraint_schema
and cc.unique_constraint_name = ctu2.constraint_name
and ctu2.table_schema = @.table_schema
and ctu2.table_name like @.parent_table_name)
open @.statements
declare @.statement varchar(1000)
fetch from @.statements into @.statement
While @.@.fetch_status = 0
begin
if @.display_only_fl = 1
begin
select @.statement as statementPreview
end
else
begin
exec (@.statement)
end
fetch next from @.statements into @.statement
end
end
go
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer
Dropping Primary Key once a week
The company I work for is planning to use a new software package for its
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
Dave
Just what is this "customer identification number" and where does it come
from? What's the reason for making the changes?
A well-chosen key should change seldom and I'm sure there must be a better
way to meet your requirements than what you have described. It sounds like
you have a fundamental data-modelling problem.
David Portas
SQL Server MVP
|||In addition to David's comments, I would consider getting some new
consultants for a number of reasons. First, changing the schema of a
packaged product is (or should be considered) equivalent to changing the
code; this is an action not likely supported by your license. Second,
dropping the PK means that you would need to disable the "system" for the
duration of this activity; is this an acceptable business limitation?
Lastly, there are likely to be other alternatives (such as some type of
"translation" mechanism that can be done outside of the product's packaged
schema). Your consultants should be identifying all possible alternatives
for accomplishing the business goal / process, evaluating the pros/cons, and
justifying their adopted approach. If not, then you need new consultants.
If your company is not requiring this, then your company very much needs
real help that it is not getting from the consultants. This might be your
lucky break! Good luck.
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>
|||Hi Dave
The customer identification number is the number our customer uses in their
daily business. It is assigned to our customers based on their company name.
If the company name is changed, so is the #. Its confusing, but unfortunately
it is not possible to change this requirement.
I agree with the fundamental data problem. What I'm hoping for is concrete
examples & documentation on the risks caused by dropping and recreating the
primary key after changing the values in some rows. It may be possible to
persuade upper management from moving ahead with the consultants suggestion.
Thanks
Dave
"David Portas" wrote:
> Just what is this "customer identification number" and where does it come
> from? What's the reason for making the changes?
> A well-chosen key should change seldom and I'm sure there must be a better
> way to meet your requirements than what you have described. It sounds like
> you have a fundamental data-modelling problem.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> The customer identification number is the number our customer uses in
their
> daily business. It is assigned to our customers based on their company
name.
> If the company name is changed, so is the #. Its confusing, but
unfortunately
> it is not possible to change this requirement.
There is no problem with this, as long as you don't use this ID as a primary
key. Use a surrogate key, and this problem disappears.
|||Thanks Scott, I agree with your comments. I'm hoping to educate myself on all
of the potential dangers of the consultants plans to be more effective in
supporting an alternative. Any details, documents, comments are very
appreciated.
The consultants believe that if the changes are done overnight - the
timeframe would not be a problem to have everything functioning the next day.
Please keep sharing your thoughts - I am appreciative of your comments.
"Scott Morris" wrote:
> In addition to David's comments, I would consider getting some new
> consultants for a number of reasons. First, changing the schema of a
> packaged product is (or should be considered) equivalent to changing the
> code; this is an action not likely supported by your license. Second,
> dropping the PK means that you would need to disable the "system" for the
> duration of this activity; is this an acceptable business limitation?
> Lastly, there are likely to be other alternatives (such as some type of
> "translation" mechanism that can be done outside of the product's packaged
> schema). Your consultants should be identifying all possible alternatives
> for accomplishing the business goal / process, evaluating the pros/cons, and
> justifying their adopted approach. If not, then you need new consultants.
> If your company is not requiring this, then your company very much needs
> real help that it is not getting from the consultants. This might be your
> lucky break! Good luck.
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> indexes
> was
> or -
>
>
|||Assuming you can take the system offline and assuming the changes themselves
won't violate data integrity inside or outside your system, I don't see any
particular risks - just a lot of disruption and associated costs. If your
system is that poorly suited to your business needs then I would say your
consultants' time would be better spent looking for a new system.
David Portas
SQL Server MVP
|||Mark - thats the problem - they want to use the primary key. They plan to
drop all indexes, triggers, constraints, etc...change the data then rebuild
- once a week.
What are the potential problems?
Thanks
Dave
"Mark Wilden" wrote:
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> their
> name.
> unfortunately
> There is no problem with this, as long as you don't use this ID as a primary
> key. Use a surrogate key, and this problem disappears.
>
>
|||One of the risks is that the recreation process will either fail or will
rebuild the schema with something other than the most current DDL. Over
time, it is inevitable that the schema will change (either by your company,
MS, or your system vendor). The rebuild process must be kept current with
the schema - a responsibility that falls on your company.
If the rebuild process fails, what is the recovery strategy?
What timeframe is required for this entire process? Any estimates from the
consultants? This assumes that someone has generated an estimate for the
maximum size of the database (which will affect the rebuild speed).
Has anyone discussed this strategy with your system vendor (assuming you
have a support agreement of some type)?
While the proposed process is do-able, it sounds like your organization does
not have a lot of experience with RDBMS servers. If true, this lack of
experience is a risk. Will the consultants be responsible for the rebuild
process? If not, will they be available (and how quickly) if "something"
happens? Does your organization have a support structure that can handle a
failure with assistance?
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:CDB9B3AF-FC42-4875-96FE-4E5D5A870124@.microsoft.com...
> Mark - thats the problem - they want to use the primary key. They plan to
> drop all indexes, triggers, constraints, etc...change the data then
rebuild[vbcol=seagreen]
> - once a week.
> What are the potential problems?
> Thanks
> Dave
> "Mark Wilden" wrote:
primary[vbcol=seagreen]
|||On Fri, 3 Dec 2004 11:11:05 -0800, ddeegan wrote:
(snip)
>Our business needs requires us to change the customer identification # for
>certain customers. This would mean changing the value of the primary key.
>The consultants are planning to have a routine that drops all of the indexes
>and constraints, change the values then rebuild all of the tables.
(snip)
Hi Dave,
What is the reason for dropping and recreating the indexes and
constraints? What is the reason for not using cascading updates?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
Dave
Just what is this "customer identification number" and where does it come
from? What's the reason for making the changes?
A well-chosen key should change seldom and I'm sure there must be a better
way to meet your requirements than what you have described. It sounds like
you have a fundamental data-modelling problem.
David Portas
SQL Server MVP
|||In addition to David's comments, I would consider getting some new
consultants for a number of reasons. First, changing the schema of a
packaged product is (or should be considered) equivalent to changing the
code; this is an action not likely supported by your license. Second,
dropping the PK means that you would need to disable the "system" for the
duration of this activity; is this an acceptable business limitation?
Lastly, there are likely to be other alternatives (such as some type of
"translation" mechanism that can be done outside of the product's packaged
schema). Your consultants should be identifying all possible alternatives
for accomplishing the business goal / process, evaluating the pros/cons, and
justifying their adopted approach. If not, then you need new consultants.
If your company is not requiring this, then your company very much needs
real help that it is not getting from the consultants. This might be your
lucky break! Good luck.
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>
|||Hi Dave
The customer identification number is the number our customer uses in their
daily business. It is assigned to our customers based on their company name.
If the company name is changed, so is the #. Its confusing, but unfortunately
it is not possible to change this requirement.
I agree with the fundamental data problem. What I'm hoping for is concrete
examples & documentation on the risks caused by dropping and recreating the
primary key after changing the values in some rows. It may be possible to
persuade upper management from moving ahead with the consultants suggestion.
Thanks
Dave
"David Portas" wrote:
> Just what is this "customer identification number" and where does it come
> from? What's the reason for making the changes?
> A well-chosen key should change seldom and I'm sure there must be a better
> way to meet your requirements than what you have described. It sounds like
> you have a fundamental data-modelling problem.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> The customer identification number is the number our customer uses in
their
> daily business. It is assigned to our customers based on their company
name.
> If the company name is changed, so is the #. Its confusing, but
unfortunately
> it is not possible to change this requirement.
There is no problem with this, as long as you don't use this ID as a primary
key. Use a surrogate key, and this problem disappears.
|||Thanks Scott, I agree with your comments. I'm hoping to educate myself on all
of the potential dangers of the consultants plans to be more effective in
supporting an alternative. Any details, documents, comments are very
appreciated.
The consultants believe that if the changes are done overnight - the
timeframe would not be a problem to have everything functioning the next day.
Please keep sharing your thoughts - I am appreciative of your comments.
"Scott Morris" wrote:
> In addition to David's comments, I would consider getting some new
> consultants for a number of reasons. First, changing the schema of a
> packaged product is (or should be considered) equivalent to changing the
> code; this is an action not likely supported by your license. Second,
> dropping the PK means that you would need to disable the "system" for the
> duration of this activity; is this an acceptable business limitation?
> Lastly, there are likely to be other alternatives (such as some type of
> "translation" mechanism that can be done outside of the product's packaged
> schema). Your consultants should be identifying all possible alternatives
> for accomplishing the business goal / process, evaluating the pros/cons, and
> justifying their adopted approach. If not, then you need new consultants.
> If your company is not requiring this, then your company very much needs
> real help that it is not getting from the consultants. This might be your
> lucky break! Good luck.
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> indexes
> was
> or -
>
>
|||Assuming you can take the system offline and assuming the changes themselves
won't violate data integrity inside or outside your system, I don't see any
particular risks - just a lot of disruption and associated costs. If your
system is that poorly suited to your business needs then I would say your
consultants' time would be better spent looking for a new system.
David Portas
SQL Server MVP
|||Mark - thats the problem - they want to use the primary key. They plan to
drop all indexes, triggers, constraints, etc...change the data then rebuild
- once a week.
What are the potential problems?
Thanks
Dave
"Mark Wilden" wrote:
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> their
> name.
> unfortunately
> There is no problem with this, as long as you don't use this ID as a primary
> key. Use a surrogate key, and this problem disappears.
>
>
|||One of the risks is that the recreation process will either fail or will
rebuild the schema with something other than the most current DDL. Over
time, it is inevitable that the schema will change (either by your company,
MS, or your system vendor). The rebuild process must be kept current with
the schema - a responsibility that falls on your company.
If the rebuild process fails, what is the recovery strategy?
What timeframe is required for this entire process? Any estimates from the
consultants? This assumes that someone has generated an estimate for the
maximum size of the database (which will affect the rebuild speed).
Has anyone discussed this strategy with your system vendor (assuming you
have a support agreement of some type)?
While the proposed process is do-able, it sounds like your organization does
not have a lot of experience with RDBMS servers. If true, this lack of
experience is a risk. Will the consultants be responsible for the rebuild
process? If not, will they be available (and how quickly) if "something"
happens? Does your organization have a support structure that can handle a
failure with assistance?
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:CDB9B3AF-FC42-4875-96FE-4E5D5A870124@.microsoft.com...
> Mark - thats the problem - they want to use the primary key. They plan to
> drop all indexes, triggers, constraints, etc...change the data then
rebuild[vbcol=seagreen]
> - once a week.
> What are the potential problems?
> Thanks
> Dave
> "Mark Wilden" wrote:
primary[vbcol=seagreen]
|||On Fri, 3 Dec 2004 11:11:05 -0800, ddeegan wrote:
(snip)
>Our business needs requires us to change the customer identification # for
>certain customers. This would mean changing the value of the primary key.
>The consultants are planning to have a routine that drops all of the indexes
>and constraints, change the values then rebuild all of the tables.
(snip)
Hi Dave,
What is the reason for dropping and recreating the indexes and
constraints? What is the reason for not using cascading updates?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
Dropping Primary Key once a week
The company I work for is planning to use a new software package for its
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
DaveJust what is this "customer identification number" and where does it come
from? What's the reason for making the changes?
A well-chosen key should change seldom and I'm sure there must be a better
way to meet your requirements than what you have described. It sounds like
you have a fundamental data-modelling problem.
--
David Portas
SQL Server MVP
--|||In addition to David's comments, I would consider getting some new
consultants for a number of reasons. First, changing the schema of a
packaged product is (or should be considered) equivalent to changing the
code; this is an action not likely supported by your license. Second,
dropping the PK means that you would need to disable the "system" for the
duration of this activity; is this an acceptable business limitation?
Lastly, there are likely to be other alternatives (such as some type of
"translation" mechanism that can be done outside of the product's packaged
schema). Your consultants should be identifying all possible alternatives
for accomplishing the business goal / process, evaluating the pros/cons, and
justifying their adopted approach. If not, then you need new consultants.
If your company is not requiring this, then your company very much needs
real help that it is not getting from the consultants. This might be your
lucky break! Good luck.
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>|||Hi Dave
The customer identification number is the number our customer uses in their
daily business. It is assigned to our customers based on their company name.
If the company name is changed, so is the #. Its confusing, but unfortunately
it is not possible to change this requirement.
I agree with the fundamental data problem. What I'm hoping for is concrete
examples & documentation on the risks caused by dropping and recreating the
primary key after changing the values in some rows. It may be possible to
persuade upper management from moving ahead with the consultants suggestion.
Thanks
Dave
"David Portas" wrote:
> Just what is this "customer identification number" and where does it come
> from? What's the reason for making the changes?
> A well-chosen key should change seldom and I'm sure there must be a better
> way to meet your requirements than what you have described. It sounds like
> you have a fundamental data-modelling problem.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> The customer identification number is the number our customer uses in
their
> daily business. It is assigned to our customers based on their company
name.
> If the company name is changed, so is the #. Its confusing, but
unfortunately
> it is not possible to change this requirement.
There is no problem with this, as long as you don't use this ID as a primary
key. Use a surrogate key, and this problem disappears.|||Thanks Scott, I agree with your comments. I'm hoping to educate myself on all
of the potential dangers of the consultants plans to be more effective in
supporting an alternative. Any details, documents, comments are very
appreciated.
The consultants believe that if the changes are done overnight - the
timeframe would not be a problem to have everything functioning the next day.
Please keep sharing your thoughts - I am appreciative of your comments.
"Scott Morris" wrote:
> In addition to David's comments, I would consider getting some new
> consultants for a number of reasons. First, changing the schema of a
> packaged product is (or should be considered) equivalent to changing the
> code; this is an action not likely supported by your license. Second,
> dropping the PK means that you would need to disable the "system" for the
> duration of this activity; is this an acceptable business limitation?
> Lastly, there are likely to be other alternatives (such as some type of
> "translation" mechanism that can be done outside of the product's packaged
> schema). Your consultants should be identifying all possible alternatives
> for accomplishing the business goal / process, evaluating the pros/cons, and
> justifying their adopted approach. If not, then you need new consultants.
> If your company is not requiring this, then your company very much needs
> real help that it is not getting from the consultants. This might be your
> lucky break! Good luck.
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> > The company I work for is planning to use a new software package for its
> > internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> > histories, etc.
> >
> > The software uses the prmary key to contain the customer identification #.
> >
> > Our business needs requires us to change the customer identification # for
> > certain customers. This would mean changing the value of the primary key.
> >
> > The consultants are planning to have a routine that drops all of the
> indexes
> > and constraints, change the values then rebuild all of the tables.
> >
> > This seems like a dangerous idea to me however it is not my decision. I
> was
> > hoping to get opinions or links to documentation warning against this -
> or -
> > is there nothing to worry about?
> >
> > Any documentation, details, or advice would be appreciated. If there is a
> > more appropriate forum for this type of question, please let me know.
> >
> > Thanks in advance
> > Dave
> >
>
>|||Assuming you can take the system offline and assuming the changes themselves
won't violate data integrity inside or outside your system, I don't see any
particular risks - just a lot of disruption and associated costs. If your
system is that poorly suited to your business needs then I would say your
consultants' time would be better spent looking for a new system.
--
David Portas
SQL Server MVP
--|||Mark - thats the problem - they want to use the primary key. They plan to
drop all indexes, triggers, constraints, etc...change the data then rebuild
- once a week.
What are the potential problems?
Thanks
Dave
"Mark Wilden" wrote:
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> >
> > The customer identification number is the number our customer uses in
> their
> > daily business. It is assigned to our customers based on their company
> name.
> > If the company name is changed, so is the #. Its confusing, but
> unfortunately
> > it is not possible to change this requirement.
> There is no problem with this, as long as you don't use this ID as a primary
> key. Use a surrogate key, and this problem disappears.
>
>|||One of the risks is that the recreation process will either fail or will
rebuild the schema with something other than the most current DDL. Over
time, it is inevitable that the schema will change (either by your company,
MS, or your system vendor). The rebuild process must be kept current with
the schema - a responsibility that falls on your company.
If the rebuild process fails, what is the recovery strategy?
What timeframe is required for this entire process? Any estimates from the
consultants? This assumes that someone has generated an estimate for the
maximum size of the database (which will affect the rebuild speed).
Has anyone discussed this strategy with your system vendor (assuming you
have a support agreement of some type)?
While the proposed process is do-able, it sounds like your organization does
not have a lot of experience with RDBMS servers. If true, this lack of
experience is a risk. Will the consultants be responsible for the rebuild
process? If not, will they be available (and how quickly) if "something"
happens? Does your organization have a support structure that can handle a
failure with assistance?
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:CDB9B3AF-FC42-4875-96FE-4E5D5A870124@.microsoft.com...
> Mark - thats the problem - they want to use the primary key. They plan to
> drop all indexes, triggers, constraints, etc...change the data then
rebuild
> - once a week.
> What are the potential problems?
> Thanks
> Dave
> "Mark Wilden" wrote:
> > "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> > news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> > >
> > > The customer identification number is the number our customer uses in
> > their
> > > daily business. It is assigned to our customers based on their company
> > name.
> > > If the company name is changed, so is the #. Its confusing, but
> > unfortunately
> > > it is not possible to change this requirement.
> >
> > There is no problem with this, as long as you don't use this ID as a
primary
> > key. Use a surrogate key, and this problem disappears.
> >
> >
> >|||On Fri, 3 Dec 2004 11:11:05 -0800, ddeegan wrote:
(snip)
>Our business needs requires us to change the customer identification # for
>certain customers. This would mean changing the value of the primary key.
>The consultants are planning to have a routine that drops all of the indexes
>and constraints, change the values then rebuild all of the tables.
(snip)
Hi Dave,
What is the reason for dropping and recreating the indexes and
constraints? What is the reason for not using cascading updates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 3 Dec 2004 11:11:05 -0800, "ddeegan"
<ddeegan@.discussions.microsoft.com> wrote:
>Any documentation, details, or advice would be appreciated. If there is a
>more appropriate forum for this type of question, please let me know.
I concur with the other comments already posted.
This sounds like a major goofy idea. Disable constraints and keys to
change a handful of data values? These guys must be nuts. What harm
is there? No HARM. Maybe they should translate all the names to
Chinese and back every night, too. What's the harm in that? Maybe
you want to rebuild your car's engine every night. What's the harm in
that? Might be a good thing, right? Yeah, right.
Use surrogate keys if this is a serious problem, cascades if you must,
or write some SP's to do it safely. Lots of better alternatives that
requires zero (0) downtime.
J.|||As the others say, the total rebuild of 'everything' is probably a bad idee.
(What is this goes wrong ?).
As the others say, can't the design be 'mended' so the problem does not
occure.
For the following answers I am going to suppose the a redesign is not
feasable within your situation. And that customer names still change and
that they are part of primairy key's and relations.
Solutions could be :
1.
Cascading.
But then all relations have to be present and in the correct 'Order', if the
relations
are not like a tree, cascading might not be possible. (For example if there
are double parallel relations, or self referencing tables).
2.
Switch of the constraints of the concerned tables.
Alter the customer id's.
Switch on the constraints of the concerned tables.
3.
For all tables which are 'parent' tables to other tables. Make a new entry
(duplicate) with
the 'new' customer name.
Update all the children of that customer.
Then delete all rows which have been duplicated.
This method has to be implemented very carefully, the order of tables has to
be correct.
There should be no unique constraint (not involving the customer_id) in the
tables.
Duplication has to be done top down. Deletion has to be done bottom up.
4.
Take all information of a customer who changes it's name out of the database
and
reenter it with the correct customer_id.
(Offcourse this has to be done in the correct order for the tables).
In all for of the above method's most changes have (or at least is prevered)
to be done
on one customer at the time. (Then the load and impact can be spread over a
larger
period so that normal performance is less hindered or interrupted).
The number of customer changing their names can not be that huge I suppose.
Method 2 is probably the easiest to implement, but switching of a constraint
of a table,
gives a large resource lock on that table. But then again the change of one
customer
can probably be done in a few seconds.
Good luck,
Please keep us informed you how you are going to tackle your problem.
And how it works when implemented.
Greetings,
Ben Brugman
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>|||Hello - just wanted to say thanks for all the helpful responses. I documented
everything and forwarded it up the ladder.
Alot of contracts have been signed, so we have to move ahead the best we can
and keep working towards a solution.
The comparison to rebuilding the car engine was great.
Thanks
Dave
"ben brugman" wrote:
> As the others say, the total rebuild of 'everything' is probably a bad idee.
> (What is this goes wrong ?).
> As the others say, can't the design be 'mended' so the problem does not
> occure.
> For the following answers I am going to suppose the a redesign is not
> feasable within your situation. And that customer names still change and
> that they are part of primairy key's and relations.
> Solutions could be :
> 1.
> Cascading.
> But then all relations have to be present and in the correct 'Order', if the
> relations
> are not like a tree, cascading might not be possible. (For example if there
> are double parallel relations, or self referencing tables).
> 2.
> Switch of the constraints of the concerned tables.
> Alter the customer id's.
> Switch on the constraints of the concerned tables.
> 3.
> For all tables which are 'parent' tables to other tables. Make a new entry
> (duplicate) with
> the 'new' customer name.
> Update all the children of that customer.
> Then delete all rows which have been duplicated.
> This method has to be implemented very carefully, the order of tables has to
> be correct.
> There should be no unique constraint (not involving the customer_id) in the
> tables.
> Duplication has to be done top down. Deletion has to be done bottom up.
> 4.
> Take all information of a customer who changes it's name out of the database
> and
> reenter it with the correct customer_id.
> (Offcourse this has to be done in the correct order for the tables).
> In all for of the above method's most changes have (or at least is prevered)
> to be done
> on one customer at the time. (Then the load and impact can be spread over a
> larger
> period so that normal performance is less hindered or interrupted).
> The number of customer changing their names can not be that huge I suppose.
> Method 2 is probably the easiest to implement, but switching of a constraint
> of a table,
> gives a large resource lock on that table. But then again the change of one
> customer
> can probably be done in a few seconds.
> Good luck,
> Please keep us informed you how you are going to tackle your problem.
> And how it works when implemented.
> Greetings,
> Ben Brugman
>
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> > The company I work for is planning to use a new software package for its
> > internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> > histories, etc.
> >
> > The software uses the prmary key to contain the customer identification #.
> >
> > Our business needs requires us to change the customer identification # for
> > certain customers. This would mean changing the value of the primary key.
> >
> > The consultants are planning to have a routine that drops all of the
> indexes
> > and constraints, change the values then rebuild all of the tables.
> >
> > This seems like a dangerous idea to me however it is not my decision. I
> was
> > hoping to get opinions or links to documentation warning against this -
> or -
> > is there nothing to worry about?
> >
> > Any documentation, details, or advice would be appreciated. If there is a
> > more appropriate forum for this type of question, please let me know.
> >
> > Thanks in advance
> > Dave
> >
>
>|||First and foremost, the definition of a database is a physical represention
of the logical relationships between data. The primary reason for the
creation of the Relational Theory of Data was to put into a mathematical
framework those ideas essential for the manipulation, integrety, and
consistency of data models.
Without data integrity, you do not have a database nor the garauntee of
logical consistency.
The modification of parent information is well formulated with the
requirement that all parent data is replicated with the updated information
and then all dependent child data is migrated to this new information, only
then is the original parent data removed. All the while, leaving the
Declaritive Referential Integrity constraints online. If they are not,
there is no garauntee of data consistency and, thus, no garuantee of a
consistent database.
Now, I said DRI, this does not include procedural referential integrity:
triggers, stored procedures, and/or API processes. All of these can be
disabled with simple statements without having to drop and recreate them,
nor should the compromise the database consistency or integrity if we are
only relabeling currently consistent data. However, as others have pointed
out, the system would need to be offline while these operations occurred.
As far as documentation and litereature, check out:
E. F. Codd's original works on the Fundemental Theory of Data
A Relational Model of Data for Large Shared Databanks,
http://www.acm.org/classics/nov95/toc.html,
C. J. Date's An Introduction to Database Systems,
http://www.dbdebunk.com/books.html,
and/or Fabian Pascal's Practical Issues in Database Management.
Sincerely,
Anthony Thomas
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
The company I work for is planning to use a new software package for its
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
Dave
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
DaveJust what is this "customer identification number" and where does it come
from? What's the reason for making the changes?
A well-chosen key should change seldom and I'm sure there must be a better
way to meet your requirements than what you have described. It sounds like
you have a fundamental data-modelling problem.
--
David Portas
SQL Server MVP
--|||In addition to David's comments, I would consider getting some new
consultants for a number of reasons. First, changing the schema of a
packaged product is (or should be considered) equivalent to changing the
code; this is an action not likely supported by your license. Second,
dropping the PK means that you would need to disable the "system" for the
duration of this activity; is this an acceptable business limitation?
Lastly, there are likely to be other alternatives (such as some type of
"translation" mechanism that can be done outside of the product's packaged
schema). Your consultants should be identifying all possible alternatives
for accomplishing the business goal / process, evaluating the pros/cons, and
justifying their adopted approach. If not, then you need new consultants.
If your company is not requiring this, then your company very much needs
real help that it is not getting from the consultants. This might be your
lucky break! Good luck.
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>|||Hi Dave
The customer identification number is the number our customer uses in their
daily business. It is assigned to our customers based on their company name.
If the company name is changed, so is the #. Its confusing, but unfortunately
it is not possible to change this requirement.
I agree with the fundamental data problem. What I'm hoping for is concrete
examples & documentation on the risks caused by dropping and recreating the
primary key after changing the values in some rows. It may be possible to
persuade upper management from moving ahead with the consultants suggestion.
Thanks
Dave
"David Portas" wrote:
> Just what is this "customer identification number" and where does it come
> from? What's the reason for making the changes?
> A well-chosen key should change seldom and I'm sure there must be a better
> way to meet your requirements than what you have described. It sounds like
> you have a fundamental data-modelling problem.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> The customer identification number is the number our customer uses in
their
> daily business. It is assigned to our customers based on their company
name.
> If the company name is changed, so is the #. Its confusing, but
unfortunately
> it is not possible to change this requirement.
There is no problem with this, as long as you don't use this ID as a primary
key. Use a surrogate key, and this problem disappears.|||Thanks Scott, I agree with your comments. I'm hoping to educate myself on all
of the potential dangers of the consultants plans to be more effective in
supporting an alternative. Any details, documents, comments are very
appreciated.
The consultants believe that if the changes are done overnight - the
timeframe would not be a problem to have everything functioning the next day.
Please keep sharing your thoughts - I am appreciative of your comments.
"Scott Morris" wrote:
> In addition to David's comments, I would consider getting some new
> consultants for a number of reasons. First, changing the schema of a
> packaged product is (or should be considered) equivalent to changing the
> code; this is an action not likely supported by your license. Second,
> dropping the PK means that you would need to disable the "system" for the
> duration of this activity; is this an acceptable business limitation?
> Lastly, there are likely to be other alternatives (such as some type of
> "translation" mechanism that can be done outside of the product's packaged
> schema). Your consultants should be identifying all possible alternatives
> for accomplishing the business goal / process, evaluating the pros/cons, and
> justifying their adopted approach. If not, then you need new consultants.
> If your company is not requiring this, then your company very much needs
> real help that it is not getting from the consultants. This might be your
> lucky break! Good luck.
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> > The company I work for is planning to use a new software package for its
> > internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> > histories, etc.
> >
> > The software uses the prmary key to contain the customer identification #.
> >
> > Our business needs requires us to change the customer identification # for
> > certain customers. This would mean changing the value of the primary key.
> >
> > The consultants are planning to have a routine that drops all of the
> indexes
> > and constraints, change the values then rebuild all of the tables.
> >
> > This seems like a dangerous idea to me however it is not my decision. I
> was
> > hoping to get opinions or links to documentation warning against this -
> or -
> > is there nothing to worry about?
> >
> > Any documentation, details, or advice would be appreciated. If there is a
> > more appropriate forum for this type of question, please let me know.
> >
> > Thanks in advance
> > Dave
> >
>
>|||Assuming you can take the system offline and assuming the changes themselves
won't violate data integrity inside or outside your system, I don't see any
particular risks - just a lot of disruption and associated costs. If your
system is that poorly suited to your business needs then I would say your
consultants' time would be better spent looking for a new system.
--
David Portas
SQL Server MVP
--|||Mark - thats the problem - they want to use the primary key. They plan to
drop all indexes, triggers, constraints, etc...change the data then rebuild
- once a week.
What are the potential problems?
Thanks
Dave
"Mark Wilden" wrote:
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> >
> > The customer identification number is the number our customer uses in
> their
> > daily business. It is assigned to our customers based on their company
> name.
> > If the company name is changed, so is the #. Its confusing, but
> unfortunately
> > it is not possible to change this requirement.
> There is no problem with this, as long as you don't use this ID as a primary
> key. Use a surrogate key, and this problem disappears.
>
>|||One of the risks is that the recreation process will either fail or will
rebuild the schema with something other than the most current DDL. Over
time, it is inevitable that the schema will change (either by your company,
MS, or your system vendor). The rebuild process must be kept current with
the schema - a responsibility that falls on your company.
If the rebuild process fails, what is the recovery strategy?
What timeframe is required for this entire process? Any estimates from the
consultants? This assumes that someone has generated an estimate for the
maximum size of the database (which will affect the rebuild speed).
Has anyone discussed this strategy with your system vendor (assuming you
have a support agreement of some type)?
While the proposed process is do-able, it sounds like your organization does
not have a lot of experience with RDBMS servers. If true, this lack of
experience is a risk. Will the consultants be responsible for the rebuild
process? If not, will they be available (and how quickly) if "something"
happens? Does your organization have a support structure that can handle a
failure with assistance?
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:CDB9B3AF-FC42-4875-96FE-4E5D5A870124@.microsoft.com...
> Mark - thats the problem - they want to use the primary key. They plan to
> drop all indexes, triggers, constraints, etc...change the data then
rebuild
> - once a week.
> What are the potential problems?
> Thanks
> Dave
> "Mark Wilden" wrote:
> > "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> > news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> > >
> > > The customer identification number is the number our customer uses in
> > their
> > > daily business. It is assigned to our customers based on their company
> > name.
> > > If the company name is changed, so is the #. Its confusing, but
> > unfortunately
> > > it is not possible to change this requirement.
> >
> > There is no problem with this, as long as you don't use this ID as a
primary
> > key. Use a surrogate key, and this problem disappears.
> >
> >
> >|||On Fri, 3 Dec 2004 11:11:05 -0800, ddeegan wrote:
(snip)
>Our business needs requires us to change the customer identification # for
>certain customers. This would mean changing the value of the primary key.
>The consultants are planning to have a routine that drops all of the indexes
>and constraints, change the values then rebuild all of the tables.
(snip)
Hi Dave,
What is the reason for dropping and recreating the indexes and
constraints? What is the reason for not using cascading updates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 3 Dec 2004 11:11:05 -0800, "ddeegan"
<ddeegan@.discussions.microsoft.com> wrote:
>Any documentation, details, or advice would be appreciated. If there is a
>more appropriate forum for this type of question, please let me know.
I concur with the other comments already posted.
This sounds like a major goofy idea. Disable constraints and keys to
change a handful of data values? These guys must be nuts. What harm
is there? No HARM. Maybe they should translate all the names to
Chinese and back every night, too. What's the harm in that? Maybe
you want to rebuild your car's engine every night. What's the harm in
that? Might be a good thing, right? Yeah, right.
Use surrogate keys if this is a serious problem, cascades if you must,
or write some SP's to do it safely. Lots of better alternatives that
requires zero (0) downtime.
J.|||As the others say, the total rebuild of 'everything' is probably a bad idee.
(What is this goes wrong ?).
As the others say, can't the design be 'mended' so the problem does not
occure.
For the following answers I am going to suppose the a redesign is not
feasable within your situation. And that customer names still change and
that they are part of primairy key's and relations.
Solutions could be :
1.
Cascading.
But then all relations have to be present and in the correct 'Order', if the
relations
are not like a tree, cascading might not be possible. (For example if there
are double parallel relations, or self referencing tables).
2.
Switch of the constraints of the concerned tables.
Alter the customer id's.
Switch on the constraints of the concerned tables.
3.
For all tables which are 'parent' tables to other tables. Make a new entry
(duplicate) with
the 'new' customer name.
Update all the children of that customer.
Then delete all rows which have been duplicated.
This method has to be implemented very carefully, the order of tables has to
be correct.
There should be no unique constraint (not involving the customer_id) in the
tables.
Duplication has to be done top down. Deletion has to be done bottom up.
4.
Take all information of a customer who changes it's name out of the database
and
reenter it with the correct customer_id.
(Offcourse this has to be done in the correct order for the tables).
In all for of the above method's most changes have (or at least is prevered)
to be done
on one customer at the time. (Then the load and impact can be spread over a
larger
period so that normal performance is less hindered or interrupted).
The number of customer changing their names can not be that huge I suppose.
Method 2 is probably the easiest to implement, but switching of a constraint
of a table,
gives a large resource lock on that table. But then again the change of one
customer
can probably be done in a few seconds.
Good luck,
Please keep us informed you how you are going to tackle your problem.
And how it works when implemented.
Greetings,
Ben Brugman
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>|||Hello - just wanted to say thanks for all the helpful responses. I documented
everything and forwarded it up the ladder.
Alot of contracts have been signed, so we have to move ahead the best we can
and keep working towards a solution.
The comparison to rebuilding the car engine was great.
Thanks
Dave
"ben brugman" wrote:
> As the others say, the total rebuild of 'everything' is probably a bad idee.
> (What is this goes wrong ?).
> As the others say, can't the design be 'mended' so the problem does not
> occure.
> For the following answers I am going to suppose the a redesign is not
> feasable within your situation. And that customer names still change and
> that they are part of primairy key's and relations.
> Solutions could be :
> 1.
> Cascading.
> But then all relations have to be present and in the correct 'Order', if the
> relations
> are not like a tree, cascading might not be possible. (For example if there
> are double parallel relations, or self referencing tables).
> 2.
> Switch of the constraints of the concerned tables.
> Alter the customer id's.
> Switch on the constraints of the concerned tables.
> 3.
> For all tables which are 'parent' tables to other tables. Make a new entry
> (duplicate) with
> the 'new' customer name.
> Update all the children of that customer.
> Then delete all rows which have been duplicated.
> This method has to be implemented very carefully, the order of tables has to
> be correct.
> There should be no unique constraint (not involving the customer_id) in the
> tables.
> Duplication has to be done top down. Deletion has to be done bottom up.
> 4.
> Take all information of a customer who changes it's name out of the database
> and
> reenter it with the correct customer_id.
> (Offcourse this has to be done in the correct order for the tables).
> In all for of the above method's most changes have (or at least is prevered)
> to be done
> on one customer at the time. (Then the load and impact can be spread over a
> larger
> period so that normal performance is less hindered or interrupted).
> The number of customer changing their names can not be that huge I suppose.
> Method 2 is probably the easiest to implement, but switching of a constraint
> of a table,
> gives a large resource lock on that table. But then again the change of one
> customer
> can probably be done in a few seconds.
> Good luck,
> Please keep us informed you how you are going to tackle your problem.
> And how it works when implemented.
> Greetings,
> Ben Brugman
>
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> > The company I work for is planning to use a new software package for its
> > internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> > histories, etc.
> >
> > The software uses the prmary key to contain the customer identification #.
> >
> > Our business needs requires us to change the customer identification # for
> > certain customers. This would mean changing the value of the primary key.
> >
> > The consultants are planning to have a routine that drops all of the
> indexes
> > and constraints, change the values then rebuild all of the tables.
> >
> > This seems like a dangerous idea to me however it is not my decision. I
> was
> > hoping to get opinions or links to documentation warning against this -
> or -
> > is there nothing to worry about?
> >
> > Any documentation, details, or advice would be appreciated. If there is a
> > more appropriate forum for this type of question, please let me know.
> >
> > Thanks in advance
> > Dave
> >
>
>|||First and foremost, the definition of a database is a physical represention
of the logical relationships between data. The primary reason for the
creation of the Relational Theory of Data was to put into a mathematical
framework those ideas essential for the manipulation, integrety, and
consistency of data models.
Without data integrity, you do not have a database nor the garauntee of
logical consistency.
The modification of parent information is well formulated with the
requirement that all parent data is replicated with the updated information
and then all dependent child data is migrated to this new information, only
then is the original parent data removed. All the while, leaving the
Declaritive Referential Integrity constraints online. If they are not,
there is no garauntee of data consistency and, thus, no garuantee of a
consistent database.
Now, I said DRI, this does not include procedural referential integrity:
triggers, stored procedures, and/or API processes. All of these can be
disabled with simple statements without having to drop and recreate them,
nor should the compromise the database consistency or integrity if we are
only relabeling currently consistent data. However, as others have pointed
out, the system would need to be offline while these operations occurred.
As far as documentation and litereature, check out:
E. F. Codd's original works on the Fundemental Theory of Data
A Relational Model of Data for Large Shared Databanks,
http://www.acm.org/classics/nov95/toc.html,
C. J. Date's An Introduction to Database Systems,
http://www.dbdebunk.com/books.html,
and/or Fabian Pascal's Practical Issues in Database Management.
Sincerely,
Anthony Thomas
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
The company I work for is planning to use a new software package for its
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
Dave
Dropping Permissions
Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.
look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>
|||Thanks...........
[vbcol=seagreen]
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
DELETE
>.
>
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.
look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>
|||Thanks...........
[vbcol=seagreen]
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
DELETE
>.
>
Dropping Permissions
Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>> Does anyone know what is the statement to drop the
>> following permissions in QA (Not DENY).
>> There are too many of them to drop from the E.M.
>>
>> GRANT REFERENCES , SELECT , UPDATE , INSERT ,
DELETE
>> ON [dbo].[mytable] TO [public]
>> GO
>> Thanks.
>.
>
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>> Does anyone know what is the statement to drop the
>> following permissions in QA (Not DENY).
>> There are too many of them to drop from the E.M.
>>
>> GRANT REFERENCES , SELECT , UPDATE , INSERT ,
DELETE
>> ON [dbo].[mytable] TO [public]
>> GO
>> Thanks.
>.
>
Dropping Permissions
Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>
DELETE[vbcol=seagreen]
>.
>
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>
DELETE[vbcol=seagreen]
>.
>
dropping performance on SQL server
I am in no way familiar with SQL server (DB2/ORACLE background), but since we have a bought application running on this RDBMS and performance is dropping off, I would like to do a little investigation into this new field.
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?
Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?Query Analyzer is one tool.Sql Server profiler is the other tool.Go to Start-->Programs-->Microsoft Sql Server--Profiler(Windows) and run it from there.You need to be sa in order to run it.Create a new trace on the server you want to run this,save it to a file,let it run for some time(may be 2-3 hours during the peak use).Stop the profiler after the desired time and Click on the Tools-->Index Tuning Wizard and that will guide you to the Index Suggestion.
There are so many other ways to tune a sql server things like update statistics,which you should be doing quite often,Index rebuilding or Defrag(I prefer defrag as it's an online operation),Recompiling your sp and you can also use the Performance monitor to monitor the box running the sql server.
There is a tool avaliable from netiq.com ,called sqlcheck which helps you in monitoring the sql box.
Hope it helps.|||So many places to start from here.
1. I think the nearest equivalent tool to show you whether a reorg is needed is DBCC SHOWCONTIG (table_name). It will show you how fragmented a particular table is. A badly fragmented table can be re-org'd using DBCC DBREINDEX (table_name). Use caution, this can be a resource pig. Check SQL BOL.
2. Check your hardware and especially your disk setup. Ideally you want RAID 1&0 for your data and your log file partitions. You want your log files on a separate partition from your data. If you can afford it, get your tempdb off onto it's own partition (this is a luxury).
3. In general, the Index Tuning Wizard mentioned above works quite well. It has stood me well in the past when I overlooked an index.
4. Try to have one clustered index per table (the limit is one, but try to have one).
5. Make sure SQL is running on a dedicated box (not concurrent with other services like IIS).
6. Boost priority to SQL Server (only if it's on a dedicated box). This will have only marginal effect.
7. If possible, SQL seems to perform very well with multiple processors. If you're running it on a single processor box, consider an upgrade.
8. Look at your memory and be sure you have enough. It's cheap and it may help.
But most of the time your problems will lie with poorly tuned queries or indices which are notably absent.
There is much more to be said, of course, but those should do for starters.
HTH,
Hugh Scott
Originally posted by blom0344
I am in no way familiar with SQL server (DB2/ORACLE background), but since we have a bought application running on this RDBMS and performance is dropping off, I would like to do a little investigation into this new field.
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?
Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?sql
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?
Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?Query Analyzer is one tool.Sql Server profiler is the other tool.Go to Start-->Programs-->Microsoft Sql Server--Profiler(Windows) and run it from there.You need to be sa in order to run it.Create a new trace on the server you want to run this,save it to a file,let it run for some time(may be 2-3 hours during the peak use).Stop the profiler after the desired time and Click on the Tools-->Index Tuning Wizard and that will guide you to the Index Suggestion.
There are so many other ways to tune a sql server things like update statistics,which you should be doing quite often,Index rebuilding or Defrag(I prefer defrag as it's an online operation),Recompiling your sp and you can also use the Performance monitor to monitor the box running the sql server.
There is a tool avaliable from netiq.com ,called sqlcheck which helps you in monitoring the sql box.
Hope it helps.|||So many places to start from here.
1. I think the nearest equivalent tool to show you whether a reorg is needed is DBCC SHOWCONTIG (table_name). It will show you how fragmented a particular table is. A badly fragmented table can be re-org'd using DBCC DBREINDEX (table_name). Use caution, this can be a resource pig. Check SQL BOL.
2. Check your hardware and especially your disk setup. Ideally you want RAID 1&0 for your data and your log file partitions. You want your log files on a separate partition from your data. If you can afford it, get your tempdb off onto it's own partition (this is a luxury).
3. In general, the Index Tuning Wizard mentioned above works quite well. It has stood me well in the past when I overlooked an index.
4. Try to have one clustered index per table (the limit is one, but try to have one).
5. Make sure SQL is running on a dedicated box (not concurrent with other services like IIS).
6. Boost priority to SQL Server (only if it's on a dedicated box). This will have only marginal effect.
7. If possible, SQL seems to perform very well with multiple processors. If you're running it on a single processor box, consider an upgrade.
8. Look at your memory and be sure you have enough. It's cheap and it may help.
But most of the time your problems will lie with poorly tuned queries or indices which are notably absent.
There is much more to be said, of course, but those should do for starters.
HTH,
Hugh Scott
Originally posted by blom0344
I am in no way familiar with SQL server (DB2/ORACLE background), but since we have a bought application running on this RDBMS and performance is dropping off, I would like to do a little investigation into this new field.
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?
Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?sql
Dropping Northwind and Pubs
Is it okay to drop northwind and pubs database? Are there
any problems with dropping them? Thanks.Nope. Most people drop them from production environments.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Barb" <anonymous@.discussions.microsoft.com> wrote in message
news:1528801c415ac$3b8c5fb0$a401280a@.phx
.gbl...
Is it okay to drop northwind and pubs database? Are there
any problems with dropping them? Thanks.|||No problems with doing this...a lot of people drop the
databases on production servers. If you ever need to install
them again, the instnwind.sql and instpubs.sql scripts in
SQL Servers install directory will create the databases.
-Sue
On Mon, 29 Mar 2004 08:38:15 -0800, "Barb"
<anonymous@.discussions.microsoft.com> wrote:
>Is it okay to drop northwind and pubs database? Are there
>any problems with dropping them? Thanks.|||Thank You.
>--Original Message--
>Nope. Most people drop them from production environments.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Barb" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1528801c415ac$3b8c5fb0$a401280a@.phx
.gbl...
>Is it okay to drop northwind and pubs database? Are there
>any problems with dropping them? Thanks.
>|||Thank You.
>--Original Message--
>No problems with doing this...a lot of people drop the
>databases on production servers. If you ever need to
install
>them again, the instnwind.sql and instpubs.sql scripts in
>SQL Servers install directory will create the databases.
>-Sue
>On Mon, 29 Mar 2004 08:38:15 -0800, "Barb"
><anonymous@.discussions.microsoft.com> wrote:
>
there
>.
>
any problems with dropping them? Thanks.Nope. Most people drop them from production environments.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Barb" <anonymous@.discussions.microsoft.com> wrote in message
news:1528801c415ac$3b8c5fb0$a401280a@.phx
.gbl...
Is it okay to drop northwind and pubs database? Are there
any problems with dropping them? Thanks.|||No problems with doing this...a lot of people drop the
databases on production servers. If you ever need to install
them again, the instnwind.sql and instpubs.sql scripts in
SQL Servers install directory will create the databases.
-Sue
On Mon, 29 Mar 2004 08:38:15 -0800, "Barb"
<anonymous@.discussions.microsoft.com> wrote:
>Is it okay to drop northwind and pubs database? Are there
>any problems with dropping them? Thanks.|||Thank You.
>--Original Message--
>Nope. Most people drop them from production environments.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Barb" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1528801c415ac$3b8c5fb0$a401280a@.phx
.gbl...
>Is it okay to drop northwind and pubs database? Are there
>any problems with dropping them? Thanks.
>|||Thank You.
>--Original Message--
>No problems with doing this...a lot of people drop the
>databases on production servers. If you ever need to
install
>them again, the instnwind.sql and instpubs.sql scripts in
>SQL Servers install directory will create the databases.
>-Sue
>On Mon, 29 Mar 2004 08:38:15 -0800, "Barb"
><anonymous@.discussions.microsoft.com> wrote:
>
there
>.
>
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
Dropping Loading/Suspect database
Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
Thanks
Sohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks
|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECTmode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>
|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>
sql
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
Thanks
Sohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks
|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECTmode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>
|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>
sql
Dropping Loading/Suspect database
Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> > Hi:
> >
> > I restored a database from the backup. It is showing Loading/Suspect in
> > enterprise manager. How do I drop this database and try again. What
> > could
> > have caused this? Please let me know.
> >
> > Thanks
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>> Sohail,
>> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
>> a
>> test box, if so and the former doesn't work, you might try KILLing the
>> process performing the RESTORE (if available).
>> HTH
>> Jerry
>> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
>> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>> > Hi:
>> >
>> > I restored a database from the backup. It is showing Loading/Suspect
>> > in
>> > enterprise manager. How do I drop this database and try again. What
>> > could
>> > have caused this? Please let me know.
>> >
>> > Thanks
>>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> > Actually I do not have access to the clients machine. Will the mode field
> > in
> > the following query tell me the spid.
> >
> > SELECT mode, status, status2
> > FROM master..sysdatabases
> > WHERE name = 'XYZ'
> >
> > Thanks
> >
> > "Jerry Spivey" wrote:
> >
> >> Sohail,
> >>
> >> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
> >> a
> >> test box, if so and the former doesn't work, you might try KILLing the
> >> process performing the RESTORE (if available).
> >>
> >> HTH
> >>
> >> Jerry
> >> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> >> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> >> > Hi:
> >> >
> >> > I restored a database from the backup. It is showing Loading/Suspect
> >> > in
> >> > enterprise manager. How do I drop this database and try again. What
> >> > could
> >> > have caused this? Please let me know.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> > Hi:
> >
> > I restored a database from the backup. It is showing Loading/Suspect in
> > enterprise manager. How do I drop this database and try again. What
> > could
> > have caused this? Please let me know.
> >
> > Thanks
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>> Sohail,
>> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
>> a
>> test box, if so and the former doesn't work, you might try KILLing the
>> process performing the RESTORE (if available).
>> HTH
>> Jerry
>> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
>> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>> > Hi:
>> >
>> > I restored a database from the backup. It is showing Loading/Suspect
>> > in
>> > enterprise manager. How do I drop this database and try again. What
>> > could
>> > have caused this? Please let me know.
>> >
>> > Thanks
>>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> > Actually I do not have access to the clients machine. Will the mode field
> > in
> > the following query tell me the spid.
> >
> > SELECT mode, status, status2
> > FROM master..sysdatabases
> > WHERE name = 'XYZ'
> >
> > Thanks
> >
> > "Jerry Spivey" wrote:
> >
> >> Sohail,
> >>
> >> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
> >> a
> >> test box, if so and the former doesn't work, you might try KILLing the
> >> process performing the RESTORE (if available).
> >>
> >> HTH
> >>
> >> Jerry
> >> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> >> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> >> > Hi:
> >> >
> >> > I restored a database from the backup. It is showing Loading/Suspect
> >> > in
> >> > enterprise manager. How do I drop this database and try again. What
> >> > could
> >> > have caused this? Please let me know.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
Dropping Loading/Suspect database
Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field i
n
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field i
n
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>
Dropping labels on a report - updated.
Is there anyway to drag the fields on to a report and have it create labels
for those fields without having to create each of them individually and
without using the table or matrix?
DavidYou mean in a list control? No, this is not supported. With the nature of
lists, it would be pretty tough to tell where you wanted the labels anyway.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"CapitalEMR" <CapHS@.hvif.com> wrote in message
news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Is there anyway to drag the fields on to a report and have it create
> labels
> for those fields without having to create each of them individually and
> without using the table or matrix?
> David
>|||No - not on a list control. Just on the report page.
Drag and drop field and label - just like VB6 used to do. I would love to
worry about where I wanted the labels instead of creating them by hand and
then placing them.
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:umOy5HjVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> You mean in a list control? No, this is not supported. With the nature of
> lists, it would be pretty tough to tell where you wanted the labels
> anyway.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "CapitalEMR" <CapHS@.hvif.com> wrote in message
> news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
>> Is there anyway to drag the fields on to a report and have it create
>> labels
>> for those fields without having to create each of them individually and
>> without using the table or matrix?
>> David
>>
>
for those fields without having to create each of them individually and
without using the table or matrix?
DavidYou mean in a list control? No, this is not supported. With the nature of
lists, it would be pretty tough to tell where you wanted the labels anyway.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"CapitalEMR" <CapHS@.hvif.com> wrote in message
news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Is there anyway to drag the fields on to a report and have it create
> labels
> for those fields without having to create each of them individually and
> without using the table or matrix?
> David
>|||No - not on a list control. Just on the report page.
Drag and drop field and label - just like VB6 used to do. I would love to
worry about where I wanted the labels instead of creating them by hand and
then placing them.
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:umOy5HjVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> You mean in a list control? No, this is not supported. With the nature of
> lists, it would be pretty tough to tell where you wanted the labels
> anyway.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "CapitalEMR" <CapHS@.hvif.com> wrote in message
> news:%23zmdg5MVFHA.544@.TK2MSFTNGP15.phx.gbl...
>> Is there anyway to drag the fields on to a report and have it create
>> labels
>> for those fields without having to create each of them individually and
>> without using the table or matrix?
>> David
>>
>
Subscribe to:
Posts (Atom)