Problem with Auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-G

i dont't think its possible and when the column is primary key, it does not make sense also.

Madhu

Problem with a publication. Please help !

Dear friend:
I got this error message inmediatly after to create a suscription for a
publication.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
insert duplicate key in object '#6A3D66CE'.
Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
What should I do ? I drop the publication, but when I configure it again, I
got the same result.
Thanks in advance.
Hi Salvador,
This is a known issue which has been fixed in SQL2000 sp4, and here is the
download link from the microsoft web site:
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
HTH
-Raymond
"Salvador De los Reyes" wrote:

> Dear friend:
> I got this error message inmediatly after to create a suscription for a
> publication.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
> insert duplicate key in object '#6A3D66CE'.
> Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
> What should I do ? I drop the publication, but when I configure it again, I
> got the same result.
> Thanks in advance.
>
>
|||Perfect ! Thks
"Raymond Mak [MSFT]" <RaymondMakMSFT@.discussions.microsoft.com> escribi en
el mensaje news:B17ACDF1-BBD0-45BD-ADDA-5D2C79A8C432@.microsoft.com...[vbcol=seagreen]
> Hi Salvador,
> This is a known issue which has been fixed in SQL2000 sp4, and here is the
> download link from the microsoft web site:
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> HTH
> -Raymond
> "Salvador De los Reyes" wrote:
|||Hi,
I have seen your email in www.codecomments.com, you said "This
problem is a bug", with sp4 sql server it is solucionated.
Last command{call sp_MSget_repl_commands(46, ?, 0, 7500000)}
Error Message Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
Error details Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
(Source: SQLA01 (Data source); Error number: 2627)
but, How the replication worked before sp4?
In my case, only appears the error when I select related tables, why?
Thanks,
Jose Luis
*** Sent via Developersdex http://www.codecomments.com ***

Problem with a foreing key

I'm a beginner in using SQLServer and I 'm trying to bring a db Schema
written for DB2 into SQLServer.
My problem is this: using a tool to translate the script for creating the
DB, I obtain the following code:

ALTER TABLE PROJECT.RSURETTA ADD FOREIGN KEY(VOCE )
REFERENCES PROJECT.RSANVOCE(VOCE ) ON DELETE SET NULL ON UPDATE NO ACTION

But, when I try to run the script the system says:

Incorrect syntax near the keyword 'SET'.

Can I assign a null value to an other table with a reference?

Thank you
Fede"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:cjbp73$h69$1@.atlantis.cu.mi.it...
> I'm a beginner in using SQLServer and I 'm trying to bring a db Schema
> written for DB2 into SQLServer.
> My problem is this: using a tool to translate the script for creating the
> DB, I obtain the following code:
> ALTER TABLE PROJECT.RSURETTA ADD FOREIGN KEY(VOCE )
> REFERENCES PROJECT.RSANVOCE(VOCE ) ON DELETE SET NULL ON UPDATE NO
> ACTION
> But, when I try to run the script the system says:
> Incorrect syntax near the keyword 'SET'.
> Can I assign a null value to an other table with a reference?
> Thank you
> Fede

No - SET NULL is not implemented in MSSQL 2000 (I think it will be in MSSQL
2005); the only options are NO ACTION or CASCADE. If you need this
functionality, then triggers would probably be the best way to go, or
perhaps a stored procedure which performs the DELETE and also sets the
values to NULL in the referencing table.

Simon|||"Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
news:41596f8d_2@.news.bluewin.ch...
> No - SET NULL is not implemented in MSSQL 2000 (I think it will be in
MSSQL
> 2005); the only options are NO ACTION or CASCADE. If you need this
> functionality, then triggers would probably be the best way to go, or
> perhaps a stored procedure which performs the DELETE and also sets the
> values to NULL in the referencing table.
Thank you!
Fede

Problem with 2 tables

So i have these 2 tables:

Cars - has a primary key car

Car Accessories - has a foreign key car

When i insert a new car i has no accessories, so i dont have to insert a record into the car accessories table, but when
i want to add a car accessories record for a specific car i bump into a problem. If i want to add it i dont know which statement to use. If i use insert and the accessories record is already present, then ill get an error or if i use update and the accessories record is not present then it will not work again.

any ideas? maybe it is possible to use some kind of relationship which automatically creates the car accessories record when you insert a new car?

In SQL Server I use an Stored Procedure called xxx_AddUpdate, where xxx is some table or other friendly descriptive name

using the keys passed as parameters, I perform process as follows

if(exists(select <your Id> from [Car Accessories] where <your Id> = @.<your id> ))

-- perform update SQL

else

-- perform insert SQL

|||

Hey thanks for the reply, what you said will do the job just fine, i didnt know you can check if a record exists like that, thanks

I have some other question:

I have most of my sql commands stored in a xsd dataset, would there be any advantage of using stored procedures instead?

|||

