Problem with SSIS and 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-GI have not used the "auto-increment" function but the only time I had to increment a value was when the field was a key in my db. I set the key as an Identity key and so the db takes care of the incrementing. Is this something you are able to do? Just a thought!sql

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
Dan
Hi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are completely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>
|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>
|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com
|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.
|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH
|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks
|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
DanHi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are complet
ely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

Problem with query

Hello everyone.
Does anybody have an idea how to optimize following?

I have a table - containing primary key (not identity) wich - on a new entry- should always be the lowest possible number (e.g. 1-234 and 236-400 are occupied - it should select 235, not 401).

Currently I'm doing it trough a dlookup loop searching for a free number (lowest possible)...

Is there any sql query (select top 1 or something like that) to speed this up?

Thanks for any replies!
Greetz - Marcselect min(key) from yourtable

ask yourself why you want to fill in the gaps

a primary key should have no meaning

a surrogate key (an assigned number, for instance) should not even be visible to users of the application

rudy
http://rudy.ca/|||Dear Rudy,
Select min(key) would give me back the lowest existing number - i need the lowest free number.
Primary Key was a bit wrong told - it has a identity besides this number, but i need this number to be unique, and given each time as low as possible (not taken)...

I'm using the system to make reservations in the local database - e.g. article ... gets reserved for a customer under reservation number 5, 1-4 and 6-10 are taken...|||doh!! (smacks self on head)

sorry, i answered too quickly, of course min(key) doesn't get the lowest available number

i could give you some sql, but it involves a left outer join with a temp table containing every integer smaller than max(key) -- it would be ugly and slow

as i said, you should re-think why you want the number to "fill in the gaps"

rudy|||Dear Rudy,
I need it to be the lowest possible number - else it would fill up my whole office :).
I've thought about this reservation system because you always have a low number (max) and so you can sort in the articles by reservation number - wich wouldn't exceed 1000 (except if really more than one thousand would be reserved)...
You can imagine it like this:
I have a wall full of articles with numbers, reserved for customers...
If I would have a ongoing number, the wall would have to get bigger and bigger (if I sort them in ascending by number)...

Do you think it would be faster if I do a SELECT * on a recordset object and loop trough than dlookup?|||consider this --create table reservations
(id integer primary key
, title varchar(50) not null
);
insert into reservations (id, title)
values (1, 'the first one');
insert into reservations (id, title)
values (937, 'the second one');
insert into reservations (id, title)
values (2, 'the third one');how big is your table? three rows

trust me, you do not have to re-use numbers to prevent your table from growing

the database does not reserve space for missing entries

;)|||Dear Robert,
It doesn't consider me if the table grows - the wall with the reservations would have to grow if i reserve by number...

e.g. pos 1 is number 1 - until pos 600 reserved... wall is full sorted by reservation number... now if I don't re-use the numbers wich go out (e.g. 50 gets sent)... my numbers will go to 1200 sometime... and i cannot store that reservation number in my office :)...|||let me explain otherwise...
i have a wall - with places for articles (reservations) - this wall is numbered from 1 to 1000 - so i need the reservations go from 1 to max 1000 - i need to reuse those numbers, elsewhere i would have to make the wall bigger ...
so what I do currently, is loop trough the table with a counter looking for a free position...|||create a new table for your reservation numbers, 1 through 600 or whatever (you can even use auto_number for this)

create table reservationnumbers
( resno integer not null
, reservationid integer null
)

reservationid will point to a reservation your existing table of reservations, the ones that can get deleted -- go ahead and assign them with an autonumber

when you want to assign a new reservation, use

select min(resno) from reservationnumbers
where reservationid is null

when you delete a reservation, make sure you set the corresponding reservationid in the the reservationnumbers table to null

rudy|||You could create a simple table called 'ALLVALUES' with only a numeric field (MYVALUE) containing all possible values (Ex. 1 - 5000).
Obviously this field must be the primary key.

then you can create a query extracting the lowest value in your table not matching with values in ALLVALUES.

Select min(ALLVALUES.MYVALUE) from ALLVALUES
LEFT OUTER JOIN YOURTABLE on (
ALLVALUES.MYVALUE = YOURTABLE.Key )
WHERE YOURTABLE.Key IS NULL

I think this will work

Bye
movendra@.yahoo.com|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||Let us say your table is called XXX and the column that you are seek to find the lowest unused value is COL_ID

select min(x1.COL_ID + 1)
from XXX x1 (nolock)
where not exists
(
select *
from XXX x2 (nolock)
where x2.COL_ID = (x1.COL_ID + 1)
)|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||May this will be more understandable for you...

create table test(id int, name varchar(10))

insert test values(1,'1')
insert test values(3,'3')
insert test values(4,'4')
insert test values(5,'5')

select min(id+1) newid from test
where (id+1) not in (select id from test)

newid
----
2|||hey guys (snail and achorozy), that's pretty slick

unfortunately it doesn't find the gap at the front of the table, but i suppose that's a minor quibble, eh

:cool: