|
You can't without resetting the seed. If you think about it, say you had 25 records and you deleted records 3, 5 and 10. When you come to insert you are all over the place. Autoincrement/identity columns are meant to just be used. If do not want gaps then you need to implement your own code to do it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks alot for your advice
u r right
but my case is delete the last row only is there a solution
is the property "NOT FOR REPLICATION" usefull in my case
thanks again for your time
Kareem Elhosseny
|
|
|
|
|
S.A brother
if u have to delete last row from taable then use this query
<br />
declare @rownum as int<br />
set @rownum =(select max(id)-1 from tablename)<br />
dbcc checkident (tablename,reseed,@rownum)
for more ask again.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
|
You are welcome always.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hello,
I am using a cross tab query which works fine for acccess but doesnot work for SQL Server I ahve version 5 Is there version issue ?
Also I wanted to make a table from the cross tab query w do I do it
Pritha
|
|
|
|
|
Sorry to post it twice .My network is not working properly
|
|
|
|
|
Well, if you posted the code maybe someone could help.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
Thanks
The following query works but the result doesn't get transferred in a table
Select Cap orderby JobWorker pivot TimePeriod into CrossTabCap
Regards
Pritha
|
|
|
|
|
Have a look at some of these, it should become clearer
Pivot
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
i have written a cross tab query which works fine with access but des not work with SQL server.Is there the version problem since i have ver 5?
Also how do I make table from the cross tab query.The into statement des not work.
Pritha
|
|
|
|
|
I have a table with 3 columns as follows:
lower upper id
12 20 100
100 150 200
I want to select range of numbers based on lower and upper column.
For example I want to display numbers between 12 to 20 or from 100 to 150.
How to do it. thanks in advance.
|
|
|
|
|
wajid ansari wrote: numbers between 12 to 20 or from 100 to 150.
Exists stored in table or not?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Here I made a sample test with temporary table.
create table #numbers (value int )<br />
<br />
declare @i as int<br />
set @i = (select min(number) from myTable)<br />
<br />
while @i<(select max(number) from myTable)-1<br />
begin<br />
set @i=@i+1<br />
<br />
insert into #numbers values (@i)<br />
<br />
end<br />
select * from #numbers <br />
drop table #numbers
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Your question doesn't give much idea of what you want, so here are some choices:
CREATE FUNCTION GetRange
(
@Lower INTEGER
,
@Upper INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@Lower)
SET @Lower=@Lower+1
END
RETURN
END
SELECT * FROM GetRange ( 12 , 20 )
CREATE FUNCTION GetRangeById
(
@ID INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER
SET @Lower = (SELECT [Lower] FROM Series WHERE ID=@ID)
SET @Upper = (SELECT [Upper] FROM Series WHERE ID=@ID)
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@Lower)
SET @Lower=@Lower+1
END
RETURN
END
SELECT * FROM GetRange ( 100 )
CREATE FUNCTION GetAllRanges
(
)
RETURNS @IntegerRange TABLE
(
ID INTEGER
,
Member INTEGER
)
AS
BEGIN
DECLARE @ID INTEGER
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER
DECLARE SeriesCursor CURSOR FOR SELECT * FROM Series
OPEN SeriesCursor
FETCH NEXT FROM SeriesCursor
INTO @ID , @Lower , @Upper
WHILE @@FETCH_STATUS=0
BEGIN
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@ID , @Lower)
SET @Lower=@Lower+1
END
FETCH NEXT FROM SeriesCursor
INTO @ID , @Lower , @Upper
END
CLOSE SeriesCursor
RETURN
END
SELECT * FROM GetAllRanges()
|
|
|
|
|
Here is what my XML document looks like.
<DATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BELGIUM</FIELD1>
<FIELD2>BEVERAGES, NON-ALCOHOLIC</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BRAZIL</FIELD1>
<FIELD2>CHEESE</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
</DATAMATRIX>
Using the following code snipet, I got the XMLDOC variable to update, but it will only update the field that previously had a value of "BELGIUM" to the value in my variable, @XMAXPPM.
SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/FIELD1[.="BELGIUM"]/text())[1]
with sql:variable("@XMAXPPM")
')
How do I write a modify statement that will update FIELD2 to a value only when FIELD1 is "BELGIUM" ?
Thanks,
David
|
|
|
|
|
SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/<code>FIELD2[../FIELD1="BELGIUM"]</code>/text())[1]
with sql:variable("@XMAXPPM")
')
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Perfect. I got my project almost complete. Your assistance is greatly appreciated.
|
|
|
|
|
I am using odp.net 10gR2.
Is there a way I can monitor the connection pool, like action connection, connection strings or active connections etc. Also, can I know the like how many connection pools will be there per cpu in my server and can I make any of the connection pool as inactive or disable or something like that ?
Thanks in advance.
|
|
|
|
|
Hi,
You should be able to monitor connection pool (and also many other things) using Performance Monitor (open from COntrol Panel). ODP installation adds several new counters and groups by default.
Hope this helps,
Mika
|
|
|
|
|
Performance Monitor integration is available in 11g and not in 10g
Still, thanks and lemme have another look at that one.
|
|
|
|
|
Sorry, you're absolutely right . It comes in 11g.
Some of the things you asked could be found out from server side. If you have a connection in your connection pool you can see it from v$-views at server. You can also separate which connections are active and running a command and which are only waiting.
In connection string you can define the maximum amount of simultaneous connections and how long they are kept in connection pool and is the connection pooling used at all.
This wasn't exactly what you we're asking but might help you to get forward.
Mika
|
|
|
|
|
Thanks Mika,
Actually I tried v$session but I am not able to figure out through which application user has logged in, and got into that connection pool. Also, its like after some number of connections (less then 20) it is not allowing me to log in and its just spinning and not giving error and I have closed all the other connection from other browsers. So practically there are no active connection, still its not letting me in.
SELECT sid, serial#, event, seconds_in_wait, client_info, status, terminal FROM V$SESSION
WHERE PROGRAM IS NOT NULL
and OSUSER = 'ASPNET'
and terminal = 'Terminal1'
order by seconds_in_wait
Anyways, lemme try some other things, if I can think of !
Thanks for your help Mika.
|
|
|
|
|
You're welcome
Mika
|
|
|
|
|
hi all, I want just to know some thing about the size of DB, if we have DB with 2 tables with no relations between both of them (no PK and FK) so does its size is less than the same one which we make a relation between both of its tables. I want to know even there is a small increment on the size of DB where there is a relation between its tables instead of the one where there is no relation between their table.
thanks for the help from all
Thanks alot
Hamody
|
|
|
|