For security and other reasons it is traditional to use stored procedures, unless you have other overriding factors in your design.

SP's provides a data access "protocol" that can be better managed from a security perspective, both in terms of what can be limited or exposed to a user for a process, but also in terms of limiting access by database and application roles

|||

Aah so u are saying if security is not a priority its not so important to use stored procedures.

In this case i am gonna stick the strongly typed (xds) dataset and the commands inside it, even though it has many flaws in my opinion, like for example having to make an instance of a dataset table adapter to use its commands, i find it just unnecessary.

|||

It is still worth implementing the suggested workflow in some of your projects as this gets you used to industry standard practice

problem when inserting

I want to insert a recorde into my table (2 column) : name and Id

id is a primary key and this is the error MSG im getting everytime im trying to insert a record

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

what is the problem

here is the code :

protectedvoid Button2_Click(object sender,EventArgs e)

{

try

{

string str ="data source=.;integrated security=true; initial catalog=NH_Project2";

SqlConnection conn =newSqlConnection(str);

SqlCommand comm =newSqlCommand("insert into [Customer](Name,Id) values(@.custName,@.custId)", conn);

comm.CommandType =CommandType.Text;

comm.Parameters.AddWithValue("@.custName", TextBox1.Text);comm.Parameters.AddWithValue("@.custId", TextBox2.Text);

conn.Open();

int rowaff = (int)comm.ExecuteNonQuery();

if (rowaff == 1)

{

Label4.Visible =true;Label4.Text ="one customer has added to our data base";

}

else

{

Label4.Visible =false;Label4.Text ="not added try again";

}

}

catch (Exception ex)

{

Response.Write("Error:" + ex.Message);

}

}

You need to understand IDENTITY columns. Please read up books online. If you want to explicitly insert a value into it you need to use SET IDENTITY_INSERT <Table> ON before the INSERT and set it to OFF after the insert. IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you.

|||

"IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you."

how can I do that ? do you mean to call stored procedure and have it insert into that column?

thanks for your advice, any specific books that you would recommend ?

|||modify your command definition line to:SqlCommand comm = new SqlCommand("insert into [Customer](Name) values(@.custName)", conn); and pass only customer name parametercomm.Parameters.AddWithValue("@.custName", TextBox1.Text)IF you do this ID value will be assigned by database itselfIf you would like to use you logic you have to allow to insert identities into table by running:SqlCommand comm = new SqlCommand("SET IDENTITY_INSERT dbo.[customer] ON insert into [Customer](Name,Id) values(@.custName,@.custId) SET IDENTITY_INSERT dbo.[customer] OFF", conn); but you have to be sure that Identity you try to insert does not exists in table before you do insert.|||

No just remove it from your INSERT list..Just insert the name, the ID will be inserted by SQL Server.

insert into [Customer](Name) values(@.custName)"

|||

that was helpful

|||

The problem is that your IDENTITY COLUMN is inserted automatically! If so the only thing you need is:
insert into [Customer](Name) values(@.custName)

When inserted, the new Customer is given an ID automatically!

SuperJB

Problem using linked tables instead of ADP

Hi
I know I probably shouldn't be doing this but...
I have a table on an MSDE database that has a bigInt for the primary key. If
I try to link to this table from Access (XP) using an ODBC connection the
table links ok. The first time I opened the table it was ok. Since then the
primary key data type is interpretted as text and all the records (though
still there) have "#deleted" in each column of every row? I have tried
relinking the table and still get he same problem.
Any ideas whats going on?
Thanks in advance
Mark
Have you linked the table such that Access recognizes the primary key?
"Mark" wrote:

> Hi
> I know I probably shouldn't be doing this but...
> I have a table on an MSDE database that has a bigInt for the primary key. If
> I try to link to this table from Access (XP) using an ODBC connection the
> table links ok. The first time I opened the table it was ok. Since then the
> primary key data type is interpretted as text and all the records (though
> still there) have "#deleted" in each column of every row? I have tried
> relinking the table and still get he same problem.
> Any ideas whats going on?
> Thanks in advance
> Mark
>
>
|||Hi Monte

> Have you linked the table such that Access recognizes the primary key?
>
As far as I know, yes? I wasn't prompted to select a primary key and looking
at the table design from within access shows the primary key present but as
text instead of number.
Cheers
Mark
|||I have also seen this problem occur when I have "bit" type fields in the SQL
table, and don't have a default value (say zero) and allow nulls checked.
"Mark" wrote:

