IDENTITY_INSERT

[AG]

Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?

DECLARE @sql nVARCHAR(2000)
SET @sql ='SET IDENTITY_INSERT dbo.Customers OFF'

EXECUTE sp_executesql @Sql

What I would like to do is utilize it in a loop to make sure that all tables
in a database have IDENTITY_INSERT off.

[Dooza]

Post by AG
Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?
What I would like to do is utilize it in a loop to make sure that all tables
in a database have IDENTITY_INSERT off.

I think you have to use SET IDENTITY_INSERT in the statement before you
do the INSERT.

Dooza

[Tom Cooper]

Set IDENTITY_INSERT only applies to the current scope. So when you do
EXECUTE sp_executesql @Sql
that is fine. But it is done in the scope of the EXECUTE, not the your
calling code. (And, of course, it really does nothing since the scope of the
EXECUTE is a new scope, and therefore, IDENTITY_INSERT for that scope cannot
possibly be on for any table in that scope.) But in any case, it can't
affect the INENTITY_INSERT value in your calling code.

I would consider it best practice when you need to turn IDENTITY_INSERT on
for a table, turn it on, do the required inserts and then immediately turn
it off. Then you should always know that IDENTITY_INSERT is off for all
tables, unless you are actually at that place in the code where you have
turned it on, in which case you know which table it is on for. So rather
than doing this, I would want to find the place(s) in your code that is
leaving IDENTITY_INSERT turned on for some table and have that code turn it
off when the inserts have been completed.

Tom

Post by AG
Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?
What I would like to do is utilize it in a loop to make sure that all
tables in a database have IDENTITY_INSERT off.
--
AG
Email: npATadhdataDOTcom

[AG]

Tom,

Thanks for a good explanation.

This is not production code, so it is not that well written.
It is for a large project of converting an application with multiple Access
back ends to a SQL server back end, utilizing a much better schema.

I need to run it several times during development, but it will only be run
once on the client's server. And by me only.

I do have a statement to set IDENTITY_INSERT off, but if the INSERT
statement fails, the set IDENTITY_INSERT OFF statement, apparently does not
run.
After I correct the problem causing the INSERT statement to fail (FK data
problems) and re-run the script, the IDENTITY_INSERT ON fails for a
different table because it is still ON for the table that the INSERT failed
on.

So, to save some development time, I tried to just set IDENTITY_INSERT off
for all tables with identities whenever I restart the script.
--
AG
Email: npATadhdataDOTcom

Post by Tom Cooper
Set IDENTITY_INSERT only applies to the current scope. So when you do
that is fine. But it is done in the scope of the EXECUTE, not the your
calling code. (And, of course, it really does nothing since the scope of
the EXECUTE is a new scope, and therefore, IDENTITY_INSERT for that scope
cannot possibly be on for any table in that scope.) But in any case, it
can't affect the INENTITY_INSERT value in your calling code.
I would consider it best practice when you need to turn IDENTITY_INSERT on
for a table, turn it on, do the required inserts and then immediately turn
it off. Then you should always know that IDENTITY_INSERT is off for all
tables, unless you are actually at that place in the code where you have
turned it on, in which case you know which table it is on for. So rather
than doing this, I would want to find the place(s) in your code that is
leaving IDENTITY_INSERT turned on for some table and have that code turn
it off when the inserts have been completed.
Tom

Post by AG
Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?
What I would like to do is utilize it in a loop to make sure that all
tables in a database have IDENTITY_INSERT off.
--
AG
Email: npATadhdataDOTcom

[AG]

Thanks Tom. A big help as usual!
--
AG
Email: npATadhdataDOTcom

