|
i am having a database and i entered some data and deleted it also.Now the Primary Key starts from 4 or say any thing..i want it to start it from 1.And the primary key is auto incremented and not for replication.So how to do it.Please help me out.
Mohinder Singh
|
|
|
|
|
DBCC CHECKIDENT[^]
DBCC CHECKIDENT('MyTable', RESEED, 0)
You need to be logged on as a user with at least one of the following roles: sysadmin , db_owner or db_dlladmin
|
|
|
|
|
Rename ur column[PK].
Add new column with the name of pk column.
Now delete the column u renamed.
Your problem will be solved.
Do good and have good.
|
|
|
|
|
That is the craziest solution I've heard!
|
|
|
|
|
Best Regards,
Apurva Kaushal
|
|
|
|
|
|
No, it won't! You'll just create an empty column. The key ID's won't be regenerated for any existing rows.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Hi
How can i extract hour from datatime varable???
Good bye
Best Regards
N.Nikolov
when i want to read something good just seat and type it
|
|
|
|
|
This is what i know you can use this code to extract hour from datetime variable
COleDateTime oOleDateTime(DateTimeVariable);
int iDay = oOleDateTime.GetDay();
int iMonth = oOleDateTime.GetMonth();
int iYear = oOleDateTime.GetYear();
int iHour = oOleDateTime.GetHour();
int iMinute = oOleDateTime.GetMinute();
int iSeconds = oOleDateTime.GetSecond();
|
|
|
|
|
Thank's a lot
when i want to read something good just seat and type it
|
|
|
|
|
Hi
Can i get parameters from the user at run time in a SQL Procedure. I want to implement a SQL function just like i used to do in my C++ introductory classes. In the function, i want to ask user to enter name, age, class, and address etc one by one and then i would store this record using INSERT query. Can anyone plz help me?
Thank you
We Believe in Excellence
www.aqueelmirza.cjb.net
|
|
|
|
|
SQL Server is a database server, and has no "run time" user interface development tools. You need to write your interface in a programming language (C++,C#,ASP,etc.), then connect to SQL Server via some data connection method (ODBC,OLEDB,etc) and send your "INSERT query" to SQL Server.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
It might be crazy, but i really wanna know if a hashtable (or any array of user defined type) can be save to database as a single object...and how?
any ideas? thanks~
|
|
|
|
|
hi
yes
u can do by converting your hashtable to DataTable or DataSet
fsdfsdfd
|
|
|
|
|
Serialize to a byte array, then store in a varbinary field. When you read it back from the database, deserialize back into a hash table. This obviously only works with serializable objects. Also, if the implementation (of hashtable) is changed at some time, reading back a hashtable from the old serialized format will fail. Also, a varbinary can only hold 8000 bytes, and you need to use an image field if you will have more than 8000 bytes.
Scott
|
|
|
|
|
Hi,
I have tables:
Fruits:
FruitID | FruitName
====================
1 | Apple
2 | Orange
3 | Pear
Baskets:
Basket | FruitID | Amount
========================
Bask1 | 1 | 3
Bask2 | 1 | 5
Bask3 | 1 | 2
Bask1 | 1 | 1
Bask2 | 1 | 2
Bask1 | 3 | 2
Bask3 | 3 | 4
What query can I use to get the following table:
Basket | Apple | Orange| Pear
=============================
Bask1 | 3 | 1 | 2
Bask2 | 5 | 2 | NULL
Bask3 | 2 | NULL | 4
Thanks in advance,
-----------------
Genaro
|
|
|
|
|
select fruits.*,baskets.*<br />
from fruits,baskets<br />
where fruits.FruitID = Baskets.FruitID
|
|
|
|
|
SELECT b.Basket AS Basket, b.Amount AS APPLE
FROM Fruits INNER JOIN
Baskets b ON Fruits.FruitID = b.FruitID
WHERE (Fruits.FruitID = 1)
SELECT c.Basket AS Basket, c.Amount AS ORANGE
FROM Fruits INNER JOIN
Baskets c ON Fruits.FruitID = c.FruitID
WHERE (Fruits.FruitID = 2)
SELECT d.Basket AS Basket, d.Amount AS PEAR
FROM Fruits INNER JOIN
Baskets d ON Fruits.FruitID = d.FruitID
WHERE (Fruits.FruitID = 3)
This will not give u the exact solution but will give you the partial solution.
Do good and have good.
|
|
|
|
|
This is the standard way of doing a Crosstab/PIVOT in SQL.
SELECT Basket,
SUM(CASE FruitID WHEN 1 THEN Amount ELSE 0 END) AS Apple,
SUM(CASE FruitID WHEN 2 THEN Amount ELSE 0 END) AS Orange,
SUM(CASE FruitID WHEN 3 THEN Amount ELSE 0 END) AS Pear
FROM Baskets
GROUP BY Basket
However, as you can see, you need to know in advance what the columns are. There is a new PIVOT command in SQL Server 2005 that may extend this functionality.
The other way is to de-normalise the data using
SELECT b.basket, f.FruitName, b.Amount
FROM Baskets b INNER JOIN Fruit f on b.FruitID = f.FruitID
and import it into Excel where you can use the Pivot tool to do the crosstabbing for you.
Ian
|
|
|
|
|
You could make a Stored Procedure that does this:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cFruitName VARCHAR(10),@cFruitID varchar(10)
SET @sql = ''
DECLARE curFruits CURSOR FOR SELECT DISTINCT FruitID,FruitName FROM Fruits
OPEN curFruits
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', SUM(CASE FruitID WHEN ' + @cFruitID + ' THEN Amount ELSE 0 END) as [' + @cFruitName + ']'
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
END
close curFruits
DEALLOCATE curFruits
SET @sql = 'SELECT Basket' + @sql + ' FROM Baskets GROUP BY Basket'
print @sql
exec(@sql)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
First let me say that a few months ago I found the answer to this question and have forgottten it (That means I know the answer does exist)
Also, let me say that I do not want alternate solutions. I only want the exact solution I previously found.
There is a method to convert a variable of type varchar into some sort of structure directly usuable in an in clause, ie
SELECT * FROM customer WHERE customerID in *something*(@variable)
This method does not use instr, does not use loops, does not use custom split functions and looks very elegant in sql code. If anyone knows I would appreciate the answer. It is driving bonkers.
I am thinking it may have something to do with the TABLE function but the TABLE solutions I see on the web are not elegant and is not the one I am looking for.
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
All of those solutions are fairly common, in fact I had already read that page. The one I want is very elegant. I know it exists because I have seen and used it but now I can't remember it!
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|
|
|
Yes I have, which is weird because I would have thought the ask tom authors would know.
What I am looking for actually exists but it is so hard to find (Trust me I am not jerking any chains). I am sure the method is a hack based on ETL functions but searching reveals nothing. I just wish I could remember the page where I originally read it. In fact, since I have had so much trouble finding it again, if I do refind it I will definately make it into an article. Even though CP isn't known for oracle buffs :p
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
|
|
|
|