If exists update, if it doesn't exist create

What I am looking for is a table that has two columns we'll say FName and Timestamp. What I want to do is to see if that name exists in my records. If it does I want to update it with a new timestamp. If it does not I want to create a new record in my table. This is going to be a check from vb 6.0 that is running continuously, so it will run the stored procedure to look for Redundancy in the FName column, if it finds it I want it to update the timestamp in that record to show the most up to date time.

Thank you in advance.First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'|||

Quote:

Originally Posted by SkinHead

First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'

Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.|||

Quote:

Originally Posted by JReneau35

Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.

You do have a specific name your searching for though right? This method will update all of the records matching your input parameter.

You come in with a name and you want it to search for that name in a table. If it doesnt find it, you want one new record created with a timestamp. If it does find it, no matter how many times it finds it, you want all of them updated.

Am I understanding you right?

If a running total is null then how to show 0.00?

Hi, I'm using Crystal Reports 8.5 and I have this issue:
I have a numeric running total field which is evaluated using a formula.
If no records satisfy that formula then the running total field becomes empty, but, instead of showing nothing (empty) I would like to show 0.00
How can I do this?

Thanks in advance

PS: I use visual basic 6.0 and reports are .rpt files outside of the .exe file.Ok, I solved it. I used a formula field which evaluates the running total, and I put the formula instead the running total on the report.|||How did you do that, Thanks.|||Create a formula having the code

If {RunningTotalField} is null then
0
else
{RunningTotalField}

IDs of updated records

I have to update a table, and after I update I need to insert a record
for each updated record in some other table.
I need to know the IDs of the records which were updated in the first
table, so that when I insert records in the second table then I can put
that ID in a field.
How would I acheive this?
Thanks in advance.With a trigger I suppose.
CREATE TRIGGER dbo.UpdateBaseTableName
ON dbo.BaseTableName
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
INSERT AuditTable(id_column) SELECT id_column FROM inserted;
GO
See the topic "CREATE TRIGGER" in Books Online for more details.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>|||On 2005, your the OUPUT option of the UPDATE command. If earlier version, do
a SELECT first based on
the WHERE condition to know the ID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>sql

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

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

Identity Increment

If delete all the records from a table that has an incremental identity. Is there a TSQL way of reset the first number on an insert back to be 1 again without going to the table taking it off then saving it the putting it back on again?TRUNCATE table clears all data and resets the Identity counter.|||Perfect! Thank you.

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA

Identity field fix

I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx

First, check for tables with a Foreign key relationship to the table under consideration.

In each of the tables with a FK, set the table to CASCADE UPDATES for the FK field.

Then set IDENTITY INSERT ON for the primary table. [ SET IDENTITY INSERT MyTable ON ]

When you complete the corrections, set the IDENTITY INSERT off, and remove the CASCADE UPDATES on the FK fields.

Any other tables that also may have the values from the primary table will have to be manually discovered and corrected.

|||

Hi Arnie

Surely you'd have to remove the IDENTITY property from the column before performing any updates?

The example below doesn't allow updates to an IDENTITY column, failing with the following error:

Msg 8102, Level 16, State 1, Line 3

Cannot update identity column 'ID'.

Thanks
Chris

USE [tempdb]

GO

CREATE TABLE dbo.Test

(

ID INT IDENTITY NOT NULL,

MyField VARCHAR(9)

)

INSERT INTO dbo.Test (MyField)

VALUES ('TestValue')

SET IDENTITY_INSERT dbo.Test ON

UPDATE dbo.Test

SET ID = 2

SET IDENTITY_INSERT dbo.Test OFF

|||

Thanks Chris for catching that (posted before coffee...)

I left out that for this approach to work, it will be necessary to move all of the rows to be updated to a temp table, delete them from the primary table, add another column to the temp table, make the updates to the new column, then with IDENTITY_INSERT ON, add those columns back to the primary table -specifying the new column in place of the original ID column.