What I would suggest is, when the script fails, before you run it again,
just close your current connection, and then open a new one. Since
IDENTITY_INSERT ON only applies to the current session, the new connection
will not have IDENTITY_INSERT set on for any table.
Alternatively, you could just create a script which sets sets
IDENTITY_INSERT OFF for every table (not by running EXEC, but directly),
by running something like
select 'Set IDENTITY_INSERT ' + o.name + ' OFF'
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where c.is_identity = 1 And o.is_ms_shipped = 0;
and saving the output of that to a file. Then you have a script which
will set identity insert off for all user tables that have an identity
column.
Tom

Post by AG
Tom,
Thanks for a good explanation.
This is not production code, so it is not that well written.
It is for a large project of converting an application with multiple
Access back ends to a SQL server back end, utilizing a much better
schema.
I need to run it several times during development, but it will only be
run once on the client's server. And by me only.
I do have a statement to set IDENTITY_INSERT off, but if the INSERT
statement fails, the set IDENTITY_INSERT OFF statement, apparently does
not run.
After I correct the problem causing the INSERT statement to fail (FK data
problems) and re-run the script, the IDENTITY_INSERT ON fails for a
different table because it is still ON for the table that the INSERT
failed on.
So, to save some development time, I tried to just set IDENTITY_INSERT
off for all tables with identities whenever I restart the script.
--
AG
Email: npATadhdataDOTcom

Post by Tom Cooper
Set IDENTITY_INSERT only applies to the current scope. So when you do
that is fine. But it is done in the scope of the EXECUTE, not the your
calling code. (And, of course, it really does nothing since the scope of
the EXECUTE is a new scope, and therefore, IDENTITY_INSERT for that
scope cannot possibly be on for any table in that scope.) But in any
case, it can't affect the INENTITY_INSERT value in your calling code.
I would consider it best practice when you need to turn IDENTITY_INSERT
on for a table, turn it on, do the required inserts and then immediately
turn it off. Then you should always know that IDENTITY_INSERT is off
for all tables, unless you are actually at that place in the code where
you have turned it on, in which case you know which table it is on for.
So rather than doing this, I would want to find the place(s) in your
code that is leaving IDENTITY_INSERT turned on for some table and have
that code turn it off when the inserts have been completed.
Tom

Post by AG
Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?
What I would like to do is utilize it in a loop to make sure that all
tables in a database have IDENTITY_INSERT off.
--
AG
Email: npATadhdataDOTcom

[Tom Cooper]

What I would suggest is, when the script fails, before you run it again,
just close your current connection, and then open a new one. Since
IDENTITY_INSERT ON only applies to the current session, the new connection
will not have IDENTITY_INSERT set on for any table.

Alternatively, you could just create a script which sets sets
IDENTITY_INSERT OFF for every table (not by running EXEC, but directly), by
running something like

select 'Set IDENTITY_INSERT ' + o.name + ' OFF'
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where c.is_identity = 1 And o.is_ms_shipped = 0;

and saving the output of that to a file. Then you have a script which will
set identity insert off for all user tables that have an identity column.

Tom

Post by AG
Tom,
Thanks for a good explanation.
This is not production code, so it is not that well written.
It is for a large project of converting an application with multiple
Access back ends to a SQL server back end, utilizing a much better schema.
I need to run it several times during development, but it will only be run
once on the client's server. And by me only.
I do have a statement to set IDENTITY_INSERT off, but if the INSERT
statement fails, the set IDENTITY_INSERT OFF statement, apparently does
not run.
After I correct the problem causing the INSERT statement to fail (FK data
problems) and re-run the script, the IDENTITY_INSERT ON fails for a
different table because it is still ON for the table that the INSERT
failed on.
So, to save some development time, I tried to just set IDENTITY_INSERT off
for all tables with identities whenever I restart the script.
--
AG
Email: npATadhdataDOTcom

