Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: Andy Hug
  Where is Andy Hug?
 Bran
 Romania
 Andy Hug
 Tags
Subject: RE: SS2K5 SET IDENTITY_INSERT ON | OFF
Thread ID: 155121 Message ID: 155150 # Views: 1 # Ratings: 0
Version: Unknown Category: Databases, Tables and SQL Server
Date: Wednesday, December 26, 2007 12:12:33 PM         
   


> Hi everybody,
>
> hi
>
> i need to set the identity_insert on and off to a remote client table in order to insert rows into it
>
> the client table is on another server 500 miles away from the server ... and I want to insert some new rows into it but one column from client table has identity set to yes
>
> I'm asking because it should be trivial to do this ...
>
> set identity_insert server.database.table ON | OFF
>
> but for whatever reason it gives an error message

What is the error?

There are a number of things about SET IDEBTITY_INSERT.

First the purpose of it is to enable you to insert a specific value into the identity column. So unless you need to populate the column with specific values (as when importing parent-child data for example) you really should not need to disable the identity values.

Second, only one table per session can have identity_insert enabled at any time. What can happen is that if you enable identity insert, then send a bad insert statement (that fails) the identity_insert remains ON, even though the transaction is rolled back. Any subsequent attempts to enable identity insert on the same (or any other) table will immediately fail.

Third, you need SysAdmin, dbo or ddl_admin permissions to use IDENTITY_INSERT.

Fourth, if any inserted value exceeds any existing value, the inserted value becomes the new 'current' value and any intermediate values are lost.

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA

ENTIRE THREAD

SS2K5 SET IDENTITY_INSERT ON | OFF Posted by Andy Hug @ 12/25/2007 11:44:36 AM
RE: SS2K5 SET IDENTITY_INSERT ON | OFF Posted by Ken Murphy @ 12/25/2007 12:01:15 PM
RE: SS2K5 SET IDENTITY_INSERT ON | OFF Posted by Andy Kramek @ 12/26/2007 12:12:33 PM