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?|||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
--

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

Issue XMLA queries via SSIS

Hello,

We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use...

More info regarding our processes:

OUR SSIS flow is designed to:

1.) backup an existing cube

2.) create a new cube leaving the original in place for users to use while the new one is building

3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly built cube back to the original cube's name

We used DDL code files (DDL Task Objects within SSIS) which contain the XMLA query code for each CREATE, DELETE, ALTER statements used for each specific task:

The flow:

1.) BACKUP Original_Cube

2.) CREATE New_Cube

3.) PROCESS New Cube

4.) DELETE Original Cube

5.) ALTER New_Cube to Original Cube

In order to execute each DDL Task code set for a specific task, it is necessary to specify a connection to a given catalogue... however, in our case, and in the general case of using CREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available...

it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow)

Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this....

the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...?

THANK YOU!

Michael

Hi Michael,

I'm hoping there is an easy answer to this. The "Execute DDL Task" should do all you need to do. Anything you can do with XMLA can be done with that task.

I've got alot of XMLA material here: http://blogs.conchango.com/jamiethomson/archive/tags/XMLA/default.aspx and alot of that talks about issuing XMLA from SSIS. Particularly this one:

Process SSAS dimensions and measure groups individually

(http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx)

-Jamie

Issue with Report Manager after SP 2 install

I'm trying to delete reports using Report Manager from the folder
level by checking multiple files and hitting the delete button. It
use to work but after SP2, the script behind the button no longer
works.
Is anyone experiencing the same issue?
Thanks,
TWe have the same problem - actually, we can't delete any reports, not just
multiple reports.
I found a post on a web site that says that it is a known problem with the
SP2 CTP and will be fixed in the final SP2 release.
<tuong.k.lam@.gmail.com> wrote in message
news:1170293384.531044.128600@.k78g2000cwa.googlegroups.com...
> I'm trying to delete reports using Report Manager from the folder
> level by checking multiple files and hitting the delete button. It
> use to work but after SP2, the script behind the button no longer
> works.
> Is anyone experiencing the same issue?
> Thanks,
> T
>|||Thanks DJX,
Can you post the website where you saw the problem, if you can
remember.