Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
Can you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
sql

Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we wan
t
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When do i make the new Column the Primary key and whats the code to do that
?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_I
d
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id reference
s
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above fo
r
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1

Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1

Replacing a NULL in a view

I have created a view over a table that extracts production
information. The undelying table contains rows not just for product
code changed but also for speed changes.
The folloing view fillers out the speed changes and seems to work bar
one small problem. The EndTime for the currently running product will
be NULL as it is still running. This is a proble as I miss the current
production information. Is there a way to make the EndTime NULL in the
MAX function if the table value is NULL or could NULL be replaced with
the current Datetime
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
Many thanks
JimYou can use ISNULL to provide a different value for one that is NULL.
for example
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, ISNULL(MAX(EndTime), GETDATE()) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230512.64cddbc6@.posting.google.com...
> I have created a view over a table that extracts production
> information. The undelying table contains rows not just for product
> code changed but also for speed changes.
> The folloing view fillers out the speed changes and seems to work bar
> one small problem. The EndTime for the currently running product will
> be NULL as it is still running. This is a proble as I miss the current
> production information. Is there a way to make the EndTime NULL in the
> MAX function if the table value is NULL or could NULL be replaced with
> the current Datetime
> SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
> HoursRun
> FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime
> FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode) ProdLog
> ORDER BY StartTime, Unit, Line, ProductCode
> Many thanks
> Jim

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to code in my application. I want that when an Insert Query is executed, a BEFORE/AFTER TRIGGER should be executed to replace the NULL values coming from Insert QUery to "-".

I also want to know whether to use a Trigger or a Function/Stored Procedure.

I'd simply add the logic to your T-SQL insert statement with the ISNULL function. If its wrapped in a stored procedure it may look like something like the following:-

Code Snippet

CREATE PROC Test

@.Var1 CHAR(1),

@.Var2 CHAR(1)

AS

INSERT INTO YourTable (Var1, Var2)

VALUES ( ISNULL(@.Var1, '-'), ISNULL(@.Var2, '-') )

Using an INSTEAD OF trigger may be possible but that would seem a little over complicated for this.

HTH!

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>sql

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.
RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>

Replace DB Files while Offline ... Potential Problems?

I have created a program using SQL-DMO to replace an SQL Server 2000 database by setting it OFFLINE, replacing the .MDF and .LDF and then setting the database back ONLINE.

SQL Server does permit replacing the files while offline. And when the database is back online, everything seems to have worked perfectly with the new database files.

My questions is ... What potential problems (if any) exist with this process replacing a database?

Other methods I have considered:
- Detach, replace DB files, re-attach
- Delete old database, attach-using-new-DB-files

Thanks for your input!!The problem you've followed to detach, rename and re-attach is fine. As the SQL server doesn't remember the previous database names and when you detach the name &* details will be cleared from sysdatabases table.

By any reason if there is a mismatch of filenames with .MDF and .LDF then you will have issue of re-attachign the database.

BTW can you explain why you need to this on regular basis?|||Thx for the reply,

Because I am just updating the same SQL database with 2 new database files (.mdf and .ldf) with the same file names, I figure setting the database offline in order to do the 'file replacement' would be more efficient than detaching the database.

You mentioned that the process of (Detach)-(Rename)-(ReAttach) is fine.

However, I am using the process of (Set Offline)-(Replace .mdf/.ldf)-(Set Online). Would there be any problems when this method is used?

This 'database replacement' process is needed because databases sometimes gets corrupted for various reasons, and it needs to be 'sent in' to repair. The fixed database is sent back to the user and replaces their corrupted database.

Thanks again.|||more efficient? the dettach and reattch sp's are real fast.|||As long as you carryon such rename operations during SQL offline then you will not have any issues, otherwise SQL will flag inconsistency with mismatche of files.

Any reason and investigation on why the database is corrupted?
If you can fix the problem then you can reduce round-robin method. ;-)|||The 'data corruption' problems I had previously mentioned, are only at the data-level (caused by errors in the client program) and not at the database-level. Sorry for the confusion.

