My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete
it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||Hi Amir
Use Row level locks before inserting a row into the Table.
best regards
Chandra
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
the
> same time - which causes an error, since the SET IDENTITY_INERT ON
statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust yo
ur
> queries to include only Archive = 'Y'. The way you're doing it now, you'r
e
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before
).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
David Portas
SQL Server MVP
--
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Friday, March 23, 2012
Issuing multiple calls to SET IDENTITY_INSERT ON
My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> > When I delete a row, I first copy it to another table and afterwards
> > delete it
> > from the original table.
> > When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> > in
> > order to avoid getting a new value for the identity column.
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>> > When I delete a row, I first copy it to another table and afterwards
>> > delete it
>> > from the original table.
>> > When I want to restore the row, I use the SET IDENTITY_INSERT
>> > statement,
>> > in
>> > order to avoid getting a new value for the identity column.
>> This makes no sense to me. Why not just assign a new IDENTITY value,
>> since
>> you apparently don't have dependent rows referencing the data in the
> > original table?
>> IDENTITY should be used only as an artificial key - if you care about
>> what
>> the value is then don't use IDENTITY.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
> queries to include only Archive = 'Y'. The way you're doing it now, you're
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
> >
> >
> > "David Portas" wrote:
> >
> >> > When I delete a row, I first copy it to another table and afterwards
> >> > delete it
> >> > from the original table.
> >> > When I want to restore the row, I use the SET IDENTITY_INSERT
> >> > statement,
> >> > in
> >> > order to avoid getting a new value for the identity column.
> >>
> >> This makes no sense to me. Why not just assign a new IDENTITY value,
> >> since
> >> you apparently don't have dependent rows referencing the data in the
> > > original table?
> >>
> >> IDENTITY should be used only as an artificial key - if you care about
> >> what
> >> the value is then don't use IDENTITY.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >
> > Well, I do have related records, thus I can't insert the row with a new
> > IDENTITY if I want to keep the relations.
> > It goes like this: I have a table full of Customers rows and another table
> > with CustomersReports rows (which is a child table of the Customers
> > table).
> > When I delete a customer, I don't really delete it, but rather move it to
> > an
> > archive table along with it's related CustomersReports child rows.
> > In another scenario, I might want to restore the Customer row to it's
> > original table, and restore it's related CustomersReports rows as well.
> > If I will restore the customer by assigning it a new ID, I will be
> > compelled
> > to modify the foreign key in the child table as well.
> > I prefer to avoid it if possible.
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
--
David Portas
SQL Server MVP
--|||On Sun, 08 May 2005 11:25:15 -0700, Amir Shitrit wrote:
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to the
> archive, I move all of it's related child rows as well (as I explaind before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
Although I fully agree with Michael and David - this is just a bad idea,
as far as I can see, you could do it using application locks. Like this:
CREATE PROCEDURE NeverRunsInParallel
AS
DECLARE @.result int
EXEC @.result = sp_getapplock @.Resource = 'myLock', @.LockMode = 'Exclusive'
IF @.result => 0
BEGIN
-- Do your thing, secure in the fact that
-- no other connection will do it at the same
-- time.
END
EXEC sp_releaseapplock @.Resource = 'myLock1'
<<
HTH,
Andrés Taylor
When I delete a row, I first copy it to another table and afterwards delete it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> > When I delete a row, I first copy it to another table and afterwards
> > delete it
> > from the original table.
> > When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> > in
> > order to avoid getting a new value for the identity column.
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>> > When I delete a row, I first copy it to another table and afterwards
>> > delete it
>> > from the original table.
>> > When I want to restore the row, I use the SET IDENTITY_INSERT
>> > statement,
>> > in
>> > order to avoid getting a new value for the identity column.
>> This makes no sense to me. Why not just assign a new IDENTITY value,
>> since
>> you apparently don't have dependent rows referencing the data in the
> > original table?
>> IDENTITY should be used only as an artificial key - if you care about
>> what
>> the value is then don't use IDENTITY.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
> queries to include only Archive = 'Y'. The way you're doing it now, you're
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
> >
> >
> > "David Portas" wrote:
> >
> >> > When I delete a row, I first copy it to another table and afterwards
> >> > delete it
> >> > from the original table.
> >> > When I want to restore the row, I use the SET IDENTITY_INSERT
> >> > statement,
> >> > in
> >> > order to avoid getting a new value for the identity column.
> >>
> >> This makes no sense to me. Why not just assign a new IDENTITY value,
> >> since
> >> you apparently don't have dependent rows referencing the data in the
> > > original table?
> >>
> >> IDENTITY should be used only as an artificial key - if you care about
> >> what
> >> the value is then don't use IDENTITY.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >
> > Well, I do have related records, thus I can't insert the row with a new
> > IDENTITY if I want to keep the relations.
> > It goes like this: I have a table full of Customers rows and another table
> > with CustomersReports rows (which is a child table of the Customers
> > table).
> > When I delete a customer, I don't really delete it, but rather move it to
> > an
> > archive table along with it's related CustomersReports child rows.
> > In another scenario, I might want to restore the Customer row to it's
> > original table, and restore it's related CustomersReports rows as well.
> > If I will restore the customer by assigning it a new ID, I will be
> > compelled
> > to modify the foreign key in the child table as well.
> > I prefer to avoid it if possible.
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
--
David Portas
SQL Server MVP
--|||On Sun, 08 May 2005 11:25:15 -0700, Amir Shitrit wrote:
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to the
> archive, I move all of it's related child rows as well (as I explaind before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
Although I fully agree with Michael and David - this is just a bad idea,
as far as I can see, you could do it using application locks. Like this:
CREATE PROCEDURE NeverRunsInParallel
AS
DECLARE @.result int
EXEC @.result = sp_getapplock @.Resource = 'myLock', @.LockMode = 'Exclusive'
IF @.result => 0
BEGIN
-- Do your thing, secure in the fact that
-- no other connection will do it at the same
-- time.
END
EXEC sp_releaseapplock @.Resource = 'myLock1'
<<
HTH,
Andrés Taylor
ISSUES. . .
I'm a developer for a rich client application with a primary grid that should be refreshed when data is changed by other users. I loath to resort to some sort of polling. It would be really cool if there is a native way to raise an event on the client from a SQL Server trigger. RAISEERROR can send a message to the Connection's InfoMessage() event. But of course this will only send the message back to the user who made the change. Is there anyway in SQL Server to raise an error message on another process so that this ADO event can pick it up?
Other that that (hypotheticals follow)
in SQL 2000 ->
You could put a com+ object in DTS. Trigger on a data-change notifies the com+ object. COM+ object has list of registered clients that it notifies of the changes.
SQL 2005 it would be even easier with the integration of .NET
wouldn't that work?
Oh dont make me do this in VBSUX because VB, well vb simply sux - how did it become so successful?! I dont want to do it in VB.NET as I want to cater the lowest common denominator. And boy is VB the lowest denominator!
Simple to do in delphi! but give me an hour. . . I'll do it in VB
God delphi rox!|||Thanks for your help. I'm not suprised that there isn't anything. However I'm disapointed SQL Server 2005 doesn't have it. SQL Server has all the capabilities as a simply event conduit between it's clients. Kind of a waste then that some other messaging service is required for simple data related events to be passed around.
For an alternative I have an idea to referesh based on user activty. A few events will call a function and refresh if some criteria has been met. Eg the form is activated after five minutes.
|||
well trying to do it using vbsux further ingrained my disdain for the piece of sh|t that vb6 is. Its not hard to do in delphi. It is Actually rather simple.
About 20 lines of actual code you need to write. . .
Create an activeX exe (DBNotifier) that defines an object (NotifyClient) that implements the interface
INotifyClient
{
Notify()
}
with an event OnNotify
another singleton object (Manager) implements the interface -
IManager
{
void RegisterClient(INotifyClient client);
void UnRegisterClient(INotifyClient client);
void NotifyClients()
}
During the call to NotifyClients you need to synchronize access to a collection it contains that will hold references to the clients.
Finally an object that (NotifyAgent) implements
INotifyAgent
{
void Notify(long ID)
}
NotifyAgent calls the Managers.NotifyClients Method
In the GUI app, get a reference to the IManager Object, Instance An INotifyClient and register it. Define an OnNotifyEvent to do what ever you want.
Now in the database (I will use northwind for example) attach a trigger of this sort:
=============================
create trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'
EXEC @.hr = sp_OACreate 'DBNotifier.Agent', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=============================
Again. . . dont try in VBSUX, its not worth the effort use a real development environment. Why you might ask? The synchronization is impossible. VB is just a load of cr@.p (have they yet shot the guys who developed it? shoot their mothers too!!!)
Now. . . why is this not implemented natively? Well, because you aren't supposed to remain connected to the database. Its bad design!
Connect, get your data and get out!!!!
Connect, change your data and get out!!!!
Boy, I could complicate a wet dream!
Its not difficult at all, provided you use Delphi as you can't do it with VBSUX alone because VBSUX is a piece of cr@.p and cant create COM+ event objects. . . Have those guys been shot yet?
Total time of implementation 10 minutes depending on how many SQL objects you want sending notifications!!!
Three parts. . .
● Build, register and install a COM+ Event Object - SQLEvents (this is not hard but you cannot do it in VBSUX!!! Use Delphi!)
● Add triggers to your SQL objects that should initiate the SQLEvents
● Create an ActiveX Event Sink for handling the COM+ Events - SQLEventSink (This can be done in VBSUX!!!)
If you don't have delphi, go home - you suck!
[Part One - Looks like alot, but it takes all of 3 minutes!!!]
1. create a Delphi Active X library, Call it SQLEvents.
2. From the file menu - > Add >Other -> ActiveX -> Automation Object call it SQLEvent with Apartment Threading (no events)
3. From view Menu -> Type Library. . . In the the TypeLibrary editor, add a method to the ISQLEvent interface called Notify that takes a long parameter called SQLObject [see here]
4. Generate the code and call the generated pas file SQLEvents_impl.pas; you dont need to write ANY code!!!!!
5. From the Run Menu -> Register Active X Server (this also builds the DLL)
On the SQL Server machine:
6. Open Component Services and drill down to COM+ Applications. Right Click and select New -> Application - Next -> Empty Application Call it 'SQLEvents' and set Server Application as your activation type -> Applciation Identity Interactive User -> use the default application roles -> dont add any roles -> finish
7. Expand the SQLEvents Aplication folder to the Components folder and right click and select New -> Component -> Next -> Click Install New Event Class(es) and locate and open the SQLEvents.dll you built in step 5. -Next -> Finish
The event object is done. and the tree should look like this when expanded.
[Part 2]
8. Add triggers to the SQL Objects that need to send notifications -
NOTES: My build has a CLSID of "{8FD50E86-203D-4939-9CAE-0F2865C69465}" yours will be different! You can find it out by right clicking the Component installed in step 7 and selecting properties.
Also. this example uses northwind and will trigger events on Update Delete and Insert on the employees table:
=====================================================
CREATE trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'
EXEC @.hr = sp_OACreate '{8FD50E86-203D-4939-9CAE-0F2865C69465}', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=====================================================
Part 3 [you can use VB here, total time 2 minutes!!!]
9. Create a new VB ActiveX DLL Project, save it as SQLEventsSink, Rename Class1 to SQLEventsSink and save the file as SQLEventsSink.cls.
10. From the Projects menu Add a reference to the SQLEvents Lbrary you built in Step 5 above.
11. Add this code to SQLEventsSink:
=================================
Option Explicit
Implements SQLEvents.SQLEvent
Private mSQLObjectID As Long 'local copy
Public Event OnNotify()
Public Property Let SQLObjectID(ByVal vData As Long)
mSQLObjectID = vData
End Property
Public Property Get SQLObjectID() As Long
SQLObjectID = mSQLObjectID
End Property
Private Sub SQLEvent_Notify(ByVal SQLObjectID As Long)
If SQLObjectID = Me.SQLObjectID Then RaiseEvent OnNotify
End Sub
=================================
12. Build the Library and you are done. . . you have an event system!!!
Here's how to use ->
1. Create A New VB Application Project Call it TestApp
2. From the Project Menu, add references to MS Active Data Object 2.6 (minimum), COM+ Admin Library and the SQLEventSink library you built in step 12 above. In the Toolbox add the MS DataGrid 6.0
3. Add a Module, name it globals and add the following code
==============================================
Option Explicit
Public Const CONNECTIONSTRING = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=.\SQL2000"
' NOTE: CHANGE THE FOLLOWING TO REFLECT THE CLSID
' OF THE SQLEvents.SQLEvent OBJECT YOU PREVIOUSLY
' BUILT AND INSTALLED IN COM+
Public Const CLSID_SQLEVENT = "{8FD50E86-203D-4939-9CAE-0F2865C69465}"
==============================================
4. Add a Module, name it ComUtils and add the following code (funny, in delphi it only takes about 6 lines of code to accomplish the same!!! Have I mentioned that VBSUX, sucks?!?)
==============================================
' This method creates a Transient Subscription to a COM+ Component
' Refer to The Windows Platform SDK and Particularly ICOMAdminCatalog
' clsID is the COM+ Event Component to which you are subscribing
' objref is the subscriber
' hostname is the machine on which the COM+ Event Component is registered
' If empty, connection stays on local machine
Public Function CreateTransientSubscription( _
ByVal clsid As String, _
ByVal objref As Object, _
Optional ByVal hostName As String = "") As String
Dim oCOMAdminCatalog As COMAdmin.COMAdminCatalog
Dim oTSCol As COMAdminCatalogCollection
Dim oSubscription As ICatalogObject
Dim objvar As Variant
On Error GoTo CreateTransientSubscriptionError
Set oCOMAdminCatalog = CreateObject("COMAdmin.COMAdminCatalog")
'Connect to the
If hostName <> "" Then oCOMAdminCatalog.Connect hostName
'Gets the TransientSubscriptions collection
Set oTSCol = oCOMAdminCatalog.GetCollection( _
"TransientSubscriptions")
Set oSubscription = oTSCol.Add
Set objvar = objref
oSubscription.Value("SubscriberInterface") = objref
oSubscription.Value("EventCLSID") = clsid
oSubscription.Value("Name") = "TransientSubscription"
oTSCol.SaveChanges
CreateTransientSubscription = oSubscription.Value("ID")
Set oSubscription = Nothing
Set oTSCol = Nothing
Set oCOMAdminCatalog = Nothing
Set objvar = Nothing
Exit Function
CreateTransientSubscriptionError:
CreateTransientSubscription = ""
Err.Raise Err.Number, "[CreateTransientSubscription]" & _
Err.Source, Err.Description
End Function
==============================================
5. Drop a DataGrid control on your form leaving the properties as their defaults. Add a Timer to the form as you need it because VBSUX does not natively support multi-threading. . . Have I told you how much VBSUX sucks? It really does! I wouldn't lie to you!
6. In the Form1 code add the following:
=========================================
Option Explicit
Private WithEvents mSink As SQLEventSink
Private Sub Form_Load()
Set mSink = New SQLEventSink
CreateTransientSubscription _
"{8FD50E86-203D-4939-9CAE-0F2865C69465}", _
mSink
LoadGrid
End Sub
Private Sub mSink_OnNotify()
Timer1.Enabled = True
End Sub
Private Sub Timer1_Timer()
Timer1.Enabled = False
LoadGrid
End Sub
Private Sub LoadGrid()
Dim con As Connection
Set con = New ADODB.Connection
con.Open CONNECTIONSTRING
If DataGrid1.DataSource Is Nothing Then
With con.Execute("SELECT ID FROM SYSOBJECTS O " & _
" INNER JOIN SYSUSERS U ON O.UID = U.UID " & _
" WHERE U.NAME = 'DBO' and O.NAME = 'EMPLOYEES'")
mSink.SQLObjectID = .Fields(0)
.Close
End With
End If
Dim rst As Recordset
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM EMPLOYEES"
Set rst.ActiveConnection = Nothing
con.Close
Set DataGrid1.DataSource = rst
Set con = Nothing
Set rst = Nothing
End Sub
=========================================
7. Run the app -
if your Northwind has not been changed, the name for employeeID 7 should be: King, Robert.
In SQL Query Analyzer, execute:
update employees set FirstName = 'Stephen' where EmployeeID = 7
and "voila!!!" Stephen King automatically appears in your datagrid!!!
References:
ICOMAdminCatalog
Registering a Transient Subscription
Complete Source Code can be found here. Zip also contains a compiled SQLEvents.dll,, just in case you don't have delphi and are still hanging around!
|||1. Don't implement the sink in VBSUX as the VBSUX com object is unstable.No problems with a sink implemented in delphi. Have I told you that VBSUX is a total piece of CR@.P?
2. You need to enter a critical immediately upon entering the handler. After entering the critical section, null the Sink reference. Reinitialize the sink right after make changes inside the thread that does the response to the notification. So Don't Implement a Sink Client in VBSUX because Critical sections in VBSUX are a total pain in the @.SS - and threads in VBSUX are even worse!!! Have I told you that VBSUX is a PIECE OF CR@.P?
3. Best perfromance is not kicking the COM+ event off in the sql server but in the applciation that makes the change to the database. Immediately after making a change you want to publish, instance a COM+ Event and call Notify.
On a 2.6 P4 H/T w 775mb mem, I had 22 publishers notifying 30 subscribers. . .slow but no blow-ups.
Subscribe to:
Posts (Atom)