Regarding auto increment of id in sql table

I currently working asp.net with c# and inserting data from webpage into sqlserver 2000 data table. I have an auto increment ID in a table. Let's say 10 are inserted. Then you delete those 10. You add another and the auto increment sets the id at 11. How can I get that table to start back to 0? Plz help me and give code for increment id programmatically using c#. Thanks in Adv,

if you execute Delete * from yourTbale then you will see what are seeing, Use Truncate instead of delete, eg, Truncate table yourTableName (this will delete all the rows from your table)

|||

I reset the autoincrement with this but I've never tried running it from a stored procedure.

DBCC CHECKIDENT(MyTempTable1,RESEED, 0)

You might have to go this route. "EXEC ('DBCC CHECKIDENT(MyTempTable1,RESEED,0)')"

Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql

Regarding Active-Active Cluster

Is their any document avaiable on Active-Active SQL cluster
on microsoft site. If yes can some one please send the link
Click Start | Help. Type in "server cluster". That gives you more than
enough information on accomplishing the Windows clustering portion of this.
After that, open up Books Online and type in "failover clustering". That
will give you the rest of the information.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Regarding Active and Inactive

Can any one let me know the shortest possible like how I can inactive and
active log shipping.
The logshopping is working fine but want to inactive for two days and will
active after that according to business need so can anyone let me know how
is it possible.
Thanks
John.
> Can any one let me know the shortest possible like how I can inactive and
> active log shipping.
Disable the jobs temporally.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
John
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:u6p00CTAFHA.2316@.TK2MSFTNGP15.phx.gbl...
> Disable the jobs temporally.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Regarding Accessing on every objects within the sql 2005

Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thxcould you explain this a bit more, I did′n′t get your point from the explanation.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Sorry for the short explanation. Er... let me give a scenario. Once I open SQL management studio, there's a user login pop up window in order I able to access into my server. After login successfully,there are 3 database displays and stored in my servers ( Db A,B and C). When I want to click on the A database, then will pop up the user login window again for authorization. After that, if I want to choose a specific object (eg: Table 'Test') , then there's the use login window pop up again for authorization. At here, which means that authorization will show first before proceed any actions on any objects from the database in my server,.

So is there any possible to make such scenario? I'm just curious about it and would like to enhance my SQL security for my server and away from data thefting (For example, while away from the pc for a while and there's a staff come towards the pc for viewing and modifying the data). Thx for any assistance. Have a nice day.

From,

Hans

|||

Hi,

no SSMS does not have this functionality. once autenticated, permissions are checked upon access to the objects not prompting for any credentials again.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You should lock your console when away from your machine.

Thanks
Laurentiu

|||Thx for the advise. Seems like SQL 2005 doesn't have ' Always prompt for username and password login ' feature which in SQL 2000 then.|||

There was no such feature in SQL Server 2000. Maybe you are referring to a client feature, in which case you should post your inquiry on the Tools forum.

Thanks
Laurentiu

Regarding a sample query

Hello,
I am a very complex problem in front of me. Kindly help me out

in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as

foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS,

Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should

be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am

very sorry, because I am new to database queries...

Thanks,
cspek

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try this:

SELECT unitname,
MIN(CASE WHEN id = 1 THEN value END) AS os,
MIN(CASE WHEN id = 2 THEN value END) AS sp,
MIN(CASE WHEN id = 3 THEN value END) AS drive
FROM InfoText
WHERE id BETWEEN 1 AND 3
GROUP BY unitname

You have to be more specific than "first three rows". Understand that
tables in SQL are not ordered. There is no fixed concept of a first,
second or Nth row.

This is called a cross-tab report. There are other solutions for
producing cross-tabs dynamically in SQL Server but many people would
say that you should do this instead in your client application or
reporting tool. See:

http://www.aspfaq.com/show.asp?id=2462

--
David Portas
SQL Server MVP
--|||Hello,
Thanks...Wil look into it...

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Regarding a query

Hello,
I am facing a very complex problem in front of me. I dont know if its complex...Kindly help me out in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS, Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am very sorry, because I am new to database queries...

Thanks,
cspekWhat you are trying to achieve is called a cross-tab query (or a pivot table). I think you should be able to do something like this:

SELECT
 I.UnitName,
 CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END AS OS,
 CASE WHEN I.ID = 2 THEN MAX(I.Value) ELSE NULL END AS SP,
 CASE WHEN I.ID = 3 THEN MAX(I.Value) ELSE NULL END AS Drive
FROM
 InFotxt I
GROUP BY
 I.UnitName
ORDER BY
 I.UnitName

Terri|||I got the following error with the above select query

Server: Msg 8120, Level 16, State 1, Line 1
Column 'I.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You can try the below statement,

select
unitName,
[Value] = (SELECT [Value] from InFotxt Where ID = 1 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 2 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 3 and s.unitName = unitName)
from (
select
UnitName
from InFotxt
group by UnitName
) S|||You must agggregate any columns not contained in the group by, ie:

MAX(CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END) as col1

sql