And when I say 'more efficient', I had some quicker response times setting a database OFFLINE/ONLINE then DETACH/RE-ATTACH. Also in code (SQL-DMO), setting a detabase offline/online is as simple as setting a boolean flag to true/false, whereas the detach/attach method requires full path names to the .MDF and .LDF.

Thx again for your responses.|||User mappings can be lost in this sort of operation. Does the client have to re-add those?

Hopefully this is not terribly important data. Especially if the database has to be 'sent in' for repairs. Sorry, but it just rubs me the wrong way.|||Thanks for the excellent reminder... a custom database USER and ROLE is used. All 'repairs' are applied to on the same database received (which will also be the same one sent back to the client). I will definately keep this in mind when testing.

And yes, it rubs me the wrong way as well... but that is another story :)
... and I plan to add backups and restores to avoid this whole process of 'sending and receiving' of an entire database.

Thx again!

replacate Inserts only

Running Win 2k & SQL 2k
How do I create or modify an already created publication
to only replacate Inserts?
Larry,
assuming you are using transactional replication, usually the way to
accomplish this is to change the command called to NONE on the article
properties. However this will force reinitialization of existing
subscriptions. In order to avoid reinitialization, you could modify the
stored procedure called - comment out the code in the update and delete
stored procedures.
HTH,
Paul Ibison
|||So, if the publications have not been created, I can
create them and change the command called to NONE on the
article properties for the delete and update, and this
should work...RIGHT?
Thanks,
Larry...
|||Larry - that's right
Paul
|||thanks, you da man!!!

Repeating footer data from a field

I have a data field called RunStatus that I place into a hidden textbox on my
report. I created a textbox in the footer and set the expression to
=ReportItems("RunStatusTextBox").Value. The footer does not repeat this
value on all pages. I am assuming it is because the hidden textbox is on the
first page only. How do I get my run status to show in the footer for all
pages?On Apr 29, 7:16 pm, Bruce Parker <bpar...@.nospam.nospam> wrote:
> I have a data field called RunStatus that I place into a hidden textbox on my
> report. I created a textbox in the footer and set the expression to
> =ReportItems("RunStatusTextBox").Value. The footer does not repeat this
> value on all pages. I am assuming it is because the hidden textbox is on the
> first page only. How do I get my run status to show in the footer for all
> pages?
You could try creating a hidden parameter and add it in the report
footer in a textbox control. Something like this should work:
=Parameters!RunStatus.Value
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Bruce,
As for the TextBox in report footer, though you can assign it the value
from another reportItem in report body, however, sincce that reportItem in
body area won't repeat as the ones in report footer, you'll find that the
value got from the reportItem is identical to where it displays in the
report body. For your scenario, I think you can consider Enrique's
suggestion about define a report parameter and dirctly reference that
parameter in footer. Is there any particular code logic for caculating the
value for this repeated textbox in footer?
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||in vs2005
right click table select property.
click repeat header or repeat footer in the property.
"Bruce Parker" <bparker@.nospam.nospam> дÈëÏûÏ¢ÐÂÎÅ:C5FA9C95-A781-41B4-8F47-51F531F96AD5@.microsoft.com...
>I have a data field called RunStatus that I place into a hidden textbox on
>my
> report. I created a textbox in the footer and set the expression to
> =ReportItems("RunStatusTextBox").Value. The footer does not repeat this
> value on all pages. I am assuming it is because the hidden textbox is on
> the
> first page only. How do I get my run status to show in the footer for all
> pages?|||That did the trick. Thanks guys.
"Steven Cheng[MSFT]" wrote:
> Hi Bruce,
> As for the TextBox in report footer, though you can assign it the value
> from another reportItem in report body, however, sincce that reportItem in
> body area won't repeat as the ones in report footer, you'll find that the
> value got from the reportItem is identical to where it displays in the
> report body. For your scenario, I think you can consider Enrique's
> suggestion about define a report parameter and dirctly reference that
> parameter in footer. Is there any particular code logic for caculating the
> value for this repeated textbox in footer?
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Repeat on Every Page - For Headers

