Help finding group totals for year end

My SQLServer 2k database has the following tables:
Transaction
Customer
Employee
Every Transaction is assigned a customerId
Every Customer is assigned an employee id
All Transactions have a total
What I am trying to do is calculate the top 5 customers by total for each
employee
I have the following which gives me the top 5 customers, but how do I get
the top 5 for each employee?
WB
Syntax:
SELECT t.CustomerId, sum(t.total)
FROM tblTransaction t
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.CustomerId
ORDER BY sum(t.total) DESCSELECT t.employeeid, sum(t.total)
FROM tblTransaction t INNER JOIN employee e
on t.customerid = e.customerid
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.employeeid
ORDER BY sum(t.total) DESC
"WB" wrote:

> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>|||Is there any uniqueness about the TransAction table like an IDENTITY
Column..? I've assumed there is :-
select * from tblTransAction t1 WHERE PK_TranID IN
(SELECT TOP 5 t2.PK_TranID
FROM tblTransAction as t2
WHERE t2.CustomerID = t1.CustomerID
GROUP BY t2.CustomerID, t2.TransactionID, t2.Total
ORDER BY SUM(t2.Total) DESC)
HTH. Ryan
"WB" <none> wrote in message news:eiqpObVFGHA.376@.TK2MSFTNGP12.phx.gbl...
> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>

Help fileof Transaction SQL 2005

Hi,

May I get the help file of Transaction SQL 2005?

Please send it to jemmy@.sentonius.com

Thank you.

Kind Regards,

Jemmy Sentonius

You can downlaod the same from

http://www.microsoft.com/downloads/details.aspx?familyid=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en

HELP - need to kill big transaction that's taking too long to rollback

Hi all,
I have a big transaction running on a table that I had to kill and
rollback. Unfortunately, the transaction has been rolling back for 1.5
hours and is supposedly only 6% done rolling back. I need to stop this
transaction, and I'm looking for the fastest way to do it with doing
the least amount of damage.
Is there any nuke button that I can press to kill this process with
extreme prejudice? - at this point I don't care about the integrity of
the table I was running the transaction on - I just want it to die.
Is there any way to force the table to drop, thus killing the process?
Is there any way to make a new log file for this database, and kill the
log file that has the rolling back?
If I stop SQL Server, will it try to wait until the transaction is
done/rolledback before it will actually stop?
Please HELP! Thanks in advance
-Rob
Stop the server service?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"robh1999atgmail" <RobH1999@.gmail.com> wrote in message
news:1159460802.483348.29370@.d34g2000cwd.googlegro ups.com...
> Hi all,
> I have a big transaction running on a table that I had to kill and
> rollback. Unfortunately, the transaction has been rolling back for 1.5
> hours and is supposedly only 6% done rolling back. I need to stop this
> transaction, and I'm looking for the fastest way to do it with doing
> the least amount of damage.
> Is there any nuke button that I can press to kill this process with
> extreme prejudice? - at this point I don't care about the integrity of
> the table I was running the transaction on - I just want it to die.
> Is there any way to force the table to drop, thus killing the process?
>
> Is there any way to make a new log file for this database, and kill the
> log file that has the rolling back?
> If I stop SQL Server, will it try to wait until the transaction is
> done/rolledback before it will actually stop?
> Please HELP! Thanks in advance
> -Rob
>

HELP - need to kill big transaction that's taking too long to rollback

Hi all,
I have a big transaction running on a table that I had to kill and
rollback. Unfortunately, the transaction has been rolling back for 1.5
hours and is supposedly only 6% done rolling back. I need to stop this
transaction, and I'm looking for the fastest way to do it with doing
the least amount of damage.
Is there any nuke button that I can press to kill this process with
extreme prejudice? - at this point I don't care about the integrity of
the table I was running the transaction on - I just want it to die.
Is there any way to force the table to drop, thus killing the process?
Is there any way to make a new log file for this database, and kill the
log file that has the rolling back?
If I stop SQL Server, will it try to wait until the transaction is
done/rolledback before it will actually stop?
Please HELP! Thanks in advance
-RobStop the server service?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"robh1999atgmail" <RobH1999@.gmail.com> wrote in message
news:1159460802.483348.29370@.d34g2000cwd.googlegroups.com...
> Hi all,
> I have a big transaction running on a table that I had to kill and
> rollback. Unfortunately, the transaction has been rolling back for 1.5
> hours and is supposedly only 6% done rolling back. I need to stop this
> transaction, and I'm looking for the fastest way to do it with doing
> the least amount of damage.
> Is there any nuke button that I can press to kill this process with
> extreme prejudice? - at this point I don't care about the integrity of
> the table I was running the transaction on - I just want it to die.
> Is there any way to force the table to drop, thus killing the process?
>
> Is there any way to make a new log file for this database, and kill the
> log file that has the rolling back?
> If I stop SQL Server, will it try to wait until the transaction is
> done/rolledback before it will actually stop?
> Please HELP! Thanks in advance
> -Rob
>

