Tracing SQL statements and number of records affected

Question

Tracing SQL statements and number of records affected

Preetham Gowda

Right there with Babe

Points: 792
More actions
August 9, 2010 at 4:44 am

#387527

I need to find the number of rows affected for each query, when I fire set of queries
For e.g.:
Select top 10 * from Employee
Insert into Employee1 Select * From Employee
Update Employee1 Set Name = 'Jon' Where name = 'mic'
RESULT:
Select top 10 * FRom Employee
10 rows affected
Insert into Employee1 Select * From Employees
50 rows affected
Update Employee1 Set Name = 'Jon' Where name = 'mic'
2 rows affected
Need: I need to audit user Queries and don't have control on the queries. So i need to write a backend scriot to check (check like query and rows affected) for the multiple queries fired by set of user.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

SET NOCOUNT OFF .

When u normally execute a query, you sure will get the number of rows affected in the "Message" Pane of the Results Window, unless u specify SET NOCOUNT ON...

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 2

Thanks for reply. But how can aduit the qureies fired by end users against the rows affected?

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 3

You can use @@rowcount function to capture the no. of rows affected for the query executed.

Thanks,

Amit Kulkarni

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 4

If i use @@rowcount, then i will get for last query but not set of qureies fired 🙁

Lowell SSC Guru Points: 323507 More actions · Answer 5

if you need the total, you;ll need to declare a variable, and add them together:

declare @TotalRows int

SET @TotalRows = 0

Select top 10 * from Employee

SET @TotalRows = @TotalRows + @@rowcount

Insert into Employee1 Select * From Employee

SET @TotalRows = @TotalRows + @@rowcount

Update Employee1 Set Name = 'Jon' Where name = 'mic'

SET @TotalRows = @TotalRows + @@rowcount

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 6

Hi OP,

It sounds to me like you want to capture the rows affected by queries run by users of the database. For instance if the application (could be a custom app written in c++ for instance) has a query window that lets you run an ad-hoc query on the database, you want to capture the rows affected when Bob from accounting selects the number of outstanding payments this month, is that correct?

As opposed to you creating multiple statements in SSMS and wanting to know the row counts of your own queries..

Jim.

SQL SERVER Central Forum Etiquette[/url]

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 7

You are correct Jim. I need to capture the rows affected for each query fired along with it's counter part. At a time multiple queries can be fired together.

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 8

Would I also be correct to assume that you want this to run in the background to create a file that shows the queries with the user's name, time and rows affected?

Meaning that the profiler won't be a solution?

Jim.

EDIT:

I've found some info that you might find useful. You could create a trace that will create a file for you.

How to create a trace file:

http://support.microsoft.com/kb/283790/

This gives some info on what can be traced:

http://technet.microsoft.com/en-us/library/ms186265.aspx

Obviously you should expect the relevant decreases in performance.

Jim

SQL SERVER Central Forum Etiquette[/url]

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 9

Let me try using profiler. Do we have any other solution than profiler?

Preetham Gowda Right there with Babe Points: 792 More actions · Answer 10

Thanks to all. Since we have trace enabled for our databased. I used them to extract required info.