Using SSRS 2005:
I created a text box in my report header, which references a text box in my
report body: =ReportItems!textbox88.Value
In my report body, hidden textbox88 contains my data:
=Format(Parameters!StartDate.Value, "dd MMM yy HH:mm:ss")
This all works great, except for the fact that textbox88 only exists on the
first page, so the header only has a value on the first page.
Here's the question: How do I make textbox88 Repeat on Every Page, which
let's me see a value in the header on Every Page?
Thanks,
--
RandyYou could copy that box into your page header instead of your report
headrer.
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:6BAB5E73-F556-4778-8B89-766C99075F90@.microsoft.com...
> Using SSRS 2005:
> I created a text box in my report header, which references a text box in
> my
> report body: =ReportItems!textbox88.Value
> In my report body, hidden textbox88 contains my data:
> =Format(Parameters!StartDate.Value, "dd MMM yy HH:mm:ss")
> This all works great, except for the fact that textbox88 only exists on
> the
> first page, so the header only has a value on the first page.
> Here's the question: How do I make textbox88 Repeat on Every Page, which
> let's me see a value in the header on Every Page?
> Thanks,
> --
> Randy|||I'm working with SSRS 2005 using Visual Studio 2005. I don't see what the
difference is between a page header and report header. I created the header
by viewing the report in the Layout tab, right-clicking off to the side of
the page, and selecting "Page Header."
There are many posts in the archives here that describe why you can't put
fields directly into headers. Fields must be put into something in the body
and then the header can reference that something to allow the data in appear
in the header.
I still haven't been able to spot anything that allows a field in the body
to repeat on every page, so that the data in the field appears in the header
on every page.
--
Randy
"Ben Watts" wrote:
> You could copy that box into your page header instead of your report
> headrer.
> "randy1200" <randy1200@.newsgroups.nospam> wrote in message
> news:6BAB5E73-F556-4778-8B89-766C99075F90@.microsoft.com...
> > Using SSRS 2005:
> >
> > I created a text box in my report header, which references a text box in
> > my
> > report body: =ReportItems!textbox88.Value
> >
> > In my report body, hidden textbox88 contains my data:
> > =Format(Parameters!StartDate.Value, "dd MMM yy HH:mm:ss")
> >
> > This all works great, except for the fact that textbox88 only exists on
> > the
> > first page, so the header only has a value on the first page.
> >
> > Here's the question: How do I make textbox88 Repeat on Every Page, which
> > let's me see a value in the header on Every Page?
> >
> > Thanks,
> > --
> > Randy
>
>|||I see that textbox88 takes its value from a report parameter rather than a
dataset, so there is no need for the page header text box to take its value
from textbox88. It can be taken directly from the parameter!The expression
for your page header textbox can be:
=Format(Parameters!StartDate.Value, "dd MMM yy HH:mm:ss")
HTH,
magendo_man
"randy1200" wrote:
> Using SSRS 2005:
> I created a text box in my report header, which references a text box in my
> report body: =ReportItems!textbox88.Value
> In my report body, hidden textbox88 contains my data:
> =Format(Parameters!StartDate.Value, "dd MMM yy HH:mm:ss")
> This all works great, except for the fact that textbox88 only exists on the
> first page, so the header only has a value on the first page.
> Here's the question: How do I make textbox88 Repeat on Every Page, which
> let's me see a value in the header on Every Page?
> Thanks,
> --
> Randy

Reorder packages in BIDS

How or is there a way to reorder the packages in BIDS? Right now they are ordered by when they are created with the newest ones on the bottom. I'd like to organize these in more of a logical order but am unsure of how to do so.

Any advise?

Thanks,

Beac

I have downloaded/used this without a problem; it only gives you the ability of sorting the items alphabetically:

http://www.sqldbatips.com/showarticle.asp?ID=78

