Microsoft SQL Server 2008 - how to view query history

MikeBinNC
MikeBinNC used Ask the Experts™
on
I am new to Microsoft SQL Server 2008, however, not new to programming...I am used to having a window that shows the "command history" so I can I can compare the results to the query I executed and see the changes I have made to the query over time.  Is there such a thing as a command history window in Microsoft SQL Server 2008??  I see that I can look at the query log, however, this just gives a high level listing of the queries that were executed - not the specific code....

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In the activity Monitor there is the recent expensive queries where I can troubleshoot the code that takes the longest time but there is no history if you change the code it becomes different query. Only if you saved the query as a stored procedure could you then store its performance

Saved in a job as a step the step time is monitored in a history of the job

Author

Commented:
thanks for the feedback..let me clarify  - I execute several lines of code and then check the results to see if I am getting what I want.  I want to see the history of the lines of code that were executed.  

As you state I do not see what I want in the Activity monitor......any other ideas?

Thanks much

Commented:
try this:

select q.TEXT QueryName, last_elapsed_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text( s.sql_handle ) q

Open in new window


or you can use SQL Profiler
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
if you want to get detailed information simply change q.TEXT QueryName, last_elapsed_time with '*'
you can see when the script is created, when it is last executed and a lot of other infos

select * from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text( s.sql_handle ) q

Open in new window

Author

Commented:
Thank you
However, I have tried the above and it provided some query data in the "text" column of the output (shows a truncated list of the commands used).  

However, still not what I would like which is a list of the commands (may or may not be a completed query) that I have executed.

Author

Commented:
this seems harder then expected....increased point value

Commented:
the following query will give you currently running queries

declare @s table(spid smallint,login_time datetime,last_batch datetime,[status] nchar(30),loginame nchar(128),[text] text)

declare @sql_handle binary(20),@spid smallint;
declare c1 cursor for select sql_handle,spid from master..sysprocesses where spid >50;
open c1;
fetch next from c1 into @sql_handle,@spid; 
while (@@FETCH_STATUS =0) 
begin 
		insert into @s
	select spid,login_time,last_batch,[status],loginame,a.text
	from ::fn_get_sql(@sql_handle) a, master..sysprocesses b
	where b.spid = @spid
	fetch next from c1 into @sql_handle,@spid
end 
close c1
deallocate c1;

select * from @s

Open in new window


source: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/77e084fa-1d97-4caa-bc35-beb5eacf4920

Author

Commented:
An improvement, but still does not give the commands that were executed in detail...

Commented:
can you please tell me more about what do you want to see as a result?

Commented:
you also can try this

Start SQL Server Profiler --> New Trace
Give Trace Name and go to Event Selection tab and choose what you want from the check boxes
see the picture below


profiler.png

Author

Commented:
what I want is the following.  If I execute the following code and then get a result....I would like to be able to see the code executed exactly as shown below...ideally with some level of history...so I can also see the code I would have used to populate the @myTable table in the previous step.  I want this history for debugging my code.

SELECT a.source, a.destination, a.product, ROUND(SUM(a.quantity/b.total*100),3) percentage
INTO #tmp
FROM @myTable a INNER JOIN #tmpTotal b ON
a.destination=b.destination AND a.product=b.product
GROUP BY a.source, a.destination, a.product

Commented:
I think you should try SSMS Tool Pack, you will have a small window on Management Studio where it will record all executed queries as history! Once you close the Management Studio history is removed...

This tool may help you I think

Author

Commented:
This tool does look like it will provide what I would like...however, I am not familiar with it and it is a little obscure in that it is a free tool from Slovenia.  Does anyone have experience with it?  Does this tool replace the management studio interface
 
No it does not replace the management studio interface, it is an "add in" as for slovenia trust your antivirus/malware
 give bartlet points for hevens sake and close this silly thing SQL management studio can be used as a development platforn but it isn't tring to be Visual Studio for you programmers
: )
Commented:
If the tool provides you what you want, and you say it does then I guess we are done!
I have offered you 3 solutions:
SQL Code,
Tracking your queries history with SQL Profiler,
and 3rd party tool 'SSMS Took Pack' (free).

I really have no more!

Author

Commented:
thanks so much for the help...the final solution solved the problem exactly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial