Last Transact-SQL Statement Executed

Hello All,
Please how can I retrieve/save the last Transact-SQL
statements executed against a database? Can I use a
trigger for this?
Thanks.Triggers work on tables and will only fire for data
updates not selects.
If all your access is via SPs then you could log in the SP.
You could log in the client if you are in control of that.
Otherwise the easiest way is to use the profiler to log
statements to a table or text file.
>--Original Message--
>Hello All,
>Please how can I retrieve/save the last Transact-SQL
>statements executed against a database? Can I use a
>trigger for this?
>Thanks.
>
>.
>

Last Inserted row

I am trying to access the row which was just inserted into a table. Will the following statements be equivalent of each other?

STATEMENT 1:
SELECT JobNumber
from tblCustServiceHistoryHdr
where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/

STATEMENT 2:
SELECT JobNumber
from inserted
where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/Would you like to know the last row inserted by your session, or by all sessions ?

About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.

Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.|||Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.|||How about introducing a new column to your table which will hold the time stamp for new rows inserted?|||I thought the DateModified column acted like a timestamp...u know everytime there r updates to that particular row the DateModified column is altered using getdate().|||Why not put an Identity Seed field on your table ?

Then do the insert through a stored procedure and pass back the identity field value.

By the way, @.@.IDENTITY returns the last generated value for the newly inserted row.|||What do you want to know the inserted row for ?
Might help us with a solution|||Originally posted by gayamantra
Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.
Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.

Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp

Large number of INSERT statements - not all are executed

Hello!

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.

When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.

I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.

I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.

Any thoughts?Hi Dmitri,

Quote:

Originally Posted by

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.

How were the statements sent to SQL Server?
One batch with 2k statements or one statement per batch?
What about transactions (autocommit mode)?
What events were set to be captured by Profiler?

Quote:

Originally Posted by

I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.

Wrong suggestion. "GO" is not an SQL statement and can be used only in
Query Analyzer (Enterprise Manager, Management Studio). It signals the
end of a batch to MSSQL utilities, SQL Server doesn't understand it at all.

--
Best regards,
Marcin Guzowski
http://guzowski.info|||Dmitri,

SQL Server doesn't just ignore transactions. If the statements are showing
up in SQL Server Profiler, then SQL is executing or attempting to execute
them. There may be other reasons why you are not getting the expected
results. Try these steps:

1. Backup the database.
2. Run a trace. Remove the Existing Connection and Audit events before
starting the trace. They are not necessary for this exercise and create
additional noise. You may want to set up a filter that captures only the
application being tested.
3. Save the trace as a SQL Script.
4. Restore the database.
5. Open the SQL Script and run it.
6. See if there are any errors or warnings that are not being trapped by the
VB.NET application.

-- Bill

"Dmitri" <nienna.gaia@.gmail.comwrote in message
news:1171036327.517795.9440@.s48g2000cws.googlegrou ps.com...

Quote:

Originally Posted by

Hello!
>
I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.
>
I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.
>
I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.
>
Any thoughts?
>

|||Dmitri (nienna.gaia@.gmail.com) writes:

Quote:

Originally Posted by

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.
>
I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.
>
I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.

Did he send one batch with 2000 statements, or 2000 batches? Without
seeing the code, it's difficult to know what we are talking about.

The most effective way of inserting data this way is:

INSERT tbl (...)
EXEC('SELECT ''thisvalue'', 1, ''thatvalue''
SELECT ''thisothervalue'', 2, ''thatothervalue''
...')

This keeps it down to one INSERT statement, but many small SELECT
statements that are easy to compile. (The alternative SELECT UNION
is very expensive to compile for 2000 rows.)

--
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