IDENTITY_INSERT Error

I have been trying to get this to work but am failing can anybody help
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT *
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@.Server;User
ID=@.UserName;Password=@.Psw' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, Companyname, fileseq
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq )))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table
'bossdata.dbo.DailyOverLimits' can only be specified when a column list is
used and IDENTITY_INSERT is ON.Don't use select * from opendatasource. Give column names.
Idenityt insert doesn't work with *.
Hope this helps|||and its always a good practice to not use select * and to also specify the
column list in the insert.
Use it this way
INSERT INTO TBL1(COL1,COL2,COL3)
SELECT COLA,COLB,COLC FROM tbl2
because that way you make sure the right values goes to the right columns.|||Thanks Omnibuzz,
Seems like im jumping from one error to another lol, any clues
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT Licence, CompanyName, FileSEQ, AccountCode, ANLRef, FileTotal,
LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=BACSSYS;User
ID=sa;Password=22559226' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, CompanyName, FileSEQ,
AccountCode, ANLRef, FileTotal, LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq Collate
SQL_Latin1_General_CP1_CI_AS)))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Explicit value must be specified for identity column in table
'DailyOverLimits' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.
"Omnibuzz" wrote:

> and its always a good practice to not use select * and to also specify the
> column list in the insert.
> Use it this way
> INSERT INTO TBL1(COL1,COL2,COL3)
> SELECT COLA,COLB,COLC FROM tbl2
> because that way you make sure the right values goes to the right columns.
>|||:)
The answer is there in my second post..
give the column name with the insert table.
See if that works.|||Always list columns, don't leave SQL Server guessing:
insert <destination table>
(
<column 1>
,<column 2>
,...
)
select <column 1>
,<column 2>
,...
from <source table>
ML
http://milambda.blogspot.com/

identity_insert?

I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
I am using:
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
but I get this error:
Error -2147217900


An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.

set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'

As I have turned ID_insert ON it must be the column list?...
not sure what to do next.good assumption -- the error message actually says that|||Are you sure both table structures are identical?
An insert without an explicit column list is never a good idea, just as a SELECT * should not be used for this. SELECT * does not necessarily return the columns in the order expected by an unqualified INSERT INTO.|||Yes, that's correct. When the identity_insert option is set to True, you must specify the column list.|||Having just read your question again, I think you are taking the wrong approach. I don't see why you need to explicitly turn on identity_insert. If you need an identity column for the soldVehicles which will contain values that are distinctly different from the identity values in the original table, then there is no need to use the identity_insert option.

If on the other hand, you only want a single identification column in the soldVehicles table which will have values corresponding to those in the vehicles table, then there is no need to use neither an identity column nor hence the identity_insert option. You would just create a standard column to represent the vehicle ID numbers from the original table and insert the values as you would with any other insert operation.

In the following code sample, constraint and index definitions have been specified in shorthand notation for convenience. In a production system, I recommend proper naming of all objects.

create table vehicles
(
vehicleID identity(1,1) not null unique,
columnA int not null
)

create table soldVehicles
(
vehicleID int not null unique,
columnA int not null,
foreign key vehicleID references vehicles (columnA)
)

insert into soldVehicles (vehicleID, columnA)
select
v.VehicleID, v.ColumnA
from
vehicles v

Although this method is easy to implement and can indeed provide the functionality that you have described, are you not just after a column to mark whether or not a vehicle has been sold? Do you really need an extra table?|||thanks guys,
As Rudy well knows, I am a DB dunce and my old tables are shockingly simplistic and er...large.
To help out in the short-term I am trying to strip out and archive as much unused data as possible, rather than just marking it.
The new ID doesn't need to relate to its old value.
Looks like I am going to have to explicitly write out the list of column names.|||Are you from Cornwall?|||more or less, Devon now|||Oh yes. It is lovely down there.|||Looks like I am going to have to explicitly write out the list of column names.
Which is exactly what the error message told you to do.|||listen guys, when a person knows jack about databases, an error message like that is still pretty cryptic. I still didn't know what to do next, OK?
Robert chose to help, and now I know what to do.

these 'stating the obvious' replies don't help anyone.

In any case I have learnt not to trust error messages explicitly.|||these 'stating the obvious' replies don't help anyone.