HELP - need to kill big transaction that's taking too long to rollback

Hi all,
I have a big transaction running on a table that I had to kill and
rollback. Unfortunately, the transaction has been rolling back for 1.5
hours and is supposedly only 6% done rolling back. I need to stop this
transaction, and I'm looking for the fastest way to do it with doing
the least amount of damage.
Is there any nuke button that I can press to kill this process with
extreme prejudice? - at this point I don't care about the integrity of
the table I was running the transaction on - I just want it to die.
Is there any way to force the table to drop, thus killing the process?
Is there any way to make a new log file for this database, and kill the
log file that has the rolling back?
If I stop SQL Server, will it try to wait until the transaction is
done/rolledback before it will actually stop?
Please HELP! Thanks in advance
-RobStop the server service?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"robh1999atgmail" <RobH1999@.gmail.com> wrote in message
news:1159460802.483348.29370@.d34g2000cwd.googlegroups.com...
> Hi all,
> I have a big transaction running on a table that I had to kill and
> rollback. Unfortunately, the transaction has been rolling back for 1.5
> hours and is supposedly only 6% done rolling back. I need to stop this
> transaction, and I'm looking for the fastest way to do it with doing
> the least amount of damage.
> Is there any nuke button that I can press to kill this process with
> extreme prejudice? - at this point I don't care about the integrity of
> the table I was running the transaction on - I just want it to die.
> Is there any way to force the table to drop, thus killing the process?
>
> Is there any way to make a new log file for this database, and kill the
> log file that has the rolling back?
> If I stop SQL Server, will it try to wait until the transaction is
> done/rolledback before it will actually stop?
> Please HELP! Thanks in advance
> -Rob
>

HELP-- execution dts in transaction -- URGENT

please I need to know what is required to properly execute a dts with steps joined to transactions in it.

i know MSDTC must be running on server but it must be running in local machine too?

user running dts must be sysadmin? is it required ?

any other requirements needed?

please write down a full list of required items or a BOL reference to look forMaybe if you tell us what you're trying to do?|||what i want is to know what are the requirements to execute a dts with steps joined to transactions in it. I talk about requirements related to services, user privileges and so on

I want to know this info that applied for a general problem (that is how to execute a transactional dts).

Anyway i will tell you what is my specific problem related to it. I have a DTS who loops over files in a directory processing each file (that is inserting rows in several tables of a sql server database) in a way that all files must be processed or if one is not processed due to an error (logged to a log table) then all previous files must be deprocessed (changes made to database must be 'rollback'ed). Thus transacctions are needed into the DTS for some steps (those who change database) while others mustn't (those who perform tests and just write to log table)

This is so easy to do in a DTS but i have experienced several problems due to transacctions. first, with MSDTC service running in server machine (where SQLServer is running) I cannot execute successfully the dts unless MSDTC also running in my local machine. Also is not possible for my user to execute DTS from dts editor unless my user being sysadmin in SQLServer server machine. So i want to know what is exactly necessary to execute such kind of DTS (transactional DTS) in terms of services, security, user profiles and roles, etc

Help

I have a database whose transaction log has swelled to 70 Gig
Overnight. Disk space free is only 30 gig therefore it won't let me
back it up. Any advice gratefully received.Barry (b.tucker@.voisins.com) writes:
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.

You are backing up the transaction log to the same disk? Maybe you should
simply see your local vendor and add another disk, as that does not sound
like good practice to me.

In the meanwhile, there are two other options. The first is to backup the
log to another disk on the network. Once you have done that, use DBCC
SHRINKFILE to shrink the log to a reasonable size. That is, you should
specify a target size to SHRINKFILE.

The other alternative is set the database in simple recovery, run a
CHECKPOINT command, shrink the log as above, and then take a full
backup of the database. The last thing is very important, as by
truncating the log you break the log chain.

You should also investigate why the log exploded. If it was due to a
maintenance operation with DBCC DBREINDEX, you should consider running
that operation with BULK_LOGGED recovery mode.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi There,
May this solve your problem..
Step 1) deattach the Database.
Step 2) Phyiscally delete the Log file.
Step 3) Reattach the database.
Step 4) Take the backup.

With warm regards
Jatinder Singh|||[posted and mailed]

jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> May this solve your problem..
> Step 1) deattach the Database.
> Step 2) Phyiscally delete the Log file.
> Step 3) Reattach the database.
> Step 4) Take the backup.

DON'T DO THIS! AND NEVER GIVE THIS ADVICE! THIS IS VERY VERY DANGEROUS!

Never delete the log file! You would never delete the data file, would
you? So why delete the other half of the database?

The above way work, if you are lucky. You may also find that you cannot
access the database after this operation.

There are T-SQL commands to use to truncate and shrink the log. Use
these. Never manipulate the database files directly. And particularly
not if you don't understand what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Barry" <b.tucker@.voisins.com> wrote in message
news:1136543236.726269.87380@.f14g2000cwb.googlegro ups.com...
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.

To add to the advice: figure out why it swelled to 70 gig overnight.

That's a LOT.