Post by Tom Cooper
Set IDENTITY_INSERT only applies to the current scope. So when you do
that is fine. But it is done in the scope of the EXECUTE, not the your
calling code. (And, of course, it really does nothing since the scope of
the EXECUTE is a new scope, and therefore, IDENTITY_INSERT for that scope
cannot possibly be on for any table in that scope.) But in any case, it
can't affect the INENTITY_INSERT value in your calling code.
I would consider it best practice when you need to turn IDENTITY_INSERT
on for a table, turn it on, do the required inserts and then immediately
turn it off. Then you should always know that IDENTITY_INSERT is off for
all tables, unless you are actually at that place in the code where you
have turned it on, in which case you know which table it is on for. So
rather than doing this, I would want to find the place(s) in your code
that is leaving IDENTITY_INSERT turned on for some table and have that
code turn it off when the inserts have been completed.
Tom

Post by AG
Could someone please tell me why the following doesn't turn off
IDENTITY_INSERT for the table, yet doesn't generate any error?
What I would like to do is utilize it in a loop to make sure that all
tables in a database have IDENTITY_INSERT off.
--
AG
Email: npATadhdataDOTcom

[Neal Ganslaw]

This is better because it includes the Schema name


select 'Set IDENTITY_INSERT [' + s.name +'].[' + o.name + '] OFF'
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
where c.is_identity = 1 And o.is_ms_shipped = 0;

Could someone please tell me why the following does not turn off
IDENTITY_INSERT for the table, yet does not generate any error?
What I would like to do is utilize it in a loop to make sure that all tables
in a database have IDENTITY_INSERT off.
--
AG
Email: npATadhdataDOTcom

Post by Dooza
I think you have to use SET IDENTITY_INSERT in the statement before you
do the INSERT.
Dooza

Post by Tom Cooper
Set IDENTITY_INSERT only applies to the current scope. So when you do
that is fine. But it is done in the scope of the EXECUTE, not the your
calling code. (And, of course, it really does nothing since the scope of the
EXECUTE is a new scope, and therefore, IDENTITY_INSERT for that scope cannot
possibly be on for any table in that scope.) But in any case, it cannot
affect the INENTITY_INSERT value in your calling code.
I would consider it best practice when you need to turn IDENTITY_INSERT on
for a table, turn it on, do the required inserts and then immediately turn
it off. Then you should always know that IDENTITY_INSERT is off for all
tables, unless you are actually at that place in the code where you have
turned it on, in which case you know which table it is on for. So rather
than doing this, I would want to find the place(s) in your code that is
leaving IDENTITY_INSERT turned on for some table and have that code turn it
off when the inserts have been completed.
Tom

Post by AG
Tom,
Thanks for a good explanation.
This is not production code, so it is not that well written.
It is for a large project of converting an application with multiple Access
back ends to a SQL server back end, utilizing a much better schema.
I need to run it several times during development, but it will only be run
once on the client's server. And by me only.
I do have a statement to set IDENTITY_INSERT off, but if the INSERT
statement fails, the set IDENTITY_INSERT OFF statement, apparently does not
run.
After I correct the problem causing the INSERT statement to fail (FK data
problems) and re-run the script, the IDENTITY_INSERT ON fails for a
different table because it is still ON for the table that the INSERT failed
on.
So, to save some development time, I tried to just set IDENTITY_INSERT off
for all tables with identities whenever I restart the script.
--
AG
Email: npATadhdataDOTcom

What I would suggest is, when the script fails, before you run it again,
just close your current connection, and then open a new one. Since
IDENTITY_INSERT ON only applies to the current session, the new connection
will not have IDENTITY_INSERT set on for any table.
Alternatively, you could just create a script which sets sets
IDENTITY_INSERT OFF for every table (not by running EXEC, but directly), by
running something like
select 'Set IDENTITY_INSERT ' + o.name + ' OFF'
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where c.is_identity = 1 And o.is_ms_shipped = 0;
and saving the output of that to a file. Then you have a script which will
set identity insert off for all user tables that have an identity column.
Tom

Post by AG
Thanks Tom. A big help as usual!
--
AG
Email: npATadhdataDOTcom