How to get the sql statement executed from external application?

Hello,

In my database (SQL Server 2005), some data were inserted from a external application.

In order to validate the data , I want to get the SQL statment executed by the application.

Is this possible?

Thanks

Robert

Hi Robert,

The Data Manipulation sql statements are not audited or logged by default.

If you want to capture SQL Statements from the application, you can use SQL Profiler.

If you need this for auditing, You could you use server side traces (its uses the same api as SQL Profiler but runs in the background).

Jag

|||

Hi Jag,

Thanks for your replay.

SQL Profiler is a good tool, I finished my work with it.

Robert

how to Get the result of executed query in file

All ,

Is it possible that i can get the result of executed select statement in a .txt file.

some thing like this

select * from mytablw to <some file name.txt>

Regards,

Ashish

You need to use BCP.

You either invoke it from a cmd window or use xp_cmdshell in SS.

Code Snippet

From cmd:

bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd

From SQL Server:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'

Look up BCP in BOL for more options and parameters.

How to get the last Query executed?

Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... ThanksOriginally posted by parmaia
Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks

Fire up SQL Profiler and run a trace with the filters configured to limit it to the table in question (object).|||if object_id('dbo.test') is not null
drop table dbo.test
go
create table dbo.test (f1 int null)
go
create trigger dbo.trgIUD_test on dbo.test for insert, update, delete as
dbcc inputbuffer (@.@.spid)
go
insert dbo.test values (1);
go
drop table dbo.test
go|||Originally posted by Steve Duncan
Fire up SQL Profiler and run a trace with the filters configured to limit it to the table in question (object).

Hey, i didn't know that exists, thank you for your help!, has very very use full!

How to get statistics on DML statements per table

Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.

Rgds

Bob

There is no easy way to obtain this information. You could also take a look at 3rd party tools that can get this sort of information from the transaction logs.

How to get rowcount

Hi,

How to get the row count for a particular query being executed within a session and scope. is there any option to get the value being displayed in the messages tab when a query is executed (no of rows affected). is there anything equivalent to scope_identity for getting the identity value inserted in a session and scope.

Vivek S

to obtain number of rows affected...

@.@.RowCount (return an int)

RowCount_Big() (return a big int)

to obtain last genereted identiy value...

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

The scope of the @.@.IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

|||did this help?

How to get recordset from only last SELECT stmt executed??

How to get recordset from only last SELECT stmt executed?

this is part of my code.. in T-Sql

Create proc some mySp
...
AS
...
set nocount on
if (@.SelUserID is null)
select 0
else if (@.SelUserID = @.userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @.incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @.logText
if (@.@.rowcount != 0)
set @.isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do?ANytime you select values without an assignment operator, they will be returned as part of the rowset. It sounds like you want to check for the existence of one rowset before selecting the second. So how about this:


if exists (select 1
from dms_prsn_trx_log
where @.incNo = ult_incid_no
and prsn_trx_no = 10
and trx_log_txt = @.logText)
begin
select b.
end
else
select a.
end|||Thank you it helps..
So, you means there is no such switch thing on off?
I thought it has...-.-