> Hi Monte
>
> As far as I know, yes? I wasn't prompted to select a primary key and looking
> at the table design from within access shows the primary key present but as
> text instead of number.
> Cheers
> Mark
>
>
|||"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:46D395BA-E150-4C96-9EDE-AF4776FBC18A@.microsoft.com...
>I have also seen this problem occur when I have "bit" type fields in the
>SQL
> table, and don't have a default value (say zero) and allow nulls checked.
Monte
Scary stuff hey :o{
I'm not going to worry about it too much as I was just playing to see the
difference in performance etc. Would still be interested to know why, may
have another look later...
Cheers
Mark

Problem updating two tables in a transaction.

Background...
In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to T1.
I have one process P1 that deletes all the records in T2 and then T1, and
then inserts new records into T1 and then T2, all done in a transaction
(default isolation level). When complete, the P1 notifies another process P2
that it has finished, and P2 immediately loads all the data from T1 and then
T2.
Problem...
P2 occasionally fails when loading T2 because it can't find a referenced row
in the data it loaded from T1. However, on a second attempt to load both
tables it succeeds. Subsequent queries on the tables show problems.
Questions...
Is this likely to be a concurrency issue? If so, why is this happening? How
can I fix it?
One thing that might be relevant is that a foreign key constraint between T2
and T1 is not defined in the database. I have limited control over this.Rick (Rick@.nowhere.com) writes:
> In Sql Server 2000 I have two tables T1 & T2, where T2 contains a key to
> T1. I have one process P1 that deletes all the records in T2 and then
> T1, and then inserts new records into T1 and then T2, all done in a
> transaction (default isolation level). When complete, the P1 notifies
> another process P2 that it has finished, and P2 immediately loads all
> the data from T1 and then T2.
> Problem...
> P2 occasionally fails when loading T2 because it can't find a referenced
> row in the data it loaded from T1. However, on a second attempt to load
> both tables it succeeds. Subsequent queries on the tables show
> problems.
> Questions...
> Is this likely to be a concurrency issue? If so, why is this happening?
> How can I fix it?
> One thing that might be relevant is that a foreign key constraint
> between T2 and T1 is not defined in the database. I have limited control
> over this.
I'm afraid that this introduction is not enough to say anything for sure.
Seeing the code would have helped?
Of course, the missing FK constraint is not good, but as long as you know
that the data you insert is consistent, it is not a problem. And, anyway,
if you were to INSERT data into T2 where the key to T1 is missing, it
should keep on failing.
How does P2 access the tables? Does it use NOLOCK or READ UNCOMMITTED?
Could you have P1 running again while P2 is running?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97665332A148Yazorman@.127.0.0.1...
> Rick (Rick@.nowhere.com) writes:
> I'm afraid that this introduction is not enough to say anything for sure.
> Seeing the code would have helped?
> Of course, the missing FK constraint is not good, but as long as you know
> that the data you insert is consistent, it is not a problem. And, anyway,
> if you were to INSERT data into T2 where the key to T1 is missing, it
> should keep on failing.
> How does P2 access the tables? Does it use NOLOCK or READ UNCOMMITTED?
> Could you have P1 running again while P2 is running?
I'm using ado.net, so the code would probably be irrelevant, even if I could
extract it from the many layers.
I was not declaring an explicit locking strategy, but instead depending on
the default (read committed?). I can only assume that when P2 read T1, it
got none or only some of the new data. I'm not sure how this could happen.
The strange thing is that when I changed the isolation level of the P1
transaction to serializable, the problem did not recur. This is troubling.
:-/|||Rick (rick@.nospam.com) writes:
> I'm using ado.net, so the code would probably be irrelevant, even if I
> could extract it from the many layers.
It may be that your code is too complex to be easily understood in a
newsgroup post, but you are wrong to assume that it is irrelevant. Without
code, I can at best guess what you are doing.
Since it helped to set the transaction isolation level to serializable for
the update process, there is obviously something you did not tell us.
My guess is that once instance of P1 runs, alerts P2. P2 starts reading T1.
At the same time a second instance of P2 starts running and deletes the
row in T2.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> Rick (rick@.nospam.com) writes:
> It may be that your code is too complex to be easily understood in a
> newsgroup post, but you are wrong to assume that it is irrelevant. Without
> code, I can at best guess what you are doing.
> Since it helped to set the transaction isolation level to serializable for
> the update process, there is obviously something you did not tell us.
> My guess is that once instance of P1 runs, alerts P2. P2 starts reading
> T1.
> At the same time a second instance of P2 starts running and deletes the
> row in T2.
Thanks Eric. It's important to know that this should not be happening IF the
sequence of events are as I described. I can only then assume, like you,
that they are not. This gives me something to work with.|||"Rick" <Rick@.nowhere.com> wrote in message
news:uHsgDcLMGHA.1124@.TK2MSFTNGP15.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9767CF95BB51EYazorman@.127.0.0.1...
> Thanks Eric. It's important to know that this should not be happening IF
> the sequence of events are as I described. I can only then assume, like
> you, that they are not. This gives me something to work with.
Sorry... ERLAND!!|||Why don't you have a ON DELETE CASCADE constraint? Never depend on app
code to maintain your data integrity. This is one of many reason that
a row in a RDBMS is nothing like a record.
The way you chain code together implies a procedural design and
mindset.