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