|||Thanks for the replay Rafael. I'll give it a try.

Rendering to PDF format

Hi everyone!
I have a problem with rendering report to PDF format. I created report using
my own report designer, developed for our company commercial product. There
is no problem to render report to some other format like HTML or IMAGE. But
when I render report to PDF format some of information simply disappears from
result report. There are empty spaces instead of printed values. Are there
any special definitions I have to make to each object, and what are the
different between, for example, HTML and PDF format from SQL reporting
services point of view.
Thanks!
Igor.The same thing is happening to me when I render a report with an embedded
image to pdf. I can either open or save the pdf, but upon reviewing the file
pieces of the report simply disappear. When I click to print, it is
completely blank.
"Igor" wrote:
> Hi everyone!
> I have a problem with rendering report to PDF format. I created report using
> my own report designer, developed for our company commercial product. There
> is no problem to render report to some other format like HTML or IMAGE. But
> when I render report to PDF format some of information simply disappears from
> result report. There are empty spaces instead of printed values. Are there
> any special definitions I have to make to each object, and what are the
> different between, for example, HTML and PDF format from SQL reporting
> services point of view.
> Thanks!
> Igor.

Rendering Report directly in the MS Excel

Hi,
We have multiple reports created in the SRS 2005. What is the best approach
to directly exporting (rendering) the created reports in Excel without using
any report viewer control or report manager for exporting on click of the
button.
Thanx in advance.You can call the report using the url access, as:
http://<server>/ReportServer?/<report
path>&rs:Command=Render&rs:Format=EXCEL
Jeronimo Vogt
"Parimal" <Parimal@.discussions.microsoft.com> wrote in message
news:3761A0CF-EFA4-4266-8E15-4832D6AF3814@.microsoft.com...
> Hi,
> We have multiple reports created in the SRS 2005. What is the best
approach
> to directly exporting (rendering) the created reports in Excel without
using
> any report viewer control or report manager for exporting on click of the
> button.
> Thanx in advance.|||The new MS Reports tool (an addition to Excel 2003) is worth looking at - it
might meet your requirements. I think it is supposed to be released sometime
in Dec 2005 - anyone has any confirmed information on the release date?
For details on MS Reports:
http://download.microsoft.com/download/d/6/f/d6f66e9b-1b72-4192-b4ae-cecbad1a4903/MSReportsTCS.doc
-K
"Jeronimo Vogt" wrote:
> You can call the report using the url access, as:
> http://<server>/ReportServer?/<report
> path>&rs:Command=Render&rs:Format=EXCEL
> Jeronimo Vogt
> "Parimal" <Parimal@.discussions.microsoft.com> wrote in message
> news:3761A0CF-EFA4-4266-8E15-4832D6AF3814@.microsoft.com...
> > Hi,
> > We have multiple reports created in the SRS 2005. What is the best
> approach
> > to directly exporting (rendering) the created reports in Excel without
> using
> > any report viewer control or report manager for exporting on click of the
> > button.
> > Thanx in advance.
>
>

Rendering Report

I have created a page in Reporting Services with one row of data, but when the data is increased to 5 rows my pages doesnt render that...Any suggestionsYou need to use a dataregion (List, Table or Matrix) to display the data. Try adding a table with detail rows, each row from your dataset would show up as a detail row in the table.|||Thanks it worked with a list.

Rendering PDF missing pages

i had rendered a report and created a excel file and each page of the 2-page
report generate a sheet in the workbook, which is great now i rendered the
same report in PDF, the PDF only generates one page instead of the expected 2
pages. What am I doing wrong?
Please help.
FYI. Using C# in a window form.Hi Nkem
I hade the same problem, but solved with service pack1 or SP2
"Nkem" wrote:
> i had rendered a report and created a excel file and each page of the 2-page
> report generate a sheet in the workbook, which is great now i rendered the
> same report in PDF, the PDF only generates one page instead of the expected 2
> pages. What am I doing wrong?
> Please help.
> FYI. Using C# in a window form.