I believe that comment was referring to you Ivon.|||thanks Robert.
I am trying to implement an insert similar to your example but I am getting a syntax error.
INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05) VALUES (SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes')
GO
Incidentally, what does the V. stand for in your example? do I need that to do this sort of batch insert?|||When using a SELECT to provide the values for an INSERT the VALUES keyword is not allowed. INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05)
SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes'
GO
See the manual (http://msdn2.microsoft.com/en-us/library/ms174335.aspx) for details.|||excellent! that's cracked it.
Thanks shammat|||Which is exactly what the error message told you to do.
Um...no. That has little to do with what the error message was saying. The main point is the IDENTITY INSERT requirement.|||The main point is the IDENTITY INSERT requirement.but the error message said two things (unless it was misquoted, and i can't be bothered to set up the test situation to reproduce the exact wording): identity_insert must be on, and you must use a column list

seeing as how identity_insert was on, it seems natural to conclude from the error message the need to use a column list, which was missing|||That's absolutely true Rudy, but some of us guys who prefer colouring-in pretty pictures, are still a bit in the dark even after an apparently obvious error message. it seems obvious now, even to me, but when I started this, I didn't know what a column list was! I had an incling but I was fed up guessing, so... I asked the experts. I speculated that it must be the column list and indicated I wasn't sure with a lovely little question mark.
Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Anyway, all done now, I now know a tiny bit more and until I manage to find and employ a DB expert locally, I am fractionally better at doing it myself.|||another happy customer.

the plan to kill all traffic to this site is almost complete.|||Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Why don't you have any DB knowledge? Read the help files, SQL Server's are excellent and I know they would have shown you the correct syntax for an INSERT statment because that's how I learned it. In fact, you should read it right now (http://msdn2.microsoft.com/en-us/library/ms189826.aspx). They break down what each part of any SQL statement means.

And you should dig around here to see recommendations for books to read. Don't pity yourself, (that's Mr. T's job) educate yourself. And it's lose, not loose.|||I believe that comment was referring to you Ivon.
Probably, yes. But if it was obvious, why post the question here?
It wasn't clear from the OP that darkmunk was a 'dunce' and 'looser with no DB knowledge' (his words, not mine). The OP made him look like someone who simply didn't read the error message. And yes, my post was totally unnecessary and unhelpful. Apologies to everyone who wants them.

(Also, I hate forums that don't quote quotes. That used to work here, didn't it?)|||ah, the world is a funny place isn't it?
Full of strange people who will never in a million years be able to visit a microsoft site and come away educated.

Thanks for the spelling lesson tho', don't know how I'd have got thru life without that.

Thanks to Robert and 'shammat' and anyone else with an ounce of empathy.
The job was sorted ages ago.|||Apologies to everyone who wants them.I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?|||I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?
Sure. D'you want fries with that order?

IDENTITY_INSERT, is it a good idea?

Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

One "gotcha" that can occur is that SET IDENTITY_INSERT requires the DDL_ADMIN privilege; this may not be a privilege that you want to give to rank-and-file users.|||

I have primarily used IDENTITY INSERT for DATA Import / Export jobs where i need to preserve the existing values of ID column and I do not want sql server to generate and I also want sql server to let me insert my own values in indentity columns.

|||

You can look at whether or not it is a good idea in two ways.

Technically: No problem whatsoever. If you know the value that was skipped and want to put a value there, it will not harm anything. All the identity property controls is a counter that adds some value to the previous value you used.

Logically: Bad idea. You should use identity valued columns as if you don't know, don't care the value. It would be better if they used a random number in some ways (though that would stink for building clustered indexes :) The idea behind using an identity value is as a surrogate for some value (as a key). Kind of like identifying you by your employee number. If you are employee 10 or employee 20 it will not change your pay. Either could be the CEO or the guy who sweeps up after the CEO.

So use identity_insert to load a table with existing values is perfectly acceptable, but once loaded, if you can accept gaps it is perfect. If not, then generate your own sequence values, looking for gaps.

IDENTITY_INSERT Problems

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8). (lines below with >>> are code lines. I am using Python, but the
syntax should be about the same as VBScript)
First, I create an ADO Connection and create my table.
[vbcol=seagreen]
[vbcol=seagreen]
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
This works fine. Then, I attempt to allow insertion into the ID_Field.
[vbcol=seagreen]
This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset
[vbcol=seagreen]
[vbcol=seagreen]
Last, I am attempt to add a record to the recordset with an explicit ID,
[vbcol=seagreen]
[vbcol=seagreen]
but this fails with the error of
"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."
Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.
I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.
There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/default...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?
Thanks for any help,
-d
> >>> r = win32com.client.Dispatch('ADODB.Recordset')[vbcol=seagreen]
Ugh, have you considered using an INSERT statement, or calling a stored
procedure that uses an INSERT statement?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi drs,
I didn't check all your code, but I noticed that the Field_2 column you use
is NOT NULL, and in the code you post you don't insert a value in this
column. I.e. the code as you have posted it will fail because of this.
Jacco Schalkwijk
SQL Server MVP
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (lines below with >>> are code lines. I am using Python, but the
> syntax should be about the same as VBScript)
>
> First, I create an ADO Connection and create my table.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> but this fails with the error of
>
> "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done."
>
> Even worse, if I now try to set the identity field to allow inserts again,
> Updating() causes an error that I must use an explicit value for ID_Field,
> but if I try to give it one, it fails with the above error. I have to
> destroy the recordset object at this point to get any further.
>
> I am told that SET IDENTITY_INSERT only remains active for one statement
> and
> thus must be combined with the insert, but I do not know how to do this.
>
> There is a similar sounding bug w/ SQL 7
> (http://support.microsoft.com/default...b;EN-US;253157), but
> there
> is no indication that it affects newer versions of the DB. Does anyone
> have
> any suggestions or ideas?
>
> Thanks for any help,
>
> -d
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e8ssHj1cEHA.2616@.TK2MSFTNGP11.phx.gbl...
> Ugh, have you considered using an INSERT statement, or calling a stored
> procedure that uses an INSERT statement?
Yeah, using an INSERT statement failed in the same way.
-d
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi drs,
> I didn't check all your code, but I noticed that the Field_2 column you
use
> is NOT NULL, and in the code you post you don't insert a value in this
> column. I.e. the code as you have posted it will fail because of this.
Just the example code I posted, not the real code that wouldn't run.
-d
|||Why would you post dummy code and not "the real code that wouldn't run"?
http://www.aspfaq.com/
(Reverse address to reply.)
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gb6057novl208@.corp.supernews.com...
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote
> in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> use
> Just the example code I posted, not the real code that wouldn't run.
> -d
>
|||> Yeah, using an INSERT statement failed in the same way.
Can you show your new code that fails? The REAL code, not stuff you make up
on the fly?
http://www.aspfaq.com/
(Reverse address to reply.)
|||IDENTITY_INSERT is only active for particular transaction. Even if you turn
IDENTITY_INSERT ON it doesn't mean that you can then insert duplicate values
into the a IDENTITY column. If you have a constant need to insert values
into an IDENTITY column then I suggest that you re-think your database
design.
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (lines below with >>> are code lines. I am using Python, but the
> syntax should be about the same as VBScript)
>
> First, I create an ADO Connection and create my table.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> but this fails with the error of
>
> "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done."
>
> Even worse, if I now try to set the identity field to allow inserts again,
> Updating() causes an error that I must use an explicit value for ID_Field,
> but if I try to give it one, it fails with the above error. I have to
> destroy the recordset object at this point to get any further.
>
> I am told that SET IDENTITY_INSERT only remains active for one statement
and
> thus must be combined with the insert, but I do not know how to do this.
>
> There is a similar sounding bug w/ SQL 7
> (http://support.microsoft.com/default...b;EN-US;253157), but
there
> is no indication that it affects newer versions of the DB. Does anyone
have
> any suggestions or ideas?
>
> Thanks for any help,
>
> -d
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNrAMv3cEHA.3128@.TK2MSFTNGP11.phx.gbl...
> Can you show your new code that fails? The REAL code, not stuff you make
up
> on the fly?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
Sure, after the line[vbcol=seagreen]
I tried
[vbcol=seagreen]
-d
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNJuBv3cEHA.1888@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Why would you post dummy code and not "the real code that wouldn't run"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
> message news:10gb6057novl208@.corp.supernews.com...
By "dummy code" I mean that I left out the line where I added a value for
the NOT NULL field -- a perfectly reasonable thing to do as actually leaving
out this line would not cause an error until calling the Update() function.
Further, I changed the dsn to take out my real name, etc. Oh, and the
CREATE statement was shortened so you did not need to read about all of the
fields I was creating which had no relevance to this question, and there
were times when I tried a create statement which did not contain a field
which was NOT NULL. Last, since Python is interactive (the >>> prompts
indicate that I was doing this from the command line) I actually tried over
a hundred different things. What I posted was reasonable example code which
did indeed not work, and which was the closest I could come in a short
amount of space to demonstraiting what I though might work, but which
didn't. It really does not work. It does not fail, however, due to some
problem other than my lack of understanding how the IDENTITY_INSERT command
works. That is to say, the NOT NULLness of a field, or the altered dsn does
not make my question more difficult to understand. I am sorry that my short
response did not elucidate this point more clearly.
Really, what I am looking for is an example of something that does work. I
have been unable to find one online.
So far, no one seems to think my code should work, but why is still a
mystery to me.
-d

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

IDENTITY_INSERT persistency

Hi all, quick question:

Is the IDENTITY_INSERT persistent, or only for a single transaction. I'm of course trying to insert into a database that has Idenity, and was wondering if I can just have a stored procedure run at startup to loop through all tables with identity fields and set IDENTITY_INSERT to on.

If not, I'll just have code up scripts to restructure the tables.

Thanks,

CooperThe IDENTITY_INSERT setting is persistant how ever what you want to do won't work. From BOL...

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.sql

IDENTITY_INSERT On/Off in sql compact

Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @.@.IDENTITY_INSERT ]

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75

The IDENTITY_INSERT concept is not supported by SQL Compact Edition. Your best workaround for this is to use the ALTER TABLE / ALTER COLUMN IDENTITY to set the appropriate identity value for each row you want to insert. Not nice? You bet! But it's the only documented solution. I have it working on my database tranfer products so you can trust me it works.|||ok. Thanks|||You wouldn't happen to have a code sample for this, would you?

Thanks in advance...|||My samples use native code, but you can use the .NET CF classes for this. The critical part is to determine the IDENTITY properties before altering the table and this can be done by querying the INFORMATION_SCHEMA (see the BOL).

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

Identity_insert not happening

I am setting insert_identity to on for a table in t-sql.
The table name is passed as a parameter in the tsql procedure.

When i write the following code.
set @.setStr = 'set IDENTITY_INSERT ' + @.toTableName +' ON'
execute (@.setStr)

and then set the insert query and execute it as follows:

set @.insQuery = 'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName
execute(@.insQuery)

when i execute the procedure it doesnt insert values into the table and gives the following error though i am setting the identity to on.

Error: cannot insert explicit value for identity column in table 'emp' when IDENTITY_INSERT is set to OFF.

i cant make out why it is not applying identity_insert to the table.
Can anybody help me out.

Thank YouDon't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||it doesnt work... :(
beacuse i've to use exec to execute the sp_executesql proc.
so it gives the same result..

so i can try to make one string by putting a newline character between the following 2 strings... and then just run one string... i guess it might be possible..

'set IDENTITY_INSERT ' + @.toTableName +' ON'

and

'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName

but the problem is that i dont know how to append a newline character in the string \r \n \\r \\n dont work... can somebody suggest something on this...

Originally posted by macka
Don't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.|||Thanks for this.. i really appriciate ur help...
space works and actually newline character is char(10).. it works with this too... :)

Originally posted by macka
Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.

Identity_Insert is set to OFF

Hi,
I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
to Duplicate a record from a form and the linked records in its subform.
Parent form duplicate goes to table "Jobs" where JobID is the key, records
from subform with (Link JobID) go to table "Samples". I've created an update
query in order to do this and I'm getting the following error-message:
Cannot insert explicit value for identity column in table "Jobs" when
IDENTITY_INSERT is set to OFF
Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
other way to duplicate these records?
Thanks in advance
gaba
Sorry,
I've meant "append query" not update
gaba
"gaba" wrote:

> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
> Thanks in advance
>
> --
> gaba
|||gaba wrote:
> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
>
Hi gaba,
The clue is in the question :-)
In order to insert data into a table which has an identity column, if
you want to insert a value instead of accepting the default, do the
following:
SET IDENTITY_INSERT <table name> ON
then doing your inserts, and then
SET IDENTITY_INSERT <table name> OFF
Rather irritatingly, you can only set this option against one table at
a time.
|||Thanks Damien. That was it. So simple...
gaba
"Damien" wrote:

