IDENTITY_INSERT problem...

Hello,

I have set the IDENTITY_INSERT on a table to ON. To test this, I tried
to set it on another table and got the message that it was already on the
table I want. But every time I try to insert records, it keeps telling me
the IDENTITY_INSERT is off!!!

Anyone know why?

Thanks.

Matthew Wells
Matthew.Wells@FirstByte.net

Re: IDENTITY_INSERT problem...

"Matthew Wells" wrote in message
news:BKudndSpqbutUbLanZ2dnUVZ_tijnZ2d@comcast.com...
> Hello,
>
> I have set the IDENTITY_INSERT on a table to ON. To test this, I tried
> to set it on another table and got the message that it was already on the
> table I want. But every time I try to insert records, it keeps telling me
> the IDENTITY_INSERT is off!!!
>

You can only enable IDENTITY_INSERT on one table at a time. Sounds like
you're trying to do it on two tables at once.


> Anyone know why?
>
> Thanks.
>
> Matthew Wells
> Matthew.Wells@FirstByte.net
>
>



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: IDENTITY_INSERT problem...

I said "To test this, I tried to set it on another table and got the
message that it was already on the table I want" meaning that I confirmed
that it was set on the table I wanted.

This one is really stumping us. I've googled all night!!!



"Greg D. Moore (Strider)" wrote in message
news:13ivmdkpulu8qd8@corp.supernews.com...
> "Matthew Wells" wrote in message
> news:BKudndSpqbutUbLanZ2dnUVZ_tijnZ2d@comcast.com...
>> Hello,
>>
>> I have set the IDENTITY_INSERT on a table to ON. To test this, I
>> tried to set it on another table and got the message that it was already
>> on the table I want. But every time I try to insert records, it keeps
>> telling me the IDENTITY_INSERT is off!!!
>>
>
> You can only enable IDENTITY_INSERT on one table at a time. Sounds like
> you're trying to do it on two tables at once.
>
>
>> Anyone know why?
>>
>> Thanks.
>>
>> Matthew Wells
>> Matthew.Wells@FirstByte.net
>>
>>
>
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
>

Re: IDENTITY_INSERT problem...

Matthew Wells (Matthew.Wells@FirstByte.net) writes:
>
> I have set the IDENTITY_INSERT on a table to ON. To test this, I tried
> to set it on another table and got the message that it was already on the
> table I want. But every time I try to insert records, it keeps telling me
> the IDENTITY_INSERT is off!!!
>
> Anyone know why?

Why do you use IDENTITY in the first place, if you want to insert explicit
values?

Maybe you could post a script that demonstrates your issue. Else we have to
guess what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx