jedimastermopar


Just wondering in sqlserver 200 I was able to view all the active running queries against a given database How can I do the same in 2005



Re: Viewing TSQL queries?

lkh


In SQL Server Management Studio (SSMS)/ Object Explorer / Management

you will find the Activity Montior. There is a column that identifies the database and a column the identifies the command. If you click a command a window will open with the details.







Re: Viewing TSQL queries?

Louis Davidson

In 2005, it is actually pretty easy. I wrote this blog (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!900.entry) on the subject, and here is the query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
der.command as commandType,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,
der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @@spid --eliminate the current connection

This gives you the entire query, and will show you the statement that is being executed in some cases.







Re: Viewing TSQL queries?

jedimastermopar

Basically I am editing a field in a table through the Management studio. ad it doesn't show up when I look at the activity log, other queries show up but not this one.




Re: Viewing TSQL queries?

jedimastermopar

I tried that and it doens't seem to show the transaction. I am running an edit on a field in a table directly through the Management Studio. Other transaction show in the log but not any that I run against the databases through the Studio.




Re: Viewing TSQL queries?

jedimastermopar

This forum is buggered sometimes it show replies and other times it doesn't If I double posted I appologize. but I tried that and it doesn't seem to show the edits I am making directly to the tables via the Management studio.

Basically I have a table that doesn't have a unique field, and want to change a value in a single column, how do I reference it in an update query






Re: Viewing TSQL queries?

jedimastermopar

I tried this but it doesn't show the update query I am making. I am editing a table directly through the Maagement studio display table function. I want to know what the tsql query is when I do this so I can script it. Nothing seems to show the transactions that are made against the databases when they are run through the management studio.




Re: Viewing TSQL queries?

jedimastermopar

It wouldn't show the updates I was making to the tables via the Management studio




Re: Viewing TSQL queries?

jedimastermopar

I tried this but it didn't show the update command. I was editting a tablles contents directly via the Management studio table viewer. The cell was modified but it wouldn't show me the trasact query that was run. Its a massive table with hundreds of columns so I don't want to type out the query manually.






Re: Viewing TSQL queries?

Chris Howarth

If you're trying to capture a partcular query then it would be a simple task to fire up SQL Profiler, start a new trace, and then perform the action that you want to trace. The command(s) will be displayed in the 'TextData' column.

Have a look at this link for info on getting started with Profiler:

http://www.developer.com/db/article.php/3482216

Chris