> gaba wrote:
> Hi gaba,
> The clue is in the question :-)
> In order to insert data into a table which has an identity column, if
> you want to insert a value instead of accepting the default, do the
> following:
> SET IDENTITY_INSERT <table name> ON
> then doing your inserts, and then
> SET IDENTITY_INSERT <table name> OFF
> Rather irritatingly, you can only set this option against one table at
> a time.
>

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

IDENTITY_INSERT is set to OFF

I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.

I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.

Any help would be most welcome. Many thanks in advance

Looks like you are trying to insert a value into a column that has been defined as IDENTITY column? Is that right?|||You need to change the Identity Insert mode. Have a read ofthis article it should explain what is going on.|||

Yes the column is set as IDENTITY. After a bit more reading I think that the issue is with the explicit naming of the identity column. I don't believe that I am explicity defining the field just as @.Trader_ID.

|||Thanks for this, I would like to insert the field without speciying it so that it will increment automatically. The solution in the doc seems to specify the record to be added to the identity field.

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

IDENTITY_INSERT in transaction?

I have to add some rows into a table that is very busy with user-actions. To add the records, I need to use IDENTITY_INSERT ON to ensure that the records keep their original id. The question is: when I execute the insert-script with IDENTITY_INSERT ON, does this setting effect all users or just my transaction?Refer this : http://msdn2.microsoft.com/en-us/library/ms188059.aspx|||I can't find the anwer to my question there, you?|||

