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

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

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fi
t
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>|||This did not yet resolve my issue.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>sql

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fit
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:
> JKKUS wrote:
> > When working in DTS, specifically in the Transform Data Properties window,
> > when I click on the drop-down in the Table/View field the path will only
> > extend for the width of the field, even if the path name is too long to fit
> > in that field. As a result, I often can't see the full path displayed.
> >
> > Thanks.
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>|||This did not yet resolve my issue.
"James Wilson" wrote:
> JKKUS wrote:
> > When working in DTS, specifically in the Transform Data Properties window,
> > when I click on the drop-down in the Table/View field the path will only
> > extend for the width of the field, even if the path name is too long to fit
> > in that field. As a result, I often can't see the full path displayed.
> >
> > Thanks.
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.
JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fit
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James
|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>
|||This did not yet resolve my issue.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>

Drop The Create Table

I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Drop The Create Table

I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Drop The Create Table

I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.