Identity_insert is set to off

A installation has been moved to run on Windows 2000 / SQL 2005, but when
adding new items we get "Identity_insert is set to off" in our application.
The application has not been changed.
- Is this related to Windows 2000 running SQL 2005?
- Is it related to access on the SQL 2005 server?
- Can it be changed on the SQL server - or only in the application
programming?
This probably happened when you migrated the data to the new server. If you
have a table with IDENTITY columns and you replicate the data over to a new
table, you first need to turn identity_insert on in order to copy the
existing data. It might be that in your old design your application
required this setting to be OFF because it was using the MAX(id_col)+1 trick
instead (in which case, why use an IDENTITY at all).
To find all tables with IDENTITY columns:
SELECT OBJECT_NAME([object_id]) FROM sys.columns WHERE is_identity = 1;
You may need to identify which specific table(s) cause the application this
problem (it might not be all). Does the error message return the table name
as well?
When you identify the table(s) that need to have IDENTITY_INSERT set to ON,
you can do this:
SET IDENTITY_INSERT table_name ON;
Cheers,
Aaron
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Could also be that when you created the tables again, you checked the "is
for replication" property of identity columns, which sets identity_insert to
false (at least vaguely according to BOL).
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Since this setting is session-specific and not server-wide, I would do a
search of your codebase for "SET IDENTITY_INSERT"...
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Thanks!
We will test it tomorrow and let you know.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Since this setting is session-specific and not server-wide, I would do a
> search of your codebase for "SET IDENTITY_INSERT"...
>
> "Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
> news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>
>