Sorry abt the link,.

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

|||

It is just for your current session/transaction only. It wont affect others

But before exiting form the SP/your Batch SET back the orginal property..

If your apps uses connection pooling it may cause a issue...

Mantra : if you start it, you have to finish it

sql

identity_insert in SQL 2005

Hi,

we're currently doing test runs to make our application run on SQL 2005. In one of our projects, we need to replicate data from one master database to several slave databases. As the primary keys need to be the same, I used the "set identity_insert <table name> on" command to insert the exact same keys in the respective identity columns. This has always worked on our SQL 2000 databases. However, in the test runs on SQL 2005, I get the following error message:

"Cannot insert explicit value for identity column in table 'NomenclatuurSleutel' when IDENTITY_INSERT is set to OFF."

I cannot understand why I get this error message. Our components run in COM+, I execute the "set identity_insert on" statement right before the insert statement, and switch it off again right away. This all happens in the same method, and thus I expect it to be executed in the same transaction. A trace on SQL server confirms this: the statements all have the same transaction ID. However, there are 2 lines that are worrying me a bit: between the "set identity_insert" and the insert statement, there seems to be a rollback-action... but this has a different transaction ID, so I don't know whether this affects the identity-insert statement...

Here's a screenshot of the trace:

trace

I am clueless as to where I should look next, is there a difference in transaction management between SQL 2000 and 2005, is the identity_insert statement executed differently,... ?

