Is it possible to view Command Text passed to the database?

[Wutime]

How do I view the "parsed" command text for a stored procedure before it gets sent to the database?

 

I have a page updating a database using a stored procedure and passed arguments...

 

*****************************************

Dim myConnection As New SqlConnection(ConnectionString)

Dim myCommand As SqlCommand = new SqlCommand("sp_update_account", myConnection)

myCommand.Connection = myConnection

 

' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

 

' setup all stored procedure parameters from text field values

......removed for brevity

 

myConnection.Open()

myCommand.ExecuteNonQuery()

*****************************************

 

I want to view the parsed myCommand.CommandText before it reaches the database.

 

When I view the CommandText I only get "sp_update_account", I want to see teh exact command being passed to the SQL Server Database.

 

My procedure isn't updating the database and I'm not recieving an error, so I need to view the exact SQL text being passed.

 

Is this possible?

[Madz]

You are Executing a Store procedure and asking to view command text . :mad:

 

STORE PROCEDURES are Database Objects that are located with in Database

 

You are just asking your command object to call that Database Object not T-SQL .

[Wutime]

Hehe, I'm definitely not using the right terminalogy. Part of the problem of being a newbie is lacking the basic knowledge to ask questions properly; I'll try again.

 

My paramaters are being passed to my stored procedure (@Value, @Value), and the command text has been told the stored procedure name (sp_blah_blah).

 

I wanted to see the completed t-sql string that is being sent to the SQL Server.

 

For instance...

 

t-sql command = " sp_blah_blah '124312', 'asldgj' "

 

There must be a way to capture the command being sent to the database....

 

My stored procedure is executing without any reported errors. However, when I check the database, no updates or inserts have been made; therefor I'm curious to see what values are being sent so that I can try to debug from the database end of things.

 

Cheers for your help, it's much appreciated.

[Wutime]

Essentially, I would like to see what "string" has been passed when this ExecuteNonQuery() takes place.

 

myCommand.ExecuteNonQuery()

[archer_coal]

re:

 

I test the string by simply sending it to a control before it's sent to the database. Like a label or a textbox

Then you can see what values are being sent as well as any other charactor issues.

[Wutime]

I would love to be able to do that, the problem is that I don't know where the string is...

 

I'm simply supplying the name of the Procedure with this line..

 

Dim myCommand As SqlCommand = new SqlCommand("sp_player_vitals", myConnection)

 

and then naming the parameters like this....

 

Dim parameterPlayerID As SqlParameter = New SqlParameter("@player_id", SqlDbType.Int, 4)

 

... I'm assuming this all gets put together by ASP.NET and then sent to the database....how do I get the completed, put together SQL statement?

 

I'm assuming it's a property of myCommand...

 

myCommand.ExecuteNonQuery()

[Madz]

Dear you are just calling a store procedure, you know you are just asking ADO.NET that it should take this parameter and execute the specified SQL Server Store procedure. you are not executing any TSQL statement . its not possible to view which query text has been sent to SQL Server.

Can you check the Syntex of your Store procedure. just write it and try to convert it in a simple TQL Statements and then try executing it from your application.

[Wutime]

I could do that, but that's not what I want. With 15 parameters it would be a waste of time if there is a property/method in the Command that would do this for me.

 

I have a feeling this will be an invaluable peice of knowledge for me as I often use stored procedures.

[Nerseus]

The only way I know of to test this, Wutime, is to use the Sql Profiler that comes with the SQL Server tools (like Query Analyzer). Start up the profiler then:

1. File->New->Trace

2. Make your connection to the server

3. In Trace Properties go to the Events tab and add the event "SP: Completed"

 

When this starts, you'll see a bunch of messages flow by. You're looking for the lines "SP: Completed". It shows the SQL Query, be it dynamic SQL or a stored proc call, including all params.

 

-Nerseus

[Nerseus]

Whoops - better use SP:Starting instead of SP:Completed since your proc is having errors and might not complete :p

 

If you'd like to see EVERY line that the proc is executing (say your proc does 3 SELECTs, an INSERT, and a DELETE), use SP:StmtStarting.

 

-Nerseus

[Wutime]

Thanks for the detailed message - it was VERY helpful

 

I'm going to give it a whirl