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

W

Wutime

Member
Joined
May 13, 2003
Messages
6
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 isnt updating the database and Im not recieving an error, so I need to view the exact SQL text being passed.

Is this possible?
 
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 .
 
Hehe, Im definitely not using the right terminalogy. Part of the problem of being a newbie is lacking the basic knowledge to ask questions properly; Ill 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 Im 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, its much appreciated.
 
Essentially, I would like to see what "string" has been passed when this ExecuteNonQuery() takes place.

myCommand.ExecuteNonQuery()
 
re:

I test the string by simply sending it to a control before its 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.
 
I would love to be able to do that, the problem is that I dont know where the string is...

Im 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)

... Im 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?

Im assuming its a property of myCommand...

myCommand.ExecuteNonQuery()
 
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.
 
I could do that, but thats 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.
 
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, youll see a bunch of messages flow by. Youre looking for the lines "SP: Completed". It shows the SQL Query, be it dynamic SQL or a stored proc call, including all params.

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

If youd 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
 
Back
Top