Looks like you are using connection pooling...notice the RPC "sp_reset_connection" call in between your statements? That is used by data providers (i.e. OleDB, Sql Client, etc.) to reset a connection when reused from a pool...One of the many things that does is abort any open transactions...

Take a look here for some additional information:

http://www.sqldev.net/misc/sp_reset_connection.htm

I'm only guessing that the trace was performed on a single SPID, if so this is your problem, as the sp_reset_connection will abort your transaction amoung other things...

HTH,

|||

I was afraid that would be the case, as I came across that very page you're linking to. I do find it strange, however, that this has always worked on our SQL 2000 databases. And the sp_reset_connection stored procedure was executed as well, no problems there though.

I did find the following comment on a blog somewhere:

When a connection gets pulled out of the pool, an "exec sp_reset_connection" quitely gets sent, and this resets the connection state. With SQL 2000, a few things (like the isolation level) don't get reset, but most things, including the current database, do. With SQL 2005, everything is supposed to get reset.

Apparently, the few things that didn't get reset, are the things that kept my code running.

I tested some new code to work around this, by sending the three SQL-statements (identity_insert on, insert-statement and identity_insert off) in one batch to the server instead of three separate commands. But I'm a little reluctant to go ahead with this, as I'd need to change a lot of code. I'd much rather see a solution in the form of a configuration adjustment in SQL Server, could this be possible?

