|
I Have a table of emp with the fields of
Empno empname Empsalary
1 sam 1000
2 joe 2000
3 harry 3000
Now i want a query for a give me the result like this
Empno empname Empsal salTotal
1 sam 1000 1000
2 joe 2000 3000(sum of previous salary Total with the current sal)
3 harry 3000 6000 sum of previous salary Total with the current sal
How can be achive this result by using query
Thanks a ton in advance
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
This will give you what you are after, but will take a long time to run if your table is large, as it executes the subquery once for each line.
select EmpNo, EmpName,
(select sum(EmpSalary) from emp e2 where e2.EmpNo <= e1.EmpNo)
from emp e1
order by e1.EmpNo
Hope that helps!!
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi
Thanks For reply.Its Working but as u said it will take long time when table is large any Other way by which our query work fine untill table is large.
Thanks a Ton
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
For something like this you might consider using cursors - things which iterate through your table like a traditional loop. They're generally best avoided although I think quite appropriate in this case.
Regards,
Rob Philpott.
|
|
|
|
|
I want to use Form Authentication for reports and that is to coordinated with my web application , whatever database i m using in my web application , only that database i have to use for formauthentication in SSRS and once user logged in , He/she didn't login again to access for report server and User can see their reports according to user role.
I was using the method which is described in Form authentication Sample in msdn but i didn't get full success.
Please help me out.
shraddha tripathi
|
|
|
|
|
i have to use the while loop
like
while(select name from table)
update table set name=name + 0
next
I know this is not a realistic , i was trying to just make u understand.
How should i implement it.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
hi
You can use Cursor for this
regards
JOE
|
|
|
|
|
can u provide some link?
i mean how to use cursors in stored procedure?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
google broken where you live ?
Cursors are very expensive, you should avoid them if you can.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi Christian...
How should i implement the following thing
i have to use the while loop
like
while(select name from table)
update table set name=name + 0
next
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Like Chris has stated, cursors are more expensive. If you can explain us on what you are trying to do, probably you may be suggested with a better workaround.
|
|
|
|
|
Like there are four Rows
S.No Total Received Balance Delete
1 1000 100 900 Delete
2 1000 200 700 Delete
3 1000 300 400 Delete
4 1000 400 0 Delete
if i delete the first row then the effect will take place on the balance column in the 2nd, 3rd , 4th column.
I want to use the sql queries in (Stored Procedure)database and not in the form
Now i am telling u what i thought to do it
first select statement , to fetch all the records, then one by one i will subtract the received from the total and ba;lance will be updated.
This is what i want to do.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: if i delete the first row then the effect will take place on the balance column in the 2nd, 3rd , 4th column.
It's poor design. Why you are calculating the balance and updating all remaining rows ? This will end in performance issues. Assume you have many rows in that table, and you are doing this calculations on each row deletion, it will be a big overhead for SQL Server.
This is presentation issue. You can calculate the balance when you do select on the table. This will be fast comparing to your method.
|
|
|
|
|
that means i should not do the calculation after deleting the row, whether i impacts the rest of record or not?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: that means i should not do the calculation after deleting the row,
Yes you can do this when records are selected.
|
|
|
|
|
hi
here is an example..fore more details on Cursor you can check help of Sql server 2000..
declare @EMpname as nvarchar(100)
declare emp_cursor cursor for
select name from employee
open emp_cursor
fetch next from emp_cursor into @EMpname
while(@@FETCH_STATUS=0)
begin
< update Statements>
fetch next from emp_cursor into @EMpname
end
close emp_cursor
deallocate emp_cursor
Regards
JOE
|
|
|
|
|
You hava a table containing about 1000000 records.Now you want to delete 950000 records and only left 50000 records in the table. As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)?
many thanks
|
|
|
|
|
richardye wrote: As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)?
What is going to happen when you delete all of those records is that the transaction log is going to grow.
Regarding disk space(I take it you mean disk space rather than memory?):
Backup the transaction log to clear it out of pending transactions first.
You will then need to shrink the transaction log.
Run your script to delete the records.
Then straightaway backup the transaction log and shrink it again.
This is the only way I can think of to keep disk space usage at a minimum.
Regarding speed:
All I can suggest is that you do all of this out of hours when nobody else is using the database.
If anybody else is accessing the table you are deleting from there is always the chance that you could get a lock, for a period of time, or even a deadlock.
You always pass failure on the way to success.
|
|
|
|
|
Shrinking a transaction log is a science in itself, so here is a very useful script that I use(you may have to run the script more than once on the same transaction log for it to recover all the free space).
Click here for the google document.
You always pass failure on the way to success.
|
|
|
|
|
I am trying Transactional Replication between SQL Server 2005 SE and SQL Server Express installed on a client machine. I am getting "Uninitialized Subscription" status on Replication monitor. The error message is like "The job failed. Unable to determine if the owner (DOMAIN\Joseph.Thomas) of job ACR-MANGO-October10-OCT-10 Pub-ACR-ANJILI\SQLEXPRESS-35 has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\Joseph.Thomas', error code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621))."
The steps i followed are
1. Create a publication in SQL Server 2005 SE.
I could successfully create Publications on Server. The security setting i gave are
SnapShot Agent Process: Run Under SQL Server Agent Service Account
Connect to publisher: By Impersonating the process account
2. Create subscription on SQL Server Express
Distribution Agent Security:Run under SQL Server Agent Service account
Connect To Distributor:By Impersonating the process account
Connect To the Subscriber:By Impersonating the process account
Sync Schedule
Agent Location: Distributor Run Continuously
3. Add a new login for the SQL Server Express user in SQL Server 2005 SE. Give role as sysadmin
Please advise
Thanks
|
|
|
|
|
I have 4 After triggers and i want to give the order for them in which they fired..
i can do that with the help of sp_setordertrigger but with this i can only specify which one will be fired first and which one will be last but what abut the rest of triggers how can i order them..please help me..in this i m new for trigger...
Sucess need one master Stroke
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
hi
you can set the First and Last trigger as you said.Rest of the triggers are fired depending on the trigger creation..So u create the trigger in the order in which you want to fire
regards
JOE
|
|
|
|
|
Thanks joe,
I got the answer.
Thanks and Regards
Aavesh Agarwal
|
|
|
|
|
Asp.Net/C#/SQL 2005
I created a user and login under security for the database.
In the web.cfg I have:
<connectionstrings>
<add name="ConnectionString1" connectionstring="Data Source=X2\SQLEXPRESS;Initial Catalog=TheDB;Integrated Security=True"
providername="System.Data.SqlClient">
<add name="ReadOnlyConnectionString"
connectionstring="Data Source=localhost\SQLEXPRESS;Initial Catalog=TheDB;
Integrated Security=False;User ID=userReaderOnly;Password=123"
providername="System.Data.SqlClient">
in the codebehind file:
string connectionString = ConfigurationManager.ConnectionStrings["ReadOnlyConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
errors at the line:
conn.Open();
The Error:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
|
|
|
|
|
I was happily working on my solution with access, everything going sweet.
Until i started to use backgroundworkers doing some inserts to the database, while the main thread was accessing the database calling the tableadapters as well for it's own purposes...
Is there any way MSAcess can manage multiple calls to the database, or should i just forget about it and start working on something different.
I was planning to include in the deploy the mdb file. I tested it, and it worked fine. But with this?
Suggestions PLEASE 
Thanks for everyones time.
Daniel Sovino
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
