Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
Jorge
Hi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>
|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:
> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> > Hello,
> >
> > Is it possible to know the last time that a stored procedure was executed
> > /
> > invoced?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools lik
e
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

last tsql command

hi, how can i obtain the info of the last tsql command executed?, in the table sysprocess i only have the number of spid
thanks so much...have you tried double-clicking the spid in Enterprise Manager->Management->Current Activity->Process Info?

...might give you what you're looking for...|||hi, yes, but sql save it in someplace? i need to extract it to a table|||Not that I'm aware of, you'd have to run profiler to capture the sql (even then I am not sure you can save it to a table...)|||(even then I am not sure you can save it to a table...)Yes you can though MS recommend you write to a trace file and insert this into a table when you want to analyse. More efficient to write to a file at trace time basically.

HTH|||@.Mvrg76: Why have you opened a new thread?
See http://www.dbforums.com/showthread.php?t=1613810sql

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 Query Statement fired into the MSSQL Engine

Hi,

How can you get the Query Statement of the last executed SQL command.

I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).

Thanks for your help and pointers.

My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.

Warm Regards,
Ranjit S Hans.

---------------------
Everywhere is a walking distance if you have the time - Steven Wrightdbcc inputbuffer(@.spid)|||You can also use fn_get_sql, but it is a little more awkward.|||Hi Matt/ Jezemine,

Thanks so much for the help .. You have been wonderful all along.

I am looking forward to try out using fn_get_sql sometime soon.

Thanks once again !!

Warm Regards,
Ranjit S Hans
----------------------
Everywhere is a walking distance if you have the time - Steven Wright|||fn_get_sql has a number of benefits over dbcc inputbuffer actually. dbcc cuts off the query returned at 255 chars as I recall. Usually thats enough, unless you have apps out there making ad-hoc sql calls doing a 10-way join. :)

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server an
d
> by which user. Is it possible to find. (I think there is some log for this
)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:
> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. Sundar
No, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

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

Large delete stmt - 2nd question

Publisher has delete stmt executed against transactionally replicated table.
270K rows deleted in 1 stmt.
1 Subscriber connected with T1
How best to replicate all the 270K simpleton delete calls to
sp_MSdel_tablename at subscriber through distr agent? What parameter
adjustments to make?
Changed -CommitBatchSize to 1000, doesn't seem to increase speed much...
Thanks,
Chris
Set HistoryVerboseLevel to 0, CommitBatchSize to 1000 and
CommitBatchThreshold 10000
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DED73E09-DA75-444C-8976-96FF475B9567@.microsoft.com...
> Publisher has delete stmt executed against transactionally replicated
table.
> 270K rows deleted in 1 stmt.
> 1 Subscriber connected with T1
> How best to replicate all the 270K simpleton delete calls to
> sp_MSdel_tablename at subscriber through distr agent? What parameter
> adjustments to make?
> Changed -CommitBatchSize to 1000, doesn't seem to increase speed much...
> Thanks,
> Chris