IDENTITY_INSERT Error

I have been trying to get this to work but am failing can anybody help
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT *
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@.Server;User
ID=@.UserName;Password=@.Psw' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, Companyname, fileseq
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq )))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table
'bossdata.dbo.DailyOverLimits' can only be specified when a column list is
used and IDENTITY_INSERT is ON.Don't use select * from opendatasource. Give column names.
Idenityt insert doesn't work with *.
Hope this helps|||and its always a good practice to not use select * and to also specify the
column list in the insert.
Use it this way
INSERT INTO TBL1(COL1,COL2,COL3)
SELECT COLA,COLB,COLC FROM tbl2
because that way you make sure the right values goes to the right columns.|||Thanks Omnibuzz,
Seems like im jumping from one error to another lol, any clues
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT Licence, CompanyName, FileSEQ, AccountCode, ANLRef, FileTotal,
LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=BACSSYS;User
ID=sa;Password=22559226' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, CompanyName, FileSEQ,
AccountCode, ANLRef, FileTotal, LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq Collate
SQL_Latin1_General_CP1_CI_AS)))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Explicit value must be specified for identity column in table
'DailyOverLimits' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.
"Omnibuzz" wrote:

> and its always a good practice to not use select * and to also specify the
> column list in the insert.
> Use it this way
> INSERT INTO TBL1(COL1,COL2,COL3)
> SELECT COLA,COLB,COLC FROM tbl2
> because that way you make sure the right values goes to the right columns.
>|||:)
The answer is there in my second post..
give the column name with the insert table.
See if that works.|||Always list columns, don't leave SQL Server guessing:
insert <destination table>
(
<column 1>
,<column 2>
,...
)
select <column 1>
,<column 2>
,...
from <source table>
ML
http://milambda.blogspot.com/

IDENTITY_INSERT

Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g. as
a server option or database property? Right now SET IDENTITY_INSERT ON/OFF
has to be issued if a hard coded identity column value is to be inserted.
This is really minor comparing to other compatibility issues. Thanks.Why would you want to do this? It would defeat the purpose of the identity p
roperty. If you don't
want SQL server to generate a value for the column, just don't set the ident
ity property...
The short answer is no, you can't do this.
Also, I don't see how you refer to this as a compatibility issue. In what wa
y would you say that the
behavior has changed. All the way since 6.0 (when identity was introduced),
only a table owner could
specify a value for the identity column and a requirement is that the sessio
n had SET
IDENTITY_INSERT ON for the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mason" <masonliu@.msn.com> wrote in message news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...[c
olor=darkred]
> Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g.
as a server option or
> database property? Right now SET IDENTITY_INSERT ON/OFF has to be issued i
f a hard coded identity
> column value is to be inserted. This is really minor comparing to other co
mpatibility issues.
> Thanks.
>[/color]|||Why don't you make the column an int instead (without an identity)
doesn't this accomplish the same?
Put a unique constraint on the column to ensure that you won't have
duplicate values
http://sqlservercode.blogspot.com/|||That's a fair question. We have a legacy database. It contains two or more
tables that accept data from several sources, some of which use the identity
feature and other don't. To minimize app changes and efforts to locate them,
I like to confirm first that MSSQL2005 does not have such an option since I
couldn't find it in online book, etc. As I expected, this particular
flexibility is not offerred by MSSQL. Thanks.
My "compatibility" was not meant for backward compatibility, but to indicate
the difference between MSSQL and other DBMSes.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Why would you want to do this? It would defeat the purpose of the identity
> property. If you don't want SQL server to generate a value for the column,
> just don't set the identity property...
> The short answer is no, you can't do this.
> Also, I don't see how you refer to this as a compatibility issue. In what
> way would you say that the behavior has changed. All the way since 6.0
> (when identity was introduced), only a table owner could specify a value
> for the identity column and a requirement is that the session had SET
> IDENTITY_INSERT ON for the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mason" <masonliu@.msn.com> wrote in message
> news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...|||I could, then some apps that rely on the identity feature will have to be
updated as well. Since what I am looking for is not available, I will have
some of the apps updated to conform with the identity designation. Thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141667501.059206.78420@.u72g2000cwu.googlegroups.com...
> Why don't you make the column an int instead (without an identity)
> doesn't this accomplish the same?
> Put a unique constraint on the column to ensure that you won't have
> duplicate values
> http://sqlservercode.blogspot.com/|||Ok, I can see where you are coming from... Unfortunately, you don't have muc
h options regarding SQL
Server here. You can't set this at any global level, has to be done at conne
ction level and you have
to be table owner or higher to set this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mason" <masonliu@.msn.com> wrote in message news:%23nfyh4UQGHA.2816@.TK2MSFTNGP15.phx.gbl...

