|
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
|
|
|
|
|
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
Jude
|
|
|
|