If Microsoft planning to provide MSDE version of SQL Server 2005 ?

If the answer is "no", well, that's sad.
If the answer is "yes", then is there a list of 2005 features that will and
will not be available in MSDE 2005 ?
Thank you.
hi,
Marek wrote:
> If the answer is "no", well, that's sad.
> If the answer is "yes", then is there a list of 2005 features that
> will and will not be available in MSDE 2005 ?
> Thank you.
http://www.microsoft.com/sql/express/default.mspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

IDENTITY_INSERT Problems

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

IDENTITY_INSERT Problem

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

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

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

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

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

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

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

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

>>> c.Execute(sql)

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

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

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

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

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

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

>>> r.AddNew()

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

but this fails with the error of

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

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

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

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

Thanks for any help,

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

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

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

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

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

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

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

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

Simon

Identity_Insert 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 seed/increment of MS SQLSERVER 2000 and MSDE

How can I change the increment value of an identity column?
I absolutely need to set a new value for the "increment"
DBCC CHECKIDENT only allow me to change the increment.
ALTER COLUMN does not allow altering identity
Is there a system column i can update or ?
I read few post suggesting using the enterprise manager to do so... but
we got lots of tables with many levels and fk. it's almost impossible in
our situation.
In fact we got many offline databases that where supposed to be
identity(-1,-1) but I just get a surprise!
Tons of data are alrealy inserted (positively) by a "kind of"
replication. but locally created records should be negative.
Thanks a lot
*** Sent via Developersdex http://www.developersdex.com ***Hi
You cannot update an IDENTITY property. Create another non_identity column
and move all data (of identity values) to the column.
You will be able to update a new created column and later on delete an
IDENTITY column
"Vincent" <anonymous@.devdex.com> wrote in message
news:Oq%23ev9A9HHA.600@.TK2MSFTNGP05.phx.gbl...
> How can I change the increment value of an identity column?
> I absolutely need to set a new value for the "increment"
> DBCC CHECKIDENT only allow me to change the increment.
> ALTER COLUMN does not allow altering identity
> Is there a system column i can update or ?
> I read few post suggesting using the enterprise manager to do so... but
> we got lots of tables with many levels and fk. it's almost impossible in
> our situation.
> In fact we got many offline databases that where supposed to be
> identity(-1,-1) but I just get a surprise!
> Tons of data are alrealy inserted (positively) by a "kind of"
> replication. but locally created records should be negative.
> Thanks a lot
>
> *** Sent via Developersdex http://www.developersdex.com ***