> That's a fair question. We have a legacy database. It contains two or more
tables that accept data
> from several sources, some of which use the identity feature and other don
't. To minimize app
> changes and efforts to locate them, I like to confirm first that MSSQL2005
does not have such an
> option since I couldn't find it in online book, etc. As I expected, this p
articular flexibility is
> not offerred by MSSQL. Thanks.
> My "compatibility" was not meant for backward compatibility, but to indica
te the difference
> between MSSQL and other DBMSes.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||To make it clear, the database was duplicatd into MSSQL2005 a w or two
ago to meet new customer requests. We used MSSQL2000 a while back for a tiny
isolated project.
"mason" <masonliu@.msn.com> wrote in message
news:%23nfyh4UQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> That's a fair question. We have a legacy database. It contains two or more
> tables that accept data from several sources, some of which use the
> identity feature and other don't. To minimize app changes and efforts to
> locate them, I like to confirm first that MSSQL2005 does not have such an
> option since I couldn't find it in online book, etc. As I expected, this
> particular flexibility is not offerred by MSSQL. Thanks.
> My "compatibility" was not meant for backward compatibility, but to
> indicate the difference between MSSQL and other DBMSes.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ebheNXUQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||The problem that you will face is that when you have SET
IDENTITY_INSERT ON all the apps will fail that rely on the identity
value being auto generated (if they try to insert before you run a SET
IDENTITY_INSERT OFF statement)
Just keep that in mind
http://sqlservercode.blogspot.com/|||I have not tried this myself, but if you do your updates with stored
procedures, you could try checking for the existence of the identity column
in your parameters and the issuing SET IDENTITY_INSERT ON/OFF within the
stored procedure as needed.
However, as others have pointed out, this tends to defeat the purpose of the
identity column.
"mason" <masonliu@.msn.com> wrote in message
news:uO6wfMUQGHA.1676@.TK2MSFTNGP14.phx.gbl...
> Is there a way in MSSQL2005 to allow "IDENTITY_INSERT" all the time, e.g.
as
> a server option or database property? Right now SET IDENTITY_INSERT ON/OFF
> has to be issued if a hard coded identity column value is to be inserted.
> This is really minor comparing to other compatibility issues. Thanks.
>|||Thanks. I will try not to play with this on/off switch.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141671850.478649.112510@.j52g2000cwj.googlegroups.com...
> The problem that you will face is that when you have SET
> IDENTITY_INSERT ON all the apps will fail that rely on the identity
> value being auto generated (if they try to insert before you run a SET
> IDENTITY_INSERT OFF statement)
> Just keep that in mind
> http://sqlservercode.blogspot.com/

Identity_insert

Hi friends,
I want to know whether IDENTITY_INSERT is on or off through an SQL Statement. Depending on whether IDENTITY_INSERT is ON or OFF i can use SET IDENTITY_INSERT ON or SET IDENTITY_INSERT OFF.
Reply ASAP ...
shanky......ASAP...

try to use ERROR, if you set IDENTITY ON and it is ON, it generates error...

see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

jiri|||Setting identity_insert on only works for a single session that is trying to set identity_insert on more than 1 table is this what you are trying to check ?

IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

Identity insert on

After setting identity_insert on for a table is there any way by which I can insert multiple or a range of records at a time?From where?

INSERT INTO myTable SELECT * FROM myOtherTable?

Or do you mean From a file?

BULK INSERT INTO myTable FROM 'C:\TEMP\newdata.dat'|||I mean from any table between a range of data.|||I mean from any table between a range of data.|||Well the INSERT INTO should do it with a predicate (WHERE clause)

Your next statement will be...

How do I get the middle 500 rows...

Yes?