|
Are you using transactions ?
If so, maybe you need to change your algorithm so that you are not deleting so many rows at one time.
Something like:
select key from mytable into #Temp1
Loop
begin transactioin
delete some rows where select top 1000 from #Temp1
delete same rows from #Temp1
Commit
Until No more rows in #Temp1
Otherwise, if this is part of a one-time data cleanup, then you might want to
A) Get your users off the database, so that you can do system maintenace.
1) Take a full backup of the database
2) switch the recover mode of the database to "simple"
3) Perform your delete
4) Take another full backup
5) Put the database back into the recovery mode you had before
6) Allow your users back into the database
Just some ideas.
Good luck. 
|
|
|
|
|
Devvy, look at David's answer, understand it and implement the idea, he has identified problem and told you the solution. Managing really large transactions can be a complete PITA.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
can somebody tell me the ways of passing values to stored proc from C++ or C# code.
vikas da
|
|
|
|
|
i am not sure if u really meant that..here i sent cek_no value to my stored proc. like that..
cmd.Parameters.AddWithValue("cek_no", System.Data.SqlDbType.NVarChar).Value=table.Row[i][0].ToString();
vemedya.com
|
|
|
|
|
Thanks for the answer i ment in my question was suppose u need to pass 5 parameters .. so we can wither pass them separately or is there any other way to pass them when i can pass as single argument.
vikas da
|
|
|
|
|
Then you repeat the above 5 times.
|
|
|
|
|
Create a command object (sql client)
add an array of sql parameters
add a valid connection string to the command object
execute the command (executescalar)
Will you please do some research, a simple Google query will do - this question has been answered 1000s of times before and there are many many examples on the net showing you how to do this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, it seems that he wants to pass a class or struct or something.
|
|
|
|
|
PIEBALDconsult wrote: pass a class or struct
How did you get that from the OP.
The the answer would still be the same with an explanation that you cannot pass objects unless they are as xml data type 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: How did you get that from the OP.
He's cheating, the mindreader function is to be released with SQL:2012.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
i have a table which was created in MsSql 2005 Express..
http://img441.imageshack.us/f/numaralar.jpg/[^]
i have numbers in 6 columns..i can group every column and count which number was selected how many times in columns...
but for example number 10 exist in column1 and column2 and column3 also..how i can add that count results that i found for every column seperately to each other to find the total of the number 10 selected ...
vemedya.com
|
|
|
|
|
Are you looking for something like this
SELECT
CASE WHEN NUM1 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM2 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM3 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM4 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM5 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM6 = 10 THEN 1 ELSE 0 END
AS TOTAL
FROM MYTABLE
|
|
|
|
|
thanks for reply friend..when i write that code
SELECT num1,count(*) FROM NumaraBilgileri
GROUP BY num1
i got that http://img827.imageshack.us/f/num1column.png/[^]
as u see i have 22 numbers there..it is just for column1 and i have 5 more columns.. i mean that after that Select executed i have number 10 here 4 times..and in column2 i have 5 times number 10 so total makes 9..and so on.. i want to get the all numbers total in that table from 1 to 49..how i can get it
vemedya.com
|
|
|
|
|
Then try using UNION Query like
SELECT A.NUM, SUM(A.TOTAL) AS TOTAL FROM (
SELECT num1 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num1
UNION ALL
SELECT num2 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num2
) AS A
GROUP BY A.NUM
You need to create union for all fields.
|
|
|
|
|
thanks for the reply man..that was the what exactly i mean
now it works for all columns
vemedya.com
|
|
|
|
|
Hi
I'll develop a website that people will post something formatted with HTML with some pictures. Which way is better to save these content (long HTML with several pictures)? Save them in db or save them on disk? Why?
Thanks a lot.
|
|
|
|
|
Alireza Loghmani wrote: Which way is better to save these content (long HTML with several pictures)?
That depends on your needs, both have advantages and disadvantages. I listed some in the answer over here[^].
A website; how many space do you have on your ftp-account, and how many space is available in your database? If it's a very busy database-server (lots o' queries, lots o' records), I'd opt to save the files in the directory-structure. Otherwise, they'd go into the database. Especially with an online-host, where they might have separate backupplans for databases and normal files.
There's an extra advantage to having them saved as files on the system; if the directory is accessibly from IIS, you could hotlink to that file from outside your server - without the need of adding any load to your databaseserver at all 
I are Troll 
|
|
|
|
|
OK man, thanx. that's my answer.
|
|
|
|
|
My pleasure
|
|
|
|
|
Alireza Loghmani wrote: Save them in db or save them on disk?
Isn't the DB on disk? 
I'd just stick it in the database; easier to backup, copy, etc. Plus, I'd live in fear of someone deleting or modifying the files.
|
|
|
|
|
how can I generate a random unique Int64 number in the activation_number field but I want to make sure the generated number doesn't exist in the table?
|
|
|
|
|
select sum(activation_number)+1 from mytable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
that will not work well, unless the table is extremely short.
1, 2, 4, 8, 16, ..., 2<<31, 0, 1, 2, 4, ...
|
|
|
|
|
|
That's "generating a number" and "verify it's occurance". For that you'd need;
An efficient way of generating a number
An efficient way of looking up a number (index the field!!)
Or, more efficient, use an auto-incremented[^] column. Isn't type BigInt allowed in MySql? Something like below, if I adapted the example correctly;
CREATE TABLE animals
(
id BIGINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
I are Troll
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