IF there were secondary tables with FK relationships, use the temp table to update the FK column in the secondary tables.

|||

Thx, for the suggestions. How about this senario:

I made a Test copy of the DB using the SQL Wizard Export. The Test DB did not contain the Identity property on the Barcode field like the LIVE DB and thus allows me to run a fix for the 1000 records that have a bad barcode field. Once fixed I am thinking of renaming the Test DB to LIVE.

I think Something like the following will solve the problem:

Update Person

Set Barcode = (Barcode + 1000000)

where Barcode < 3001000 (this identitfies the 1000 bad records)

TIA

IDENTITY Column!

A table named Products1 containing 20 records has the following
columns:
PID int IDENTITY (1,1)
PCode varchar(50)
PName varchar(50)
PDesc varchar(500)
Price money
Qty int
I created another table named Products2 whose design is exactly the
same as the design of the table named Products1 i.e. the PID column in
Products2 is also an IDENTITY(1,1) column. I issued the following query
to populate Products2:
---
SET IDENTITY_INSERT Products2 ON
GO
INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
SELECT * FROM Products1
---
The above query, when executed in QA, populates Products2 with the
records existing in Products1 but if the above query is executed again,
Products2 again gets populated with 20 records existing in Products1
which is OK but the PID values of the 2nd set of 20 records remain the
same as that of the first set of 20 records i.e. there are duplicate
PID values but IDENTITY columns are supposed to identify each row
uniquely which the IDENTITY column PID doesn't do here! So does this
mean that PID no longer remains an IDENTITY column? Shouldn't SQL
Server generated an error when the INSERT query was executed for the
second time?
Had the PID column been a PRIMARY KEY column, executing the INSERT
query in QA two (or more) times rightly generates a PRIMARY KEY
Constraint error but why doesn't the same happen with an IDENTITY
column?
Thanks,
ArpanIDENTITY doesn't guarantee uniquness--especially when you're using
IDENTITY_INSERT, something that should only be used when combining tables or
databases (once in a blue moon). You must either have a primary key or
unique constraint on the IDENTITY column.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123968967.850494.94880@.o13g2000cwo.googlegroups.com...
> A table named Products1 containing 20 records has the following
> columns:
> PID int IDENTITY (1,1)
> PCode varchar(50)
> PName varchar(50)
> PDesc varchar(500)
> Price money
> Qty int
> I created another table named Products2 whose design is exactly the
> same as the design of the table named Products1 i.e. the PID column in
> Products2 is also an IDENTITY(1,1) column. I issued the following query
> to populate Products2:
> ---
> SET IDENTITY_INSERT Products2 ON
> GO
> INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
> SELECT * FROM Products1
> ---
> The above query, when executed in QA, populates Products2 with the
> records existing in Products1 but if the above query is executed again,
> Products2 again gets populated with 20 records existing in Products1
> which is OK but the PID values of the 2nd set of 20 records remain the
> same as that of the first set of 20 records i.e. there are duplicate
> PID values but IDENTITY columns are supposed to identify each row
> uniquely which the IDENTITY column PID doesn't do here! So does this
> mean that PID no longer remains an IDENTITY column? Shouldn't SQL
> Server generated an error when the INSERT query was executed for the
> second time?
> Had the PID column been a PRIMARY KEY column, executing the INSERT
> query in QA two (or more) times rightly generates a PRIMARY KEY
> Constraint error but why doesn't the same happen with an IDENTITY
> column?
> Thanks,
> Arpan
>|||Thanks, Brian, for your input but BOL states that IDENTITY columns
contain system-generated values that uniquely identify each row within
a table. So is BOL wrong?
Thanks once again,
Regards,
Arpan|||You got everything wrong. Please read a book on RDBMS.
Rows are not records; IDENTITY cannot ever be a relational key. I find
it amazing that you have a product code that changes size and can be
CHAR(50) and NULL, etc. If you knew what you were doing and had posted
DDl, would look like this?
CREATE TABLE Products
(product_id CHAR(13) NOT NULL PRIMARY KEY -- upc' idustry standard
product_name CHAR(20) NOT NULL,
product_descr VARCHAR (250) NOT NULL,
product_price DECIMAL (8,2) NOT NULL
CHECK (product_price > 0.00),
qty_on_hand INTEGER NOT NULL
CHECK (qty_on_hand > 0),
product_status INTEGER DEFAULT 1 NOT NULL
CHECK (product_status IN (1,2) );
One of the basic ideas of RDBMS is that each table is a set of the same
kind of entities. If two tables have the same structure then they
model the same entity. What you probably need is a status code to show
the LOGICAL difference between a table 1 and table 2 products. Surely,
you are not just shifting rows from table to table, to mimic a punch
card or magnetic tape file system!
This is a dangerous option used with a non-relational, proiprietary
feature that should not have been there anyway. You have gone from bad
to worse.
Stop what you are doing. Read a book or two. Start over.|||Well, Celko, I guess you have dug in too deep in the example I have
shown or you are trying to read too much in between the lines. This is
just a hypothetical scenario....definitely not a practical one. Of
course, having 2 such tables just doesn't make any sense. I wanted to
get my doubt on IDENTITY clarified which is why I cited those 2 tables.
Maybe I could have given a better example but couldn't think of
anything else within the stipulated time of 2-3 minutes I was given to
frame my query (I am on my friend's computer)!!
So please take it easy :-)
Thanks,
Regards,
Arpan|||BOL is not wrong. IDENTITY_INSERT bypasses the normal behavior of IDENTITY.
If you don't use IDENTITY_INSERT, then absent a catastrophic system failure,
the generated IDENTITY values will always be unique. That's why it's use
should be limited. There are instances when you want to specify the
identity values, for example, when you're combining databases or tables. A
further limitation is that IDENTITY_INSERT can only be on for one table at a
time per session. It is a tool for a database administrator, to be used
only when absolutely necessary.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123972518.924767.159220@.g47g2000cwa.googlegroups.com...
> Thanks, Brian, for your input but BOL states that IDENTITY columns
> contain system-generated values that uniquely identify each row within
> a table. So is BOL wrong?
> Thanks once again,
> Regards,
> Arpan
>|||Thank you very much, Brian, for helping me clarify my doubt.
Regards,
Arpan

Identity Column Rollover

If I have an Identity column defined as a smallint
seed=1, increment=1
Does it automatically roll back to 1 when the number of records reaches
32,767. Assuming of course, the last record assigned identity 1 has been
removed.
I assume it does, but hey, I've assumed stuff like this before and gotten
burned.
JohnNo it does not automatically roll over. Instead you will receive an
overflow error and future inserts will fail. You could then reseed the
value using the DBCC CHECKIDENT command. A much better plan however, is
to create the column large enough that it won't overflow for the
forseeable future.
David Portas
SQL Server MVP
--|||No, you assume wrong. You'll get an error when you try to insert a new row.
"Arithmetic overflow error for data type smallint, value = 32768"
Change to Int if you believe that it may be a problem.
"John Manion" <JohnManion@.discussions.microsoft.com> wrote in message
news:785FF58A-2C96-4AAF-9E2A-30C918853DDE@.microsoft.com...
> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||No. SQL Server will try to increment the identity value and also give you an
error when trying to store the new value.
Example:
use northwind
go
create table t (
colA smallint not null identity
)
go
set identity_insert t on
go
insert into t (colA) values(32767)
go
set identity_insert t off
go
select * from t
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"John Manion" wrote:

> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||Not surprising.
Thanks,
John
"David Portas" wrote:

> No it does not automatically roll over. Instead you will receive an
> overflow error and future inserts will fail. You could then reseed the
> value using the DBCC CHECKIDENT command. A much better plan however, is
> to create the column large enough that it won't overflow for the
> forseeable future.
> --
> David Portas
> SQL Server MVP
> --
>

IDENTITY COLUMN QUESTION??

Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.

How can I reset the IDENTITY to start from 1 without re-creating the table?

Please advice. Thanks in advance.

PankajTo my knowledge, you need to use TRUNCATE TABLE tablename to reset the identity value|||You can also use:

DBCC CHECKIDENT (authors, RESEED, 30)

where authors is the tablename and 30 is where you want to restart the IDENTITY. keep in mind that you will need to be aware of any potential conflicts in your data, such as if you are using the IDENTITY column as a primary key.

cs

Identity Column problem

Hi,
I have Identity column in my table, Once records is inserted into table I
pull some records out from the table. Now I want to regenrate Identity column
number after removing some records so there is no gap in Sequence number in
my table ..
How Can I regenrate Identity column again ..
Thanks
-Kris
You cannot do this reliably with an IDENTITY column. You may end up changing
seeds, using identity_insert options etc. which is not worth the effort.
Generally, "Gap-less" identity columns are almost impractical. What you are
looking for, at least it seems like, is a rank based on the values in some
column. There are several solutions posted in these newsgroups, which can be
found in the google archives. Also refer to the MS knowledge base article :
186133
Anith
|||You can drop the column and add a new one which will populate itself.
But why do you want it to be consecutive?
"Kris" wrote:

> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity column
> number after removing some records so there is no gap in Sequence number in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris
|||Sounds like you are using the IDENTITY property for the wrong reasons. (See
http://www.aspfaq.com/2523)
If all you want is a seamless row counter for display purposes, you should
do that at query time as opposed to storing them. See
http://www.aspfaq.com/2427 for some ideas.
http://www.aspfaq.com/
(Reverse address to reply.)
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:777BE06D-97B0-4687-8459-75EDF26EA941@.microsoft.com...
> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity
column
> number after removing some records so there is no gap in Sequence number
in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris

Identity Column problem

Hi,
I have Identity column in my table, Once records is inserted into table I
pull some records out from the table. Now I want to regenrate Identity column
number after removing some records so there is no gap in Sequence number in
my table ..
How Can I regenrate Identity column again ..
Thanks
-KrisYou cannot do this reliably with an IDENTITY column. You may end up changing
seeds, using identity_insert options etc. which is not worth the effort.
Generally, "Gap-less" identity columns are almost impractical. What you are
looking for, at least it seems like, is a rank based on the values in some
column. There are several solutions posted in these newsgroups, which can be
found in the google archives. Also refer to the MS knowledge base article :
186133
--
Anith|||You can drop the column and add a new one which will populate itself.
But why do you want it to be consecutive?
"Kris" wrote:
> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity column
> number after removing some records so there is no gap in Sequence number in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris|||Sounds like you are using the IDENTITY property for the wrong reasons. (See
http://www.aspfaq.com/2523)
If all you want is a seamless row counter for display purposes, you should
do that at query time as opposed to storing them. See
http://www.aspfaq.com/2427 for some ideas.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:777BE06D-97B0-4687-8459-75EDF26EA941@.microsoft.com...
> Hi,
> I have Identity column in my table, Once records is inserted into table I
> pull some records out from the table. Now I want to regenrate Identity
column
> number after removing some records so there is no gap in Sequence number
in
> my table ..
> How Can I regenrate Identity column again ..
> Thanks
> -Kris

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

I found the source of the error.

Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).

The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn

where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

--

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?
It's used in a complex bound form with an n-m relationship (3 records). Needless to say changing this code to reference the tables directly is a big change and lots of work.
|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

I found the source of the error.

Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).

The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn

where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

--

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

I found the source of the error.

Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).

The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn

where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

--

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

I found the source of the error.

Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).

The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn

where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

--

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George