Out Of Order Identity Field - Sql2000
Jun 15, 2006
Hi All
I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).
Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.
Have I been wrong in assuming that it should reflect the order from the
sort?
The code is ...
create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
and I don't understand why the values in tmp.C aren't in the order
suggested by the sort.
Any comments most appreciated
Bevan
View 13 Replies
ADVERTISEMENT
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
Jul 30, 2001
How can I alter the default SORT order when SQL2000 originally
installed ? --Is there a Store_Proc in SQL2000 to do this ?
Thank you very much if you can help me !!
Lewis
View 2 Replies
View Related
Jun 30, 2006
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View 12 Replies
View Related
May 5, 2006
Okay... GoDaddy's driving me nuts!!! Anyone... please help.
I am doing an insert into a database with:
INSERT blah blah blah; SELECT @@IDENTITY;
On EVERY OTHER SERVER I have tried this on (local SQLExpress 2005's and my companies various SQL2000 servers, this always returns for me the row number (of autoIndex number of the row just added to the database). My autoIndex was created with the following code:
ALTER TABLE sites ADD autoIndex INT NOT NULL IDENTITY (1,1) PRIMARY KEY
The code I have tried getting the identity with is:
cmd.CommandText = "INSERT blah blah; SELECT @@IDENTITY;";
string autoIndex = Convert.ToString(cmd.ExecuteScalar());
cmd.Dispose();
I also tried:
// bulding of SQL INSERT here
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SqlCommand(SQLDataSource_pics.SelectCommand, conn);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT @@IDENTITY;";
string autoIndex = Convert.ToString(cmd.ExecuteScalar());
Session.Add("autoIndex", autoIndex);
Most of the time, I get a value of "0" (zero) for autoIndex. Every now an then (5%) of the time I get back a correct value.
I don't know where to go from here.
Thank you.
Hayedid
View 4 Replies
View Related
Sep 14, 2006
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
View 1 Replies
View Related
Apr 19, 2006
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
View 18 Replies
View Related
Aug 26, 2007
Hi,
I have some tables in a database with a identity (autoincrement) column (PK).
After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:
ContactId Contact
1 John
2 Mary
5 Sam
9 David
where ContactId is the identity column.
Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:
ContactId Contact
1 John
2 Mary
3 Sam
4 David
(I'm using SQL Server 2005.)
Thank you in advance.
View 5 Replies
View Related
Jan 16, 2004
I would like to retrieve and view .jpg pictures that have been stored in a Data Type "image", Length "16", SQL2000 field.
Any ideas on "How to......."
Thanks alot and enjoy
HandyMac
View 3 Replies
View Related
Aug 20, 2007
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.
How can I set this field with the IDENTITY properties?
Thanks
View 8 Replies
View Related
Jul 20, 2005
I have an application written in Access 97 that connects to a SQL2000backend. One field is a description field that is a data type NTEXT in theSQL database. In my access form, I can not enter more than 255 characters.Before I converted the backend to SQL, the description field was a memofield in Access.What do I need to do to make it so I can enter more text into this field?
View 1 Replies
View Related
Jul 2, 2006
hi friends,i have an identity column in my table.(e.x : id )and it's identity increment is 1.when i insert a row the id field is 1, and in next record the field is 2.....now , i delete second record(id=2)and now when i insert a record again , the id column is 3.i want to record be 2 instead 3.plz help me.thanks
View 2 Replies
View Related
Nov 7, 2007
Hi:
I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?
Can you assist?
Thanks
View 1 Replies
View Related
Mar 10, 2008
Hello friends,
I had created a web application and uploaded the application. Now the problem is with the DB. I had created the DB on the server (using script). But the fields that have identity field yes is not been set Now how I can set the fields identity field to yes. Fields are already there. Only I want to set there identity field to yes.Let me know how this can be done.
Thanks & RegardsGirish Nehte
View 2 Replies
View Related
Nov 5, 2005
Please, How can I get the value of the identity field of the register that I was including in the data base. I am using a stored procedure in SQLSERVER in a asp .net application and I need to show that for the user, it´s like the number of the reclamation.
View 2 Replies
View Related
Apr 2, 2001
If my table has an IDENTITY field, say, the table schema is:
CREATE TABLE BBB(
id int NOT NULL IDENTITY(1,1),
name varchar(20),
job varchar(40))
My data file, which does not carry the IDENTITY field
and its field terminator. The data file looks like this:
debbie cao,programmer
John Doe,engineer
Mary Smith,consultant
I tried to use a format file to bulk copy data from the
data file to the table. Never had any luck.
On the other hand, if I put a comma before the name field,
say, the data file looks like the following:
,debbie cao,programmer
,John Doe,engineer
,Mary Smith,consultant
then, bcp works fine.
But, my data file is automatically generated. It does not
suppose to have the leading comma. Without the leading comma,
I have no idea how may I make bcp work. The SQLBOL says it
can be done. Does anybody have an example to show me?
Please help, thanks!
View 2 Replies
View Related
Jul 12, 1999
Dear Fellow Administrators,
We are experiencing an identity problem on a table with 3.7 millions rows of data in the table. The identity field is not auto numbering any more and are wondering if anyone has a suggestion on how to get the auto number field working again without doing a bcp and restoring the table back to the database from bcp. Any suggestions?
Thanks in Advance,
Daimon
View 1 Replies
View Related
Jul 9, 1998
Greetings !
Having an unusual problem - have created several (20) tables in a database.
All of these tables have an identity field in them (defined as FIELDNAME int IDENTITY(1,1) NOT NULL ). In most of the tables this works as expected, but in 2, so far, when I do the first load (an insert based on a different database) the identity fields are all zero.
Have recreated the tables, changed the field name and location, etc.
Anyone have any clues ?
TIA
Eric
View 3 Replies
View Related
Apr 19, 1999
I need to update an identity field that I have on a table. How can
I go about doing this?
Thanks!
Toni
View 1 Replies
View Related
Mar 8, 2004
Folks
I am inserting some values into a table with the following stmt
Insert into table(number,name) values ('12','name')
In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.
ie Select @@identity from table (any condition????)
How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.
Thanks for the help,
View 3 Replies
View Related
Jun 29, 2007
Hello...
New to SQL and have the following situation....
Example of table (currently)
PayID (Primary Key) PaymentNumber (Identity Field)
John 1
Tim 2
Chris 3
John (same as John above) 4
Jack 5
Steve 6
Would like to see it work this way
PayID (Primary Key) PaymentNumber (Identity Field)
John 1
John (same as John above) 2
Tim 1
Chris 1
Jack 1
Steve 1
Can the identity column be setup to start the counter over depending on value of PayeeID...If another Jack was added then the Payment Number would be 2 for Jack since there is already a record with value 1....
Hope this makes sense...
Thanks
View 2 Replies
View Related
Mar 14, 2007
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
View 6 Replies
View Related
Jun 11, 2001
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
View 1 Replies
View Related
Jun 12, 2008
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View 8 Replies
View Related
Jan 4, 2006
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View 2 Replies
View Related
Nov 13, 2006
How can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
View 1 Replies
View Related
May 23, 2006
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
View 1 Replies
View Related
Jun 12, 2006
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
class file
public class Cotacoes
{
public int codigoCotacao;
}
public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes();
public CotacaoAtualiza() { }
public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
...
SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao);
...
myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader();
while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; }
myConnection.Close(); }
calling into code-behind file:
CotacaoAtualiza ca = new CotacaoAtualiza();
Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
View 1 Replies
View Related
Apr 17, 2000
1. Can someone tell me how to replicate the identity field.
2. When I use the 'NOT FOR REPLICATION' option, I keeps getting synx error. Here is the command:
alter table tusers alter column {userid INT} IDENTITY (1, 1) [NOT FOR REPLICATION]. Do you know why?
3. When I use the transaction replication wizard, why some of the table do not allow me to replicate(There is a key and cross symbol on the articles selection)?
Thanks
View 3 Replies
View Related
Dec 6, 2001
I have a table which has a single column ,which is an IDENTITY colum.How do you insert values in it by using an insert statement.Thanks in advance
View 2 Replies
View Related
Jan 12, 1999
Dear All,
Please help me! I have a table will a identity field the value are,
for example, 1, 2, 4, 6, 8, 10, 11 .... etc.
I want to make it in a sequence, that is, 1,2,3,4,5,6,7...
How can I do it, Please help me, thank you for you helping.
Best Regards,
J.H.
View 3 Replies
View Related
Nov 14, 2005
ok, so i get this error,
Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'TBL_LAPTOP_BOOKINGS' when IDENTITY_INSERT is set to OFF.
i ran SET INDENTITY_INSERT TBL_LAPTOP_BOOKINGS ON before i ran my script... and i also checked, we are running SQL 2000 with service pack four, because of the cursor error....
so im just wondering, is there anyway to make identity_insert to always be on, or any other way to have a unique id in my booking_id field??
Thanks Guys, Justin
View 1 Replies
View Related
Sep 14, 2006
I want to kown if a field is an identity (counter) using a query or a stored procedure ?
Thanks
View 1 Replies
View Related
